2011/1/27 Michael Kohl <michael.kohl@xxxxxxxxxx>: > Hi all, > > we are running a fairly big Ruby on Rails application on Postgres 8.4. > Our traffic grew quite a bit lately, and since then we are facing DB > performance issues. System load occasionally explodes (around 170 > yesterday on a 16 core system), which seems to be caused by disk I/O > (iowait in our Munin graphs goes up significantly during these > periods). At other times the laod stays rather low under pretty much > the same circumstances. > > There are 6 application servers with 18 unicorns each, as well as 12 > beanstalk workers talking to the DB. I know the problem description is > very vague, but so far we haven't consistently managed to reproduce > the problem. Turning of the beanstalk workers usually leads to a great > decreases in writes and system loads, but during yesterday's debugging > session they obviously ran fine (thanks, Murphy). > > Below you'll find our system information and Postgres config, maybe > someone could be so kind as to point out any obvious flaws in our > current configuration while I'm trying to get a better description of > the underlying problem. > > Postgres version: 8.4.6 > > Number of logical CPUs: 16 (4x Quadcore Xeon E5520 @ 2.27GHz) > > RAM: 16GB > > total used free shared buffers cached > Mem: 16461012 16399520 61492 0 72392 12546112 > -/+ buffers/cache: 3781016 12679996 > Swap: 999992 195336 804656 you have swap used, IO on the swap partition ? can you paste the /proc/meminfo ? Also turn on log_checkpoint if it is not already and check the duration to write the data. You didn't said the DB size (and size of active part of it), it would help here. > > HDD: 2x 120 GB OCZ Vertex 2 SSD; RAID 1 > > Concurrent connections (according to our monitoring tool): 7 (min), 74 > (avg), 197 (max) > > Our config (all other settings at default value): > > max_connections = 200 > ssl = true > shared_buffers = 4096MB > work_mem = 256MB it is too much with 200 connections. you may experiment case where you try to use more than the memory available. see http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server --> work_mem maintainance_work_mem > maintenance_work_mem = 512MB 128MB is usualy enough > synchronous_commit = off > wal_buffers = 8MB 16MB should work well > checkpoint_segments = 30 > checkpoint_timeout = 15min > checkpoint_completion_target = 0.9 > random_page_cost = 2.0 > effective_cache_size = 8192MB 12-14GB looks better > logging_collector = on > log_directory = '/var/log/postgresql' > log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' > log_min_duration_statement = 1000 > log_connections = on > log_disconnections = on > log_line_prefix = '%t ' > datestyle = 'iso, mdy' > gin_fuzzy_search_limit = 10000 you use full_text_search ? > > The config options are a mix of the article "Configuring PostgreSQL > for Pretty Good Performance" [1] and the talk "PostgreSQL as a secret > weapon for high-performance Ruby on Rails applications" [2]. do you monitor the 'locks' ? and the commit/rollbacks ? > > Thanks, > Michael > > [1] http://www.linux.com/learn/tutorials/394523-configuring-postgresql-for-pretty-good-performance > [2] http://www.pgcon.org/2010/schedule/events/210.en.html > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ ; PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
