Hi,
The configuration information is listed at the end.
I met this problem last weekend. The presentation was that, the db locks became enormous, up to 8.3k, and the db hanged there. About half an hour to one hour later, it recovered: the locks became 1 or 2 hundreds, which was its average level. It happened every 5-8 hours.
I checked the log, but nothing interesting. The log about dead lock happened several times a day, and not when hanging. I had a cron job running every minute to record the locks using the command below:
select pg_class.relname, pg_locks.mode, pg_locks.granted, pg_stat_activity.current_query, pg_stat_activity.query_start, pg_stat_activity.xact_start as transaction_start, age(now(),pg_stat_activity.query_start) as query_age, age(now(),pg_stat_activity.xact_start) as transaction_age, pg_stat_activity.procpid from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_locks.pid=pg_stat_activity.procpid and substr(pg_class.relname,1,3) != 'pg_' order by query_start;
The only special thing I can find is that there were a lot ExclusiveLock, while it's normal the locks are only AccessShareLock and RowExclusiveLock.
After suffering from that for whole weekend, I restarted postgresql, and my service, and reduced a bit db pressure by disabling some service, and it didn't happen again till now.
The possible reason I think of is that someone was reindexing index, which is almost impossible; or the hardware problem, which is also little possible.
Have any one experienced that, or any suggestion on researching/debugging?
The configuration information:
System: Ubuntu server 10.04.2
Postgresql version: 8.4.8-0ubuntu0.10.04
CPU: Intel(R) Xeon(R) CPU X5650 @ 2.67GHz (24 cores)
Disk: Fusion IO drive
Memory: 32G
Postgresql configuration:
max_connection = 800
shared_buffers = 2000MB
effective_cache_size = 14000MB
autovacuum = off