Re: [PERFORM] Query performance over a large proportion of data
decibel wrote: On Mar 10, 2009, at 4:12 PM, Steve McLellan wrote: The server itself is a dual-core 3.7GHz Xeon Dell (each core reporting 2 logical CPUs) running an amd64 build of FreeBSD 6.2, and postgres 8.3.5 built from source. Uh, you're running an amd64 build on top of an Intel CPU? I didn't think FBSD would allow that, but if it does it wouldn't surprise me if kernel/OS performance stunk. If Postgres then used the same settings it would make matters even worse (IIRC there is some code that's different in an AMD vs Intel build). Uh? Amd64 just the name of the FreeBSD port for AMD/Intel 64 bit CPUs. See: http://www.freebsd.org/platforms/amd64.html and: http://en.wikipedia.org/wiki/X86-64 Cheers -- Matteo Beccati OpenX - http://www.openx.org -- 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 Mar 13, 2009, at 3:02 PM, Jignesh K. Shah wrote: vmstat seems similar to wakeup some kthr memorypagedisk faults cpu r b w swap free re mf pi po fr de sr s0 s1 s2 sd in sy cs us sy id 63 0 0 45535728 38689856 0 14 0 0 0 0 0 0 0 0 0 163318 334225 360179 47 17 36 85 0 0 45436736 38690760 0 6 0 0 0 0 0 0 0 0 0 165536 347462 365987 47 17 36 59 0 0 45405184 38681752 0 11 0 0 0 0 0 0 0 0 0 155153 326182 345527 47 16 37 53 0 0 45393816 38673344 0 6 0 0 0 0 0 0 0 0 0 152752 317851 340737 47 16 37 66 0 0 45378312 38651920 0 11 0 0 0 0 0 0 0 0 0 150979 304350 336915 47 16 38 67 0 0 45489520 38639664 0 5 0 0 0 0 0 0 0 0 0 157188 318958 351905 47 16 37 82 0 0 45483600 38633344 0 10 0 0 0 0 0 0 0 0 0 168797 348619 375827 47 17 36 68 0 0 45463008 38614432 0 9 0 0 0 0 0 0 0 0 0 173020 376594 385370 47 18 35 54 0 0 45451376 38603792 0 13 0 0 0 0 0 0 0 0 0 161891 342522 364286 48 17 35 41 0 0 45356544 38605976 0 5 0 0 0 0 0 0 0 0 0 167250 358320 372469 47 17 36 27 0 0 45323472 38596952 0 11 0 0 0 0 0 0 0 0 0 165099 344695 364256 48 17 35 The good news is there's now at least enough runnable procs. What I find *extremely* odd is the CPU usage is almost dead constant... -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- 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 Mar 13, 2009, at 8:05 AM, Gregory Stark wrote: "Jignesh K. Shah" writes: Scott Carey wrote: On 3/12/09 11:37 AM, "Jignesh K. Shah" wrote: In general, I suggest that it is useful to run tests with a few different types of pacing. Zero delay pacing will not have realistic number of connections, but will expose bottlenecks that are universal, and less controversial I think I have done that before so I can do that again by running the users at 0 think time which will represent a "Connection pool" which is highly utilized" and test how big the connection pool can be before the throughput tanks.. This can be useful for App Servers which sets up connections pools of their own talking with PostgreSQL. Keep in mind when you do this that it's not interesting to test a number of connections much larger than the number of processors you have. Once the system reaches 100% cpu usage it would be a misconfigured connection pooler that kept more than that number of connections open. How certain are you of that? I believe that assertion would only be true if a backend could never block on *anything*, which simply isn't the case. Of course in most systems you'll usually be blocking on IO, but even in a ramdisk scenario there's other things you can end up blocking on. That means having more threads than cores isn't unreasonable. If you want to see this in action in an easy to repeat test, try compiling a complex system (such as FreeBSD) with different levels of -j handed to make (of course you'll need to wait until everything is in cache, and I'm assuming you have enough memory so that everything would fit in cache). -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- 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 Mar 12, 2009, at 2:22 PM, Jignesh K. Shah wrote: Something that might be useful for him to report is the avg number of active backends for each data point ... short of doing select * from pg_stat_activity and removing the IDLE entries, any other clean way to get that information. Uh, isn't there a DTrace probe that would provide that info? It certainly seems like something you'd want to know... -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- 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 Mar 11, 2009, at 10:48 PM, Jignesh K. Shah wrote: Fair enough.. Well I am now appealing to all who has a fairly decent sized hardware want to try it out and see whether there are "gains", "no-changes" or "regressions" based on your workload. Also it will help if you report number of cpus when you respond back to help collect feedback. Do you have a self-contained test case? I have several boxes with 16- cores worth of Xeon with 96GB I could try it on (though you might not care about having "only" 16 cores :P) -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- 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] Query performance over a large proportion of data
On Mar 10, 2009, at 4:12 PM, Steve McLellan wrote: The server itself is a dual-core 3.7GHz Xeon Dell (each core reporting 2 logical CPUs) running an amd64 build of FreeBSD 6.2, and postgres 8.3.5 built from source. Uh, you're running an amd64 build on top of an Intel CPU? I didn't think FBSD would allow that, but if it does it wouldn't surprise me if kernel/OS performance stunk. If Postgres then used the same settings it would make matters even worse (IIRC there is some code that's different in an AMD vs Intel build). -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- 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] Query much slower when run from postgres function
On Mar 10, 2009, at 12:20 PM, Tom Lane wrote: f...@redhat.com (Frank Ch. Eigler) writes: For a prepared statement, could the planner produce *several* plans, if it guesses great sensitivity to the parameter values? Then it could choose amongst them at run time. We've discussed that in the past. "Choose at runtime" is a bit more easily said than done though --- you can't readily flip between plan choices part way through, if you've already emitted some result rows. True, but what if we planned for both high and low cardinality cases, assuming that pg_stats indicated both were a possibility? We would have to store multiple plans for one prepared statement, which wouldn't work well for more complex queries (if you did high and low cardinality estimates for each table you'd end up with 2^r plans, where r is the number of relations), so we'd need a way to cap it somehow. Of course, whether that's easier than having the ability to throw out a current result set and start over with a different plan is up for debate... On a related note, I wish there was a way to tell plpgsql not to pre- plan a query. Sure, you can use EXECUTE, but building the query plan is a serious pain in the rear. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- 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] Query much slower when run from postgres function
On Mar 9, 2009, at 8:36 AM, Mario Splivalo wrote: Now, as I was explained on pg-jdbc mailinglist, that 'SET enable_seqscan TO false' affects all queries on that persistent connection from tomcat, and It's not good solution. So I wanted to post here to ask what other options do I have. FWIW, you can avoid that with SET LOCAL (though it would still affect the rest of the transaction). You could also store whatever enable_seqscan was set to in a variable before setting it to false and then set it back when you're done. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- 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
Heikki Linnakangas writes: > WALInsertLock is also quite high on Jignesh's list. That I've seen > become the bottleneck on other tests too. Yeah, that's been seen to be an issue before. I had the germ of an idea about how to fix that: ... with no lock, determine size of WAL record ... obtain WALInsertLock identify WAL start address of my record, advance insert pointer past record end *release* WALInsertLock without lock, copy record into the space just reserved The idea here is to allow parallelization of the copying of data into the buffers. The hold time on WALInsertLock would be very short. Maybe it could even become a spinlock, though I'm not sure, because the "advance insert pointer" bit is more complicated than it looks (you have to allow for the extra overhead when crossing a WAL page boundary). Now the fly in the ointment is that there would need to be some way to ensure that we didn't write data out to disk until it was valid; in particular how do we implement a request to flush WAL up to a particular LSN value, when maybe some of the records before that haven't been fully transferred into the buffers yet? The best idea I've thought of so far is shared/exclusive locks on the individual WAL buffer pages, with the rather unusual behavior that writers of the page would take shared lock and only the reader (he who has to dump to disk) would take exclusive lock. But maybe there's a better way. Currently I don't believe that dumping a WAL buffer (WALWriteLock) blocks insertion of new WAL data, and it would be nice to preserve that property. 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] 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
Robert Haas writes: > On Fri, Mar 13, 2009 at 10:06 PM, Tom Lane wrote: >> I assume you meant effective_io_concurrency. We'd still need a special >> case because the default is currently hard-wired at 1, not 0, if >> configure thinks the function exists. > I think 1 should mean no prefetching, rather than 0. No, 1 means "prefetch a single block ahead". It doesn't involve I/O concurrency in the sense of multiple I/O requests being processed at once; what it does give you is CPU vs I/O concurrency. 0 shuts that down and returns the system to pre-8.4 behavior. 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] Proposal of tunable fix for scalability of 8.4
On Wed, 2009-03-11 at 16:53 -0400, Jignesh K. Shah wrote: > 1200: 2000: Medium Throughput: -1781969.000 Avg Medium Resp: 0.019 I think you need to iron out bugs in your test script before we put too much stock into the results generated. Your throughput should not be negative. I'd be interested in knowing the number of S and X locks requested, so we can think about this from first principles. My understanding is that ratio of S:X is about 10:1. Do you have more exact numbers? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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
Tom Lane wrote: Robert Haas writes: I think that changing the locking behavior is attacking the problem at the wrong level anyway. Right. By the time a patch here could have any effect, you've already lost the game --- having to deschedule and reschedule a process is a large cost compared to the typical lock hold time for most LWLocks. So it would be better to look at how to avoid blocking in the first place. I think the elephant in the room is that we have a single lock that needs to be acquired every time a transaction commits, and every time a backend takes a snapshot. It has worked well, and it still does for smaller numbers of CPUs, but I'm not surprised it starts to become a bottleneck on a test like the one Jignesh is running. To make matters worse, the more backends there are, the longer the lock needs to be held to take a snapshot. It's going require some hard thinking to bust that bottleneck. I've sometimes thought about maintaining a pre-calculated array of in-progress XIDs in shared memory. GetSnapshotData would simply memcpy() that to private memory, instead of collecting the xids from ProcArray. Or we could try to move some of the if-tests inside the for-loop to after the ProcArrayLock is released. For example, we could easily remove the check for "proc == MyProc", and remove our own xid from the array afterwards. That's just linear speed up, though. I can't immediately think of a way to completely avoid / partition away the contention. WALInsertLock is also quite high on Jignesh's list. That I've seen become the bottleneck on other tests too. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
