Skip site navigation (1) Skip section navigation (2)

Re: Possible explanations for catastrophic performacedeterioration?

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Carlos Moreno <moreno_pg(at)mochima(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Possible explanations for catastrophic performacedeterioration?
Date: 2007-09-23 18:23:49
Message-ID: 20070923182349.GC5679@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Carlos MorenoDate: 2007-09-23 19:12:02
Subject: Re: Possible explanations for catastrophic performace deterioration?
Previous:From: Carlos MorenoDate: 2007-09-23 18:15:09
Subject: Re: Possible explanations for catastrophic performace deterioration?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group