Hi,
On 2 February 2012 11:38, Christopher Opena <counterveil@gmail.com> wrote:
> We've been running into some very strange issues of late with our PostgreSQL
> database(s). We have an issue where a couple of queries push high CPU on a
> few of our processors and the entire database locks (reads, writes, console
> cannot be achieved unless the high CPU query procs are killed). Further
> investigation shows ~59% total cpu usage (we have 16 total cores), low io,
> and mid-to-low memory usage (we have 74GB of memory, shared_buffers=16GB).
I think 16GB is too much. We started with 9GB (16 cores, 80GB RAM,
SAN) and then experimented with lower value (6GB) but never used in
the production because we switched to different database / storage
technology. Anyway, Overal CPU utilisation was lower using 6GB.
If CPU util is high because of io waits then it might be worth to play
with dirty_background_ratio and dirty_ratio. The problem is that the
value is percentage and you have 74GB. CentOS has 10% and 40% as
default value for dirty_background_ratio and dirty_ratio respectively.
10% of 74GB is 7.4GB and there is no storage controller with 7.4GB of
cache so you get IO waits (and high load). So writes will backup until
you hit 40% hard limit (vm.dirty_ratio) which is even worse (~30GB to
flush). I think you should try lower both. For example, try 1 and 10
for vm.dirty_background_ratio and vm.dirty_ratio respectively.
> One thing that we aren't sure of is whether or not we are running into a
> general connection pooling issue. Our typical number of postgresql
> processes fluctuates between 1,400 and 1,600 - most of which are idle - as
> we have a number of application servers all connecting to a central
> read/write master (the master replicates out to a secondary via streaming
> replication). We have max_processes set to 3,000 after tweaking some kernel
> memory parameters so at least we know we aren't exceeding that, but is there
> a practical "real world" limit or issue with setting this too high?
I would use connection pooler like PG-Pool II. It can add transparent
failover and you don't need max_processes set so high (plus parallel
query feature could be useful).
--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)