Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux