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
