Date: Mon, 10 Mar 2008 01:10:21 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: Miguel Arroz <arroz@guiamac.com>
cc: pgsql-performance@postgresql.org
Subject: Re: UPDATE 66k rows too slow
In-Reply-To: <879F65BD-D1B0-4D15-BAE3-056305B84474@guiamac.com>
Message-ID: <Pine.GSO.4.64.0803100040560.318@westnet.com>
References: <879F65BD-D1B0-4D15-BAE3-056305B84474@guiamac.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Mon, 10 Mar 2008, Miguel Arroz wrote:

> My question is, how can I "ask" PgSQL what's happening? How can I avoid 
> guessing, and be sure of what is causing this slowdown?

There are many pieces involved here, and any one or multiple of them could 
be to blame.  Someone may make a guess and get lucky about the cause, but 
the only generic way to solve this sort of thing is to have a systematic 
approach that goes through the likely possible causes one by one until 
you've discovered the source of the problem.  Since as you say you're new 
to this, you've got the double task of learning that outline and then 
finding out how to run each of the tests.

For your particular case, slow updates, I usually follow the following 
series of tests.  I happen to have articles on most of these sitting 
around because they're common issues:

-Confirm disks are working as expected: 
http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm

-Look at differences between fsync commit behavior between the two 
systems.  It's often the case that when servers appear slower than 
development systems it's because the server is doing fsync properly, while 
the development one is caching fsync in a way that is unsafe for database 
use but much faster. 
http://www.postgresql.org/docs/8.3/static/wal-reliability.html is a brief 
intro to this while 
http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm goes 
into extreme detail.  The test_fsync section there is probably the most 
useful one for your comparision.

-Setup basic buffer memory parameters: 
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm

-VACUUM VERBOSE ANALYZE and make sure that's working properly.  This 
requires actually understanding the output from that command which is 
"fun" to figure out.  A related topic is looking for index bloat which I 
haven't found a good tutorial on yet.

-Investigate whether checkpoints are to blame.  Since you're running 8.3 
you can just turn on log_checkpoints and see how often they're showing up 
and get an idea how big the performance impact is.  Increasing 
checkpoint_segments is the usual first thing to do if this is the case.

-Collect data with vmstat, iostat, and top to figure out what's happening 
during the problem query

-Look for application problems (not your issue here)

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD