Re: Occasional giant spikes in CPU load - Mailing list pgsql-performance
| From | Craig James |
|---|---|
| Subject | Re: Occasional giant spikes in CPU load |
| Date | |
| Msg-id | 4C23FD52.40508@emolecules.com Whole thread Raw |
| In response to | Re: Occasional giant spikes in CPU load (Robert Haas <robertmhaas@gmail.com>) |
| Responses |
Re: Occasional giant spikes in CPU load
Re: Occasional giant spikes in CPU load Re: Occasional giant spikes in CPU load Re: Occasional giant spikes in CPU load |
| List | pgsql-performance |
I'm reviving this question because I never figured it out. To summarize: At random intervals anywhere from a few times perhour to once or twice a day, we see a huge spike in CPU load that essentially brings the system to a halt for up to aminute or two. Previous answers focused on "what is it doing", i.e. is it really Postgres or something else? Now the question has narrowed down to this: what could trigger EVERY postgres backend to do something at the same time? See the attached output from "top -b", which shows what is happening during one of the CPU spikes. A little background about our system. We have roughly 100 FastCGI clients connected at all times that are called on to generateimages from data in the database. Even though there are a lot of these, they don't do much. They sit there mostof the time, then they spew out a couple dozen GIF images in about one second as a user gets a new page of data. EachGIF image requires fetching a single row using a single indexed column, so it's a trival amount of work for Postgres. We also have the "heavy lift" application that does the search. Typically one or two of these is running at a time, andtakes from a fraction of a second to a few minutes to complete. In this particular instance, immediately before thisspike, the CPU load was only at about 10% -- a couple users poking around with easy queries. So what is it that will cause every single Postgres backend to come to life at the same moment, when there's no real loadon the server? Maybe if a backend crashes? Some other problem? There's nothing in the serverlog. Thanks, Craig top - 12:15:09 up 81 days, 21:18, 4 users, load average: 0.38, 0.38, 0.73 Tasks: 374 total, 95 running, 279 sleeping, 0 stopped, 0 zombie Cpu(s): 62.5%us, 2.2%sy, 0.0%ni, 34.9%id, 0.2%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 8194800k total, 7948928k used, 245872k free, 36k buffers Swap: 2031608k total, 161136k used, 1870472k free, 7129744k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 22120 postgres 20 0 2514m 17m 13m R 11 0.2 0:01.02 postmaster 18497 postgres 20 0 2514m 11m 8832 R 6 0.1 0:00.62 postmaster 22962 postgres 20 0 2514m 12m 9548 R 6 0.2 0:00.22 postmaster 24002 postgres 20 0 2514m 11m 8804 R 6 0.1 0:00.15 postmaster 25900 postgres 20 0 2514m 11m 8824 R 6 0.1 0:00.55 postmaster 8941 postgres 20 0 2324m 6172 4676 R 5 0.1 0:00.32 postmaster 10622 postgres 20 0 2514m 12m 9444 R 5 0.2 0:00.79 postmaster 14021 postgres 20 0 2514m 11m 8548 R 5 0.1 0:00.28 postmaster 14075 postgres 20 0 2514m 11m 8672 R 5 0.1 0:00.27 postmaster 14423 postgres 20 0 2514m 11m 8572 R 5 0.1 0:00.29 postmaster 18896 postgres 20 0 2324m 5644 4204 R 5 0.1 0:00.11 postmaster 18897 postgres 20 0 2514m 12m 9800 R 5 0.2 0:00.27 postmaster 18928 postgres 20 0 2514m 11m 8792 R 5 0.1 0:00.18 postmaster 18973 postgres 20 0 2514m 11m 8792 R 5 0.1 0:00.70 postmaster 22049 postgres 20 0 2514m 17m 14m R 5 0.2 0:01.11 postmaster 22050 postgres 20 0 2514m 16m 13m R 5 0.2 0:01.06 postmaster 22843 postgres 20 0 2514m 12m 9328 R 5 0.2 0:00.20 postmaster 24202 postgres 20 0 2324m 5560 4120 R 5 0.1 0:00.07 postmaster 24388 postgres 20 0 2514m 12m 9380 R 5 0.2 0:00.16 postmaster 25903 postgres 20 0 2514m 11m 8828 R 5 0.1 0:00.55 postmaster 28362 postgres 20 0 2514m 11m 8952 R 5 0.1 0:00.48 postmaster 5667 postgres 20 0 2324m 6752 5588 R 4 0.1 0:08.93 postmaster 7531 postgres 20 0 2324m 5452 4008 R 4 0.1 0:03.21 postmaster 9219 postgres 20 0 2514m 11m 8476 R 4 0.1 0:00.89 postmaster 9820 postgres 20 0 2514m 12m 9.9m R 4 0.2 0:00.92 postmaster 10050 postgres 20 0 2324m 6172 4676 R 4 0.1 0:00.31 postmaster 10645 postgres 20 0 2514m 12m 9512 R 4 0.2 0:00.72 postmaster 14582 postgres 20 0 2514m 25m 21m R 4 0.3 0:02.10 postmaster 18502 postgres 20 0 2514m 11m 9040 R 4 0.1 0:00.64 postmaster 18972 postgres 20 0 2514m 11m 8792 R 4 0.1 0:00.76 postmaster 18975 postgres 20 0 2514m 11m 8904 R 4 0.1 0:00.63 postmaster 19496 postgres 20 0 2514m 14m 11m R 4 0.2 0:00.44 postmaster 22121 postgres 20 0 2514m 16m 13m R 4 0.2 0:00.81 postmaster 24340 postgres 20 0 2514m 12m 9424 R 4 0.2 0:00.15 postmaster 24483 postgres 20 0 2324m 6008 4536 R 4 0.1 0:00.21 postmaster 25668 postgres 20 0 2514m 16m 13m R 4 0.2 0:00.91 postmaster 26382 postgres 20 0 2514m 11m 8996 R 4 0.1 0:00.50 postmaster 28363 postgres 20 0 2514m 11m 8908 R 4 0.1 0:00.34 postmaster 9754 postgres 20 0 2514m 11m 8752 R 3 0.1 0:00.29 postmaster 16113 postgres 20 0 2514m 17m 14m R 3 0.2 0:01.10 postmaster 18498 postgres 20 0 2514m 11m 8844 R 3 0.1 0:00.63 postmaster 18500 postgres 20 0 2514m 11m 8812 R 3 0.1 0:00.66 postmaster 22116 postgres 20 0 2514m 17m 13m R 3 0.2 0:01.05 postmaster 22287 postgres 20 0 2324m 6072 4596 R 3 0.1 0:00.24 postmaster 22425 postgres 20 0 2514m 17m 14m R 3 0.2 0:01.02 postmaster 22827 postgres 20 0 2514m 13m 10m R 3 0.2 0:00.43 postmaster 23285 postgres 20 0 2514m 13m 10m R 3 0.2 0:00.40 postmaster 24384 postgres 20 0 2514m 12m 9300 R 3 0.2 0:00.15 postmaster 30501 postgres 20 0 2514m 11m 9012 R 3 0.1 0:00.47 postmaster 5665 postgres 20 0 2324m 6528 5396 R 2 0.1 0:08.71 postmaster 5671 postgres 20 0 2324m 6720 5596 R 2 0.1 0:08.73 postmaster 7428 postgres 20 0 2324m 6176 4928 R 2 0.1 0:07.37 postmaster 7431 postgres 20 0 2324m 6140 4920 R 2 0.1 0:07.40 postmaster 7433 postgres 20 0 2324m 6372 4924 R 2 0.1 0:07.29 postmaster 7525 postgres 20 0 2324m 5468 4024 R 2 0.1 0:03.36 postmaster 7530 postgres 20 0 2324m 5452 4008 R 2 0.1 0:03.40 postmaster 7532 postgres 20 0 2324m 5440 3996 R 2 0.1 0:03.23 postmaster 7533 postgres 20 0 2324m 5484 4040 R 2 0.1 0:03.25 postmaster 8944 postgres 20 0 2514m 26m 23m R 2 0.3 0:02.16 postmaster 8946 postgres 20 0 2514m 26m 22m R 2 0.3 0:02.06 postmaster 9821 postgres 20 0 2514m 12m 9948 R 2 0.2 0:00.93 postmaster 10051 postgres 20 0 2514m 13m 10m R 2 0.2 0:01.03 postmaster 10226 postgres 20 0 2514m 27m 23m R 2 0.3 0:02.24 postmaster 10626 postgres 20 0 2514m 12m 9212 R 2 0.1 0:00.83 postmaster 14580 postgres 20 0 2324m 6120 4632 R 2 0.1 0:00.27 postmaster 16112 postgres 20 0 2514m 18m 14m R 2 0.2 0:01.18 postmaster 19450 postgres 20 0 2324m 6108 4620 R 2 0.1 0:00.22 postmaster 22289 postgres 20 0 2514m 22m 19m R 2 0.3 0:01.66 postmaster 5663 postgres 20 0 2324m 6700 5576 R 1 0.1 0:08.23 postmaster 7526 postgres 20 0 2324m 5444 4000 R 1 0.1 0:03.44 postmaster 7528 postgres 20 0 2324m 5444 4000 R 1 0.1 0:03.44 postmaster 7529 postgres 20 0 2324m 5420 3976 R 1 0.1 0:03.04 postmaster 8888 postgres 20 0 2514m 25m 22m R 1 0.3 0:02.01 postmaster 9622 postgres 20 0 2514m 13m 10m R 1 0.2 0:01.08 postmaster 9625 postgres 20 0 2514m 13m 10m R 1 0.2 0:01.00 postmaster 14686 postgres 20 0 2324m 6116 4628 R 1 0.1 0:00.30 postmaster 14687 postgres 20 0 2514m 24m 21m R 1 0.3 0:01.95 postmaster 16111 postgres 20 0 2514m 17m 14m R 1 0.2 0:01.01 postmaster 16854 postgres 20 0 2324m 5468 4024 R 1 0.1 0:03.31 postmaster 5664 postgres 20 0 2324m 6740 5584 R 0 0.1 0:08.45 postmaster 5666 postgres 20 0 2324m 6744 5584 R 0 0.1 0:08.70 postmaster 5668 postgres 20 0 2324m 6720 5588 R 0 0.1 0:08.58 postmaster 5670 postgres 20 0 2324m 6748 5584 R 0 0.1 0:08.99 postmaster 5672 postgres 20 0 2324m 6764 5596 R 0 0.1 0:08.30 postmaster 7429 postgres 20 0 2324m 6000 4760 R 0 0.1 0:07.41 postmaster 7430 postgres 20 0 2324m 6080 4928 R 0 0.1 0:07.09 postmaster 7463 postgres 20 0 2324m 6412 4928 R 0 0.1 0:07.14 postmaster 7538 postgres 20 0 2324m 5472 4028 R 0 0.1 0:03.42 postmaster 8887 postgres 20 0 2324m 6184 4680 R 0 0.1 0:00.23 postmaster 8942 postgres 20 0 2514m 26m 22m R 0 0.3 0:01.97 postmaster 10636 postgres 20 0 2514m 12m 9380 R 0 0.2 0:00.75 postmaster 10640 postgres 20 0 2514m 11m 9148 R 0 0.1 0:00.75 postmaster 15687 postgres 20 0 2321m 35m 35m R 0 0.4 8:38.38 postmaster
pgsql-performance by date: