Message-ID: <4F6AF3FE.30407@gmail.com>
Date: Thu, 22 Mar 2012 11:42:22 +0200
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.27) Gecko/20120216 Lightning/1.0b2 Thunderbird/3.1.19
MIME-Version: 1.0
To: gnanam@zoniac.com
CC: pgsql-performance@postgresql.org
Subject: Re: Write workload is causing severe slowdown in Production
References: <006301cd07fd$358ebe40$a0ac3ac0$@com>
In-Reply-To: <006301cd07fd$358ebe40$a0ac3ac0$@com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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.