Hi, On 2 February 2012 11:38, Christopher Opena <counterveil@xxxxxxxxx> 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@xxxxxxxxx) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
