[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
Re: [PERFORM] Context switch storm
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=spam&template=history&history_page=1" !DSPAM:454b34ac206028992556831! --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Context switch storm
[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
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
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
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
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
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
> 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
[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
[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
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
Re: [PERFORM] Context switch storm
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
Re: [PERFORM] Context switch storm
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
* 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
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
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
> 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
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? merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Context switch storm
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
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
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