Обсуждение: Context switch storm

Based on what other people have posted, hyperthreading seems not to be beneficial for postgres -- try searching through
thearchives of this list. (And then turn it off and see if it helps.) 

You might also post a few details:

config settings (shared_buffers, work_mem, maintenance_work_mem, wal and checkpoint settings, etc.)

are you using autovacuum ?

all tables are vacuumed and analyzed regularly ? How big are they ? Do they and indexes fit in RAM ?

any particular queries that running and might be related (explain analyze results of them would be useful)

disk configuration

Other processes on this box ?

# of connections to it (I've seen this alone push servers over the edge)

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From:    pgsql-performance-owner@postgresql.org on behalf of creimer@brturbo.com.br
Sent:    Fri 11/3/2006 2:32 AM
To:    pgsql-performance@postgresql.org
Cc:
Subject:    [PERFORM] Context switch storm

Hi,

We've migrated one of our servers from pg 7.4 to 8.1 and from times to times (4 hours) the server start doing a lot of
contextswitching and all transactions become very slow. 

The average context switching for this server as vmstat shows is 1 but when the problem occurs it goes to 250000.

CPU and memory usage are ok.

What is producing this context switching storms?

It is a box with 16GB RAM and 4 XEON processors running RedHat Enterprise Linux AS.

Should I disable Hyperthreading?

Thank you in advance!

Reimer



-------------------------------------------------------
Click link below if it is SPAM gsw@globexplorer.com

"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=454b34ac206028992556831&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1"
!DSPAM:454b34ac206028992556831!
-------------------------------------------------------

creimer@brturbo.com.br wrote:
> Hi,
>
> We've migrated one of our servers from pg 7.4 to 8.1 and from times
> to times (4 hours) the server start doing a lot of context switching
> and all transactions become very slow.
>
> The average context switching for this server as vmstat shows is 1
> but when the problem occurs it goes to 250000.
>
> CPU and memory usage are ok.
>
> What is producing this context switching storms?
 >
> It is a box with 16GB RAM and 4 XEON processors running RedHat
> Enterprise Linux AS.

It's memory bandwidth issues on the older Xeons. If you search the
archives you'll see a lot of discussion of this. I'd have thought 8.1
would be better than 7.4 though.

You'll tend to see it when you have multiple clients and most queries
can use RAM rather than disk I/O. My understanding of what happens is
that PG requests data from RAM - it's not in cache so the process gets
suspended to wait. The next process does the same, with the same result.
   You end up with lots of processes all fighting over what data is in
the cache and no-one gets much work done.

> Should I disable Hyperthreading?

I seem to remember that helps, but do check the mailing list archives
for discussion on this.

If you can keep your numbers of clients down below the critical level,
you should find the overall workload is fine. The problem is of course
that as the context-switching increases, each query takes longer which
means more clients connect, which increases the context-swtching, which
means...

HTH
--
   Richard Huxton
   Archonet Ltd

Cosimo Streppone wrote:
> Richard Huxton wrote:
>
>> creimer@brturbo.com.br wrote:
>>>
>>> The average context switching for this server as vmstat shows is 1
>>> but when the problem occurs it goes to 250000.
>>
>> You'll tend to see it when you have multiple clients and most queries
>> can use RAM rather than disk I/O. My understanding of what happens is
>> that PG requests data from RAM - it's not in cache so the process gets
>> suspended to wait. The next process does the same, with the same
>> result.   You end up with lots of processes all fighting over what
>> data is in the cache and no-one gets much work done.
>
> Does this happen also with 8.0, or is specific to 8.1 ?

All versions suffer to a degree - they just push the old Xeon in the
wrong way. However, more recent versions *should* be better than older
versions. I believe some work was put in to prevent contention on
various locks which should reduce context-switching across the board.

> I seem to have the same exact behaviour for an OLTP-loaded 8.0.1 server

upgrade from 8.0.1 - the most recent is 8.0.9 iirc

