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

Re: Possible explanations for catastrophic performace deterioration?

From: "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>
To: "Carlos Moreno" <moreno_pg(at)mochima(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Possible explanations for catastrophic performace deterioration?
Date: 2007-09-23 16:56:35
Message-ID: 36e682920709230956p1312745eif1c6ba7a342dae3d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
You didn't specify the database size, 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.  On the old database, you probably had a lot of fragmentation
which would've caused significantly more I/O to be performed thereby
causing a slowdown.  You could compare relation sizes to check easily.

My guess is that a vacuum full would've brought the other database
back up to speed.  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.
-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation                | fax: 732.331.1301
499 Thornall Street, 2nd Floor          | jonah(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837                        | http://www.enterprisedb.com/

In response to

Responses

pgsql-performance by date

Next:From: Yinan LiDate: 2007-09-23 17:00:24
Subject: zero value in statistics collector's result
Previous:From: Carlos MorenoDate: 2007-09-23 16:45:15
Subject: Possible explanations for catastrophic performace deterioration?

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