Обсуждение: PostgreSQL Process memory architecture
<div class="WordSection1"><p class="MsoNormal">Hi,<p class="MsoNormal"> <p class="MsoNormal">I have a question regardingthe memory consumption per process in PostgreSQL 9.2<p class="MsoNormal"> <p class="MsoNormal">Does each PostgreSQLprocess allocating in its own memory (Not shared memory) a cache of all the database catalog which it access duringthe SQL execution?<p class="MsoNormal">I mean does each process holds all the catalog indexes data which it accessed,all the catalog index statistics etc’ accessed<p class="MsoNormal"> <p class="MsoNormal">If yes is there a way toavoid this behavior?<p class="MsoNormal"> <p class="MsoNormal">(I asked Josh Berkus from PGExperts and he said that eachprocess holds memory for sorts, hashes, temp tables, vaccum, etc’)<p class="MsoNormal"> <p class="MsoNormal">Thanks,<pclass="MsoNormal">Lior<p class="MsoNormal"> </div>
> 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 AFAIK, the shared disk buffers are the only part shared between the processes. Regards, Atri -- Regards, Atri l'apprenant
On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior <lior.ben-zeev@hp.com> 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 indexeson the DB table? Well, I am not too sure, but indexes always take up more space, so if your backend has a lot of indexes, it will cause the process to consume more memory. Indexes should be used with care, as too many indexes can cause a memory overhead,which can cause performance degradations. Regards, Atri -- Regards, Atri l'apprenant
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? Lior -----Original Message----- From: Atri Sharma [mailto:atri.jiit@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 <lior.ben-zeev@hp.com> 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 indexeson the DB table? Well, I am not too sure, but indexes always take up more space, so if your backend has a lot of indexes, it will cause theprocess to consume more memory. Indexes should be used with care, as too many indexes can cause a memory overhead,which can cause performance degradations. Regards, Atri -- Regards, Atri l'apprenant
On Mon, May 27, 2013 at 3:55 PM, Ben Zeev, Lior <lior.ben-zeev@hp.com> 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, sorry for a bit of a confusion here. I am assuming that the multiple processes are accessing the same database. What happens essentially is that each index is stored as a separate file in the data directory of the database in the directory of the cluster in which your database belongs. So,indexes are essentially stored the same way as tables, in form of files which are accessed in 8K blocks. If your index is big/you have too many indexes in your database, it should affect *all* backends accessing that specific database. So,my point is that,there is no question of indexes being stored in shared memory or individually. You can treat indexes the same as your tables,from the point of view of physical storage. For more details,you can see http://www.postgresql.org/docs/8.1/static/storage.html Regards, Atri -- Regards, Atri l'apprenant
On Mon, May 27, 2013 at 7:25 PM, Ben Zeev, Lior <lior.ben-zeev@hp.com> 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 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. -- Amit Langote
> 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 read,just like tables are read. Regards, Atri -- Regards, Atri l'apprenant
Lior,
* Ben Zeev, Lior (lior.ben-zeev@hp.com) wrote:
> Does each PostgreSQL process allocating in its own memory (Not shared memory) a cache of all the database catalog
whichit access during the SQL execution?
PG will look up and cache the catalog information regarding all of the
relations involved, yes. In *accessing* those relations, PG will pull
needed blocks into shared buffers. PG will use backend-local memory to
process through the data (generally on a per-tuple basis).
> I mean does each process holds all the catalog indexes data which it accessed, all the catalog index statistics etc'
accessed
Catalog information (eg: information in pg_class) is kept, but the
*data* will only be pulled through shared buffers and then processed.
Anything in shared buffers (eg: the data in the tables or indexes) will
be cleaned up as new blocks are needed which push out old ones.
> If yes is there a way to avoid this behavior?
Catalog information is only cached- if the information isn't being used
then it should get purged out in favor of new data which is needed. Can
you explain a bit more exactly what the issue is..?
> (I asked Josh Berkus from PGExperts and he said that each process holds memory for sorts, hashes, temp tables,
vaccum,etc')
Correct, most backend local usage of memory is for running queries and
doing what is required in those queries. Regarding temp tables, you can
control how much memory is used for those with the temp_buffers
parameter.
Thanks,
Stephen
* Atri Sharma (atri.jiit@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 while it's needed and then purged out to allow for new
data coming in. It would be great if we could understand what the issue
is that you're seeing.
> > I mean does each process holds all the catalog indexes data which it
> > accessed, all the catalog index statistics etc’ accessed
Each backend shouldn't try to hold all the data, if there is pressure
for that memory.
> 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.
Thanks,
Stephen
* Atri Sharma (atri.jiit@gmail.com) wrote:
> On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior <lior.ben-zeev@hp.com> wrote:
> > Do you have idea what may be the reason that PostgreSQL process consume more memory when there are more partial
indexeson the DB table?
It might use a bit more, but it shouldn't be excessive.. What, exactly,
are you seeing and would it be possible for you to provide a repeatable
test case with a small-ish set of data?
> Well, I am not too sure, but indexes always take up more space, so if
> your backend has a lot of indexes, it will cause the process to
> consume more memory.
Indexes require additional disk space, certainly. Having a lot of
indexes, by itself, shouldn't seriously increase memory usage.
> Indexes should be used with care, as too many indexes can cause a
> memory overhead,which can cause performance degradations.
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.
Thanks,
Stephen
>> 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
Lior,
* Ben Zeev, Lior (lior.ben-zeev@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
and so there isn't any point having that be in shared memory.
Thanks,
Stephen
> 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 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? Regards, Atri -- Regards, Atri l'apprenant
* Atri Sharma (atri.jiit@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 indexes on to different partitions on the host server. > So,my point is that,there is no question of indexes being stored in > shared memory or individually. You can treat indexes the same as your > tables,from the point of view of physical storage. Correct. > For more details,you can see > > http://www.postgresql.org/docs/8.1/static/storage.html 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 Thanks, Stephen
> 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'apprenant
On Mon, May 27, 2013 at 9:16 PM, Atri Sharma <atri.jiit@gmail.com> 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? CreateSharedMemoryAndSemaphores() (src/backend/storage/ipc/ipci.c) seems to be the place where we can see what all things reside in shared memory, since at the beginning of the function, you can see size being computed for shared memory to hold all the things that need to be in shared memory. -- Amit Langote
* Atri Sharma (atri.jiit@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 their own area.
Thanks,
Stephen
* Atri Sharma (atri.jiit@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 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 area.
Thanks,
Stephen
> 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 area. Right, this does sound interesting. Thanks a ton! Regards, Atri -- Regards, Atri l'apprenant
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 several partial indexes on this table:
Create index(index_1_c1) on test(c1) where Num=1;
Create index(index_2_c1) on test(c1) where Num=2;
Create index(index_1_c2) on test(c1) where Num=1;
Create index(index_2_c2) on test(c1) where Num=2;
...
This doesn't consume much memory on the PostgreSQL backend process,
But if I create 500 indexes It consume several MB of memory.
If I have 10 tables with 500 indexes each PostgreSql backend process consume 20MB,
If I have 100 tables with 500 indexes each PostgreSQL backend process consume 200MB
All tables are empty without data.
If have Connection pool of 100 connections then All this processes consume 100*200MB = 20GB of memory
What is the reason to consume so much memory for empty indexes?
Thanks,
Lior
-----Original Message-----
From: Stephen Frost [mailto:sfrost@snowman.net]
Sent: Monday, May 27, 2013 15:16
To: Atri Sharma
Cc: Ben Zeev, Lior; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
* Atri Sharma (atri.jiit@gmail.com) wrote:
> On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior <lior.ben-zeev@hp.com> wrote:
> > Do you have idea what may be the reason that PostgreSQL process consume more memory when there are more partial
indexeson the DB table?
It might use a bit more, but it shouldn't be excessive.. What, exactly, are you seeing and would it be possible for
youto provide a repeatable test case with a small-ish set of data?
> Well, I am not too sure, but indexes always take up more space, so if
> your backend has a lot of indexes, it will cause the process to
> consume more memory.
Indexes require additional disk space, certainly. Having a lot of indexes, by itself, shouldn't seriously increase
memoryusage.
> Indexes should be used with care, as too many indexes can cause a
> memory overhead,which can cause performance degradations.
This is not generally a reason to avoid indexes. Indexes require more disk space and must be kept up to date, making
themexpensive to maintain due to increased disk i/o. Building an index uses as much memory as it's allowed to- it uses
maintenance_work_memto limit itself.
Thanks,
Stephen
On Mon, May 27, 2013 at 6:02 PM, Ben Zeev, Lior <lior.ben-zeev@hp.com> 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 several partial indexes on this table: > > Create index(index_1_c1) on test(c1) where Num=1; > Create index(index_2_c1) on test(c1) where Num=2; > Create index(index_1_c2) on test(c1) where Num=1; > Create index(index_2_c2) on test(c1) where Num=2; It is just a hunch, but all of your attributes are character varying. Could TOAST be an issue here? Regards, Atri -- Regards, Atri l'apprenant
Hi Atri, But TOAST only occur if the tuple size exceed 2KB, doesn't it? Lior -----Original Message----- From: Atri Sharma [mailto:atri.jiit@gmail.com] Sent: Monday, May 27, 2013 15:39 To: Ben Zeev, Lior Cc: Stephen Frost; Pg Hackers Subject: Re: [HACKERS] PostgreSQL Process memory architecture On Mon, May 27, 2013 at 6:02 PM, Ben Zeev, Lior <lior.ben-zeev@hp.com> 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 several partial indexes on this table: > > Create index(index_1_c1) on test(c1) where Num=1; Create > index(index_2_c1) on test(c1) where Num=2; Create index(index_1_c2) on > test(c1) where Num=1; Create index(index_2_c2) on test(c1) where > Num=2; It is just a hunch, but all of your attributes are character varying. Could TOAST be an issue here? Regards, Atri -- Regards, Atri l'apprenant
Lior,
* Ben Zeev, Lior (lior.ben-zeev@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 indexes on this table:
>
> Create index(index_1_c1) on test(c1) where Num=1;
> Create index(index_2_c1) on test(c1) where Num=2;
> Create index(index_1_c2) on test(c1) where Num=1;
> Create index(index_2_c2) on test(c1) where Num=2;
> ...
>
> This doesn't consume much memory on the PostgreSQL backend process,
> But if I create 500 indexes It consume several MB of memory.
When are you seeing this memory utilization..? When running a query
against that table? At backend start?
> If I have 10 tables with 500 indexes each PostgreSql backend process consume 20MB,
> If I have 100 tables with 500 indexes each PostgreSQL backend process consume 200MB
>
> All tables are empty without data.
Are you accessing all of those tables inside of one query? Or one
transaction, or..?
> What is the reason to consume so much memory for empty indexes?
I'm curious what you would expect to be happening here. We need to pull
in information about the index in order to consider it during planning.
Special-caseing empty indexes might be possible, but what's the point of
having hundreds of empty indexes against a table in the first place?
Thanks,
Stephen
* Atri Sharma (atri.jiit@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 should not be impacted by TOAST at all unless large values
in the tables (which had to be moved to a TOAST table due to size) are
actually being queried against.
Thanks,
Stephen
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 a way to prevent it store the indexes data per process, and force it storing it in the shared memory? It already does. Per-query sorts and hashtables are stored in local memory, ordinary tables and indexes are in shared. -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ
Hi Stephen,
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 against more and more indexes the memory consumption grows
This tables should contain data, But I truncate the data of the tables because I wanted to make sure that the memory
consumptionis not relate to the data inside the table, but rather to the structure of the tables
Thanks,
Lior
-----Original Message-----
From: Stephen Frost [mailto:sfrost@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-zeev@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 indexes on this table:
>
> Create index(index_1_c1) on test(c1) where Num=1; Create
> index(index_2_c1) on test(c1) where Num=2; Create index(index_1_c2) on
> test(c1) where Num=1; Create index(index_2_c2) on test(c1) where
> Num=2; ...
>
> This doesn't consume much memory on the PostgreSQL backend process,
> But if I create 500 indexes It consume several MB of memory.
When are you seeing this memory utilization..? When running a query against that table? At backend start?
> If I have 10 tables with 500 indexes each PostgreSql backend process
> consume 20MB, If I have 100 tables with 500 indexes each PostgreSQL
> backend process consume 200MB
>
> All tables are empty without data.
Are you accessing all of those tables inside of one query? Or one transaction, or..?
> What is the reason to consume so much memory for empty indexes?
I'm curious what you would expect to be happening here. We need to pull in information about the index in order to
considerit during planning.
Special-caseing empty indexes might be possible, but what's the point of having hundreds of empty indexes against a
tablein the first place?
Thanks,
Stephen
Lior,
* Ben Zeev, Lior (lior.ben-zeev@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 against more and more indexes the memory consumption grows
Are these all running in one transaction, or is this usage growth across
multiple transactions? If this is all in the same transaction, what
happens when you do these queries in independent transactions?
> This tables should contain data, But I truncate the data of the tables because I wanted to make sure that the memory
consumptionis not relate to the data inside the table, but rather to the structure of the tables
If you actually have sufficient data to make having 500 indexes on a
table sensible, it strikes me that this memory utilization may not be
the biggest issue you run into. If you're looking for partitioning,
that's much better done, in PG at least, by using inheiritance and
constraint exclusion.
Thanks,
Stephen
Hi Stephen,
Each query is running in a separate transaction.
Why does portioning is done better rather than using partial index?
Thanks,
Lior
-----Original Message-----
From: Stephen Frost [mailto:sfrost@snowman.net]
Sent: Monday, May 27, 2013 16:15
To: Ben Zeev, Lior
Cc: Atri Sharma; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
Lior,
* Ben Zeev, Lior (lior.ben-zeev@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
> against more and more indexes the memory consumption grows
Are these all running in one transaction, or is this usage growth across multiple transactions? If this is all in the
sametransaction, what happens when you do these queries in independent transactions?
> This tables should contain data, But I truncate the data of the tables
> because I wanted to make sure that the memory consumption is not
> relate to the data inside the table, but rather to the structure of
> the tables
If you actually have sufficient data to make having 500 indexes on a table sensible, it strikes me that this memory
utilizationmay not be the biggest issue you run into. If you're looking for partitioning, that's much better done, in
PGat least, by using inheiritance and constraint exclusion.
Thanks,
Stephen
Lior, * Ben Zeev, Lior (lior.ben-zeev@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 against more and more indexes the memory consumption grows It might be interesting, if possible for you, to recompile PG with -DCATCACHE_FORCE_RELEASE, which should cause PG to immediately release cached information when it's no longer being used. You'll be trading memory usage for CPU cycles, of course, but it might be better for your situation. 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. Thanks, Stephen
Great, Thanks !!! I will try and let you update -----Original Message----- From: Stephen Frost [mailto:sfrost@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@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 > against more and more indexes the memory consumption grows It might be interesting, if possible for you, to recompile PG with -DCATCACHE_FORCE_RELEASE, which should cause PG to immediatelyrelease cached information when it's no longer being used. You'll be trading memory usage for CPU cycles, ofcourse, but it might be better for your situation. We may still be able to do better than what we're doing today, butI'm still suspicious that you're going to run into other issues with having 500 indexes on a table anyway. Thanks, Stephen
* Ben Zeev, Lior (lior.ben-zeev@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 the logging
level to DEBUG2 if you don't see anything initially).
> Why does portioning is done better rather than using partial index?
There's a couple of reasons, but for one thing, you can do parallel
loading of data into partitioned tables (particularly if you refer to
the individual partitions directly rather than going through the
top-level table with a trigger or similar). Trying to parallel load
into one table with 500 indexes would be pretty painful, I expect.
Thanks,
Stephen
>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 associated with those indexes. Regards, Atri -- Regards, Atri l'apprenant
On Mon, May 27, 2013 at 10:23 AM, Atri Sharma <atri.jiit@gmail.com> 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 the problem > here,even if the problem is not with book keeping associated with > those indexes. Right. The problem seems likely to be that each additional index requires a relcache entry, which uses some backend-local memory. But NOT having those backend-local relcache entries would likely be devastating for performance. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, May 27, 2013 at 7:29 AM, Stephen Frost <sfrost@snowman.net> wrote: > * Atri Sharma (atri.jiit@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 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 area. Note, backends do use private memory to cache various things (relcache, etc). Absolutely pathological workloads (tons of tables, tons of (especially) views, etc can exercise this into the gigabytes and there is no effective way to monitor and control it. Normally, it's not a very big deal though. So, to be a bit more specific, the index *data* (like all on disk structures) is buffered in shared memory. But certain plans/metadata stuff is in private memory. merlin
Hi Stephen,
I have some basic question - How do I add this flags CATCACHE_STATS and CATCACHE_FORCE_RELEASE when building
postgresql?
I added it to src/Makefile.global in this line:
CPPFLAGS = -D_GNU_SOURCE -DCATCACHE_STATS -DCATCACHE_FORCE_RELEASE
And changed log level to debug2, but it doesn't log the catcache statistcs
Lior
-----Original Message-----
From: Stephen Frost [mailto:sfrost@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-zeev@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)
andthen check out your logs after the process ends (you might need to increase the logging level to DEBUG2 if you don't
seeanything initially).
> Why does portioning is done better rather than using partial index?
There's a couple of reasons, but for one thing, you can do parallel loading of data into partitioned tables
(particularlyif you refer to the individual partitions directly rather than going through the top-level table with a
triggeror similar). Trying to parallel load into one table with 500 indexes would be pretty painful, I expect.
Thanks,
Stephen
No matter how I try to redesign the schema the indexes consume large amount of memory, About 8KB per index. Is there a way to invalidated this cache? Is there a way to limit the amount of memory and use some kind of LRU/LFU algorithm to clean old cache? -----Original Message----- From: Atri Sharma [mailto:atri.jiit@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'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 associated with those indexes. Regards, Atri -- Regards, Atri l'apprenant
On Tue, Jun 4, 2013 at 12:57 AM, Ben Zeev, Lior <lior.ben-zeev@hp.com> 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
No it isn't a typo, All the tables are empty and all the indexes are empty -----Original Message----- From: Merlin Moncure [mailto:mmoncure@gmail.com] Sent: Tuesday, June 04, 2013 16:10 To: Ben Zeev, Lior Cc: Atri Sharma; Stephen Frost; Pg Hackers Subject: Re: [HACKERS] PostgreSQL Process memory architecture On Tue, Jun 4, 2013 at 12:57 AM, Ben Zeev, Lior <lior.ben-zeev@hp.com> 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