General performance/load issue

From: Gaëtan Allart <gaetan(at)nexylan(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: General performance/load issue
Date: 2011-11-24 13:51:07
Message-ID: CAF40A58.12F64%gaetan@nexylan.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 :

21537 postgres 20 0 6420m 899m 892m D 22 1.9 0:01.86 postgres


21534 postgres 20 0 6554m 1.3g 1.1g D 14 2.7 0:01.72 postgres


21518 postgres 20 0 6419m 2.1g 2.1g D 10 4.6 0:02.10 postgres


21038 postgres 20 0 6716m 3.8g 3.5g D 10 8.0 0:45.46 postgres


21103 postgres 20 0 6571m 3.7g 3.6g D 9 7.8 0:32.19 postgres


21079 postgres 20 0 6575m 3.8g 3.7g D 8 8.1 0:36.39 postgres


21359 postgres 20 0 6563m 1.9g 1.8g D 4 4.1 0:09.10 postgres


21422 postgres 20 0 6563m 1.9g 1.7g D 4 3.9 0:08.34 postgres


19656 postgres 20 0 6727m 5.3g 5.0g D 3 11.1 1:58.25 postgres


21418 postgres 20 0 6685m 2.1g 1.9g D 2 4.5 0:12.42 postgres


21413 postgres 20 0 6693m 2.1g 1.8g D 2 4.4 0:11.06 postgres


21541 postgres 20 0 6421m 719m 711m D 1 1.5 0:00.48 postgres


14044 postgres 20 0 6418m 755m 750m D 1 1.6 0:04.71 postgres


21326 postgres 20 0 6685m 2.2g 2.0g D 1 4.7 0:15.82 postgres


21031 postgres 20 0 6688m 3.6g 3.4g D 1 7.7 0:44.18 postgres


21055 postgres 20 0 6575m 4.1g 3.9g D 1 8.6 0:39.11 postgres


21357 postgres 20 0 6693m 2.3g 2.0g D 1 4.9 0:13.74 postgres

Memory when overloaded :

~ # free -m
total used free shared buffers cached
Mem: 48339 47087 1251 0 248 38720
-/+ buffers/cache: 8118 40220
Swap: 8190 1 8189

Postgresql.conf :

max_connections = 50
shared_buffers = 12G
temp_buffers = 40MB
work_mem = 128MB
maintenance_work_mem = 256MB
effective_cache_size = 12GB
max_files_per_process = 8192
fsync = off
checkpoint_segments = 256
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9
seq_page_cost = 2.0
random_page_cost = 2.0

Did I do anything wrong? Any idea?

Regards,

Gaëtan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gavin Casey 2011-11-24 13:52:16 Reassign value of IN parameter in 9.1.1
Previous Message Andrus 2011-11-24 13:47:12 How to get normalized data from tekst column