Re: [PERFORM] Context switch storm

2006-11-16 Thread Jim Nasby

On Nov 14, 2006, at 1:11 PM, Merlin Moncure wrote:

On 11/14/06, Jim C. Nasby [EMAIL PROTECTED] wrote:

On Tue, Nov 14, 2006 at 09:17:08AM -0500, Merlin Moncure wrote:
 On 11/14/06, Cosimo Streppone [EMAIL PROTECTED] 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[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] Context switch storm

2006-11-15 Thread Simon Riggs
On Tue, 2006-11-14 at 09:17 -0500, Merlin Moncure wrote:
 On 11/14/06, Cosimo Streppone [EMAIL PROTECTED] 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



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [PERFORM] Context switch storm

2006-11-14 Thread Cosimo Streppone

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 25.



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


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Context switch storm

2006-11-14 Thread Andreas Kostyrka
* Cosimo Streppone [EMAIL PROTECTED] [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 25.
 
 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

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Re: [PERFORM] Context switch storm

2006-11-14 Thread Jim C. Nasby
On Tue, Nov 14, 2006 at 09:17:08AM -0500, Merlin Moncure wrote:
 On 11/14/06, Cosimo Streppone [EMAIL PROTECTED] 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[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Re: [PERFORM] Context switch storm

2006-11-14 Thread Merlin Moncure

On 11/14/06, Cosimo Streppone [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate


Re: [PERFORM] Context switch storm

2006-11-14 Thread Bucky Jordan
 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

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [PERFORM] Context switch storm

2006-11-14 Thread Cosimo Streppone

Merlin wrote:


On 11/14/06, Jim C. Nasby [EMAIL PROTECTED] wrote:


On Tue, Nov 14, 2006 at 09:17:08AM -0500, Merlin Moncure wrote:
 On 11/14/06, Cosimo Streppone [EMAIL PROTECTED] 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


---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] Context switch storm

2006-11-06 Thread Cosimo Streppone

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


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


[PERFORM] Context switch storm

2006-11-03 Thread creimer


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 25.

CPU and memory usage are ok.

What is producing this context switching storms?

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

Should I disable Hyperthreading?

Thank you in advance!

Reimer


Re: [PERFORM] Context switch storm

2006-11-03 Thread Gregory S. Williamson
Based on what other people have posted, hyperthreading seems not to be 
beneficial for postgres -- try searching through the archives 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:   [EMAIL PROTECTED] on behalf of [EMAIL PROTECTED]
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 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 25.
 
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 [EMAIL PROTECTED]
https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=454b34ac206028992556831[EMAIL
 PROTECTED]retrain=spamtemplate=historyhistory_page=1
!DSPAM:454b34ac206028992556831!
---




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] Context switch storm

2006-11-03 Thread Richard Huxton

[EMAIL PROTECTED] 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 25.

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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Context switch storm

2006-11-03 Thread Richard Huxton

Cosimo Streppone wrote:

Richard Huxton wrote:


[EMAIL PROTECTED] wrote:


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


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 4.
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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] Context switch storm

2006-11-03 Thread Andreas Kostyrka
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 + schrieb Richard Huxton:
 Cosimo Streppone wrote:
  Richard Huxton wrote:
  
  [EMAIL PROTECTED] wrote:
 
  The average context switching for this server as vmstat shows is 1
  but when the problem occurs it goes to 25.
 
  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 4.
  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.
 


signature.asc
Description: Dies ist ein digital signierter Nachrichtenteil


Re: [PERFORM] Context switch storm

2006-11-03 Thread Richard Troy

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
[EMAIL PROTECTED], http://ScienceTools.com/


---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] Context switch storm

2006-11-03 Thread Richard Huxton

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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [PERFORM] Context switch storm

2006-11-03 Thread Cosimo Streppone

Richard Huxton wrote:


[EMAIL PROTECTED] 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 25.


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 4.
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


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Context switch storm

2006-11-03 Thread creimer
 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?

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




Re: [PERFORM] Context switch storm

2006-11-03 Thread Richard Huxton

[EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] Context switch storm

2006-11-03 Thread Tom Lane
[EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] Context switch storm

2006-11-03 Thread Andreas Kostyrka
Am Freitag, den 03.11.2006, 14:38 + schrieb Richard Huxton:
 [EMAIL PROTECTED] 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.
 


signature.asc
Description: Dies ist ein digital signierter Nachrichtenteil