Re: Occasional giant spikes in CPU load

I'm reviving this question because I never figured it out. To summarize: At random intervals anywhere from a few times per hour 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 a minute 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 generate images from data in the database. Even though there are a lot of these, they don't do much. They sit there most of the time, then they spew out a couple dozen GIF images in about one second as a user gets a new page of data. Each GIF 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, and takes from a fraction of a second to a few minutes to complete. In this particular instance, immediately before this spike, 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 load on 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