From: Gnanakumar <gnanam@zoniac.com>
To: pgsql-performance@postgresql.org
Subject: Write workload is causing severe slowdown in Production
Date: Thu, 22 Mar 2012 12:57:13 +0530
Message-ID: <006301cd07fd$358ebe40$a0ac3ac0$@com> (raw)
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.
We want to optimize our Production database to handle both reads and writes,
any suggestions/advice/guidelines on this are highly appreciated.
Some important "postgresql.conf" parameters are:
# - Memory -
shared_buffers=1536MB
# - Planner Cost Constants -
effective_cache_size = 4GB
# - Checkpoints -
checkpoint_segments=32
checkpoint_timeout=5min
checkpoint_warning=270s
# - Background writer -
bgwriter_delay = 200ms
bgwriter_lru_percent = 1.0
bgwriter_lru_maxpages = 5
bgwriter_all_percent = 0.333
bgwriter_all_maxpages = 5
Regards,
Gnanam
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gnanam@zoniac.com
Subject: Re: Write workload is causing severe slowdown in Production
In-Reply-To: <006301cd07fd$358ebe40$a0ac3ac0$@com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox