Hi Thomas, I will be using iotop ;) Right now, most i/o come from "postgres: wal writer process". -> effective_cache_size Okay, I'll rise it to 32Gb. -> fsync : changed to on ;) -> seq_pages : i'll run tests. Thanks. -> dirty : cat /proc/sys/vm/dirty_ratio 20 cat /proc/sys/vm/dirty_background_ratio10 Thanks a lot Tomas. You're really helpful! Gaëtan Le 24/11/11 15:09, « Tomas Vondra » <tv@xxxxxxxx> a écrit : >On 24 Listopad 2011, 14:51, Gaëtan Allart wrote: >> Hello everyone, >> >> I'm having some troubles with a Postgresql server. >> We're using PG has a database backend for a very big website (lots of >>data >> and much traffic). >> >> The issue : server suddenly (1H after restart) becomes slow (queries not >> responding), load rises (>20 instead of 1), iowait rises (20 to 70%) >> >> Version : 9.0.5 >> Server : Dual Xeon X5650 (24 cores total) >> Memory : 48 GB >> Disks : SSD >> >> >> Top when overloaded : > >Top is not the most useful tool here, I guess. Use "iotop" (will show you >which processes are doing the I/O) and tools like vmstat / iostat. > >> Postgresql.conf : >> >> max_connections = 50 >> shared_buffers = 12G >> temp_buffers = 40MB >> work_mem = 128MB >> maintenance_work_mem = 256MB >> max_files_per_process = 8192 >> checkpoint_segments = 256 >> checkpoint_timeout = 30min >> checkpoint_completion_target = 0.9 > >Fine. Let's see the options that look suspicious. > >> effective_cache_size = 12GB > >Why have you set it like this? According to the "free" output you've >posted the cache has about 38G, so why just 12G here? There are possible >reasons, but I don't think this is the case. > >> fsync = off > >A really bad idea. I guess your data are worthless to you, right? > >> seq_page_cost = 2.0 >> random_page_cost = 2.0 > >Eh? First of all, what really matters is the relative value of those two >values, and it's good habit to leave seq_page_cost = 1.0 and change just >the other values. > >Plus the random I/O is not as cheap as sequential I/O even on SSD drives, >so I't recommend something like this: > >seq_page_cost = 1.0 >random_page_cost = 2.0 (or maybe 1.5) > >Anyway this needs to be tested properly - watch the performance and tune >if needed. > >> Did I do anything wrong? Any idea? > >Not sure. My guess is you're getting bitten by a checkpoint. We need to >know a few more details. > >1) What is dirty_background_ratio / dirty_ratio (see /proc/sys/vm/ >directory) > >2) enable log_checkpoints in postgresql.conf and see how it correlates to >the bad performance > >3) check which processes are responsible for the I/O (use iotop) > >Tomas -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