> when I raise `shared_buffers' from 8192 to 40000.
> I would expect an increase in tps/concurrent clients, but I see an average
> performance below a certain threshold of users, and when concurrent users
> get above that level, performance starts to drop, no matter what I do.

Are you seeing a jump in context-switching in top? You'll know when you
do - it's a *large* jump. That's the key diagnosis. Otherwise it might
simply be your configuration settings aren't ideal for that workload.

> Server logs and io/vm statistics seem to indicate that there is little
> or no disk activity but machine loads increases to 7.0/8.0.
> After some minutes, the problem goes away, and performance returns
> to acceptable levels.

That sounds like it. Query time increases across the board as all the
clients fail to get any data back.

> When the load increases, *random* database queries show this "slowness",
> even if they are perfectly planned and indexed.
>
> Is there anything we can do?

Well, the client I saw it with just bought a dual-opteron server and
used their quad-Xeon for something else. However, I do remember that 8.1
seemed better than 7.4 before they switched. Part of that might just
have been better query-planning and other efficiences though.

--
   Richard Huxton
   Archonet Ltd

The solution for us has been twofold:

upgrade to the newest PG version available at the time while we waited
for our new Opteron-based DB hardware to arrive.

Andreas


Am Freitag, den 03.11.2006, 13:29 +0000 schrieb Richard Huxton:
> Cosimo Streppone wrote:
> > Richard Huxton wrote:
> >
> >> creimer@brturbo.com.br wrote:
> >>>
> >>> The average context switching for this server as vmstat shows is 1
> >>> but when the problem occurs it goes to 250000.
> >>
> >> You'll tend to see it when you have multiple clients and most queries
> >> can use RAM rather than disk I/O. My understanding of what happens is
> >> that PG requests data from RAM - it's not in cache so the process gets
> >> suspended to wait. The next process does the same, with the same
> >> result.   You end up with lots of processes all fighting over what
> >> data is in the cache and no-one gets much work done.
> >
> > Does this happen also with 8.0, or is specific to 8.1 ?
>
> All versions suffer to a degree - they just push the old Xeon in the
> wrong way. However, more recent versions *should* be better than older
> versions. I believe some work was put in to prevent contention on
> various locks which should reduce context-switching across the board.
>
> > I seem to have the same exact behaviour for an OLTP-loaded 8.0.1 server
>
> upgrade from 8.0.1 - the most recent is 8.0.9 iirc
>
> > when I raise `shared_buffers' from 8192 to 40000.
> > I would expect an increase in tps/concurrent clients, but I see an average
> > performance below a certain threshold of users, and when concurrent users
> > get above that level, performance starts to drop, no matter what I do.
>
> Are you seeing a jump in context-switching in top? You'll know when you
> do - it's a *large* jump. That's the key diagnosis. Otherwise it might
> simply be your configuration settings aren't ideal for that workload.
>
> > Server logs and io/vm statistics seem to indicate that there is little
> > or no disk activity but machine loads increases to 7.0/8.0.
> > After some minutes, the problem goes away, and performance returns
> > to acceptable levels.
>
> That sounds like it. Query time increases across the board as all the
> clients fail to get any data back.
>
> > When the load increases, *random* database queries show this "slowness",
> > even if they are perfectly planned and indexed.
> >
> > Is there anything we can do?
>
> Well, the client I saw it with just bought a dual-opteron server and
> used their quad-Xeon for something else. However, I do remember that 8.1
> seemed better than 7.4 before they switched. Part of that might just
> have been better query-planning and other efficiences though.
>

On Fri, 3 Nov 2006, Richard Huxton wrote:
>
> It's memory bandwidth issues on the older Xeons. If you search the
> archives you'll see a lot of discussion of this. I'd have thought 8.1
> would be better than 7.4 though.

