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

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