From: Alvaro Herrera <alvherre@commandprompt.com>
To: Carlos Moreno <moreno_pg@mochima.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Possible explanations for catastrophic performace deterioration?
Date: Sun, 23 Sep 2007 14:23:49 -0400
Message-ID: <20070923182349.GC5679@alvh.no-ip.org> (raw)
In-Reply-To: <46F6AD2D.4090300@mochima.com>
References: <46F6981B.1010600@mochima.com>
<36e682920709230956p1312745eif1c6ba7a342dae3d@mail.gmail.com>
<46F6AD2D.4090300@mochima.com>
Carlos Moreno wrote:
>> , 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??
Because on the older server it is bloated, while on the new one it is
fresh thus no dead tuples.
> 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).
Bloat can explain this as well.
>> 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?
Never. What you need to do is make sure your FSM settings
(fsm_max_pages in particular) are high enough, and that you VACUUM (not
full) frequently enough.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
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: alvherre@commandprompt.com, moreno_pg@mochima.com
Subject: Re: Possible explanations for catastrophic performace deterioration?
In-Reply-To: <20070923182349.GC5679@alvh.no-ip.org>
* 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