Subject: Re: Low CPU Usage
From: Mark Lewis <mark.lewis@mir3.com>
To: brauagustin-susc@yahoo.com.ar
Cc: pgsql-performance@postgresql.org
In-Reply-To: <65712.86940.qm@web30004.mail.mud.yahoo.com>
References: <65712.86940.qm@web30004.mail.mud.yahoo.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: MIR3, Inc.
Date: Wed, 19 Sep 2007 11:03:06 -0700
Message-Id: <1190224986.11528.47.camel@archimedes>
Mime-Version: 1.0

On Wed, 2007-09-19 at 10:38 -0700, brauagustin-susc@yahoo.com.ar wrote:
> Hi all.
> Recently I have installed a brand new server with a Pentium IV 3.2
> GHz, SATA Disk, 2GB of Ram in Debian 4.0r1 with PostgreSQL 8.2.4
> (previously a 8.1.9).
> I have other similar server with an IDE disk, Red Hat EL 4 and
> PostgreSQL 8.2.3
> 
> I have almost the same postgresql.conf in both servers, but in the new
> one (I have more work_mem than the other one) things go really slow.
> I began to monitor i/o disk and it's really ok, I have test disk with
> hdparm and it's 5 times faster than the IDE one.
> Running the same queries in both servers in the new one it envolves
> almost 4 minutes instead of 18 seconds in the old one.
> Both databases are the same, I have vacuum them and I don't know how
> to manage this issue.
> The only weird thing is than in the older server running the query it
> uses 30% of CPU instead of 3 o 5 % of the new one!!!
> What's is happening with this server? I upgrade from 8.1.9 to 8.2.4
> trying to solve this issue but I can't find a solution.
> 
> Any ideas?

It could be a planning issue.  Have you analyzed the new database to
gather up-to-date statistics?  A comparison of EXPLAIN ANALYZE results
for an example query in both systems should answer that one.

Another possibility because you're dealing with lower-end drives is that
you have a case of one drive lying about fsync where the other is not.
If possible, try running your test with fsync=off on both servers.  If
there's a marked improvement on the new server but no significant change
on the old server then you've found your culprit.

-- Mark Lewis