Hmmm... I just checked; one of our production systems is a multi-cpu Xeon
based system of uncertain age (nobody remember 'zactly). While we haven't
seen this problem yet, it's scheduled to take over demo-duty shortly and
it would be an embarassment if we had this trouble during a demo... Is
there any easy way to tell if you're at risk?

Thanks,
Richard


--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy@ScienceTools.com, http://ScienceTools.com/

Richard Troy wrote:
> On Fri, 3 Nov 2006, Richard Huxton wrote:
>> It's memory bandwidth issues on the older Xeons. If you search the
>> archives you'll see a lot of discussion of this. I'd have thought 8.1
>> would be better than 7.4 though.
>
> Hmmm... I just checked; one of our production systems is a multi-cpu Xeon
> based system of uncertain age (nobody remember 'zactly). While we haven't
> seen this problem yet, it's scheduled to take over demo-duty shortly and
> it would be an embarassment if we had this trouble during a demo... Is
> there any easy way to tell if you're at risk?

Try:
- multiple clients
- query doing sorts that fit into work_mem

--
   Richard Huxton
   Archonet Ltd

Richard Huxton wrote:

> creimer@brturbo.com.br wrote:
>> Hi,
>>
>> We've migrated one of our servers from pg 7.4 to 8.1 and from times
>> to times (4 hours) the server start doing a lot of context switching
>> and all transactions become very slow.
>>
>> The average context switching for this server as vmstat shows is 1
>> but when the problem occurs it goes to 250000.
>
> You'll tend to see it when you have multiple clients and most queries
> can use RAM rather than disk I/O. My understanding of what happens is
> that PG requests data from RAM - it's not in cache so the process gets
> suspended to wait. The next process does the same, with the same result.
>   You end up with lots of processes all fighting over what data is in
> the cache and no-one gets much work done.

Does this happen also with 8.0, or is specific to 8.1 ?
I seem to have the same exact behaviour for an OLTP-loaded 8.0.1 server
when I raise `shared_buffers' from 8192 to 40000.
I would expect an increase in tps/concurrent clients, but I see an average
performance below a certain threshold of users, and when concurrent users
get above that level, performance starts to drop, no matter what I do.

Server logs and io/vm statistics seem to indicate that there is little
or no disk activity but machine loads increases to 7.0/8.0.
After some minutes, the problem goes away, and performance returns
to acceptable levels.

When the load increases, *random* database queries show this "slowness",
even if they are perfectly planned and indexed.

Is there anything we can do?

--
Cosimo

creimer@brturbo.com.br wrote:
>> If you can keep your numbers of clients down below the critical
>> level, you should find the overall workload is fine.
>
> We have at about 600 connections. Is this a case to use a connection
> pool (pg_pool) system?

Possibly - that should help. I'm assuming that most of your queries are
very short, so you could probably get that figure down a lot lower.
You'll keep the same amount of queries running through the system, just
queue them up.

> And why this happens only with 8.0 and 8.1 and not with the 7.4?

Not sure. Maybe 8.x is making more intensive use of your memory,
possibly with a change in your plans.

--
   Richard Huxton
   Archonet Ltd

creimer@brturbo.com.br writes:
> And why this happens only with 8.0 and 8.1 and not with the 7.4?

8.0 and 8.1 are vulnerable to this behavior because of conflicts for
access to pg_subtrans (which didn't exist in 7.4).  The problem occurs
when you have old open transactions, causing the window over which
pg_subtrans must be accessed to become much wider than normal.
8.2 should eliminate or at least alleviate the issue, but in the
meantime see if you can get your applications to not sit on open
transactions.

            regards, tom lane

Am Freitag, den 03.11.2006, 14:38 +0000 schrieb Richard Huxton:
> creimer@brturbo.com.br wrote:
> >> If you can keep your numbers of clients down below the critical
> >> level, you should find the overall workload is fine.
> >
> > We have at about 600 connections. Is this a case to use a connection
> > pool (pg_pool) system?
>
> Possibly - that should help. I'm assuming that most of your queries are
> very short, so you could probably get that figure down a lot lower.
> You'll keep the same amount of queries running through the system, just
> queue them up.
that have
Ah, yes, now that you mention, avoid running many queries with a
similiar timing behaviour, PG8 seems to have a lock design that's very
bad for the memory architecture of the Xeons.

So running SELECT * FROM table WHERE id=1234567890; from 600 clients in
parallel can be quite bad than say a complicated 6-way join :(

Andreas

>
> > And why this happens only with 8.0 and 8.1 and not with the 7.4?
>
> Not sure. Maybe 8.x is making more intensive use of your memory,
> possibly with a change in your plans.
>

Andreas Kostyrka wrote:

> The solution for us has been twofold:
>
> upgrade to the newest PG version available at the time while we waited
> for our new Opteron-based DB hardware to arrive.

Do you remember the exact Pg version?

--
Cosimo

Richard Huxton wrote:
> Cosimo Streppone wrote:
>> Richard Huxton wrote:
>>
>>>> The average context switching for this server as vmstat shows is 1
>>>> but when the problem occurs it goes to 250000.
>>>
>> I seem to have the same exact behaviour for an OLTP-loaded 8.0.1 server
>
> upgrade from 8.0.1 - the most recent is 8.0.9 iirc
> [...]
> Are you seeing a jump in context-switching in top? You'll know when you
> do - it's a *large* jump. That's the key diagnosis. Otherwise it might
> simply be your configuration settings aren't ideal for that workload.
>

Sorry for the delay.

I have logged vmstat results for the last 3 days.
Max context switches figure is 20500.

If I understand correctly, this does not mean a "storm",
but only that the 2 Xeons are overloaded.
Probably, I can do a good thing switching off the HyperThreading.
I get something like 12/15 *real* concurrent processes hitting
the server.

I must say I lowered "shared_buffers" to 8192, as it was before.
I tried raising it to 16384, but I can't seem to find a relationship
between shared_buffers and performance level for this server.

> Well, the client I saw it with just bought a dual-opteron server and
> used their quad-Xeon for something else. However, I do remember that 8.1
> seemed better than 7.4 before they switched. Part of that might just
> have been better query-planning and other efficiences though.

An upgrade to 8.1 is definitely the way to go.
Any 8.0 - 8.1 migration advice?

Thanks.

--
Cosimo

* Cosimo Streppone <cosimo@streppone.it> [061114 10:52]:
> Richard Huxton wrote:
> >Cosimo Streppone wrote:
> >>Richard Huxton wrote:
> >>
> >>>>The average context switching for this server as vmstat shows is 1
> >>>>but when the problem occurs it goes to 250000.
> >>>
> >>I seem to have the same exact behaviour for an OLTP-loaded 8.0.1 server
> >upgrade from 8.0.1 - the most recent is 8.0.9 iirc
> >[...]
> >Are you seeing a jump in context-switching in top? You'll know when you do - it's a *large* jump. That's the key
diagnosis.Otherwise it might simply be your configuration settings  
> >aren't ideal for that workload.
>
> Sorry for the delay.
>
> I have logged vmstat results for the last 3 days.
> Max context switches figure is 20500.
>
> If I understand correctly, this does not mean a "storm",
Nope, 20500 is a magnitude to low to the storms we were experiencing.

> but only that the 2 Xeons are overloaded.
> Probably, I can do a good thing switching off the HyperThreading.
> I get something like 12/15 *real* concurrent processes hitting
> the server.

Actually, for the storms we had, the number of concurrent processes
AND the workload is important:

many processes that do all different things => overloaded server
many processes that do all the same queries => storm.

Basically, it seems that postgresql implementation of locking is on
quite unfriendly standings with the Xeon memory subsystems. googling
around might provide more details.

>
> I must say I lowered "shared_buffers" to 8192, as it was before.
> I tried raising it to 16384, but I can't seem to find a relationship
> between shared_buffers and performance level for this server.
>
> >Well, the client I saw it with just bought a dual-opteron server and used their quad-Xeon for something else.
However,I do remember that 8.1 seemed better than 7.4 before they  
> >switched. Part of that might just have been better query-planning and other efficiences though.
>
> An upgrade to 8.1 is definitely the way to go.
> Any 8.0 - 8.1 migration advice?
Simple, there are basically two ways:
a) you can take downtime: pg_dump + restore
b) you cannot take downtime: install slony, install your new 8.1
server, replicate into it, switchover to the new server.

If you can get new hardware for the 8.1 box, you have two benefits:
a) order Opterons. That doesn't solve the overload problem as such,
but these pesky cs storms seems to have gone away this way.
(that was basically the "free" advice from an external consultant,
which luckily matched with my ideas what the problem could be. Cheap
solution at $3k :) )
b) you can use the older box still as readonly replica.
c) you've got a hot backup of your db.

Andreas

On 11/14/06, Cosimo Streppone <cosimo@streppone.it> wrote:
> I must say I lowered "shared_buffers" to 8192, as it was before.
> I tried raising it to 16384, but I can't seem to find a relationship
> between shared_buffers and performance level for this server.

My findings are pretty much the same here.  I don't see any link
between shared buffers and performance.  I'm still looking for hard
evidence to rebut this point.   Lower shared buffers leaves more
memory for what really matters, which is sorting.

> > Well, the client I saw it with just bought a dual-opteron server and
> > used their quad-Xeon for something else. However, I do remember that 8.1
> > seemed better than 7.4 before they switched. Part of that might just
> > have been better query-planning and other efficiences though.
>
> An upgrade to 8.1 is definitely the way to go.
> Any 8.0 - 8.1 migration advice?

If you are getting ready to stage an upgrade, you definately will want
to test on 8.2 and 8.1.  8.2 might give you better results in the lab,
and has some nice features.

merlin

On Tue, Nov 14, 2006 at 09:17:08AM -0500, Merlin Moncure wrote:
> On 11/14/06, Cosimo Streppone <cosimo@streppone.it> wrote:
> >I must say I lowered "shared_buffers" to 8192, as it was before.
> >I tried raising it to 16384, but I can't seem to find a relationship
> >between shared_buffers and performance level for this server.
>
> My findings are pretty much the same here.  I don't see any link
> between shared buffers and performance.  I'm still looking for hard
> evidence to rebut this point.   Lower shared buffers leaves more
> memory for what really matters, which is sorting.

It depends on your workload. If you're really sort-heavy, then having
memory available for that will be hard to beat. Otherwise, having a
large shared_buffers setting can really help cut down on switching back
and forth between the kernel and PostgreSQL.

BTW, shared_buffers of 16384 is pretty low by today's standards, so that
could be why you're not seeing much difference between that and 8192.
Try upping it to 1/4 - 1/2 of memory and see if that changes things.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

> a) order Opterons. That doesn't solve the overload problem as such,
> but these pesky cs storms seems to have gone away this way.

I haven't run into context switch storms or similar issues with the new
Intel Woodcrests (yet.. they're still pretty new and not yet under real
production load), has anyone else had any more experience with these
(good/bad)? From what I understand, the memory architecture is quite a
bit different than the Xeon, and they got rid of Hyperthreading in favor
of the dual core with shared cache.

If/when I run into the issue, I'll be sure to post, but I was wondering
if anyone had gotten there first.

Thanks,
- Bucky

On 11/14/06, Jim C. Nasby <jim@nasby.net> wrote:
> On Tue, Nov 14, 2006 at 09:17:08AM -0500, Merlin Moncure wrote:
> > On 11/14/06, Cosimo Streppone <cosimo@streppone.it> wrote:
> > >I must say I lowered "shared_buffers" to 8192, as it was before.
> > >I tried raising it to 16384, but I can't seem to find a relationship
> > >between shared_buffers and performance level for this server.
> >
> > My findings are pretty much the same here.  I don't see any link
> > between shared buffers and performance.  I'm still looking for hard
> > evidence to rebut this point.   Lower shared buffers leaves more
> > memory for what really matters, which is sorting.
>
> It depends on your workload. If you're really sort-heavy, then having
> memory available for that will be hard to beat. Otherwise, having a
> large shared_buffers setting can really help cut down on switching back
> and forth between the kernel and PostgreSQL.
>
> BTW, shared_buffers of 16384 is pretty low by today's standards, so that
> could be why you're not seeing much difference between that and 8192.
> Try upping it to 1/4 - 1/2 of memory and see if that changes things.

Can you think of a good way to construct a test case that would
demonstrate the difference?  What would be the 'best case' where a
high shared buffers would be favored over a low setting?

merlin

Merlin wrote:

> On 11/14/06, Jim C. Nasby <jim@nasby.net> wrote:
>
>> On Tue, Nov 14, 2006 at 09:17:08AM -0500, Merlin Moncure wrote:
>> > On 11/14/06, Cosimo Streppone <cosimo@streppone.it> wrote:
>> > >I must say I lowered "shared_buffers" to 8192, as it was before.
>> > >I tried raising it to 16384, but I can't seem to find a relationship
>> > >between shared_buffers and performance level for this server.
>> >
>> > My findings are pretty much the same here.
>> > [...]
>>
>> BTW, shared_buffers of 16384 is pretty low by today's standards
>
> Can you think of a good way to construct a test case that would
> demonstrate the difference?

Not sure of actual relevance, but some time ago I performed
(with 8.0) several pg_bench tests with 1,5,10,20 concurrent
clients with same pg configuration except one parameter for
every run.

In one of these tests I run pgbench with shared_buffers starting
at 1024 and doubling it to 2048, ..., until 16384.
I found the best performance in terms of transactions per second
around 4096/8192.

That said, I don't know if pgbench stresses the database
like my typical oltp application does.

And also, I suspect that shared_buffers should not be
evaluated as an absolute number, but rather as a number relative to
maximum main memory (say 1/2 the total ram, 1/3, 2/3, ...).

--
Cosimo

On Tue, 2006-11-14 at 09:17 -0500, Merlin Moncure wrote:
> On 11/14/06, Cosimo Streppone <cosimo@streppone.it> wrote:
> > I must say I lowered "shared_buffers" to 8192, as it was before.
> > I tried raising it to 16384, but I can't seem to find a relationship
> > between shared_buffers and performance level for this server.
>
> My findings are pretty much the same here.  I don't see any link
> between shared buffers and performance.  I'm still looking for hard
> evidence to rebut this point.   Lower shared buffers leaves more
> memory for what really matters, which is sorting.

In 8.0 there is a performance issue such that bgwriter will cause a
performance problem with large shared_buffers setting. That in itself
could lead to some fairly poor measurements of the value of
shared_buffers.

In 7.4 and prior releases setting shared_buffers higher was counter
productive in many ways, so isn't highly recommended.

In general, setting shared_buffers higher works for some workloads and
doesn't for others. So any measurements anybody makes depend upon the
workload and the size of the database. The more uniformly/randomly you
access a large database, the more benefit you'll see from large
shared_buffers. 8.1 benefits from having a higher shared_buffers in some
cases because it reduces contention on the buffer lwlocks; 8.2 solves
this issue.

Even in 8.2 ISTM that a higher shared_buffers setting wastes memory with
many connected users since the PrivRefCount array uses memory that could
have been used as filesystem cache.

--
  Simon Riggs
  EnterpriseDB   http://www.enterprisedb.com

On Nov 14, 2006, at 1:11 PM, Merlin Moncure wrote:
> On 11/14/06, Jim C. Nasby <jim@nasby.net> wrote:
>> On Tue, Nov 14, 2006 at 09:17:08AM -0500, Merlin Moncure wrote:
>> > On 11/14/06, Cosimo Streppone <cosimo@streppone.it> wrote:
>> > >I must say I lowered "shared_buffers" to 8192, as it was before.
>> > >I tried raising it to 16384, but I can't seem to find a
>> relationship
>> > >between shared_buffers and performance level for this server.
>> >
>> > My findings are pretty much the same here.  I don't see any link
>> > between shared buffers and performance.  I'm still looking for hard
>> > evidence to rebut this point.   Lower shared buffers leaves more
>> > memory for what really matters, which is sorting.
>>
>> It depends on your workload. If you're really sort-heavy, then having
>> memory available for that will be hard to beat. Otherwise, having a
>> large shared_buffers setting can really help cut down on switching
>> back
>> and forth between the kernel and PostgreSQL.
>>
>> BTW, shared_buffers of 16384 is pretty low by today's standards,
>> so that
>> could be why you're not seeing much difference between that and 8192.
>> Try upping it to 1/4 - 1/2 of memory and see if that changes things.
>
> Can you think of a good way to construct a test case that would
> demonstrate the difference?  What would be the 'best case' where a
> high shared buffers would be favored over a low setting?

Something that's read-heavy will benefit the most from a large
shared_buffers setting, since it means less trips to the kernel.
Write-heavy apps won't benefit that much because you'll end up double-
buffering written data.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)