Re: Weird problem that enormous locks

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 <wwwjfy(at)gmail(dot)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
>