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
>
>