Re: High load, - Mailing list pgsql-performance

2011/1/27 Michael Kohl <michael.kohl@tupalo.com>:
> 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@postgresql.org)
> 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