From: Carlos Moreno <moreno_pg@mochima.com>
To: pgsql-performance@postgresql.org
Subject: Re: Possible explanations for catastrophic performace deterioration?
Date: Sun, 23 Sep 2007 14:15:09 -0400
Message-ID: <46F6AD2D.4090300@mochima.com> (raw)
In-Reply-To: <36e682920709230956p1312745eif1c6ba7a342dae3d@mail.gmail.com>
References: <46F6981B.1010600@mochima.com>
<36e682920709230956p1312745eif1c6ba7a342dae3d@mail.gmail.com>
Jonah H. Harris wrote:
> You didn't specify the database size
Oops, sorry about that one --- the full backup is a 950MB file. The
entire database
should fit in memory (and the effective_cache_size was set to 2GB for
the machine
with 4GB of memory)
> , but my guess is that the total
> data size about enough to fit in shared_buffers or kernel cache. On
> the new system (or dropped/recreated database), it would've all or
> mostly fit in memory which would make things like count(*) work
> quickly.
I don't understand this argument --- the newer system has actually less
memory
than the old one; how could it fit there and not on the old one? Plus,
how could
dropping-recreating the database on the same machine change the fact
that the
entire dataset entirely fit or not in memory??
The other part that puzzled me is that after running "select count(*)
... " several
times (that particular table is *very* small --- just 200 thousand
records of no
more than 100 or 200 bytes each), then the entire table *should* have been
in memory ... Yet, it would still take a few seconds (notice that
there was a
*considerable* improvement from the first run of that query to the
second one
on the old server --- from more than a minute, to just above two
seconds.... But
still, on the new server, and after recreating the DB on the old one, it
runs in
*no time* the first time).
> My guess is that a vacuum full would've brought the other database
> back up to speed.
I'm furious now that it didn't occur to me the vacuum full until *after*
I had
recreated the database to see th problem disappear...
I wonder if I should then periodically run a vacuum full --- say, once a
week?
Once a month?
> In the future, you probably want to set fillfactor
> to a reasonable amount to account for updates-to-blocks-between-vacuum
> to try and capture as few row-migrations as possible.
>
Could you elaborate a bit on this? Or point me to the right places in the
documentation to help me understand the above?? (I'm 100% blank after
reading the above paragraph)
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: <46F6AD2D.4090300@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