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
