Re: Queries seldomly take >4s while normally take <1ms?

Christian Hammers <ch(at)lathspell(dot)de> wrote:

> 9.2.3

You really need to think about 9.2.4 Real Soon Now; there's a
security fix that you probably should not wait on.

> max_connections = 1000                  # (change requires restart)
> shared_buffers = 20GB                  # min 128kB

Those are both potential causes.  For max_connections, see this:

http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

Maybe you happened to have enough users hit the enter key at the
same moment to cause a process holding a lock to be starved of
cycles or something similar.

One problem with a large shared_buffers setting is that PostgreSQL
can accumulate a very large number of dirty pages and flush them to
the OS all at once.  This can overwhelm the storage system and
cause exactly the kind of symptoms you're seeing.

> effective_cache_size is way too low, I noticed, can that be the cause?

Probably not.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company