"Guillaume Smet" <guillaume.smet@gmail.com> writes: > I took a couple of very simple read only queries executed in the pages > to create a pgbench script and I have the following results: Hmm ... I can reproduce a consistent difference of about three percent between 8.2 and HEAD. Using pgbench's built-in SELECT-only transaction after "pgbench -i -s 10 bench", I get HEAD: $ time pgbench -n -S -c 10 -t 100000 bench transaction type: SELECT only scaling factor: 10 number of clients: 10 number of transactions per client: 100000 number of transactions actually processed: 1000000/1000000 tps = 9399.185809 (including connections establishing) tps = 9402.305058 (excluding connections establishing) real 1m46.402s user 0m19.889s sys 0m23.497s 8.2: $ time pgbench -n -S -c 10 -t 100000 bench82 transaction type: SELECT only scaling factor: 10 number of clients: 10 number of transactions per client: 100000 number of transactions actually processed: 1000000/1000000 tps = 9729.892729 (including connections establishing) tps = 9732.769774 (excluding connections establishing) real 1m42.785s user 0m19.250s sys 0m23.646s Vanilla build (--enable-debug but not much else), C locale, SQL_ASCII encoding, dual Xeon/EMT on Fedora Core 6. Configuration parameters are all defaults in both cases, except I had fsync off, which shouldn't matter anyway in a read-only test. The weird thing is that after a couple of hours of poking at it with oprofile and other sharp objects, I have no idea *why* it's slower. oprofile shows just about the same relative percentages for all the hot-spot functions in the backend. strace shows that there's no particular increase in kernel calls (indeed, HEAD seems to use significantly fewer semops/selects, indicating that we had some success in reducing contention). It's not that autovacuum is now on by default --- turning it off made no particular difference. It's not that stats collection is now on by default --- ditto. Slowing down the walwriter and bgwriter doesn't help either. It's not pgbench itself --- I get about the same results if I use 8.2 pgbench with HEAD or vice versa. The best theory I can come up with is that all the new stuff added to the backend (the executable is about 12% larger than in 8.2) has resulted in some generalized slowdown just because the code is larger. But most of the added code isn't getting exercised by this test, so in theory the code bloat shouldn't be hurting us either. Weird. Given that it's only a couple percent I'm not gonna panic about it, but I would like to know where the time is going ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly

Greg Smith <gsmith@gregsmith.com> writes: > On Wed, 21 Nov 2007, Guillaume Smet wrote: >> *** 8.2 *** >> tps = 853.360277 (including connections establishing) >> >> *** 8.3 *** >> tps = 784.819087 (including connections establishing) > This is an 8% drop. I've seen a larger difference than that between two > identical installations of the same version when the database is many GB > large. Hard drives deliver a higher transfer rate at their inner > portions, typically the start of the disk from the operating system's > perspective. It's not unusual for the slow parts of the disk to be 30-40% > slower than the fast ones. FWIW, the test cases I was just comparing are entirely CPU-bound --- vmstat says there are no disk reads happening at all. Now I only got a 3% drop, so that may not be the same effect Guillaume is seeing. But the whole thing is a bit upsetting seeing that we thought we'd reduced the overhead for short read-only transactions ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org

I wrote: > The weird thing is that after a couple of hours of poking at it with > oprofile and other sharp objects, I have no idea *why* it's slower. > oprofile shows just about the same relative percentages for all the > hot-spot functions in the backend. However, some comparisons with gprof show that the planner is calling the hot-spot functions more than it used to, which might be enough to account for a couple percent on trivial queries like the ones being issued by pg_bench ("SELECT abalance FROM accounts WHERE aid = :aid;"). After the holiday I'll look into refactoring to try to avoid the extra calls. Another issue is that on read-only transactions there's an extra gettimeofday() call caused by pgstat_report_tabstats, which could be a problem on machines with slow gettimeofday(). However that shouldn't happen if you've got track_counts turned off, so if you don't see any difference with or without stats then it's not the problem for you. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

"Guillaume Smet" <guillaume.smet@gmail.com> writes: > I'm not saying my benchmark is perfectly relevant: I made it > excessively simple on purpose. I just see a general slow down which is > quite consistent accross all the tests I did (with pgbench or the > application) and I'd really like to know if it's just my case on this > particular box or something more general. Are you examining only "trivial" queries? I've been able to identify a couple of new planner hotspots that could explain some slowdown if the planning time is material compared to the execution time. If you're seeing a slowdown on queries that run for awhile, that would be something else ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

