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
On Fri, Nov 18, 2005 at 10:05:47AM +1300, David Mitchell wrote:
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.
It sounds like a use-pattern issue. Did something in your use
change? Any time you get
David Mitchell [EMAIL PROTECTED] writes:
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
Tom Lane wrote:
Have you checked to see whether you are encountering table or index
bloat? Your vacuuming policy doesn't sound unreasonable on its face,
but there's no substitute for actually looking at physical file sizes
when you aren't sure what's wrong. Try doing a pg_dump and reload into
David Mitchell [EMAIL PROTECTED] writes:
I dumped and loaded then wrote a script to compare the sizes of tables.
I noticed that a few of the indexes, especially the indexes on the
15million table shrunk a lot (~50% reduction - ~400Mb). Also, the
pg_attribute_relid_attnam_index index shrank
Tom Lane wrote:
Also, the
pg_attribute_relid_attnam_index index shrank loads, to 1% of its
original size.
Hm, better check whether your vacuuming policy is taking care of the
system catalogs ...
Yes, fair point. So I did:
18/11/2005 03:02:29 INFO: vacuuming pg_catalog.pg_attribute
David Mitchell [EMAIL PROTECTED] writes:
18/11/2005 03:02:29 INFO: index pg_attribute_relid_attnam_index now
contains 2861 row versions in 11900 pages
18/11/2005 03:02:29 DETAIL: 11834 index pages have been deleted, 11834
are currently reusable.
18/11/2005 03:02:30 INFO: index
Tom Lane wrote:
I speculate that you did a VACUUM FULL on it recently.
You speculate right, we do a vacuum full every sunday night as a safety
net. So the vacuum full was a week old.
The condition of the indexes suggests strongly that you've not been
vacuuming pg_attribute often enough (and
