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