Thread: 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  ?

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

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.