100% cpu usage on some postmaster processes kill the complete database

[Paul Dunkler <paul.dunkler@xxxxxxxxxxxx>]

Hi List,

we are currently running a rather large postgresql-installation with approximately 4k Transactions and 50k index scans per second.

In the last days on some times of the day (irregular - 3-4 times a day), some of the postmaster processes are running with 100% cpu usage. That leads to a totally breakdown of the query execution. We see tons of statements which are correctly automatically aborted by our statement_timeout set to 15 seconds. I tried to search, but do not really recognize what the problem could be there...

Some things i have checked:

- We are not running any bulk jobs or maintenance scripts at this time

- No system errors in any logs during that slowdowns

- I/O Performance seems fine. No high IO Wait amount... But IO Write totally drops in that times because it seems that no postgres process can perform any update

I just installed a script, which prints me out the top and ps axf information for facing out the problem. I will post a snippet of the top here:

top - 15:55:02 up 59 days, 37 min,  1 user,  load average: 35.95, 14.04, 7.32

Tasks: 2417 total,  54 running, 2363 sleeping,   0 stopped,   0 zombie

Cpu(s):  6.3%us,  1.0%sy,  0.0%ni, 90.2%id,  1.9%wa,  0.0%hi,  0.6%si,  0.0%st

Mem:  264523700k total, 250145228k used, 14378472k free,   207032k buffers

Swap:  2097144k total,   553624k used,  1543520k free, 166905748k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND            

29852 postgres  20   0  131g  59m  35m R 100.0  0.0   1:27.71 postmaster        

29854 postgres  20   0  131g  70m  45m R 100.0  0.0   1:35.43 postmaster        

17449 postgres  20   0  131g 1.2g 1.2g R 100.0  0.5   1:52.62 postmaster        

29868 postgres  20   0  131g 1.1g 1.0g R 100.0  0.4   1:58.93 postmaster        

30136 postgres  20   0  131g  77m  52m R 100.0  0.0   1:34.33 postmaster        

30294 postgres  20   0  131g  66m  41m R 100.0  0.0   1:33.33 postmaster        

30864 postgres  20   0  131g  66m  41m R 100.0  0.0   1:36.17 postmaster        

30872 postgres  20   0  131g  61m  36m R 100.0  0.0   1:26.81 postmaster        

30876 postgres  20   0  131g  68m  43m R 100.0  0.0   1:33.97 postmaster        

30899 postgres  20   0  131g  68m  44m R 100.0  0.0   1:38.95 postmaster        

30906 postgres  20   0  131g  67m  42m R 100.0  0.0   1:27.82 postmaster        

31173 postgres  20   0  131g  68m  44m R 100.0  0.0   1:28.49 postmaster        

31239 postgres  20   0  131g  71m  46m R 100.0  0.0   1:31.42 postmaster        

31248 postgres  20   0  131g  90m  65m R 100.0  0.0   1:26.20 postmaster        

34934 postgres  20   0  131g 5580 3456 R 100.0  0.0   1:23.96 postmaster        

47945 postgres  20   0  131g 3.0g 3.0g R 100.0  1.2   6:08.41 postmaster        

16116 postgres  20   0  131g  84m  59m R 100.0  0.0   1:30.60 postmaster        

16304 postgres  20   0  131g  85m  60m R 100.0  0.0   1:38.89 postmaster        

17104 postgres  20   0  131g  96m  72m R 100.0  0.0   1:27.54 postmaster        

17111 postgres  20   0  131g  98m  73m R 100.0  0.0   1:38.23 postmaster        

17320 postgres  20   0  131g  98m  74m R 100.0  0.0   1:38.51 postmaster        

31221 postgres  20   0  131g  63m  38m R 100.0  0.0   1:33.63 postmaster        

31272 postgres  20   0  131g 1.0g 1.0g R 100.0  0.4   1:32.71 postmaster        

 3290 postgres  20   0  131g  99m  74m R 100.0  0.0   1:32.76 postmaster        

 3459 postgres  20   0  131g 2.1g 2.0g R 100.0  0.8   1:44.92 postmaster        

16492 postgres  20   0  131g 100m  75m R 100.0  0.0   1:33.36 postmaster        

16562 postgres  20   0  131g 114m  89m R 100.0  0.0   1:35.14 postmaster        

17146 postgres  20   0  131g  91m  66m R 100.0  0.0   1:37.39 postmaster        

17403 postgres  20   0  131g  98m  73m R 100.0  0.0   1:32.13 postmaster        

31100 postgres  20   0  131g  62m  38m R 100.0  0.0   1:29.06 postmaster        

 2019 postgres  20   0  131g 1.2g 1.2g R 98.7  0.5   1:40.91 postmaster         

 2150 postgres  20   0  131g 1.3g 1.3g R 98.7  0.5   2:53.14 postmaster         

16048 postgres  20   0  131g  71m  46m R 98.7  0.0   1:29.75 postmaster         

30190 postgres  20   0  131g 1.4g 1.3g R 98.7  0.5   0:55.98 postmaster         

16112 postgres  20   0  131g 862m 827m R 97.1  0.3   0:48.00 postmaster         

31202 postgres  20   0  131g  74m  49m R 97.1  0.0   1:34.62 postmaster         

35658 postgres  20   0  131g 5948 3788 R 97.1  0.0   0:12.29 postmaster         

16134 postgres  20   0  131g 1.9g 1.9g R 95.4  0.8   1:47.27 postmaster         

31034 postgres  20   0  131g  69m  44m R 95.4  0.0   1:26.35 postmaster         

16120 postgres  20   0  131g 1.2g 1.2g R 93.8  0.5   2:04.02 postmaster         

30891 postgres  20   0  131g  57m  33m R 93.8  0.0   1:23.08 postmaster         

31261 postgres  20   0  131g  81m  56m R 93.8  0.0   1:24.51 postmaster         

29790 postgres  20   0  131g  62m  37m R 92.2  0.0   1:35.34 postmaster         

30426 postgres  20   0  131g  62m  37m R 87.4  0.0   1:34.51 postmaster         

30857 postgres  20   0  131g  50m  26m R 79.3  0.0   1:37.82 postmaster         

  507 root      39  19     0    0    0 R 67.9  0.0  19:19.71 khugepaged         

16095 postgres  20   0  131g  83m  58m R 67.9  0.0   1:27.64 postmaster         

30856 postgres  20   0  131g  69m  44m R 67.9  0.0   1:34.46 postmaster         

17442 postgres  20   0  131g 2.4g 2.4g S 11.3  0.9   1:02.14 postmaster         

Postgresql Version information:

- PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit

- Running Hot Replication to another node (same hardware setup there)

Server Hardware:

- 4x 12 Core AMD Magny cours

- 256 GB of RAM (36% currently used)

- 1,3 TB SAS Raid (LSI Raid controller) - 15k rpm

If i lost to include some important informations for you analyzing my problem, let me please know. I did my best to post the question as accurate as possible for me.

--
Mit freundlichen Grüßen

Paul Dunkler