Re: UPDATE 66k rows too slow

Hi!

   I read and did many stuff you pointed me too. Raised shared buffers
to 180 MB, and tried again. Same results.

   I deleted the DB, created a new one and generated new test data. I
know have 72k rows, and the same query finishes in... 9 seconds.

   I'm totally clueless. Anyway, two questions:

   1) My working_mem is 2 MB. Does an UPDATE query like main depend on
working_mem?

   2) I still feel this is all very trial-and-error. Change value, run
query, hope it solves the problem. Well, the DB itself knows what is
doing. Isn't there any way to make it tell us that? Like "the working
mem is too low" or anything else. I know the problem is not the
checkpoints, at least nothing appears on the log related to that. But
it irritates me to be in front of a such complex system and not being
able to know what's going on.

   Yours

Miguel Arroz

On 2008/03/10, at 05:10, Greg Smith wrote:

> 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Miguel Arroz
http://www.terminalapp.net
http://www.ipragma.com