Re: Massive table (500M rows) update nightmare

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-performance(at)postgresql(dot)org>, "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca>
Subject: Re: Massive table (500M rows) update nightmare
Date: 2010-01-08 18:12:40
Message-ID: 4B472138020000250002E0B8@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

"Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca> wrote:

> In order for me to validate that rows would have been updated, I
> had to run a SELECT with the same WHERE clause in PgAdminIII first
> to see how many rows would have qualified. But this was for
> testing purposes only. The SELECT statement does not exist in the
> code.

OK, I did misunderstand your earlier post. Got it now, I think.

> This is hosted on a new server the client set up so I am waiting
> for the exact OS and hardware config. PG Version is PostgreSQL
> 8.3.6, compiled by Visual C++ build 1400, OS appears to be Windows
> 2003 x64 Server.

That might provide more clues, when you get it.

> bgwriter_lru_maxpages = 100

With the large database size and the apparent checkpoint-related
delays, I would make that more aggressive. Getting dirty pages to
the OS cache reduces how much physical I/O needs to happen during
checkpoint. We use this on our large databases:

bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4.0

Boosting your checkpoint_completion_target along with or instead of
a more aggressive background writer might also help.

> max_fsm_pages = 204800

This looks suspiciously low for the size of your database. If you
do a VACUUM VERBOSE (for the database), what do the last few lines
show?

> work_mem = 512MB

That's OK only if you are sure you don't have a lot of connections
requesting that much RAM at one time, or you could drive yourself
into swapping.

I hope this helps.

-Kevin

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2010-01-08 18:27:03 Re: Change query join order
Previous Message Greg Smith 2010-01-08 18:12:00 Re: Massive table (500M rows) update nightmare