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@xxxxxxxxx> writes:
On Mon, May 12, 2014 at 7:57 AM, Souquieres Adam
<adam.souquieres@xxxxxxxxx> 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
