Postgres, from time to time just hangs

Question

We are using Postgres as DB backend for our Django project. Everything works fine except from time to time Postgres jus hangs. Load average spikes to 80 points, and there are many posgtres processes. As far I see problematic is our table with users online. On every request there is update, we are updating "last seen" column. On every minute there is cronjob deleting users inactive for 1 minute. And in example today, when there was Postgres hang I've seen many updates and deletes to and from this table. I think it's some kind of race condition?

This table with users online is not big. In rush hours there are ~800 records. Here is schema.

 Column | Type | Modifiers ------------+--------------------------+----------------------------------------------------------------- id | integer | not null default nextval('spoleczniak_online_id_seq'::regclass) postac_id | integer | not null data | timestamp with time zone | not null zalogowany | timestamp with time zone | not null Indexes: "spoleczniak_online_pkey" PRIMARY KEY, btree (id) "spoleczniak_online_postac_id_key" UNIQUE CONSTRAINT, btree (postac_id) "spoleczniak_online_data" btree (data) Foreign-key constraints: "spoleczniak_online_postac_id_fkey" FOREIGN KEY (postac_id) REFERENCES postac_postacie(id) DEFERRABLE INITIALLY DEFERRED 

Normally Postgres is generating less than 1.5 load average points. We are running it on i7, 16 GB of RAM, and hardware RAID-10 (3x2 disks) for OS/data + RAID-1 (2 disks) for WAL. Hardware RAID has 512 MB of cache.

I've tried 9.0 and 9.1 beta (I even set no WAL logs for that table in 9.1).

I'm really thinking about moving to MySQL but don't know any conversion tools. :P

PS. I don't know if it's more Serverfault or Stackoverflow... but as you can see I decided to put it on Serverfault. :P

Edit:

Some information from logs:

Jul 31 20:37:16 postgres postgres[1420]: [3-1] LOG: 00000: process 1420 acquired ExclusiveLock on tuple (29,7) of relation 33107 of database 20005 after 2071.481 ms Jul 31 20:37:16 postgres postgres[1420]: [3-2] LOCATION: ProcSleep, proc.c:1076 Jul 31 20:37:16 postgres postgres[1420]: [3-3] STATEMENT: UPDATE "spoleczniak_online" SET "postac_id" = 101651, "data" = '2011-07-31 20:39:18.000699', "zalogowany" = '2011-07-31 20:31:04.843741' WHERE "spoleczniak_online"."id" = 559650 Jul 31 20:37:16 postgres postgres[1493]: [3-1] LOG: 00000: process 1493 acquired ExclusiveLock on tuple (29,7) of relation 33107 of database 20005 after 1393.154 ms Jul 31 20:37:16 postgres postgres[1493]: [3-2] LOCATION: ProcSleep, proc.c:1076 Jul 31 20:37:16 postgres postgres[1493]: [3-3] STATEMENT: UPDATE "spoleczniak_online" SET "postac_id" = 101651, "data" = '2011-07-31 20:39:15.646537', "zalogowany" = '2011-07-31 20:31:04.843741' WHERE "spoleczniak_online"."id" = 559650 

So ok, it's locking issue... but how can I avoid that?

Answer 1

This seems like locking problem: some large query locks too much, so other running queries cannot complete until lock is freed. Turn on slow query logging, add more monitoring. I strongly suggest using some monitoring system (zabbix, zenoss, etc) to be aware of your postgresql database status.

As a quick 'monitoring', take a look to pg_stat_activity and pg_locks views during the hang. Here is a good query that I'm often starting with:

SELECT datname, NOW() - query_start AS duration, procpid, current_query FROM pg_stat_activity WHERE current_query <> '<IDLE>' AND NOW() - query_start > '1 second' ORDER BY duration DESC; 

It often gives enough information, but sometimes I have to run more in order to understand what's going on.

Please update your question with output of this (or similar) query.

Answer 2

I would recommend the book PostgreSQL 9.0 High Performance chapter 11, "Database activity and statistics." Another great place to take this problem would be the postgresql-performance mailing list.