"Guillaume Smet" <guillaume.smet@gmail.com> writes: > On Nov 22, 2007 6:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Are you examining only "trivial" queries? I've been able to identify a >> couple of new planner hotspots that could explain some slowdown if the >> planning time is material compared to the execution time. If you're >> seeing a slowdown on queries that run for awhile, that would be >> something else ... > Yes, I kept only queries with no join and a couple of where > conditions. As I explained previously, I can reproduce the behavior > with a single index scan on only one table (plan posted previously). > If anyone is interested I can post the content of this table (there's > nothing confidential in it so I should have the customer permission) > and a couple of instructions to reproduce the test case. I don't think you need to --- the "read-only transaction" case built into pgbench is probably an equivalent test. What it looks like to me is that the EquivalenceClass mechanism has added a little bit of overhead, which isn't actually buying much of anything in these trivial cases. I'll look at whether it can be short-circuited. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster

Gregory Stark <stark@enterprisedb.com> writes: > "Tom Lane" <tgl@sss.pgh.pa.us> writes: >> The weird thing is that after a couple of hours of poking at it with >> oprofile and other sharp objects, I have no idea *why* it's slower. >> oprofile shows just about the same relative percentages for all the >> hot-spot functions in the backend. > Out of curiosity have you recompiled 8.2.5 recently? That is, are they > compiled with the same version of gcc? CVS tip of both branches, freshly compiled for this test. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

"Guillaume Smet" <guillaume.smet@gmail.com> writes: > I thought I could also perform a test on CVS head every month from > December 2006 to now to see if it can give us a better idea of when > the overhead first appeared. Ping me if you're interested in it. If you feel like doing that, it might be interesting just on general principles ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster

I wrote: > I don't think you need to --- the "read-only transaction" case built > into pgbench is probably an equivalent test. What it looks like to > me is that the EquivalenceClass mechanism has added a little bit of > overhead, which isn't actually buying much of anything in these > trivial cases. I'll look at whether it can be short-circuited. I've knocked down a few bits of low-hanging fruit. What I see with this evening's CVS HEAD is that 8.3 is roughly on par with 8.2 for the "pgbench -S -c 10" case, if you compare them with stats collection turned off. Turning stats collection on slows 8.3 by a percent or so --- but 8.2 takes a very much larger hit with stats collection on, about 25%. So I'm satisfied with these results, particularly in view of the fact that what we're measuring is certainly the stupidest, least efficient way to use Postgres. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend

"Jonah H. Harris" <jonah.harris@gmail.com> writes: > On Nov 23, 2007 8:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> So I'm satisfied with these results, particularly in view of the >> fact that what we're measuring is certainly the stupidest, least >> efficient way to use Postgres. > Given the emerging world of data-driven content management systems and > select-mostly web applications, I'd hesitate to say that select-only > transactions aren't worth optimizing for. I didn't intend to say that select-only transactions aren't interesting; rather that there should be some minimal effort on the application side. The cases we are testing here involve: 1. One query per transaction. Even with the 8.3 improvements to reduce overhead for select-only transactions, this isn't necessarily a good idea. 2. *Extremely* trivial queries --- fetching one row from one table on the basis of its primary key --- which make you wonder why the programmer is using a SQL database rather than ndb or some such. Anyone who's used SQL for any length of time knows that it's better to push more of the application logic onto the database side, but these queries are typical of apps that think they should do most of the work. 3. No use of prepared statements. Duh, especially in view of #2. There are reasons to avoid prepared statements in the case that you're issuing commands that have some intellectual interest for the planner, but these are not those. The whole thing is the worst-case scenario for a DBMS that spends any real effort on analyzing/planning SQL commands. Even given all that, I think 8.3 would look pretty good if we were throwing enough concurrent queries at it to make Florian's transaction concurrency improvements a factor. But with only 10 concurrent sessions, and pgbench's known limitations for issuing concurrent commands fast enough to keep things busy, there's no opportunity to shine in that dimension either. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly

"Guillaume Smet" <guillaume.smet@gmail.com> writes: > Using pgbench -n -S -c 10 -t 100000, I also have CVS tip as fast as > CVS from january. But using my set of queries, it's not. Would you show us the test case you're using? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend

"Guillaume Smet" <guillaume.smet@gmail.com> writes: > On Nov 25, 2007 6:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Would you show us the test case you're using? > Sure, it's the same queries I posted earlier. What about the table schemas? > I send a link to the data to both of you in private. I doubt that the specific data is important. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster

"Guillaume Smet" <guillaume.smet@gmail.com> writes: > Sure, it's the same queries I posted earlier. My pgbench script is the > following: > BEGIN > select libvil from vilsitelang where codelang='FRA' and codevil='LYO' > select TL.motsclesmetatags, TL.descriptifmeta, TL.motcleoverture_l, > TL.motcleoverture_c, TL.baselinetheme from themelang TL where > TL.codeth = 'ASS' and TL.codelang = 'FRA' > SELECT libvilpubwoo, codelang, codepays, petiteville FROM vilsite > WHERE codevil = 'LYO' > select libvil from vilsitelang where codelang='FRA' and codevil='LYO' > END I poked into this a bit, and it seems the extra overhead is all coming from resolving the ambiguous "=" operators. That didn't show up in my test because my query had "int4_column = int4_const" which is an exact match to a pg_operator entry. But since your columns are varchar, which doesn't have any operators of its own, we have to go through oper_select_candidate(), which is noticeably slower than before. The slowdown seems to have two causes: 1. Datatype bloat: there are 58 "=" operators in pg_operator today, versus 54 at the beginning of the year. That's 7% more work right there to sort through the additional operators. 2. Removal of pg_cast entries associated with explicit varchar coercions: when there's not a pg_cast entry for the desired coercion, find_coercion_pathway does a second catalog lookup to see if it might be an array case. That happens more often in this test case than it did at the start of the year, because I got rid of pg_cast entries that could be replaced by the generic CoerceViaIO mechanism. I'm not sure how big a hit #2 really is. Presumably the removal of the redundant entries has some distributed savings associated with it, which would partially counteract the extra lookup; but I don't have any tools that can isolate the cost of those particular SearchSysCache calls out of all the rest. In any case, #2 is specific to varchar and text while effect #1 is an issue for just about everything. The cost of resolving ambiguous operators has been an issue for a long time, of course, but it seems particularly bad in this case --- gprof blames 37% of the runtime on oper_select_candidate(). It might be time to think about caching the results of operator searches somehow. Too late for 8.3 though. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Gregory Stark <stark@enterprisedb.com> writes: > "Tom Lane" <tgl@sss.pgh.pa.us> writes: >> But since your columns are varchar, which doesn't have any operators of its >> own, we have to go through oper_select_candidate() > I wonder whether at some point we shouldn't just eliminate this distinction > entirely. Just make "text" and "varchar" the same type and spell it "text" > when there's no typmod length restriction and "varchar(x)" when there is. I've thought about that more than once, but I'm worried that it would eliminate one of the few heavily-used cases we have for binary-compatible operations, thereby making it even harder to find performance issues for those situations. In any case, it wouldn't do anything to fix the basic problem that ambiguous-operator resolution is expensive when there are lots of similarly-named operators. We've chipped away at that with various hacks over the years, but I don't think it's ever occurred to us (or at least to me) before to try short-circuiting the entire process through a lookaside cache. We'd probably need to flush the cache on changes in pg_operator or pg_cast, but neither of those change often ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Simon Riggs <simon@2ndquadrant.com> writes: > But I think there must be an action that we can take for 8.3 and that > much runtime should not be given away easily. ISTM that we can win back > the losses Guillaume has identified, plus gain a little more even. Perhaps some sanity could be restored to this discussion by pointing out that the 2007-01-01 code *also* clocks in at 37% spent in oper_select_candidate. IOW it's been like this for a very long time. I'm not interested in destabilizing 8.3 with panicky last-minute patches. > So how about we have a cache-of-one: Cache-of-one has exactly the same difficulty as cache-of-many, other than the table lookup itself, which is a solved problem (hashtable). You still have to determine how you identify the cached value and what events require a cache flush. Nor do I see any particular reason to assume that a cache of only one operator would be of any use for real-world apps, as opposed to toy examples. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings

Gregory Stark <stark@enterprisedb.com> writes: > A 5-line patch which improves performance by 40% for any case sounds amazing, > but how fragile is that gain? The kind of thing which would be worryign is if > runing a query which uses both varchar and some other ambiguous operator > causes it to lose all its gain. Yeah, exactly. If we're going to risk anything like this at all, the cache-of-one restriction is simply not acceptable (especially given that the part of the coding it would eliminate is the simplest and easiest-to-get-right part). In the test case Guillame provided, every single WHERE clause happens to be of the form varchar_column = 'unknown-type literal' and there are no other operators used in the SELECT lists; but I can hardly believe that this is representative of any significant number of real-world applications. Even pgbench uses more than one operator. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > Are we likely to see the 3% or the 7% performance degradation with > version 8.3? Probably not, since it sounds like your queries are typically not as trivial as the ones in Guillame's test case. IOW there will be some slowdown but it's likely to be in the noise for you, and also very likely made up by improvements elsewhere. The test case is in the unfortunate position of not being helped materially by *any* of the work we've done for 8.3. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster

Simon Riggs <simon@2ndquadrant.com> writes: > Here's where I am: > Basic test was to replace call to oper_select_candidate with a single > item that was fed by a hardcoded value for varchar equality operator. Well, that confirms what we knew from gprof, but surely you aren't proposing that as a usable patch. > What I'm actually proposing is a patch implementing a oper_select_hook > function pointer, which allows the user to do anything they want. Why in the world would that be a good idea? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match