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
