[PERFORM] High load,
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 sharedbuffers cached Mem: 16461012 16399520 61492 0 72392 12546112 -/+ buffers/cache:3781016 12679996 Swap: 92 195336 804656 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 maintenance_work_mem = 512MB synchronous_commit = off wal_buffers = 8MB checkpoint_segments = 30 checkpoint_timeout = 15min checkpoint_completion_target = 0.9 random_page_cost = 2.0 effective_cache_size = 8192MB 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 = 1 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]. 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
Re: [PERFORM] High load,
Cédric, thanks a lot for your answer so far!
On Thu, Jan 27, 2011 at 12:24 PM, Cédric Villemain
wrote:
> you have swap used, IO on the swap partition ?
Memory-wise we are fine.
> can you paste the /proc/meminfo ?
Sure:
# cat /proc/meminfo
MemTotal: 16461012 kB
MemFree: 280440 kB
Buffers: 60984 kB
Cached: 13757080 kB
SwapCached: 6112 kB
Active: 7049744 kB
Inactive:7716308 kB
Active(anon):2743696 kB
Inactive(anon): 2498056 kB
Active(file):4306048 kB
Inactive(file): 5218252 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal:92 kB
SwapFree: 989496 kB
Dirty: 3500 kB
Writeback: 0 kB
AnonPages:943752 kB
Mapped: 4114916 kB
Shmem: 4293312 kB
Slab: 247036 kB
SReclaimable: 212788 kB
SUnreclaim:34248 kB
KernelStack:3144 kB
PageTables: 832768 kB
NFS_Unstable: 0 kB
Bounce:0 kB
WritebackTmp: 0 kB
CommitLimit: 9230496 kB
Committed_AS:5651528 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 51060 kB
VmallocChunk: 34350787468 kB
HardwareCorrupted: 0 kB
HugePages_Total: 0
HugePages_Free:0
HugePages_Rsvd:0
HugePages_Surp:0
Hugepagesize: 2048 kB
DirectMap4k:7936 kB
DirectMap2M:16760832 kB
> Also turn on log_checkpoint if it is not already and check the
> duration to write the data.
Will do, thanks!
> You didn't said the DB size (and size of active part of it), it would help
> here.
=> select pg_size_pretty(pg_database_size('xxx'));
pg_size_pretty
32 GB
(1 row)
> it is too much with 200 connections. you may experiment case where you
> try to use more than the memory available.
So far memory never really was a problem, but I'll keep these
suggestions in mind.
> 16MB should work well
We already thought of increasing that, will do so now.
>> effective_cache_size = 8192MB
>
> 12-14GB looks better
Thank you, I was rather unsure on this on.
> you use full_text_search ?
Not anymore, probably a leftover.
> do you monitor the 'locks' ? and the commit/rollbacks ?
No, but I'll look into doing that.
Thanks a lot for the feedback again,
Michael
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High load,
On Thu, Jan 27, 2011 at 1:30 PM, Justin Pitts wrote: > That is a foot-gun waiting to go off. Thanks, I had already changed this after Cedric's mail. >> HDD: 2x 120 GB OCZ Vertex 2 SSD; RAID 1 >> random_page_cost = 2.0 > I thought these drives were a lot better at random IO than this gives > them credit for. I'll look into that. Thanks a lot, Michael -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High load,
On Thu, Jan 27, 2011 at 4:06 PM, Andy Colson wrote: > Have you run each of your queries through explain analyze lately? A code review including checking of queries is on our agenda. > You are vacuuming/autovacuuming, correct? Sure :-) Thank you, Michael -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High load,
On Thu, Jan 27, 2011 at 6:05 PM, Scott Marlowe wrote: > A good method to start is to log long running queries and then explain > analyze just them. We are already doing the logging part, we are just a bit behind on the "explain analyze" part of things. One day soon... Thanks, Michael -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
