Message-ID: <dc1f59dfd626334d6f37d5a6ffb97bb9.squirrel@sq.gransy.com>
In-Reply-To: <4F6AF3FE.30407@gmail.com>
References: <006301cd07fd$358ebe40$a0ac3ac0$@com> <4F6AF3FE.30407@gmail.com>
Date: Thu, 22 Mar 2012 11:04:14 +0100
Subject: Re: Write workload is causing severe slowdown in
 Production
From: "Tomas Vondra" <tv@fuzzy.cz>
To: "Vitalii Tymchyshyn" <tivv00@gmail.com>
Cc: gnanam@zoniac.com,
 pgsql-performance@postgresql.org
User-Agent: SquirrelMail/1.4.22
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Importance: Normal
Content-Transfer-Encoding: quoted-printable

On 22 B=C5=99ezen 2012, 10:42, Vitalii Tymchyshyn wrote:
> 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 =D0=BD=D0=B0=D0=BF=D0=B8=D1=81=D0=B0=D0=B2(=D0=
=BB=D0=B0):
>> 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 t=
he
>> 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 crossin=
g
>> 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.

I'd say you should investigate what the application actually does. The
chances are it's poorly written, issuing a lot of queries and causing a
log of IO.

And 80% utilization does not mean the operations need to be writes - it's
about IO operations, i.e. both reads and writes.

>> 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 b=
y
>> turning on "log_checkpoints".
>>
>> My question is, how do I determine whether "checkpoint" occurrences ar=
e
>> 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.

There's a checkpoint_warning option. Set it to 3600 and you should get
messages in the log. Correlate those to the issues (do they happen at the
same time?). Sadly, 8.2 doesn't have any of the nice statistic views :-(

Check this: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83=
.htm

It talks about improvements in 8.3 but it mentions older version too.

If you can, install iotop and watch the processes that cause the I/O.
IIRC, the title of the process should say 'CHECKPOINT' etc. But if the
issues disappear once the application is stopped, it's unlikely the
checkpoints are the issue.

What we need is more details about your setup, especially

   - checkpoint_segments
   - checkpoint_timeout
   - shared_buffers

also it'd be nice to have samples from the vmstat/iostat and messages fro=
m
the log.

kind regards
Tomas