Re: [PERFORM] Proposal of tunable fix for scalability of 8.4
From: pgsql-performance-ow...@postgresql.org [pgsql-performance-ow...@postgresql.org] On Behalf Of Simon Riggs [si...@2ndquadrant.com] Sent: Wednesday, March 18, 2009 12:53 AM To: Matthew Wakeling Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Proposal of tunable fix for scalability of 8.4 > On Mon, 2009-03-16 at 16:26 +, Matthew Wakeling wrote: > > One possibility would be for the locks to alternate between exclusive > > and > > shared - that is: > > > > 1. Take a snapshot of all shared waits, and grant them all - > > thundering > > herd style. > > 2. Wait until ALL of them have finished, granting no more. > > 3. Take a snapshot of all exclusive waits, and grant them all, one by > > one. > > 4. Wait until all of them have been finished, granting no more. > > 5. Back to (1) > > I agree with that, apart from the "granting no more" bit. > > Currently we queue up exclusive locks, but there is no need to since for > ProcArrayLock commits are all changing different data. > > The most useful behaviour is just to have two modes: > * exclusive-lock held - all other x locks welcome, s locks queue > * shared-lock held - all other s locks welcome, x locks queue > > This *only* works for ProcArrayLock. > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > I want to comment on an important distinction between these two variants. The "granting no more" bit WILL decrease performance under high contention. Here is my reasoning. We have two "two lists" proposals. Type A: allow line cutting (Simon, above): * exclusive-lock held and all exclusives process - all other NEW x locks welcome, s locks queue * shared-lock held and all shareds process- all other NEW s locks welcome, x locks queue Type B: forbid line cutting (Matthew, above, modified to allow multiple exclusive for ProcArrayLock -- for other types exclusive would be one at a time) * exclusive-lock held and all exclusives process - all NEW lock requests queue * shared-lock held and shareds process - all NEW lock requests queue A big benefit of the "wake all" proposal, is that a lot of access does not have to context switch out and back in. On a quick assessment, the type A above would lock and context switch even less than the wake-all (since exclusives don't go one at a time) but otherwise be similar. But this won't matter much if it is shared lock dominated. I would LOVE to have seen context switch rate numbers with the results so far, but many base unix tools don't show it by default (can get it from sar, rstat reports it) average # of context switches per transaction is an awesome measure of lock contention and lock efficiency. In type A above, the ratio of requests that require a context switch is Q / (M + Q), where Q is the average queue size when the 'shared-exclusive' swap occrs and M is the average number of "line cutters". In type B, the ratio of requests that must context switch is always == 1. Every request must queue and wait! This may perform worse than the current lock! One way to guarantee some fairness is to compromise between the two. Lets call this proposal C. Unfortunately, this is less elegant than the other two, since it has logic for both. It could be made tunable to be the complete spectrum though. * exclusive-lock held and all exclusives process - first N new X requests welcome, N+1 and later X requests and all shared locks queue. * shared-lock held and shareds process - first N new S requests welcom, N+1 and later S requests and all X locks queue So, if shared locks are queuing and exclusive hold the lock and are operating, and another exclusive request arrives, it can cut in line only if it is one of the first N to do so before it will queue and wait and give shared locks their turn. This counting condition can be done with an atomically incrementing integer using compare and set operations and no locks, and under heavy contention will reduce the number of context switches per operation to Q/(N + Q) where N is the number of 'line cutters' achieved and Q is the average queue size when the queued items are unlocked. Note how this is the same as the 'unbounded' equation with M above, except that N can never be greater than M (the 'natural' line cut count). So for N = Q half are forced to context switch and half cut in line without a context switch. N can be tunable, and it can be a different number for shared and exclusive to bias towards one or the other if desired. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Proposal of tunable fix for scalability of 8.4
From: Robert Haas [robertmh...@gmail.com] Sent: Thursday, March 19, 2009 8:45 PM To: Scott Carey Cc: Jignesh K. Shah; Greg Smith; Kevin Grittner; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Proposal of tunable fix for scalability of 8.4 > > >On Thu, Mar 19, 2009 at 5:43 PM, Scott Carey wrote: > >> Well, unless I'm misunderstanding something, waking all waiters every > >> time could lead to arbitrarily long delays for writers on mostly > >> read-only workloads... and by arbitrarily along, we mean to say > >> "potentially just about forever". That doesn't sound safe for > >> production to me. > > > > The other discussion going on indicates that that condition already can > > happen, shared can always currently cut in line while other shared locks > > have the lock, though I don't understand all the details. > > No. If the first process waiting for an LWLock wants an exclusive > lock, we wake up that process, and only that process. If the first > process waiting for an LWLock wants a shared lock, we wake up that > process, and the processes which it follow it in the queue that also > want shared locks. But if we come to a process which holds an > exclusive lock, we stop. So if the wait queue looks like this > SSSXSSSXSSS, then the first three processes will be woken up, but the > remainder will not. The new wait queue will look like this: XSSSXSSS > - and the exclusive waiter at the head of the queue is guaranteed to > get the next turn. Your description (much of which I cut out) is exactly how I understood it until Simon Riggs' post which changed my view and understanding. Under that situation, waking all shared will leave all X at the front and hence alternate shared/exclusive/shared/exclusive as long as both types are contending. Simon's post changed my view. Below is some cut/paste from it: NOTE: things without a > in front here represent Simon until the ENDQUOTE: QUOTE --- On Wed, 2009-03-18 at 11:45 +, Matthew Wakeling wrote: > On Wed, 18 Mar 2009, Simon Riggs wrote: > > I agree with that, apart from the "granting no more" bit. > > > > The most useful behaviour is just to have two modes: > > * exclusive-lock held - all other x locks welcome, s locks queue > > * shared-lock held - all other s locks welcome, x locks queue > > The problem with making all other locks welcome is that there is a > possibility of starvation. Imagine a case where there is a constant stream > of shared locks - the exclusive locks may never actually get hold of the > lock under the "all other shared locks welcome" strategy. That's exactly what happens now. -- > [Scott Carey] (Further down in Simon's post, a quote from months ago: ) -- "Each time a Shared request is dequeued, we effectively re-enable queue jumping, so a Shared request arriving during that point will actually jump ahead of Shared requests that were unlucky enough to arrive while an Exclusive lock was held. Worse than that, the new incoming Shared requests exacerbate the starvation, so the more non-adjacent groups of Shared lock requests there are in the queue, the worse the starvation of the exclusive requestors becomes. We are effectively randomly starving some shared locks as well as exclusive locks in the current scheme, based upon the state of the lock when they make their request." ENDQUOTE ( Simon Riggs, cut/paste by me. post from his post Wednesday 3/18 5:10 AM pacific time). -- I read that to mean that what is happening now is that in ADDITION to your explanation of how the queue works, while a batch of shared locks are executing, NEW shared locks execute immediately and don't even queue. That is, there is shared request queue jumping. The queue operates as your description but not everythig queues. It seems pretty conclusive if that is truthful -- that there is starvation possible in the current system. At this stage, it would seem that neither of us are experts on the current behavior, or that Simon is wrong, or that I completely misunderstood his comments above. > Now, of course, EVENTUALLY one of the X guys will probably beat out > all the S-lock waiters and he'll get to do his thing. But there's no > upper bound on how long this can take, and if the rate at which S-lock > waiters are joining the queue is much higher than the rate at which > X-lock waiters are joining the queue, it may be quite a long time. And the average expected time and distribution of those events can be statistically calculated and empirically measured. The fact that there is a chance at all is not as important as the magitude of the chance and the distribution of those probabilities. > Even if the overall system throughput is better with this change, the > fact that the guys who need the X-lock get seriously shafted is a > really serious problem. If 'serious shafting' is so, yes! We only disagree on the current possibility of this and the magnitude/likelihood
Re: [PERFORM] Proposal of tunable fix for scalability of 8.4
On Thu, Mar 19, 2009 at 5:43 PM, Scott Carey wrote: >> Well, unless I'm misunderstanding something, waking all waiters every >> time could lead to arbitrarily long delays for writers on mostly >> read-only workloads... and by arbitrarily along, we mean to say >> "potentially just about forever". That doesn't sound safe for >> production to me. > > The other discussion going on indicates that that condition already can > happen, shared can always currently cut in line while other shared locks > have the lock, though I don't understand all the details. No. If the first process waiting for an LWLock wants an exclusive lock, we wake up that process, and only that process. If the first process waiting for an LWLock wants a shared lock, we wake up that process, and the processes which it follow it in the queue that also want shared locks. But if we come to a process which holds an exclusive lock, we stop. So if the wait queue looks like this SSSXSSSXSSS, then the first three processes will be woken up, but the remainder will not. The new wait queue will look like this: XSSSXSSS - and the exclusive waiter at the head of the queue is guaranteed to get the next turn. If you wake up everybody, then the new queue will look like this: XXX. Superficially that's a good thing because you let 9 guys run rather than 3. But suppose that while those 9 guys hold the lock, twenty more shared locks join the end of the queue, so it looks like this XXX. Now when the last of the 9 guys releases the lock, we wake up everybody again, and odds are good that since there are a lot more S guys than X guys, once of the S guys will grab the lock first. The other S guys will all acquire the lock too, but the X guys are frozen out. This whole cycle can repeat: by the time those 20 guys are done with their S locks, there can be 20 more guys waiting for S locks, and once again when we wake everyone up one of the new S guys will probably grab it again. This can continue for an indefinitely long period of time. Now, of course, EVENTUALLY one of the X guys will probably beat out all the S-lock waiters and he'll get to do his thing. But there's no upper bound on how long this can take, and if the rate at which S-lock waiters are joining the queue is much higher than the rate at which X-lock waiters are joining the queue, it may be quite a long time. Even if the overall system throughput is better with this change, the fact that the guys who need the X-lock get seriously shafted is a really serious problem. If I start a million transactions on my system and they all complete in average of 1 second each, that sounds pretty good - unless it's because 999,999 of them completed almost instantaneously and the last one took a million seconds. Now, I'm not familiar enough with the use of ProcArrayLock to suggest a workload that will produce this pathological behavior in PG. But, I'm pretty confident based on what I know about locking in general that they exist. > Also, the tests on the 'wake all' version clearly aren't starving anything > in a load test with thousands of threads and very heavy lock contention, > mostly for shared locks. > Instead throughput increases and all wait times decrease. On the average, yes... > There are several other proposals to make starvation less possible (wake > only shared and other proposals that alternate between shared and exclusive; > waking only X sized chunks, etc -- its all just investigation into fixing > what can be improved on -- solutions that are easily testable should not > just be thrown out: the first ones were just the easiest to try). Alternating between shared and exclusive is safe. But a lot more testing in a lot more situations would be needed to determine whether it is better, I think. Waking chunks of a certain size I believe will produce a more complicated version of the problem described above. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres benchmarking with pgbench
m...@bortal.de wrote: Hi Greg, thanks a lot for your hints. I changed my config and changed raid6 to raid10, but whatever i do, the benchmark breaks down at a scaling factor 75 where the database is "only" 1126MB big. Here are my benchmark Results (scaling factor, DB size in MB, TPS) using: pgbench -S -c X -t 1000 -U pgsql -d benchmark -h MYHOST 1 19 8600 5 79 8743 10 154 8774 20 303 8479 30 453 8775 40 602 8093 50 752 6334 75 1126 3881 150 2247 2297 200 2994 701 250 3742 656 300 4489 596 400 5984 552 500 7479 513 I have no idea if this is any good for a QuardCore Intel(R) Xeon(R) CPU E5320 @ 1.86GHz with 4GB Ram and 6 SATA disk (7200rpm) in raid 10. Here is my config (maybe with some odd setting): http://pastebin.com/m5d7f5717 I played around with: - max_connections - shared_buffers - work_mem - maintenance_work_mem - checkpoint_segments - effective_cache_size ..but whatever i do, the graph looks the same. Any hints or tips what my config should look like? Or are these results even okay? Maybe i am driving myself crazy for nothing? Are you running the pgbench client from a different system? Did you check if the pgbench client itself is bottlenecked or not. I have seen before the client of pgbench is severely limited on the load it can drive and process. -Jignesh -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Proposal of tunable fix for scalability of 8.4
Scott Carey wrote: > On 3/19/09 10:37 AM, "Bruce Momjian" wrote: > > > Robert Haas wrote: > >>> The original poster's request is for a config parameter, for > >>> experimentation > >>> and testing by the brave. My own request was for that version of the lock > >>> to > >>> prevent possible starvation but improve performance by unlocking all > >>> shared > >>> at once, then doing all exclusives one at a time next, etc. > >> > >> That doesn't prevent starvation in general, although it will for some > >> workloads. > >> > >> Anyway, it seems rather pointless to add a config parameter that isn't > >> at all safe, and adds overhead to a critical part of the system for > >> people who don't use it. After all, if you find that it helps, what > >> are you going to do? Turn it on in production? I just don't see how > >> this is any good other than as a thought-experiment. > > > > We prefer things to be auto-tuned, and if not, it should be clear > > how/when to set the configuration parameter. > > Of course. The proposal was to leave it at the default, and obviously > document that it is not likely to be used. Its 1000x safer than fsync=off . Right, but even if people don't use it, people tuning their systems have to understand the setting to know if they should use it, so there is a cost even if a parameter is never used by anyone. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Proposal of tunable fix for scalability of 8.4
Robert Haas wrote: Actually the patch I submitted shows no overhead from what I have seen and I think it is useful depending on workloads where it can be turned on even on production. Well, unless I'm misunderstanding something, waking all waiters every time could lead to arbitrarily long delays for writers on mostly read-only workloads... and by arbitrarily along, we mean to say "potentially just about forever". That doesn't sound safe for production to me. Hi Robert, The patch I submmitted does not do any manipulation with the list. All it changes is gives the flexibility to change how many to wake up at one go. 0 is default which wakes up only 1 X (Exclusive) at a time or all sequential S (Shared). Changing the value to 1 will wake up all sequential X or all sequential S as they are in the queue (no manipulation). Values 2 and higher upto 32 wakes up the next n waiter in the queue (X or S) AS they are in the queue. It absolutely does no manipulation and hence there is no overhead. Absolutely safe for Production as Scott mentioned there are other things in postgresql.conf which can be more dangerous than this tunable. I dont think anything is majorly wrong in my system.. Sometimes it is PostgreSQL locks in play and sometimes it can be OS/system related locks in play (network, IO, file system, etc). Right now in my patch after I fix waiting procarray problem other PostgreSQL locks comes into play: CLogControlLock, WALInsertLock , etc. Right now out of the box we have no means of tweaking something in production if you do land in that problem. With the patch there is means of doing knob control to tweak the bottlenecks of Locks for the main workload for which it is put in production. I'll reiterate my previous objection: I think your approach is too simplistic. I think Tom said it the best: a lot of work has gone into making the locking mechanism lightweight and safe. I'm pretty doubtful that you're going to find a change that is still safe, but performs much better. The discussions by Heikki, Simon, and others about changing the way locks are used or inventing new kinds of locks seem much more promising to me. That is the beauty : The approach is simplistic but very effective. Lot of work has gone which is more incremental and this is another one of those incremental changes which allows minor tweaks which the workload may like very much and perform very well.. Performance tuning game is almost like harmonic frequency. I agree that other kinds of locks seem more promising. I had infact proposed one last year too: http://archives.postgresql.org//pgsql-hackers/2008-06/msg00291.php Seriously speaking a change will definitely cannot be done before 8.5 time frame while this one is simple enough to go for 8.4. The best thing one can contribute to the thread is to actually try the patch on the test system and run your own tests to see how it behaves. -Jignesh Right now.. the standard answer applies.. nope you are running the wrong workload for PostgreSQL, use a connection pooler or your own application logic. Or maybe.. you have too many users for PostgreSQL use some proprietary database. Well I certainly agree that we need to get away from that mentality, although there's nothing particularly evil about a connection pooler... it might not be suitable for every workload, but you haven't specified why one couldn't or shouldn't be used in the situation you're trying to simulate here. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres benchmarking with pgbench
On 3/19/09 2:25 PM, "m...@bortal.de" wrote: > > Here is my config (maybe with some odd setting): > http://pastebin.com/m5d7f5717 > > I played around with: > - max_connections > - shared_buffers > - work_mem > - maintenance_work_mem > - checkpoint_segments > - effective_cache_size > > ..but whatever i do, the graph looks the same. Any hints or tips what my > config should look like? Or are these results even okay? Maybe i am > driving myself crazy for nothing? > > Cheers, > Mario > I'm assuming this is linux: What linux version? What is your kernel's dirty_ratio and background_dirty_ratio? The default for a long time was 40 and 10, respectively. This is far too large for most uses on today's servers, you would not want 40% of your RAM to have pages not yet flushed to disk except perhaps on a small workstation. See Current kernels default to 10 and 5, which is better. What is best for your real life workload will differ from pg_bench here. I don't know if this is the cause for any of your problems, but it is related closely to the checkpoint_segments and checkpoint size/time configuration. Is your xlog on the same device as the data? I have found that for most real world workloads, having the xlog on a separate device helps tremendously. Even more so for 'poor' RAID controllers like the PERC5 -- your sync writes in xlog will be interfering with the RAID controller cache of your data due to bad design. But my pg_bench knowledge with respect to this is limited. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres benchmarking with pgbench
On Thu, Mar 19, 2009 at 3:25 PM, m...@bortal.de wrote: > Hi Greg, > > thanks a lot for your hints. I changed my config and changed raid6 to > raid10, but whatever i do, the benchmark breaks down at a scaling factor 75 > where the database is "only" 1126MB big. > > Here are my benchmark Results (scaling factor, DB size in MB, TPS) using: > pgbench -S -c X -t 1000 -U pgsql -d benchmark -h MYHOST -t 1000 is WAY too short to judge, you'll be seeing a lot of caching effects and no WAL flushing. Try a setting that gets you a run of at least 5 or 10 minutes, preferably a half an hour for more realistic results. Also what is -c X ??? Are you following the -c with the same scaling factor that you used to create the test db? And why the select only (-S)??? > 1 19 8600 > 5 79 8743 > 10 154 8774 > 20 303 8479 > 30 453 8775 > 40 602 8093 > 50 752 6334 > 75 1126 3881 > 150 2247 2297 > 200 2994 701 > 250 3742 656 > 300 4489 596 > 400 5984 552 > 500 7479 513 > > I have no idea if this is any good for a QuardCore Intel(R) Xeon(R) CPU > E5320 @ 1.86GHz with 4GB Ram and 6 SATA disk (7200rpm) in raid 10. > > Here is my config (maybe with some odd setting): > http://pastebin.com/m5d7f5717 > > I played around with: > - max_connections > - shared_buffers You've got this set to 1/2 your memory (2G). I've found that for transactional work it's almost always better to set this much lower and let the OS do the caching, especially once your db is too big to fit in memory. Try setting lowering it and see what happens to your performance envelope. > - work_mem > - maintenance_work_mem > - checkpoint_segments > - effective_cache_size This is set to 3G, but with shared_mem set to 2G, you can't cache more than 2G, because the OS will just be caching the same stuff as pgsql, or less. No biggie. Just not realistic > ..but whatever i do, the graph looks the same. Any hints or tips what my > config should look like? Or are these results even okay? Maybe i am driving > myself crazy for nothing? Could be. What do top and vmstat say during your test run? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Proposal of tunable fix for scalability of 8.4
On 3/19/09 1:49 PM, "Robert Haas" wrote: >> Actually the patch I submitted shows no overhead from what I have seen and I >> think it is useful depending on workloads where it can be turned on even on >> production. > > Well, unless I'm misunderstanding something, waking all waiters every > time could lead to arbitrarily long delays for writers on mostly > read-only workloads... and by arbitrarily along, we mean to say > "potentially just about forever". That doesn't sound safe for > production to me. The other discussion going on indicates that that condition already can happen, shared can always currently cut in line while other shared locks have the lock, though I don't understand all the details. Also, the tests on the 'wake all' version clearly aren't starving anything in a load test with thousands of threads and very heavy lock contention, mostly for shared locks. Instead throughput increases and all wait times decrease. There are several other proposals to make starvation less possible (wake only shared and other proposals that alternate between shared and exclusive; waking only X sized chunks, etc -- its all just investigation into fixing what can be improved on -- solutions that are easily testable should not just be thrown out: the first ones were just the easiest to try). > >> I dont think anything is majorly wrong in my system.. Sometimes it is >> PostgreSQL locks in play and sometimes it can be OS/system related locks in >> play (network, IO, file system, etc). Right now in my patch after I fix >> waiting procarray problem other PostgreSQL locks comes into play: >> CLogControlLock, WALInsertLock , etc. Right now out of the box we have no >> means of tweaking something in production if you do land in that problem. >> With the patch there is means of doing knob control to tweak the bottlenecks >> of Locks for the main workload for which it is put in production. > > I'll reiterate my previous objection: I think your approach is too > simplistic. I think Tom said it the best: a lot of work has gone into > making the locking mechanism lightweight and safe. I'm pretty > doubtful that you're going to find a change that is still safe, but > performs much better. The discussions by Heikki, Simon, and others > about changing the way locks are used or inventing new kinds of locks > seem much more promising to me. The data shows that in this use case, it is not lightweight enough. Enhancing or avoiding a few of these larger global locks is necessary to scale up to larger systems. The other discussions are a direct result of this and excellent -- I don't see the separation you are defining. But If I understand correctly what was said in that other discussion, the current lock implementation can starve out both exclusive access and some shared too. If it hasn't happened in this version, its not likely to happen in the 'wake all' version either, especially since it has been shown to decrease contention. Sometimes, the simplest solution is a good one. I can't tell you how many times I've seen a ton of sophisticated enhancements / proposals to improve scalability or performance be defeated by the simpler solution that most engineers thought was not good enough until faced with empirical evidence. That evidence is what should guide this. > >> Right now.. the standard answer applies.. nope you are running the wrong >> workload for PostgreSQL, use a connection pooler or your own application >> logic. Or maybe.. you have too many users for PostgreSQL use some >> proprietary database. > > Well I certainly agree that we need to get away from that mentality, > although there's nothing particularly evil about a connection > pooler... it might not be suitable for every workload, but you haven't > specified why one couldn't or shouldn't be used in the situation > you're trying to simulate here. > > ...Robert > There's nothing evil about a pooler, and there is nothing evil about making Postgres' concurrency overhead a lot lower either. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres benchmarking with pgbench
Hi Greg, thanks a lot for your hints. I changed my config and changed raid6 to raid10, but whatever i do, the benchmark breaks down at a scaling factor 75 where the database is "only" 1126MB big. Here are my benchmark Results (scaling factor, DB size in MB, TPS) using: pgbench -S -c X -t 1000 -U pgsql -d benchmark -h MYHOST 1 19 8600 5 79 8743 10 154 8774 20 303 8479 30 453 8775 40 602 8093 50 752 6334 75 1126 3881 150 2247 2297 200 2994 701 250 3742 656 300 4489 596 400 5984 552 500 7479 513 I have no idea if this is any good for a QuardCore Intel(R) Xeon(R) CPU E5320 @ 1.86GHz with 4GB Ram and 6 SATA disk (7200rpm) in raid 10. Here is my config (maybe with some odd setting): http://pastebin.com/m5d7f5717 I played around with: - max_connections - shared_buffers - work_mem - maintenance_work_mem - checkpoint_segments - effective_cache_size ..but whatever i do, the graph looks the same. Any hints or tips what my config should look like? Or are these results even okay? Maybe i am driving myself crazy for nothing? Cheers, Mario Greg Smith wrote: On Mon, 16 Mar 2009, m...@bortal.de wrote: Any idea why my performance colapses at 2GB Database size? pgbench results follow a general curve I outlined at http://www.westnet.com/~gsmith/content/postgresql/pgbench-scaling.htm and the spot where performance drops hard depends on how big of a working set of data you can hold in RAM. (That shows a select-only test which is why the results are so much higher than yours, all the tests work similarly as far as the curve they trace). In your case, you've got shared_buffers=1GB, but the rest of the RAM is the server isn't so useful to you because you've got checkpoint_segments set to the default of 3. That means your system is continuously doing small checkpoints (check your database log files, you'll see what I meant), which keeps things from ever really using much RAM before everything has to get forced to disk. Increase checkpoint_segments to at least 30, and bump your transactions/client to at least 10,000 while you're at it--the 32000 transactions you're doing right now aren't nearly enough to get good results from pgbench, 320K is in the right ballpark. That might be enough to push your TPS fall-off a bit closer to 4GB, and you'll certainly get more useful results out of such a longer test. I'd suggest adding in scaling factors of 25, 50, and 150, those should let you see the standard pgbench curve more clearly. On this topic: I'm actually doing a talk introducing pgbench use at tonight's meeting of the Baltimore/Washington PUG, if any readers of this list are in the area it should be informative: http://archives.postgresql.org/bwpug/2009-03/msg0.php and http://omniti.com/is/here for directions. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Proposal of tunable fix for scalability of 8.4
On 3/19/09 10:37 AM, "Bruce Momjian" wrote: > Robert Haas wrote: >>> The original poster's request is for a config parameter, for experimentation >>> and testing by the brave. My own request was for that version of the lock to >>> prevent possible starvation but improve performance by unlocking all shared >>> at once, then doing all exclusives one at a time next, etc. >> >> That doesn't prevent starvation in general, although it will for some >> workloads. >> >> Anyway, it seems rather pointless to add a config parameter that isn't >> at all safe, and adds overhead to a critical part of the system for >> people who don't use it. After all, if you find that it helps, what >> are you going to do? Turn it on in production? I just don't see how >> this is any good other than as a thought-experiment. > > We prefer things to be auto-tuned, and if not, it should be clear > how/when to set the configuration parameter. Of course. The proposal was to leave it at the default, and obviously document that it is not likely to be used. Its 1000x safer than fsync=off . . . > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Proposal of tunable fix for scalability of 8.4
On 3/18/09 2:25 PM, "Robert Haas" wrote: > On Wed, Mar 18, 2009 at 1:43 PM, Scott Carey wrote: Its worth ruling out given that even if the likelihood is small, the fix is easy. However, I don¹t see the throughput drop from peak as more concurrency is added that is the hallmark of this problem < usually with a lot of context switching and a sudden increase in CPU use per transaction. >>> >>> The problem is that the proposed "fix" bears a strong resemblence to >>> attempting to improve your gas mileage by removing a few non-critical >>> parts from your card, like, say, the bumpers, muffler, turn signals, >>> windshield wipers, and emergency brake. >> >> The fix I was referring to as easy was using a connection pooler -- as a >> reply to the previous post. Even if its a low likelihood that the connection >> pooler fixes this case, its worth looking at. > > Oh, OK. There seem to be some smart people saying that's a pretty > high-likelihood fix. I thought you were talking about the proposed > locking change. > Sorry for the confusion, I was countering the contention that a connection pool would fix all of this, and gave that low likelihood of removing the lock contention given the results of the first set of data and its linear ramp-up. I frankly think it is extremely unlikely given the test results that figuring out how to run this with 64 threads (instead of the current linear ramp up to 128) will give 100% CPU utilization. Any system that gets 100% CPU utilization with CPU_COUNT concurrent processes or threads and only 35% with CPU_COUNT*2 would be seriously flawed anyway... The only reasonable reasons for this I can think of would be if each one used enough memory to cause swapping or something else that forces disk i/o. Granted, that Postgres isn't perfect and there is overhead for idle, tiny connections, handling CPU_COUNT*2 connections with half idle and half active as the current test case does, does not invalidate the test -- it makes it realistic. A 64 thread test case that can spend zero time in the client would be useful to provide more information however. >>> While it's true that the car >>> might be drivable in that condition (as long as nothing unexpected >>> happens), you're going to have a hard time convincing the manufacturer >>> to offer that as an options package. >> >> The original poster's request is for a config parameter, for experimentation >> and testing by the brave. My own request was for that version of the lock to >> prevent possible starvation but improve performance by unlocking all shared >> at once, then doing all exclusives one at a time next, etc. > > That doesn't prevent starvation in general, although it will for some > workloads. I'm pretty sure it would, it would guarantee that you alternate between shared and exclusive. Although if the implementation lets shared lockers cut in line at the wrong time it would not be. > > Anyway, it seems rather pointless to add a config parameter that isn't > at all safe, and adds overhead to a critical part of the system for > people who don't use it. After all, if you find that it helps, what > are you going to do? Turn it on in production? I just don't see how > this is any good other than as a thought-experiment. The safety is yet to be determined. The overhead is yet to be determined. You are assuming the worst case for both. If it turns out that the current implementation can cause starvation already, which the parallel discussion here indicates, that makes your starvation concern an issue for both. > > At any rate, as I understand it, even after Jignesh eliminated the > waits, he wasn't able to push his CPU utilization above 48%. Surely > something's not right there. And he also said that when he added a > knob to control the behavior, he got a performance improvement even > when the knob was set to 0, which corresponds to the behavior we have > already anyway. So I'm very skeptical that there's something wrong > with either the system or the test. Until that's understood and > fixed, I don't think that looking at the numbers is worth much. > The next bottleneck at 48% CPU is definitely very interesting. However, it has an explanation: the test blocked on other locks. The observation about the "old" algorithm with his patch going faster should be understood to a point, but you don't need to understand everything in order to show that it is safe or better. There are changes made though that may explain that. In Jignesh's words: " still using default logic (thought different way I compare sequential using fields from the previous proc structure instead of comparing with constant boolean) " It is possible that that minor change did some cache locality and/or branch prediction trick on the processor he has. I've seen plenty of strange effects caused by tiny changes before. Its expected to find the unexpected. It will be useful to know what caused the improvement (was it the
Re: [PERFORM] Proposal of tunable fix for scalability of 8.4
> Actually the patch I submitted shows no overhead from what I have seen and I > think it is useful depending on workloads where it can be turned on even on > production. Well, unless I'm misunderstanding something, waking all waiters every time could lead to arbitrarily long delays for writers on mostly read-only workloads... and by arbitrarily along, we mean to say "potentially just about forever". That doesn't sound safe for production to me. > I dont think anything is majorly wrong in my system.. Sometimes it is > PostgreSQL locks in play and sometimes it can be OS/system related locks in > play (network, IO, file system, etc). Right now in my patch after I fix > waiting procarray problem other PostgreSQL locks comes into play: > CLogControlLock, WALInsertLock , etc. Right now out of the box we have no > means of tweaking something in production if you do land in that problem. > With the patch there is means of doing knob control to tweak the bottlenecks > of Locks for the main workload for which it is put in production. I'll reiterate my previous objection: I think your approach is too simplistic. I think Tom said it the best: a lot of work has gone into making the locking mechanism lightweight and safe. I'm pretty doubtful that you're going to find a change that is still safe, but performs much better. The discussions by Heikki, Simon, and others about changing the way locks are used or inventing new kinds of locks seem much more promising to me. > Right now.. the standard answer applies.. nope you are running the wrong > workload for PostgreSQL, use a connection pooler or your own application > logic. Or maybe.. you have too many users for PostgreSQL use some > proprietary database. Well I certainly agree that we need to get away from that mentality, although there's nothing particularly evil about a connection pooler... it might not be suitable for every workload, but you haven't specified why one couldn't or shouldn't be used in the situation you're trying to simulate here. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Need help with one query
Hi,
We have the following 2 tables:
\d audit_change
Table "public.audit_change"
Column | Type | Modifiers
++---
id | character varying(32) | not null
audit_entry_id | character varying(32) |
...
Indexes:
"audit_change_pk" primary key, btree (id)
"audit_change_entry" btree (audit_entry_id)
and
\d audit_entry;
Table "public.audit_entry"
Column | Type | Modifiers
+--+---
id | character varying(32)| not null
object_id | character varying(32)| not null
...
Indexes:
"audit_entry_pk" primary key, btree (id)
"audit_entry_object" btree (object_id)
We do the following query:
EXPLAIN ANALYZE
SELECT audit_change.id AS id,
audit_change.audit_entry_id AS auditEntryId,
audit_entry.object_id AS objectId,
audit_change.property_name AS propertyName,
audit_change.property_type AS propertyType,
audit_change.old_value AS
oldValue, audit_change.new_value AS newValue,
audit_change.flexfield AS flexField
FROM audit_entry audit_entry, audit_change audit_change
WHERE audit_change.audit_entry_id=audit_entry.id
AND audit_entry.object_id='artf414029';
QUERY
PLAN
-
Hash Join (cost=8.79..253664.55 rows=4 width=136) (actual
time=4612.674..6683.158 rows=4 loops=1)
Hash Cond: ((audit_change.audit_entry_id)::text = (audit_entry.id)::text)
-> Seq Scan on audit_change (cost=0.00..225212.52 rows=7584852
width=123) (actual time=0.009..2838.216 rows=7584852 loops=1)
-> Hash (cost=8.75..8.75 rows=3 width=45) (actual time=0.049..0.049
rows=4 loops=1)
-> Index Scan using audit_entry_object on audit_entry
(cost=0.00..8.75 rows=3 width=45) (actual time=0.033..0.042 rows=4 loops=1)
Index Cond: ((object_id)::text = 'artf414029'::text)
Total runtime: 6683.220 ms
(7 rows)
Why does the query not use the index on audit_entry_id and do a seq scan
(as you see the table has many rows)?
If we split the query into 2 queries, it only takes less than 0.3 ms
EXPLAIN ANALYZE select * from audit_entry WHERE
audit_entry.object_id='artf414029';
QUERY PLAN
--
Index Scan using audit_entry_object on audit_entry (cost=0.00..8.75
rows=3 width=111) (actual time=0.037..0.044 rows=4 loops=1)
Index Cond: ((object_id)::text = 'artf414029'::text)
Total runtime: 0.073 ms
(3 rows)
EXPLAIN ANALYZE select * from audit_change WHERE audit_entry_id in
('adte1DDFEA5B011C8988C3928752', 'adte5DDFEA5B011D441230BD20CC',
'adte5DDFEA5B011E40601E8DA10F', 'adte5DDFEA5B011E8CC26071627C') ORDER BY
property_name ASC;
QUERY PLAN
---
Sort (cost=30.25..30.27 rows=10 width=123) (actual time=0.190..0.192
rows=4 loops=1)
Sort Key: property_name
-> Bitmap Heap Scan on audit_change (cost=9.99..30.08 rows=10
width=123) (actual time=0.173..0.177 rows=4 loops=1)
Recheck Cond: ((audit_entry_id)::text = ANY
(('{adte1DDFEA5B011C8988C3928752,adte5DDFEA5B011D441230BD20CC,adte5DDFEA5B011E40601E8DA10F,adte5DDFEA5B011E8CC26071627C}'::character
varying[])::text[]))
-> Bitmap Index Scan on audit_change_entry (cost=0.00..9.99
rows=10 width=0) (actual time=0.167..0.167 rows=4 loops=1)
Index Cond: ((audit_entry_id)::text = ANY
(('{adte1DDFEA5B011C8988C3928752,adte5DDFEA5B011D441230BD20CC,adte5DDFEA5B011E40601E8DA10F,adte5DDFEA5B011E8CC26071627C}'::character
varying[])::text[]))
Total runtime: 0.219 ms
(7 rows)
Thanks for your help,
Anne
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Proposal of tunable fix for scalability of 8.4
Robert Haas wrote: > > The original poster's request is for a config parameter, for experimentation > > and testing by the brave. My own request was for that version of the lock to > > prevent possible starvation but improve performance by unlocking all shared > > at once, then doing all exclusives one at a time next, etc. > > That doesn't prevent starvation in general, although it will for some > workloads. > > Anyway, it seems rather pointless to add a config parameter that isn't > at all safe, and adds overhead to a critical part of the system for > people who don't use it. After all, if you find that it helps, what > are you going to do? Turn it on in production? I just don't see how > this is any good other than as a thought-experiment. We prefer things to be auto-tuned, and if not, it should be clear how/when to set the configuration parameter. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Extremely slow intarray index creation and inserts.
On Thu, 19 Mar 2009, Oleg Bartunov wrote: On Thu, 19 Mar 2009, Tom Lane wrote: Oleg Bartunov writes: We usually say about 200 unique values as a limit for gist_int_ops. That seems awfully small ... should we make gist_intbig_ops the default, or more likely, raise the signature size of both opclasses? Even at a crossover point of 1 I'm not sure that many real-world apps would bother considering gist_int_ops. gist__int_ops doesn't uses signatures, it uses range compression, which is not lossy, but not capacious. Perhaps, that's why we decided to use it as sorry, it's lossy default opclass. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Extremely slow intarray index creation and inserts.
On Thu, 19 Mar 2009, Tom Lane wrote: Oleg Bartunov writes: We usually say about 200 unique values as a limit for gist_int_ops. That seems awfully small ... should we make gist_intbig_ops the default, or more likely, raise the signature size of both opclasses? Even at a crossover point of 1 I'm not sure that many real-world apps would bother considering gist_int_ops. gist__int_ops doesn't uses signatures, it uses range compression, which is not lossy, but not capacious. Perhaps, that's why we decided to use it as default opclass. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Extremely slow intarray index creation and inserts.
Oleg Bartunov writes: > We usually say about 200 unique values as a limit for > gist_int_ops. That seems awfully small ... should we make gist_intbig_ops the default, or more likely, raise the signature size of both opclasses? Even at a crossover point of 1 I'm not sure that many real-world apps would bother considering gist_int_ops. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Prepared statement does not exist
--- On Thu, 19/3/09, Nimesh Satam wrote: > > We are receving the following error in the postgres > database logs: > > 2009-03-19 02:14:20 PDT [2547]: [79-1] LOG: duration: > 0.039 ms statement: > RESET ALL > 2009-03-19 02:14:20 PDT [2547]: [80-1] LOG: duration: > 0.027 ms statement: > SET SESSION AUTHORIZATION DEFAULT > 2009-03-19 02:14:20 PDT [2547]: [81-1] ERROR: prepared > statement "S_1" does > not exist > 2009-03-19 02:14:20 PDT [2547]: [82-1] STATEMENT: > DEALLOCATE "S_1" > 2009-03-19 02:14:20 PDT [2547]: [83-1] ERROR: prepared > statement "S_4" does > not exist > 2009-03-19 02:14:20 PDT [2547]: [84-1] STATEMENT: > DEALLOCATE "S_4" > > We receive this errors when we start connecting the java > application > thorugh pgpool. What causes this problem and how can it be > avoided? Looks like your app is dissconnecting from pgpool which is causing pgpool to send the RESET ALL, this will deallocate the prepared statement. Then the app is reconnecting to pgpool again and expecting the prepared statement to still be available, which it will not be. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Prepared statement does not exist
Hi, I am not sure if sending this to the right place. I did try to get the answer from pgpool mailing list but no luck . Would appreciate if someone can help here. We are receving the following error in the postgres database logs: 2009-03-19 02:14:20 PDT [2547]: [79-1] LOG: duration: 0.039 ms statement: RESET ALL 2009-03-19 02:14:20 PDT [2547]: [80-1] LOG: duration: 0.027 ms statement: SET SESSION AUTHORIZATION DEFAULT 2009-03-19 02:14:20 PDT [2547]: [81-1] ERROR: prepared statement "S_1" does not exist 2009-03-19 02:14:20 PDT [2547]: [82-1] STATEMENT: DEALLOCATE "S_1" 2009-03-19 02:14:20 PDT [2547]: [83-1] ERROR: prepared statement "S_4" does not exist 2009-03-19 02:14:20 PDT [2547]: [84-1] STATEMENT: DEALLOCATE "S_4" We receive this errors when we start connecting the java application thorugh pgpool. What causes this problem and how can it be avoided? Postgres version: 8.3.3 pgpool II: 2.0.1 Thanks & Regards, Nimesh.
Re: [PERFORM] Extremely slow intarray index creation and inserts.
We usually say about 200 unique values as a limit for gist_int_ops. On Wed, 18 Mar 2009, Tom Lane wrote: Ron Mayer writes: Oleg Bartunov wrote: OB:> it's not about short or long arrays, it's about small or big OB:> cardinality of the whole set (the number of unique elements) I'm re-reading the docs and still wasn't obvious to me. A potential docs patch is attached below. Done, though not in exactly those words. I wonder though if we can be less vague about it --- can we suggest a typical cutover point? Like "use gist__intbig_ops if there are more than about 10,000 distinct array values"? Even a rough order of magnitude for where to worry about this would save a lot of people time. regards, tom lane Index: intarray.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/intarray.sgml,v retrieving revision 1.5 retrieving revision 1.6 diff -c -r1.5 -r1.6 *** intarray.sgml 10 Dec 2007 05:32:51 - 1.5 --- intarray.sgml 18 Mar 2009 20:18:18 - 1.6 *** *** 237,245 Two GiST index operator classes are provided: gist__int_ops (used by default) is suitable for !small and medium-size arrays, while gist__intbig_ops uses a larger signature and is more !suitable for indexing large arrays. --- 237,246 Two GiST index operator classes are provided: gist__int_ops (used by default) is suitable for !small- to medium-size data sets, while gist__intbig_ops uses a larger signature and is more !suitable for indexing large data sets (i.e., columns containing !a large number of distinct array values). Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
