Re: can't handle large number of INSERT/UPDATEs

It just seems that the more activity there is (that is when there's a
lot of disk activity) the checkpoints happen quicker too.

Here's a snapshot from the /var/log/messages -

Oct 26 17:21:22 vl-pe6650-003 postgres[13978]: [2-1] LOG:  recycled
transaction
log file "0000000B0000007E"
Oct 26 17:21:22 vl-pe6650-003 postgres[13978]: [3-1] LOG:  recycled
transaction
log file "0000000B0000007F"
...
Oct 26 17:26:25 vl-pe6650-003 postgres[14273]: [2-1] LOG:  recycled
transaction
log file "0000000B00000080"
Oct 26 17:26:25 vl-pe6650-003 postgres[14273]: [3-1] LOG:  recycled
transaction
log file "0000000B00000081"
Oct 26 17:26:25 vl-pe6650-003 postgres[14273]: [4-1] LOG:  recycled
transaction
log file "0000000B00000082"
...
Oct 26 17:31:27 vl-pe6650-003 postgres[14508]: [2-1] LOG:  recycled
transaction
log file "0000000B00000083"
Oct 26 17:31:27 vl-pe6650-003 postgres[14508]: [3-1] LOG:  recycled
transaction
log file "0000000B00000084"
Oct 26 17:31:27 vl-pe6650-003 postgres[14508]: [4-1] LOG:  recycled
transaction
log file "0000000B00000085"
...

I have increased them from default 3 to 15. Haven't altered the
frequency though....

Thanks,
Anjan

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, October 26, 2004 5:53 PM
To: Anjan Dave
Cc: Rod Taylor; Postgresql Performance
Subject: Re: [PERFORM] can't handle large number of INSERT/UPDATEs

"Anjan Dave" <adave@vantage.com> writes:
> None of the locks are in state false actually.

In that case you don't have a locking problem.

> I don't have iostat on that machine, but vmstat shows a lot of writes
to
> the drives, and the runnable processes are more than 1:

I get the impression that you are just saturating the write bandwidth of
your disk :-(

It's fairly likely that this happens during checkpoints.  Look to see if
the postmaster has a child that shows itself as a checkpointer in "ps"
when the saturation is occurring.  You might be able to improve matters
by altering the checkpoint frequency parameters (though beware that
either too small or too large will likely make matters even worse).

            regards, tom lane