Skip site navigation (1) Skip section navigation (2)

Re: Occasional giant spikes in CPU load

From: Craig James <craig_james(at)emolecules(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Occasional giant spikes in CPU load
Date: 2010-06-25 00:50:26
Message-ID: 4C23FD52.40508@emolecules.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Joshua D. DrakeDate: 2010-06-25 01:01:40
Subject: Re: Occasional giant spikes in CPU load
Previous:From: Alvaro HerreraDate: 2010-06-24 23:55:01
Subject: Re: System tables screwed up? (WAS requested shared memory size overflows size_t)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group