From: Carlos Moreno <moreno_pg@mochima.com>
To: pgsql-performance@postgresql.org
Subject: Possible explanations for catastrophic performace deterioration?
Date: Sun, 23 Sep 2007 12:45:15 -0400
Message-ID: <46F6981B.1010600@mochima.com> (raw)
I recently had a puzzling experience (performace related).
Had a DB running presumably smoothly, on a server with Dual-Core
Opteron and 4GB of RAM (and SATA2 drives with Hardware RAID-1).
(PostgreSQL 8.2.4 installed from source, on a FC4 system --- databases
with no encoding --- initdb -E SQL_ASCII --no-locale, and all the
databases created with encoding SQL_ASCII)
We thought that performance had gone little by little down, but the
evidence now suggests that something must have triggered a big step
down in the performance of the server.
Thinking that it was simply a bottleneck with the hardware, we moved
to a different machine (lower performance CPU-wise, but with dual hard
disk, so I configured the pg_xlog directory on a partition on a separate
hard disk, estimating that this would take precedence over the lower CPU
power and the 2GB of RAM instead of 4).
Not only the performance was faster --- a query like:
select count(*) from customer
was *instantaneous* on the new machine (just after populating it,
without having even analyzed it!), and would take over a minute on
the old machine (the first time). Then, the second time, it would
take a little over two seconds on the old machine (at this point, both
machines had *zero* activity --- they were both essentially disconnected
from the outside world; serving exclusively my psql connection).
Funny thing, I dropped the database (on the old machine) and re-created
it with the backup I had just created, and now the performance on the
old one was again normal (the above query now gives me a result in
essentially no time --- same as on the new machine).
In retrospect, I'm now wondering if a vacuum full would have solved
the issue? (we do run vacuumdb -z --- vacuum analyze --- daily)
Any comments?? I'm worried that three months down the road we'll
face the same issue with this new server (that's about the time it took
since we had started running the other server until we noticed the
poor performance level) --- and we can not afford to completely stop
the system to drop-and-recreate the db on a regular basis.
Thanks,
Carlos
--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: moreno_pg@mochima.com
Subject: Re: Possible explanations for catastrophic performace deterioration?
In-Reply-To: <46F6981B.1010600@mochima.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox