Обсуждение: 8.4 -> 9.1 : ANALYSE VERBOSE; -> out of shared memory
Hi,
we have a problem since we migrate from 8.4 to 9.1
when we play : ANALYSE VERBOSE; ( stat on all databases, with 500 tables and 1to DATA in all tables)
we now have this message :
org.postgresql.util.PSQLException: ERROR: out of shared memory Indice : You might need to increase max_locks_per_transaction.
When we was in 8.4, there was no error,
there is our specific postgresql.conf configuration on the server :
default_statistics_target = 200
maintenance_work_mem = 1GB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 7GB
work_mem = 48MB
wal_buffers = 32MB
checkpoint_segments = 64
shared_buffers = 2304MB
max_connections = 150
random_page_cost = 2.0
max_locks_per_transaction = 128 # was at default val ( 64?), we already try to increase it without sucess
Have you any suggestions ?
we have a problem since we migrate from 8.4 to 9.1
when we play : ANALYSE VERBOSE; ( stat on all databases, with 500 tables and 1to DATA in all tables)
we now have this message :
org.postgresql.util.PSQLException: ERROR: out of shared memory Indice : You might need to increase max_locks_per_transaction.
When we was in 8.4, there was no error,
there is our specific postgresql.conf configuration on the server :
default_statistics_target = 200
maintenance_work_mem = 1GB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 7GB
work_mem = 48MB
wal_buffers = 32MB
checkpoint_segments = 64
shared_buffers = 2304MB
max_connections = 150
random_page_cost = 2.0
max_locks_per_transaction = 128 # was at default val ( 64?), we already try to increase it without sucess
Have you any suggestions ?
On Mon, May 12, 2014 at 7:57 AM, Souquieres Adam <adam.souquieres@axege.com> wrote: > Hi, > > we have a problem since we migrate from 8.4 to 9.1 > > when we play : ANALYSE VERBOSE; ( stat on all databases, with 500 tables and > 1to DATA in all tables) > > we now have this message : > > org.postgresql.util.PSQLException: ERROR: out of shared memory Indice : You > might need to increase max_locks_per_transaction. > > When we was in 8.4, there was no error, > > there is our specific postgresql.conf configuration on the server : > > default_statistics_target = 200 > maintenance_work_mem = 1GB > constraint_exclusion = on > checkpoint_completion_target = 0.9 > effective_cache_size = 7GB > work_mem = 48MB > wal_buffers = 32MB > checkpoint_segments = 64 > shared_buffers = 2304MB > max_connections = 150 > random_page_cost = 2.0 > max_locks_per_transaction = 128 # was at default val ( 64?), we already try > to increase it without sucess How high did you increase it? It's not uncommon to have to raise that parameter significantly if you have a lot of tables. Try 2048. merlin
Merlin Moncure <mmoncure@gmail.com> writes: > On Mon, May 12, 2014 at 7:57 AM, Souquieres Adam > <adam.souquieres@axege.com> wrote: >> when we play : ANALYSE VERBOSE; ( stat on all databases, with 500 tables and >> 1to DATA in all tables) >> we now have this message : >> org.postgresql.util.PSQLException: ERROR: out of shared memory Indice : You >> might need to increase max_locks_per_transaction. >> max_connections = 150 >> max_locks_per_transaction = 128 # was at default val ( 64?), we already try >> to increase it without sucess > How high did you increase it? It's not uncommon to have to raise that > parameter significantly if you have a lot of tables. Try 2048. It's unsurprising for analyze across 500 tables to require 500 locks. However, with those settings you should already have 150*128 = 19200 slots in the shared lock table, so there's no way that the analyze is eating them all. What else is going on in the system? How many entries do you see in pg_locks while this is happening? regards, tom lane
Hi, thanks you both for your quick answers, Le 12/05/2014 15:29, Tom Lane a écrit : > Merlin Moncure <mmoncure@gmail.com> writes: >> On Mon, May 12, 2014 at 7:57 AM, Souquieres Adam >> <adam.souquieres@axege.com> wrote: >>> when we play : ANALYSE VERBOSE; ( stat on all databases, with 500 tables and >>> 1to DATA in all tables) >>> we now have this message : >>> org.postgresql.util.PSQLException: ERROR: out of shared memory Indice : You >>> might need to increase max_locks_per_transaction. >>> max_connections = 150 >>> max_locks_per_transaction = 128 # was at default val ( 64?), we already try >>> to increase it without sucess >> How high did you increase it? It's not uncommon to have to raise that >> parameter significantly if you have a lot of tables. Try 2048. We change the parameter from default value 64 to 128 > It's unsurprising for analyze across 500 tables to require 500 locks. > However, with those settings you should already have 150*128 = 19200 > slots in the shared lock table, so there's no way that the analyze > is eating them all. What else is going on in the system? How many > entries do you see in pg_locks while this is happening? > > regards, tom lane > > ANALYSE VERBOSE; should use only one transaction or one transaction per table it analyse ? anyway, i try too list pg_locks table during this issue and i'll post you the result. Adam
Souquieres Adam <adam.souquieres@axege.com> writes: > ANALYSE VERBOSE; should use only one transaction or one transaction per > table it analyse ? ANALYZE is just a simple statement: it doesn't start or stop any transactions. So all the locks will be acquired in the calling transaction. You might be better off using VACUUM ANALYZE, which although it does more work will divide the work into a transaction per table. regards, tom lane
When i relaunch my ANALYSE VERBOSE, pg_locks table grows quickly from 20 lines to more than 1000 lines and still growing, all the lines are owned by the same "virtual transaction" and the same "pid". max locks is 128, so i don't understand what happening, When i finish to write this email, i juste hit more than 3200 lock owned by the same transaction ! Can you explain what is the difference between 8.4 and 9.1 on this point please ? regards, Adam Le 12/05/2014 15:33, Souquieres Adam a écrit : > Hi, > > thanks you both for your quick answers, > > > > > Le 12/05/2014 15:29, Tom Lane a écrit : >> Merlin Moncure <mmoncure@gmail.com> writes: >>> On Mon, May 12, 2014 at 7:57 AM, Souquieres Adam >>> <adam.souquieres@axege.com> wrote: >>>> when we play : ANALYSE VERBOSE; ( stat on all databases, with 500 >>>> tables and >>>> 1to DATA in all tables) >>>> we now have this message : >>>> org.postgresql.util.PSQLException: ERROR: out of shared memory >>>> Indice : You >>>> might need to increase max_locks_per_transaction. >>>> max_connections = 150 >>>> max_locks_per_transaction = 128 # was at default val ( 64?), we >>>> already try >>>> to increase it without sucess >>> How high did you increase it? It's not uncommon to have to raise that >>> parameter significantly if you have a lot of tables. Try 2048. > We change the parameter from default value 64 to 128 > >> It's unsurprising for analyze across 500 tables to require 500 locks. >> However, with those settings you should already have 150*128 = 19200 >> slots in the shared lock table, so there's no way that the analyze >> is eating them all. What else is going on in the system? How many >> entries do you see in pg_locks while this is happening? >> >> regards, tom lane >> >> > ANALYSE VERBOSE; should use only one transaction or one transaction > per table it analyse ? > > anyway, i try too list pg_locks table during this issue and i'll post > you the result. > > > Adam > >
I just hit the 20k locks in pg_locks, on 18k differents relations owned by the same virtual transaction and PID. I only have like 500 tables and like 2k indexes, i must miss something. Le 12/05/2014 15:42, Tom Lane a écrit : > Souquieres Adam <adam.souquieres@axege.com> writes: >> ANALYSE VERBOSE; should use only one transaction or one transaction per >> table it analyse ? > ANALYZE is just a simple statement: it doesn't start or stop any > transactions. So all the locks will be acquired in the calling > transaction. > > You might be better off using VACUUM ANALYZE, which although it > does more work will divide the work into a transaction per table. > > regards, tom lane > >
Souquieres Adam <adam.souquieres@axege.com> writes: > When i relaunch my ANALYSE VERBOSE, pg_locks table grows quickly from 20 > lines to more than 1000 lines and still growing, all the lines are owned > by the same "virtual transaction" and the same "pid". Hm. I experimented a bit and looked at the code, and I find I was not remembering ANALYZE's behavior exactly right: it only processes all the tables inside one transaction if you start it inside a transaction block (ie, after BEGIN, or inside a function). If you just run it by itself then it does a transaction per table, just like VACUUM. So I'm thinking there's something you're not telling us about exactly how you invoke ANALYZE. > When i finish to write this email, i juste hit more than 3200 lock owned > by the same transaction ! Could you show us some of those locks (a few dozen lines from pg_locks)? regards, tom lane
Le 12/05/2014 16:24, Tom Lane a écrit :
Souquieres Adam <adam.souquieres@axege.com> writes:When i relaunch my ANALYSE VERBOSE, pg_locks table grows quickly from 20 lines to more than 1000 lines and still growing, all the lines are owned by the same "virtual transaction" and the same "pid".Hm. I experimented a bit and looked at the code, and I find I was not remembering ANALYZE's behavior exactly right: it only processes all the tables inside one transaction if you start it inside a transaction block (ie, after BEGIN, or inside a function). If you just run it by itself then it does a transaction per table, just like VACUUM. So I'm thinking there's something you're not telling us about exactly how you invoke ANALYZE.
Ok, thank you for your help, we finally undestood what the problem is with your hints.
In our production environment, which is "living", we have 4200 tables (97% are generated by our program for BI performance) and not only 500 ( the real ones) ... this is the difference between test env and prod env,
moreover we launch the analyse verbose using JDBC with an ORM, and i think it add begin; and end;.
I just tested it on pgadmin,
- without begin end, there is not so much lock,
- with begin end, there is a lock explosion that is normal when we look at the number of tables involved.
The solution must be to define a better strategy for tables statistics... we dont really need stats on all the tables because the most part of them is static...
Thank you very much....
Regards,
Adam
When i finish to write this email, i juste hit more than 3200 lock owned by the same transaction !Could you show us some of those locks (a few dozen lines from pg_locks)? regards, tom lane
Souquieres Adam wrote: > The solution must be to define a *better strategy for tables > statistics*... we dont really need stats on all the tables because > the most part of them is static... Tune autovacuum so that it does the analyses for you? -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Alvaro Herrera-9 wrote > Souquieres Adam wrote: > >> The solution must be to define a *better strategy for tables >> statistics*... we dont really need stats on all the tables because >> the most part of them is static... > > Tune autovacuum so that it does the analyses for you? And for those few tables that need special handling, use: ANALYZE [table name]; and/or VACUUM ANALYZE [table name]; The observation that you "don't need statistic [updates] on all tables [every time]" is accurate but you decided to implement a custom solution without taking that into account... Maybe the manual versions of these routines could be coded to evaluate usage statistics and skip any tables that are not in need of updating - but then you'd need some way to control thresholds and force it to perform regardless of what it thinks. Considerable effort when much of that code is already embedded into autovacuum. Not saying the existing system is perfect but it is what currently exists and works well in the majority of situations - the remainder of which a logic-less VACUUM/ANALYZE handles adequately. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/8-4-9-1-ANALYSE-VERBOSE-out-of-shared-memory-tp5803630p5803669.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.