"Carlo Stonebanks" <stonec.register@sympatico.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