Sean Ma <seanxma@xxxxxxxxx> wrote: > I have a table about 50 million rows. There are a few writers to > pump data into the table at the rate of 40000 row/hours. Most the > time, the SELECT is less than 100 ms. However sometime it is very > slow, from 30 seconds to 500 seconds. The database is vacuum analyze > regularly. What version of PostgreSQL is this? On what OS? What hardware? We had similar problems on some of our servers under 8.2 and earlier due to the tendency of PostgreSQL to build up a very large set of dirty pages and then throw them all at the drives with an immediate fsync. The RAID controller queued up the requests, and fast reads got stuck in the queue behind all those writes. You may want to look at this excellent coverage of the topic by Greg Smith: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm We got around the problem by keeping the checkpoint interval and shared buffer size fairly small, and making the background writer fairly aggressive. What works for you, if this is your problem, may be different. I've heard that some have had to tune their OS caching configuration. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
