Hi all,
Recently our databases started to experience a significant slowdown.
Queries that were taking 500ms now take up to 20 seconds. An insert can
take 150ms or more. This is strange since we are still hitting indexes
and we vacuum regularly. Here is the description of our system:
A single server has a medium size database with around 30-40 tables.
Some tables have ~1000 rows. These tables tend to have lots of inserts
and deletes, so we vacuum them regularly (every ten minutes). One table
has 15 million rows, but it only every has inserts, never updated or
deleted, so this table never gets vacuumed. Vacuuming is very low impact:
vacuum_cost_delay = 50
vacuum_cost_limit = 100
We have fsync = true and max_connections = 150. There are 12 servers
each with the same schema and config, but different data (although the
number of rows in the tables are very similar). We assign users to a
server when we set them up so all the data for a ser is on one server.
This whole setup typically works well and is speedy, we have checked all
the indexes and they are hit when appropriate.
The table with 15million is the table on which inserts can take 150ms or
more (I've seen 800ms for a single insert before).
Strangely, this slowdown is on all 12 servers. Perhaps this is a
configuration issue? If anyone has any ideas we'd love to hear them.
Since we vacuum regularly and all the indexes get hit I don't know where
to go next.
Machines are Pentium 4 3.2Ghz running Linux kernel 2.6.10. Postgres
version 8.0.1. Machines have 2Gb ram and two 10k RPM disks in a RAID-0
configuration.
Regards
--
David Mitchell
Software Engineer
Telogis