Re: postgresql meltdown on PlanetMath.org

From: Aaron Krowne <akrowne(at)vt(dot)edu>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: postgresql meltdown on PlanetMath.org
Date: 2003-03-16 07:52:06
Message-ID: 20030316075206.GE19570@vt.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

> - Keep shared memory use reasonable; your final settings of 64M shared
> buffers and 16M sort_mem sound OK. In any case, be sure you're not
> disk-swapping.

Yeah, those seem like reasonable values to me. But I am not sure I'm
not disk-swapping, in fact it is almost certainly going on here bigtime.

> - If you don't already, run VACUUM ANALYZE on some regular schedule
> (how often depends on your data turnover rate)

I've done it here and there, especially when things seem slow. Never
seems to help much; the data turnover isn't high.

> - Possibly consider running REINDEX periodically

Ok thats a new one, I'll try that out.

> - Post the SQL and EXPLAIN ANALYZE output for the queries causing the
> worst of your woes to the list
> - Are all queries slow, or particular ones?

I'm grouping two separate things together to reply to, because the
second point answers the first: there's really no single culprit. Every
SELECT has a lag on the scale of a second; resolving all of the foreign
keys in various tables to construct a typical data-rich page piles up
many of these. I'm assuming the badness of this depends on how much
swapping is going on.

> Explanations of these can be found by searching the list archives and
> reading the related sections of the manual.

Will check that out, thanks.

> A few questions:
> - What version of Postgres?

7.2.1

> - Have you run VACUUM FULL ANALYZE lately (or at least VACUUM ANALYZE)?

Yes, after a particularly bad slowdown... it didn't seem to fix things.

> - Does the database see mostly SELECTs and INSERTs, or are there many
> UPDATEs and/or DELETEs too?

Almost exclusively SELECTs.

OK, I have just run a VACUUM FULL ANALYZE and things seem much better...
which would be the first time its really made a difference =) I tried
comparing an EXPLAIN ANALYZE of a single row select on the main objects
table before and after the vacuum, and the plan didn't change
(sequential scan still), but the response time went from ~1 second to
~5msec! I'm not really sure what could have happened here
behind-the-scenes since it didn't start using the index, and there
probably weren't more than 10% updated/added rows since the last VACUUM.

I actually thought I had a task scheduled which was running a VACUUM
periodically, but maybe it broke for some reason or another. Still, I
have not been getting consistent results from running VACUUMs, so I'm
not entirely confident that the book is closed on the problem.

Thanks for your help.

apk

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Aaron Krowne 2003-03-16 08:06:01 Re: postgresql meltdown on PlanetMath.org
Previous Message Joe Conway 2003-03-16 06:37:07 Re: postgresql meltdown on PlanetMath.org