pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOccasional giant spikes in CPU load
31+ messages / 11 participants
[nested] [flat]
* Occasional giant spikes in CPU load
@ 2010-04-07 21:37 Craig James <craig_james@emolecules.com>
0 siblings, 4 replies; 31+ messages in thread
From: Craig James @ 2010-04-07 21:37 UTC (permalink / raw)
To: pgsql-performance
Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh).
I managed to capture one such event using top(1) with the "batch" option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike.
For some reason, every postgres backend suddenly decides (is told?) to do something. When this happens, the system become unusable for anywhere from ten seconds to a minute or so, depending on how much web traffic stacks up behind this event. We have two servers, one offline and one public, and they both do this, so it's not caused by actual web traffic (and the Apache logs don't show any HTTP activity correlated with the spikes).
I thought based on other posts that this might be a background-writer problem, but it's not I/O, it's all CPU as far as I can tell.
Any ideas where I can look to find what's triggering this?
8 CPUs, 8 GB memory
8-disk RAID10 (10k SATA)
Postgres 8.3.0
Fedora 8, kernel is 2.6.24.4-64.fc8
Diffs from original postgres.conf:
max_connections = 1000
shared_buffers = 2000MB
work_mem = 256MB
max_fsm_pages = 16000000
max_fsm_relations = 625000
synchronous_commit = off
wal_buffers = 256kB
checkpoint_segments = 30
effective_cache_size = 4GB
escape_string_warning = off
Thanks,
Craig
top - 11:24:59 up 81 days, 20:27, 4 users, load average: 0.98, 0.83, 0.92
Tasks: 366 total, 20 running, 346 sleeping, 0 stopped, 0 zombie
Cpu(s): 30.6%us, 1.5%sy, 0.0%ni, 66.3%id, 1.5%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8194800k total, 8118688k used, 76112k free, 36k buffers
Swap: 2031608k total, 169348k used, 1862260k free, 7313232k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
18972 postgres 20 0 2514m 11m 8752 R 11 0.1 0:00.35 postmaster
10618 postgres 20 0 2514m 12m 9456 R 9 0.2 0:00.54 postmaster
10636 postgres 20 0 2514m 11m 9192 R 9 0.1 0:00.45 postmaster
25903 postgres 20 0 2514m 11m 8784 R 9 0.1 0:00.21 postmaster
10626 postgres 20 0 2514m 11m 8716 R 6 0.1 0:00.45 postmaster
10645 postgres 20 0 2514m 12m 9352 R 6 0.2 0:00.42 postmaster
10647 postgres 20 0 2514m 11m 9172 R 6 0.1 0:00.51 postmaster
18502 postgres 20 0 2514m 11m 9016 R 6 0.1 0:00.23 postmaster
10641 postgres 20 0 2514m 12m 9296 R 5 0.2 0:00.36 postmaster
10051 postgres 20 0 2514m 13m 10m R 4 0.2 0:00.70 postmaster
10622 postgres 20 0 2514m 12m 9216 R 4 0.2 0:00.39 postmaster
10640 postgres 20 0 2514m 11m 8592 R 4 0.1 0:00.52 postmaster
18497 postgres 20 0 2514m 11m 8804 R 4 0.1 0:00.25 postmaster
18498 postgres 20 0 2514m 11m 8804 R 4 0.1 0:00.22 postmaster
10341 postgres 20 0 2514m 13m 9m R 2 0.2 0:00.57 postmaster
10619 postgres 20 0 2514m 12m 9336 R 1 0.2 0:00.38 postmaster
15687 postgres 20 0 2321m 35m 35m R 0 0.4 8:36.12 postmaster
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-07 21:40 Joshua D. Drake <jd@commandprompt.com>
parent: Craig James <craig_james@emolecules.com>
3 siblings, 1 reply; 31+ messages in thread
From: Joshua D. Drake @ 2010-04-07 21:40 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
On Wed, 2010-04-07 at 14:37 -0700, Craig James wrote:
> Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh).
>
> I managed to capture one such event using top(1) with the "batch" option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike.
What does iostat 5 say during the jump?
Joshua D. Drake
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-07 21:45 Craig James <craig_james@emolecules.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 31+ messages in thread
From: Craig James @ 2010-04-07 21:45 UTC (permalink / raw)
To: jd@commandprompt.com; +Cc: pgsql-performance
On 4/7/10 2:40 PM, Joshua D. Drake wrote:
> On Wed, 2010-04-07 at 14:37 -0700, Craig James wrote:
>> Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh).
>>
>> I managed to capture one such event using top(1) with the "batch" option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike.
>
> What does iostat 5 say during the jump?
It's very hard to say ... I'll have to start a background job to watch for a day or so. While it's happening, you can't login, and any open windows become unresponsive. I'll probably have to run it at high priority using nice(1) to get any data at all during the event.
Would vmstat be informative?
Thanks,
Craig
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-07 21:54 Greg Smith <greg@2ndquadrant.com>
parent: Craig James <craig_james@emolecules.com>
3 siblings, 0 replies; 31+ messages in thread
From: Greg Smith @ 2010-04-07 21:54 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James wrote:
> I managed to capture one such event using top(1) with the "batch"
> option as a background process. See output below
You should add "-c" to your batch top capture, then you'll be able to
see what the individual postmaster processes are actually doing when
things get stuck.
> max_connections = 1000
> shared_buffers = 2000MB
> work_mem = 256MB
> Mem: 8194800k total, 8118688k used, 76112k free, 36k buffers
> Swap: 2031608k total, 169348k used, 1862260k free, 7313232k cached
These settings appear way too high for a server with 8GB of RAM. I'm
not sure if max_connections is too large, or if it's work_mem that's too
big, but one or both of them may need to be tuned way down from where
they are now to get your memory usage under control. Your server might
running out of RAM during the periods where it becomes
unresponsive--that could be the system paging stuff out to swap, which
isn't necessarily a high user of I/O but it will block things. Not
having any memory used for buffers is never a good sign.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-07 21:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig James <craig_james@emolecules.com>
3 siblings, 1 reply; 31+ messages in thread
From: Tom Lane @ 2010-04-07 21:59 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh).
> I managed to capture one such event using top(1) with the "batch" option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike.
> Any ideas where I can look to find what's triggering this?
> Postgres 8.3.0
^^^^^
If it's really 8.3.0, try updating to 8.3.something-recent. We've fixed
a whole lot of bugs since then.
I have a suspicion that this might be an sinval overrun scenario, in
which case you'd need to update to 8.4 to get a real fix. But updating
in the 8.3 branch would be cheap and easy.
If it is sinval overrun, it would presumably be triggered by a whole lot
of catalog changes being made at approximately the same time. Can you
correlate the spikes with anything like that?
regards, tom lane
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-07 22:36 Joshua D. Drake <jd@commandprompt.com>
parent: Craig James <craig_james@emolecules.com>
0 siblings, 1 reply; 31+ messages in thread
From: Joshua D. Drake @ 2010-04-07 22:36 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
On Wed, 2010-04-07 at 14:45 -0700, Craig James wrote:
> On 4/7/10 2:40 PM, Joshua D. Drake wrote:
> > On Wed, 2010-04-07 at 14:37 -0700, Craig James wrote:
> >> Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh).
> >>
> >> I managed to capture one such event using top(1) with the "batch" option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike.
> >
> > What does iostat 5 say during the jump?
>
> It's very hard to say ... I'll have to start a background job to watch for a day or so. While it's happening, you can't login, and any open windows become unresponsive. I'll probably have to run it at high priority using nice(1) to get any data at all during the event.
Do you have sar runing? Say a sar -A ?
>
> Would vmstat be informative?
Yes.
My guess is that it is not CPU, it is IO and your CPU usage is all WAIT
on IO.
To have your CPUs so flooded that they are the cause of an inability to
log in is pretty suspect.
Joshua D. Drake
>
> Thanks,
> Craig
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-07 22:56 David Rees <drees76@gmail.com>
parent: Craig James <craig_james@emolecules.com>
3 siblings, 2 replies; 31+ messages in thread
From: David Rees @ 2010-04-07 22:56 UTC (permalink / raw)
To: pgsql-performance
On Wed, Apr 7, 2010 at 2:37 PM, Craig James <craig_james@emolecules.com> wrote:
> Most of the time Postgres runs nicely, but two or three times a day we get a
> huge spike in the CPU load that lasts just a short time -- it jumps to 10-20
> CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike
> events. During these spikes, the system is completely unresponsive (you
> can't even login via ssh).
You need to find out what all those Postgres processes are doing. You
might try enabling update_process_title and then using ps to figure
out what each instance is using. Otherwise, you might try enabling
logging of commands that take a certain amount of time to run (see
log_min_duration_statement).
> I managed to capture one such event using top(1) with the "batch" option as
> a background process. See output below - it shows 19 active postgress
> processes, but I think it missed the bulk of the spike.
Looks like it. The system doesn't appear to be overloaded at all at that point.
> 8 CPUs, 8 GB memory
> 8-disk RAID10 (10k SATA)
> Postgres 8.3.0
Should definitely update to the latest 8.3.10 - 8.3 has a LOT of known bugs.
> Fedora 8, kernel is 2.6.24.4-64.fc8
Wow, that is very old, too.
> Diffs from original postgres.conf:
>
> max_connections = 1000
> shared_buffers = 2000MB
> work_mem = 256MB
work_mem is way too high for 1000 connections and 8GB ram. You could
simply be starting up too many postgres processes and overwhelming the
machine. Either significantly reduce max_connections or work_mem.
> max_fsm_pages = 16000000
> max_fsm_relations = 625000
> synchronous_commit = off
You are playing with fire here. You should never turn this off unless
you do not care if your data becomes irrecoverably corrupted.
> top - 11:24:59 up 81 days, 20:27, 4 users, load average: 0.98, 0.83, 0.92
> Tasks: 366 total, 20 running, 346 sleeping, 0 stopped, 0 zombie
> Cpu(s): 30.6%us, 1.5%sy, 0.0%ni, 66.3%id, 1.5%wa, 0.0%hi, 0.0%si,
> 0.0%st
> Mem: 8194800k total, 8118688k used, 76112k free, 36k buffers
> Swap: 2031608k total, 169348k used, 1862260k free, 7313232k cached
System load looks very much OK given that you have 8 CPUs.
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 18972 postgres 20 0 2514m 11m 8752 R 11 0.1 0:00.35 postmaster
> 10618 postgres 20 0 2514m 12m 9456 R 9 0.2 0:00.54 postmaster
> 10636 postgres 20 0 2514m 11m 9192 R 9 0.1 0:00.45 postmaster
> 25903 postgres 20 0 2514m 11m 8784 R 9 0.1 0:00.21 postmaster
> 10626 postgres 20 0 2514m 11m 8716 R 6 0.1 0:00.45 postmaster
> 10645 postgres 20 0 2514m 12m 9352 R 6 0.2 0:00.42 postmaster
> 10647 postgres 20 0 2514m 11m 9172 R 6 0.1 0:00.51 postmaster
> 18502 postgres 20 0 2514m 11m 9016 R 6 0.1 0:00.23 postmaster
> 10641 postgres 20 0 2514m 12m 9296 R 5 0.2 0:00.36 postmaster
> 10051 postgres 20 0 2514m 13m 10m R 4 0.2 0:00.70 postmaster
> 10622 postgres 20 0 2514m 12m 9216 R 4 0.2 0:00.39 postmaster
> 10640 postgres 20 0 2514m 11m 8592 R 4 0.1 0:00.52 postmaster
> 18497 postgres 20 0 2514m 11m 8804 R 4 0.1 0:00.25 postmaster
> 18498 postgres 20 0 2514m 11m 8804 R 4 0.1 0:00.22 postmaster
> 10341 postgres 20 0 2514m 13m 9m R 2 0.2 0:00.57 postmaster
> 10619 postgres 20 0 2514m 12m 9336 R 1 0.2 0:00.38 postmaster
> 15687 postgres 20 0 2321m 35m 35m R 0 0.4 8:36.12 postmaster
Judging by the amount of CPU time each postmaster as accumulated, they
are all fairly new processes. How many pg proceses of the ~350
currently running are there?
-Dave
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-07 22:57 Craig James <craig_james@emolecules.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 3 replies; 31+ messages in thread
From: Craig James @ 2010-04-07 22:57 UTC (permalink / raw)
To: jd@commandprompt.com; +Cc: pgsql-performance
On 4/7/10 3:36 PM, Joshua D. Drake wrote:
> On Wed, 2010-04-07 at 14:45 -0700, Craig James wrote:
>> On 4/7/10 2:40 PM, Joshua D. Drake wrote:
>>> On Wed, 2010-04-07 at 14:37 -0700, Craig James wrote:
>>>> Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh).
>>>>
>>>> I managed to capture one such event using top(1) with the "batch" option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike.
>>>
>>> What does iostat 5 say during the jump?
>>
>> It's very hard to say ... I'll have to start a background job to watch for a day or so. While it's happening, you can't login, and any open windows become unresponsive. I'll probably have to run it at high priority using nice(1) to get any data at all during the event.
>
> Do you have sar runing? Say a sar -A ?
No, I don't have it installed. I'll have a look. At first glance it looks like a combination of what I can get with "top -b" and vmstat, but with a single program.
> My guess is that it is not CPU, it is IO and your CPU usage is all WAIT
> on IO.
>
> To have your CPUs so flooded that they are the cause of an inability to
> log in is pretty suspect.
I thought so too, except that I can't login during the flood. If the CPUs were all doing iowaits, logging in should be easy.
Greg's suggestion that shared_buffers and work_mem are too big for an 8 GB system fits these symptoms -- if it's having a swap storm, login is effectively impossible.
Craig
>
> Joshua D. Drake
>
>
>>
>> Thanks,
>> Craig
>>
>
>
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-07 23:07 Craig James <craig_james@emolecules.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 31+ messages in thread
From: Craig James @ 2010-04-07 23:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 4/7/10 2:59 PM, Tom Lane wrote:
> Craig James<craig_james@emolecules.com> writes:
>> Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh).
>> I managed to capture one such event using top(1) with the "batch" option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike.
>
>> Any ideas where I can look to find what's triggering this?
>
>> Postgres 8.3.0
> ^^^^^
>
> If it's really 8.3.0, try updating to 8.3.something-recent. We've fixed
> a whole lot of bugs since then.
Good advice, I've been meaning to do this, maybe this will be a kick in the pants to motivate me.
> I have a suspicion that this might be an sinval overrun scenario, in
> which case you'd need to update to 8.4 to get a real fix. But updating
> in the 8.3 branch would be cheap and easy.
>
> If it is sinval overrun, it would presumably be triggered by a whole lot
> of catalog changes being made at approximately the same time. Can you
> correlate the spikes with anything like that?
Not that I know of. Just regular web traffic. On the backup server these events happen occasionally even when there is little or no web traffic, and nobody logged in doing maintenance.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-07 23:14 Steve Crawford <scrawford@pinpointresearch.com>
parent: Craig James <craig_james@emolecules.com>
0 siblings, 0 replies; 31+ messages in thread
From: Steve Crawford @ 2010-04-07 23:14 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
>> ...Can you
>> correlate the spikes with anything like that?
>
> Not that I know of. Just regular web traffic. On the backup server
> these events happen occasionally even when there is little or no web
> traffic, and nobody logged in doing maintenance.
What, if anything, are you logging in the PostgreSQL logs? Anything
interesting, there?
Cheers,
Steve
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-07 23:38 Alan Hodgson <ahodgson@reinvent.com>
parent: Craig James <craig_james@emolecules.com>
2 siblings, 0 replies; 31+ messages in thread
From: Alan Hodgson @ 2010-04-07 23:38 UTC (permalink / raw)
To: pgsql-performance
On Wednesday 07 April 2010, Craig James <craig_james@emolecules.com> wrote:
> I thought so too, except that I can't login during the flood. If the
> CPUs were all doing iowaits, logging in should be easy.
Busying out the drives is about the most reliable way to make logging in
very slow (especially, but not only, if it's due to swapping).
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-07 23:52 David Rees <drees76@gmail.com>
parent: Craig James <craig_james@emolecules.com>
2 siblings, 0 replies; 31+ messages in thread
From: David Rees @ 2010-04-07 23:52 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: jd@commandprompt.com; pgsql-performance
On Wed, Apr 7, 2010 at 3:57 PM, Craig James <craig_james@emolecules.com> wrote:
> On 4/7/10 3:36 PM, Joshua D. Drake wrote:
>> My guess is that it is not CPU, it is IO and your CPU usage is all WAIT
>> on IO.
>>
>> To have your CPUs so flooded that they are the cause of an inability to
>> log in is pretty suspect.
>
> I thought so too, except that I can't login during the flood. If the CPUs
> were all doing iowaits, logging in should be easy.
No - logging in with high iowait is very harder to do than high CPU
time because of latency of disk access.
> Greg's suggestion that shared_buffers and work_mem are too big for an 8 GB
> system fits these symptoms -- if it's having a swap storm, login is
> effectively impossible.
A swap storm effectively puts the machine into very high iowait time.
-Dave
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-08 00:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig James <craig_james@emolecules.com>
2 siblings, 0 replies; 31+ messages in thread
From: Tom Lane @ 2010-04-08 00:18 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: jd@commandprompt.com; pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> On 4/7/10 3:36 PM, Joshua D. Drake wrote:
>> To have your CPUs so flooded that they are the cause of an inability to
>> log in is pretty suspect.
> I thought so too, except that I can't login during the flood. If the CPUs were all doing iowaits, logging in should be easy.
> Greg's suggestion that shared_buffers and work_mem are too big for an 8 GB system fits these symptoms -- if it's having a swap storm, login is effectively impossible.
Yeah, but there is also the question of what's causing all the backends
to try to run at the same time. Oversubscribed memory could well be the
direct cause of the machine getting driven into the ground, but there's
something else going on here too IMO.
Anyway I concur with the advice to lower shared_buffers, and run fewer
backends if possible, to see if that ameliorates the problem.
regards, tom lane
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-08 00:47 Robert Haas <robertmhaas@gmail.com>
parent: David Rees <drees76@gmail.com>
1 sibling, 1 reply; 31+ messages in thread
From: Robert Haas @ 2010-04-08 00:47 UTC (permalink / raw)
To: David Rees <drees76@gmail.com>; +Cc: pgsql-performance
On Wed, Apr 7, 2010 at 6:56 PM, David Rees <drees76@gmail.com> wrote:
>> max_fsm_pages = 16000000
>> max_fsm_relations = 625000
>> synchronous_commit = off
>
> You are playing with fire here. You should never turn this off unless
> you do not care if your data becomes irrecoverably corrupted.
That is not correct. Turning off synchronous_commit is sensible if
you don't mind losing the last few transactions on a crash. What will
corrupt your database is if you turn off fsync.
...Robert
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-08 02:06 Craig James <craig_james@emolecules.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 2 replies; 31+ messages in thread
From: Craig James @ 2010-04-08 02:06 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: David Rees <drees76@gmail.com>; pgsql-performance
On 4/7/10 5:47 PM, Robert Haas wrote:
> On Wed, Apr 7, 2010 at 6:56 PM, David Rees<drees76@gmail.com> wrote:
>>> max_fsm_pages = 16000000
>>> max_fsm_relations = 625000
>>> synchronous_commit = off
>>
>> You are playing with fire here. You should never turn this off unless
>> you do not care if your data becomes irrecoverably corrupted.
>
> That is not correct. Turning off synchronous_commit is sensible if
> you don't mind losing the last few transactions on a crash. What will
> corrupt your database is if you turn off fsync.
A bit off the original topic, but ...
I set it this way because I was advised that with a battery-backed RAID controller, this was a safe setting. Is that not the case?
Craig
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-08 02:50 David Rees <drees76@gmail.com>
parent: Craig James <craig_james@emolecules.com>
1 sibling, 1 reply; 31+ messages in thread
From: David Rees @ 2010-04-08 02:50 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Wed, Apr 7, 2010 at 7:06 PM, Craig James <craig_james@emolecules.com> wrote:
> On 4/7/10 5:47 PM, Robert Haas wrote:
>> On Wed, Apr 7, 2010 at 6:56 PM, David Rees<drees76@gmail.com> wrote:
>>>> synchronous_commit = off
>>>
>>> You are playing with fire here. You should never turn this off unless
>>> you do not care if your data becomes irrecoverably corrupted.
>>
>> That is not correct. Turning off synchronous_commit is sensible if
>> you don't mind losing the last few transactions on a crash. What will
>> corrupt your database is if you turn off fsync.
Whoops, you're right.
> A bit off the original topic, but ...
>
> I set it this way because I was advised that with a battery-backed RAID
> controller, this was a safe setting. Is that not the case?
Robert has it right - with synchronous_commit off, your database will
always be consistent, but you may lose transactions in the event of a
crash.
Doesn't matter if you have a BBU or not - all the BBU does is give the
controller the ability to acknowledge a write without the data
actually having been written to disk.
According to the documentation, with synchronous_commit off and a
default wal_writer_delay of 200ms, it's possible to lose up to a
maximum of 600ms of data you thought were written to disk.
-Dave
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-08 03:07 Robert Haas <robertmhaas@gmail.com>
parent: David Rees <drees76@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Robert Haas @ 2010-04-08 03:07 UTC (permalink / raw)
To: David Rees <drees76@gmail.com>; +Cc: Craig James <craig_james@emolecules.com>; pgsql-performance
On Wed, Apr 7, 2010 at 10:50 PM, David Rees <drees76@gmail.com> wrote:
> On Wed, Apr 7, 2010 at 7:06 PM, Craig James <craig_james@emolecules.com> wrote:
>> On 4/7/10 5:47 PM, Robert Haas wrote:
>>> On Wed, Apr 7, 2010 at 6:56 PM, David Rees<drees76@gmail.com> wrote:
>>>>> synchronous_commit = off
>>>>
>>>> You are playing with fire here. You should never turn this off unless
>>>> you do not care if your data becomes irrecoverably corrupted.
>>>
>>> That is not correct. Turning off synchronous_commit is sensible if
>>> you don't mind losing the last few transactions on a crash. What will
>>> corrupt your database is if you turn off fsync.
>
> Whoops, you're right.
>
>> A bit off the original topic, but ...
>>
>> I set it this way because I was advised that with a battery-backed RAID
>> controller, this was a safe setting. Is that not the case?
>
> Robert has it right - with synchronous_commit off, your database will
> always be consistent, but you may lose transactions in the event of a
> crash.
>
> Doesn't matter if you have a BBU or not - all the BBU does is give the
> controller the ability to acknowledge a write without the data
> actually having been written to disk.
>
> According to the documentation, with synchronous_commit off and a
> default wal_writer_delay of 200ms, it's possible to lose up to a
> maximum of 600ms of data you thought were written to disk.
So, IOW, if you're running a social networking web site and your
database is full of status updates sent by teenagers to other
teenagers, you might judge that turning off synchronous_commit is a
reasonable thing to do, if you need the performance. If you're
running a bank and your database is full of information on wire
transfers sent and received, not so much.
...Robert
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-08 06:08 Greg Smith <greg@2ndquadrant.com>
parent: David Rees <drees76@gmail.com>
1 sibling, 0 replies; 31+ messages in thread
From: Greg Smith @ 2010-04-08 06:08 UTC (permalink / raw)
To: David Rees <drees76@gmail.com>; +Cc: pgsql-performance
David Rees wrote:
> You need to find out what all those Postgres processes are doing. You
> might try enabling update_process_title and then using ps to figure
> out what each instance is using.
That's what the addition of "-c" to top I suggested does on Linux; it
shows the updated process titles where the command line is in the
default config.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-04-14 21:58 Bruce Momjian <bruce@momjian.us>
parent: Craig James <craig_james@emolecules.com>
1 sibling, 0 replies; 31+ messages in thread
From: Bruce Momjian @ 2010-04-14 21:58 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; David Rees <drees76@gmail.com>; pgsql-performance
Craig James wrote:
> On 4/7/10 5:47 PM, Robert Haas wrote:
> > On Wed, Apr 7, 2010 at 6:56 PM, David Rees<drees76@gmail.com> wrote:
> >>> max_fsm_pages = 16000000
> >>> max_fsm_relations = 625000
> >>> synchronous_commit = off
> >>
> >> You are playing with fire here. You should never turn this off unless
> >> you do not care if your data becomes irrecoverably corrupted.
> >
> > That is not correct. Turning off synchronous_commit is sensible if
> > you don't mind losing the last few transactions on a crash. What will
> > corrupt your database is if you turn off fsync.
>
> A bit off the original topic, but ...
>
> I set it this way because I was advised that with a battery-backed
> RAID controller, this was a safe setting. Is that not the case?
To get good performance, you can either get a battery-backed RAID
controller or risk losing a few transaction with synchronous_commit =
off. If you already have a battery-backed RAID controller, there is
little benefit to turning synchronous_commit off, and some major
downsides (possible data loss).
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-06-25 00:50 Craig James <craig_james@emolecules.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 3 replies; 31+ messages in thread
From: Craig James @ 2010-06-25 00:50 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-06-25 01:01 Joshua D. Drake <jd@commandprompt.com>
parent: Craig James <craig_james@emolecules.com>
2 siblings, 0 replies; 31+ messages in thread
From: Joshua D. Drake @ 2010-06-25 01:01 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
On Thu, 2010-06-24 at 17:50 -0700, Craig James wrote:
> 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.
checkpoint causing IO Wait.
What does sar say about these times?
Joshua D. Drake
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-06-25 01:13 Greg Smith <greg@2ndquadrant.com>
parent: Craig James <craig_james@emolecules.com>
2 siblings, 0 replies; 31+ messages in thread
From: Greg Smith @ 2010-06-25 01:13 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James wrote:
> 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.
By the way: you probably want "top -b -c", which will actually show you
what each client is doing via inspecting what it's set its command line to.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-06-25 04:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig James <craig_james@emolecules.com>
2 siblings, 1 reply; 31+ messages in thread
From: Tom Lane @ 2010-06-25 04:04 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> 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?
sinval queue overflow comes to mind ... although that really shouldn't
happen if there's "no real load" on the server. What PG version is
this? Also, the pg_stat_activity view contents when this happens would
probably be more useful to look at than "top" output.
regards, tom lane
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-06-25 13:48 Craig James <craig_james@emolecules.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 31+ messages in thread
From: Craig James @ 2010-06-25 13:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 6/24/10 9:04 PM, Tom Lane wrote:
> Craig James<craig_james@emolecules.com> writes:
>> 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?
>
> sinval queue overflow comes to mind ... although that really shouldn't
> happen if there's "no real load" on the server. What PG version is
> this?
8.3.10. Upgraded based on your advice when I first asked this question.
> Also, the pg_stat_activity view contents when this happens would
> probably be more useful to look at than "top" output.
I'll try. It's hard to discover anything because the whole machine is overwhelmed when this happens. The only way I got the top(1) output was by running it high priority as root using nice(1). I can't do that with a Postgres backend, but I'll see what I can do.
Craig
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-06-25 14:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig James <craig_james@emolecules.com>
1 sibling, 1 reply; 31+ messages in thread
From: Tom Lane @ 2010-06-25 14:47 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> On 6/24/10 9:04 PM, Tom Lane wrote:
>> sinval queue overflow comes to mind ... although that really shouldn't
>> happen if there's "no real load" on the server. What PG version is
>> this?
> 8.3.10. Upgraded based on your advice when I first asked this question.
Any chance of going to 8.4? If this is what I suspect, you really need
this 8.4 fix:
http://archives.postgresql.org/pgsql-committers/2008-06/msg00227.php
which eliminated the thundering-herd behavior that previous releases
exhibit when the sinval queue overflows.
If you're stuck on 8.3 then you are going to have to modify your
application's behavior to eliminate sinval overflows. If the overall
system load isn't high then I would have to guess that the problem is
some individual sessions sitting "idle in transaction" for long periods,
long enough that a number of DDL operations happen elsewhere.
You could also consider throwing memory at the problem by raising the
sinval queue size. That'd require a custom build since it's not exposed
as a configurable parameter, but it'd be a one-line patch I think.
Or you could look at using connection pooling so you don't have quite
so many backends ...
regards, tom lane
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-06-25 16:07 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Craig James <craig_james@emolecules.com>
1 sibling, 0 replies; 31+ messages in thread
From: Rajesh Kumar Mallah @ 2010-06-25 16:07 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Dear Criag,
also check for the possibility of installing sysstat in our system.
it goes a long way in collecting the system stats. you may
consider increasing the frequency of data collection by
changing the interval of cron job manually in /etc/cron.d/
normally its */10 , you may make it */2 for time being.
the software automatically maintains historical records
of data for 1 month.
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-06-25 16:16 Craig James <craig_james@emolecules.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 31+ messages in thread
From: Craig James @ 2010-06-25 16:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 6/25/10 7:47 AM, Tom Lane wrote:
> Craig James<craig_james@emolecules.com> writes:
>> On 6/24/10 9:04 PM, Tom Lane wrote:
>>> sinval queue overflow comes to mind ... although that really shouldn't
>>> happen if there's "no real load" on the server. What PG version is
>>> this?
>
>> 8.3.10. Upgraded based on your advice when I first asked this question.
>
> Any chance of going to 8.4? If this is what I suspect, you really need
> this 8.4 fix:
> http://archives.postgresql.org/pgsql-committers/2008-06/msg00227.php
> which eliminated the thundering-herd behavior that previous releases
> exhibit when the sinval queue overflows.
Yes, there is a chance of upgrading to 8.4.4. I just bought a new server and it has 8.4.4 on it, but it won't be online for a while so I can't compare yet. This may motivate me to upgrade the current servers to 8.4.4 too. I was pleased to see that 8.4 has a new upgrade-in-place feature that means we don't have to dump/restore. That really helps a lot.
A question about 8.4.4: I've been having problems with bloat. I thought I'd adjusted the FSM parameters correctly based on advice I got here, but apparently not. 8.4.4 has removed the configurable FSM parameters completely, which is very cool. But ... if I upgrade a bloated database using the upgrade-in-place feature, will 8.4.4 recover the bloat and return it to the OS, or do I still have to recover the space manually (like vacuum-full/reindex, or cluster, or copy/drop a table)?
> Or you could look at using connection pooling so you don't have quite
> so many backends ...
I always just assumed that lots of backends that would be harmless if each one was doing very little. If I understand your explanation, it sounds like that's not entirely true in pre-8.4.4 releases due to the sinval queue problems.
Thanks,
Craig
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-06-25 16:41 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Craig James <craig_james@emolecules.com>
2 siblings, 1 reply; 31+ messages in thread
From: Kevin Grittner @ 2010-06-25 16:41 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> wrote:
> I always just assumed that lots of backends that would be harmless
> if each one was doing very little.
Even if each is doing very little, if a large number of them happen
to make a request at the same time, you can have problems. This is
exactly where a connection pool can massively improve both
throughput and response time. If you can arrange it, you want a
connection pool which will put a limit on active database
transactions and queue requests to start a new transaction until one
of the pending ones finishes.
-Kevin
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-06-25 16:55 Greg Smith <greg@2ndquadrant.com>
parent: Craig James <craig_james@emolecules.com>
2 siblings, 0 replies; 31+ messages in thread
From: Greg Smith @ 2010-06-25 16:55 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Craig James wrote:
> if I upgrade a bloated database using the upgrade-in-place feature,
> will 8.4.4 recover the bloat and return it to the OS, or do I still
> have to recover the space manually (like vacuum-full/reindex, or
> cluster, or copy/drop a table)?
There's no way for an upgrade in place to do anything about bloat. The
changes in 8.4 reduce the potential sources for new bloat (like running
out of a FSM pages), and the overhead of running VACUUM drops some due
to things like the "Partial VACUUM" changes. But existing bloated
tables and indexes are moved forward to the new version without any change.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-06-25 17:01 Craig James <craig_james@emolecules.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 31+ messages in thread
From: Craig James @ 2010-06-25 17:01 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On 6/25/10 9:41 AM, Kevin Grittner wrote:
> Craig James<craig_james@emolecules.com> wrote:
>
>> I always just assumed that lots of backends that would be harmless
>> if each one was doing very little.
>
> Even if each is doing very little, if a large number of them happen
> to make a request at the same time, you can have problems. This is
> exactly where a connection pool can massively improve both
> throughput and response time. If you can arrange it, you want a
> connection pool which will put a limit on active database
> transactions and queue requests to start a new transaction until one
> of the pending ones finishes.
No, that's doesn't seem to be the case. There is no external activity that triggers this huge spike in usage. It even happens to our backup server when only one of us is using it to do a single query. This problem seems to be triggered by Postgres itself, not by anything external.
Per Tom's suggestion, I think upgrading to 8.4.4 is the answer. I'll learn more when our new hardware comes into use with a shiny new 8.4.4 installation.
Craig
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Occasional giant spikes in CPU load
@ 2010-06-25 17:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig James <craig_james@emolecules.com>
2 siblings, 0 replies; 31+ messages in thread
From: Tom Lane @ 2010-06-25 17:01 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> On 6/25/10 7:47 AM, Tom Lane wrote:
>> Any chance of going to 8.4? If this is what I suspect, you really need
>> this 8.4 fix:
>> http://archives.postgresql.org/pgsql-committers/2008-06/msg00227.php
>> which eliminated the thundering-herd behavior that previous releases
>> exhibit when the sinval queue overflows.
> Yes, there is a chance of upgrading to 8.4.4. I just bought a new server and it has 8.4.4 on it, but it won't be online for a while so I can't compare yet. This may motivate me to upgrade the current servers to 8.4.4 too. I was pleased to see that 8.4 has a new upgrade-in-place feature that means we don't have to dump/restore. That really helps a lot.
I wouldn't put a lot of faith in pg_migrator for an 8.3 to 8.4
conversion ... it might work, but test it on a copy of your DB first.
Possibly it'll actually be recommendable in 9.0.
> A question about 8.4.4: I've been having problems with bloat. I thought I'd adjusted the FSM parameters correctly based on advice I got here, but apparently not. 8.4.4 has removed the configurable FSM parameters completely, which is very cool. But ... if I upgrade a bloated database using the upgrade-in-place feature, will 8.4.4 recover the bloat and return it to the OS, or do I still have to recover the space manually (like vacuum-full/reindex, or cluster, or copy/drop a table)?
No, an in-place upgrade to 8.4 isn't magically going to fix that. This
might actually be sufficient reason to stick with the tried&true dump
and reload method, since you're going to have to do something fairly
expensive anyway to clean out the bloat.
regards, tom lane
^ permalink raw reply [nested|flat] 31+ messages in thread
end of thread, other threads:[~2010-06-25 17:01 UTC | newest]
Thread overview: 31+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-07 21:37 Occasional giant spikes in CPU load Craig James <craig_james@emolecules.com>
2010-04-07 21:40 ` Joshua D. Drake <jd@commandprompt.com>
2010-04-07 21:45 ` Craig James <craig_james@emolecules.com>
2010-04-07 22:36 ` Joshua D. Drake <jd@commandprompt.com>
2010-04-07 22:57 ` Craig James <craig_james@emolecules.com>
2010-04-07 23:38 ` Alan Hodgson <ahodgson@reinvent.com>
2010-04-07 23:52 ` David Rees <drees76@gmail.com>
2010-04-08 00:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-07 21:54 ` Greg Smith <greg@2ndquadrant.com>
2010-04-07 21:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-07 23:07 ` Craig James <craig_james@emolecules.com>
2010-04-07 23:14 ` Steve Crawford <scrawford@pinpointresearch.com>
2010-04-07 22:56 ` David Rees <drees76@gmail.com>
2010-04-08 00:47 ` Robert Haas <robertmhaas@gmail.com>
2010-04-08 02:06 ` Craig James <craig_james@emolecules.com>
2010-04-08 02:50 ` David Rees <drees76@gmail.com>
2010-04-08 03:07 ` Robert Haas <robertmhaas@gmail.com>
2010-06-25 00:50 ` Craig James <craig_james@emolecules.com>
2010-06-25 01:01 ` Joshua D. Drake <jd@commandprompt.com>
2010-06-25 01:13 ` Greg Smith <greg@2ndquadrant.com>
2010-06-25 04:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-25 13:48 ` Craig James <craig_james@emolecules.com>
2010-06-25 14:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-25 16:16 ` Craig James <craig_james@emolecules.com>
2010-06-25 16:41 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-25 17:01 ` Craig James <craig_james@emolecules.com>
2010-06-25 16:55 ` Greg Smith <greg@2ndquadrant.com>
2010-06-25 17:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-25 16:07 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2010-04-14 21:58 ` Bruce Momjian <bruce@momjian.us>
2010-04-08 06:08 ` Greg Smith <greg@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox