Sean Ma <seanxma(at)gmail(dot)com> 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