Could I consider it a hardware problem, or postgresql running too long which causes problems? (It ran about half a month, however, it ran much longer than that without problems)
On Wed, Jul 13, 2011 at 00:52, Tony Wang <www...@gmail.com> wrote: > 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 > > -- > BR, > Tony Wang >
