Date: Sun, 23 Sep 2007 14:23:49 -0400
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?
Message-ID: <20070923182349.GC5679@alvh.no-ip.org>
References: <46F6981B.1010600@mochima.com>
	<36e682920709230956p1312745eif1c6ba7a342dae3d@mail.gmail.com>
	<46F6AD2D.4090300@mochima.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <46F6AD2D.4090300@mochima.com>
User-Agent: Mutt/1.5.16 (2007-06-11)

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.