Re: Write workload is causing severe slowdown in Production

Check for next messages in your log:
LOG: checkpoints are occurring too frequently (ZZZ seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".

Best regards, Vitalii Tymchyshyn

22.03.12 09:27, Gnanakumar написав(ла):
> Hi,
>
> We're running a web-based application powered by PostgreSQL. Recently,
> we've developed a "new" separate Java-based standalone (daemon process)
> threaded program that performs both read and write operations heavily on 2
> "huge" tables. One table has got 5.4 million records and other has 1.3
> million records. Moreover, more than one read and/or write operations may
> be executing concurrently.
>
> The issue that we're facing currently in our Production server is, whenever
> this "newly" developed Java program is started/run, then immediately the
> entire web application becomes very slow in response. At this time, I could
> also see from the output of " iostat -tx" that "%util" is even crossing more
> than 80%. So, what I could infer here based on my knowledge is, this is
> creating heavy IO traffic because of write operation. Since it was entirely
> slowing down web application, we've temporarily stopped running this
> standalone application.
>
> Meantime, I also read about "checkpoint spikes" could be a reason for slow
> down in "write workload" database. I'm also reading that starting in
> PostgreSQL 8.3, we can get verbose logging of the checkpoint process by
> turning on "log_checkpoints".
>
> My question is, how do I determine whether "checkpoint" occurrences are the
> root cause of this slowdown in my case? We're running PostgreSQL v8.2.22 on
> CentOS5.2 having 35 GB RAM. "log_checkpoints" is not available in
> PostgreSQL v8.2.22.