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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
