Hi, We use the fine Bucardo check_postgres Nagios plugin, and it reported a "CRITICAL" level spike in database locks (171 locks). I looked closely at my database logs and found an even bigger spike just a few minutes earlier (208 locks). I saw 8 EXCLUSIVE locks on table X. All of these queries completed within 5 seconds. (I know that because they do not appear in my slow query log.) The fine Postgres manual states: EXCLUSIVE lock: Only reads from the table can proceed in parallel with a transaction holding this lock mode. This lock mode is not automatically acquired on user tables by any PostgreSQL command. My questions: 1. How I could have had eight queries all with EXCLUSIVE locks on the same table? Wouldn't have Postgres only allowed one EXCLUSIVE lock at a time? 2. Since there was no sign of any trouble in the application itself (everything appeared to work fine), I'm wondering whether we need to increase our threshold for critical for number of locks, and what factors are involved in deciding what to set it to (double it? 10x it?) What value to use... We recently upgraded our server and it is pretty beefy compared to the workload being thrown at it. (For example, we can do 800 Postgres transactions per second without breaking a sweat on the disk subsystem, which records about 150,000 write IOPS, showing 3% utilization on the disk subsystem.) Best, -at -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
