No it isn't a typo,
All the tables are empty and all the indexes are empty
-Original Message-
From: Merlin Moncure [mailto:mmonc...@gmail.com]
Sent: Tuesday, June 04, 2013 16:10
To: Ben Zeev, Lior
Cc: Atri Sharma; Stephen Frost; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Pr
On Tue, Jun 4, 2013 at 12:57 AM, Ben Zeev, Lior wrote:
> No matter how I try to redesign the schema the indexes consume large amount
> of memory,
> About 8KB per index.
8KB per index -- is that a typo? that doesn't seem like a lot to me.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-
Sharma [mailto:atri.j...@gmail.com]
Sent: Monday, May 27, 2013 17:24
To: Stephen Frost
Cc: Ben Zeev, Lior; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
>We may still be able to do better than what we're doing
> today, but I'm still suspicious that you
doesn't log the catcache statistcs
Lior
-Original Message-
From: Stephen Frost [mailto:sfr...@snowman.net]
Sent: Monday, May 27, 2013 16:44
To: Ben Zeev, Lior
Cc: Atri Sharma; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
* Ben Zeev, Lior (lior.ben-z...@h
On Mon, May 27, 2013 at 7:29 AM, Stephen Frost wrote:
> * Atri Sharma (atri.j...@gmail.com) wrote:
>> Yes, too many indexes wont hurt much.BTW,wont making too many indexes
>> on columns that probably dont have as many values as to deserve
>> them(so,essentially,indiscriminately making indexes) hur
On Mon, May 27, 2013 at 10:23 AM, Atri Sharma wrote:
> >We may still be able to do better than what we're doing
>> today, but I'm still suspicious that you're going to run into other
>> issues with having 500 indexes on a table anyway.
>
> +1. I am suspicious that the large number of indexes is
>We may still be able to do better than what we're doing
> today, but I'm still suspicious that you're going to run into other
> issues with having 500 indexes on a table anyway.
+1. I am suspicious that the large number of indexes is the problem
here,even if the problem is not with book keeping
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
> Each query is running in a separate transaction.
Interesting. You might also compile with CATCACHE_STATS (and not
CATCACHE_FORCE_RELEASE, or perhaps with and without) and then check out
your logs after the process ends (you might need to increase t
Great, Thanks !!!
I will try and let you update
-Original Message-
From: Stephen Frost [mailto:sfr...@snowman.net]
Sent: Monday, May 27, 2013 16:29
To: Ben Zeev, Lior
Cc: Atri Sharma; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
Lior,
* Ben Zeev, Lior
Lior,
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
> Yes, The memory utilization per PostgreSQL backend process is when running
> queries against this tables,
> For example: select * from test where num=2 and c2='abc'
> When It start it doesn't consume to much memory,
> But as it execute agains
Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
Lior,
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
> Yes, The memory utilization per PostgreSQL backend process is when
> running queries against this tables, For example: select * from test where
> num=2 and c2='
Lior,
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
> Yes, The memory utilization per PostgreSQL backend process is when running
> queries against this tables,
> For example: select * from test where num=2 and c2='abc'
> When It start it doesn't consume to much memory,
> But as it execute agains
ost [mailto:sfr...@snowman.net]
Sent: Monday, May 27, 2013 15:43
To: Ben Zeev, Lior
Cc: Atri Sharma; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
Lior,
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
> The case which I'm seeing is that I have an empty table with
On 05/27/2013 01:25 PM, Ben Zeev, Lior wrote:
> Thanks Atri!
>
> Do you know why PostgreSQL store the indexes in memory per process and not in
> the shared memory?
>From shared_buffers point of view tables and indexes are identical, both
use the
same shared memory in (usually) 8KB pages
> Is there
* Atri Sharma (atri.j...@gmail.com) wrote:
> It is just a hunch, but all of your attributes are character varying.
> Could TOAST be an issue here?
TOAST tables are only created when needed. In addition, I believe
Lior's concerned about memory utilization and not disk usage; memory
utilization sho
Lior,
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
> The case which I'm seeing is that I have an empty table without any rows,
> Create table test (
> Num Integer,
> C1 character varying(512),
> C2 character varying(512),
> C3 character varying(512));
>
> I create several partial indexe
Hi Atri,
But TOAST only occur if the tuple size exceed 2KB, doesn't it?
Lior
-Original Message-
From: Atri Sharma [mailto:atri.j...@gmail.com]
Sent: Monday, May 27, 2013 15:39
To: Ben Zeev, Lior
Cc: Stephen Frost; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process m
On Mon, May 27, 2013 at 6:02 PM, Ben Zeev, Lior wrote:
> Hi Stephen,
>
> The case which I'm seeing is that I have an empty table without any rows,
> Create table test (
> Num Integer,
> C1 character varying(512),
> C2 character varying(512),
> C3 character varying(512));
>
> I create sever
= 20GB of memory
What is the reason to consume so much memory for empty indexes?
Thanks,
Lior
-Original Message-
From: Stephen Frost [mailto:sfr...@snowman.net]
Sent: Monday, May 27, 2013 15:16
To: Atri Sharma
Cc: Ben Zeev, Lior; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Proc
> I'd expect the performance issue would be from planner time more than
> memory usage- but if there is a serious memory usage issue here, then
> it'd be valuable to have a test case showing what's happening. We may
> not be releasing the sys cache in some cases or otherwise have a bug in
> this a
* Atri Sharma (atri.j...@gmail.com) wrote:
> Yes, too many indexes wont hurt much.BTW,wont making too many indexes
> on columns that probably dont have as many values as to deserve
> them(so,essentially,indiscriminately making indexes) hurt the
> performance/memory usage?
I'd expect the performanc
* Atri Sharma (atri.j...@gmail.com) wrote:
> > There's a bit of other information shared, but disk buffers are
> > certainly the bulk of it.
>
> The other information being locks?
Depends, but yes. Per-row locks are actually in the disk cache portion
of shared buffers, but heavyweight locks have
On Mon, May 27, 2013 at 9:16 PM, Atri Sharma wrote:
>>> AFAIK, the shared disk buffers are the only part shared between the
>>> processes.
>>
>> There's a bit of other information shared, but disk buffers are
>> certainly the bulk of it.
>
> The other information being locks?
CreateSharedMemoryA
> This is not generally a reason to avoid indexes. Indexes require more
> disk space and must be kept up to date, making them expensive to
> maintain due to increased disk i/o. Building an index uses as much
> memory as it's allowed to- it uses maintenance_work_mem to limit itself.
Yes, too many
> A better place to look would be the documentation for the release of PG
> which you are on, or the latest release otherwise, which is:
>
> http://www.postgresql.org/docs/9.2/static/storage.html
Oops,yes,sorry about that.
Thanks a ton for pointing that out.
Regards,
Atri
--
Regards,
Atri
l'
* Atri Sharma (atri.j...@gmail.com) wrote:
> If your index is big/you have too many indexes in your database, it
> should affect *all* backends accessing that specific database.
More indexes will require more disk space, certainly, but tablespaces
can be used to seperate databases, or tables, or i
Lior,
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
> Does each PostgreSQL process allocating in its own memory (Not shared memory)
> a cache of all the database catalog which it access during the SQL execution?
PG will look up and cache the catalog information regarding all of the
relations in
Lior,
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
> Do you know why PostgreSQL store the indexes in memory per process and not in
> the shared memory?
The disk blocks from an index are not stored per-process, they are kept
in shared memory. When building an index, PG can only use one process
>> AFAIK, the shared disk buffers are the only part shared between the
>> processes.
>
> There's a bit of other information shared, but disk buffers are
> certainly the bulk of it.
The other information being locks?
Regards,
Atri
--
Regards,
Atri
l'apprenant
--
Sent via pgsql-hackers maili
* Atri Sharma (atri.j...@gmail.com) wrote:
> On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior wrote:
> > Do you have idea what may be the reason that PostgreSQL process consume
> > more memory when there are more partial indexes on the DB table?
It might use a bit more, but it shouldn't be excess
* Atri Sharma (atri.j...@gmail.com) wrote:
> > Does each PostgreSQL process allocating in its own memory (Not shared
> > memory) a cache of all the database catalog which it access during the SQL
> > execution?
This information is pulled into a backend-local cache, but it should
only be cached whi
> An index is built in backend process's local memory, but, when
> accessing, index pages are stored in shared memory. That is, for
> example, when an index scan is performed, index pages are brought into
> shared memory and accessed from there.
>
>
Yes, brought into the shared disk buffers and re
On Mon, May 27, 2013 at 7:25 PM, Ben Zeev, Lior wrote:
> Thanks Atri!
>
> Do you know why PostgreSQL store the indexes in memory per process and not in
> the shared memory?
> Is there a way to prevent it store the indexes data per process, and force it
> storing it in the shared memory?
>
An in
On Mon, May 27, 2013 at 3:55 PM, Ben Zeev, Lior wrote:
> Thanks Atri!
>
> Do you know why PostgreSQL store the indexes in memory per process and not in
> the shared memory?
> Is there a way to prevent it store the indexes data per process, and force it
> storing it in the shared memory?
Ok, so
...@gmail.com]
Sent: Monday, May 27, 2013 13:19
To: Ben Zeev, Lior; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior wrote:
> Hi Atri,
>
> Thanks for your answer!
> Do you have idea what may be the reason that Postgr
On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior wrote:
> Hi Atri,
>
> Thanks for your answer!
> Do you have idea what may be the reason that PostgreSQL process consume more
> memory when there are more partial indexes on the DB table?
Well, I am not too sure, but indexes always take up more spa
> Does each PostgreSQL process allocating in its own memory (Not shared
> memory) a cache of all the database catalog which it access during the SQL
> execution?
>
> I mean does each process holds all the catalog indexes data which it
> accessed, all the catalog index statistics etc’ accessed
AFAI
Hi,
I have a question regarding the memory consumption per process in PostgreSQL 9.2
Does each PostgreSQL process allocating in its own memory (Not shared memory) a
cache of all the database catalog which it access during the SQL execution?
I mean does each process holds all the catalog indexes
38 matches
Mail list logo
