Thread
-
Re: eWeek Poll: Which database is most critical to your
Dann Corbit <dcorbit@connx.com> — 2002-02-26T23:20:17Z
-----Original Message----- From: Neil Conway [mailto:nconway@klamath.dyndns.org] Sent: Tuesday, February 26, 2002 3:04 PM To: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] eWeek Poll: Which database is most critical to your On Tue, 2002-02-26 at 15:30, Zak Greant wrote: > Good Day All, > > eWeek has posted a poll that asks which database server is most critical > to your organization. The article mentions a MySQL feature which apparently improved performance considerably: // MySQL 4.0.1's new, extremely fast query cache is also quite notable, as no other database we tested had this feature. If the text of an incoming query has a byte-for-byte match with a cached query, MySQL can retrieve the results directly from the cache without compiling the query, getting locks or doing index accesses. This query caching will be effective only for tables with few updates because any table updates that clear the cache to guarantee correct results are always returned. // My guess is that it would be relatively simple to implement. Any comments on this? If I implemented this, any chance this would make it into the tree? Of course, it would be: - disabled by default - enabled on a table-by-table basis (maybe an ALTER TABLE command) >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >> I don't see how it will do any good. There is no "prepare" in Postgresql and therefore you will simply be reexecuting the queries every time any way. Also, parameter markers only work in embedded SQL and that is a single tasking system. I think it would be a major piece of work to do anything useful along those lines. If you look at how DB/2 works, you will see that they store prepared statements. Another alternative would be to keep some point in the parser marked and somehow jump to that point, but you would have to be able to save a parse tree somewhere and also recognize the query. Here is where problems come in... -- Someone wants blue and blue-green, etc shirts that are backordered SELECT shirt, color, backorder_qty FROM garments WHERE color like "BLUE%" Now, another query comes along: -- Someone else wants reddish, etc shirts that are backordered: SELECT shirt, color, backorder_qty FROM garments WHERE color like "RED%" It's the same query with different data. Without parameter markers you will never know it. And yet this is exactly the sort of caching that is useful. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< << -
Re: eWeek Poll: Which database is most critical to your
Neil Conway <nconway@klamath.dyndns.org> — 2002-02-26T23:42:11Z
On Tue, 2002-02-26 at 18:20, Dann Corbit wrote: > I don't see how it will do any good. There is no "prepare" in > Postgresql > and therefore you will simply be reexecuting the queries every time any > way. Also, parameter markers only work in embedded SQL and that is a > single tasking system. Perhaps I wasn't clear. The feature I'm proposing is this: When processing SELECT queries but before any real work has been done, lookup the query in a hash table. If it already exists, return the cached result. If it doesn't exist, execute the query and cache the result in the hash table. Optionally, we could not immediately cache the query, just increment a "frequency" counter stored in the hash table. If the counter goes above a certain constant, we decide that the query is worth caching, so we cache the full result in the hash table. When processing INSERTs, UPDATEs and DELETEs, check if the query would affect any of the tables for which we are maintaing this cache. If so, flush the cache. This ensures that we will never return invalid results. We could perhaps be fancy and keep stats on which columns our cached queries utilize and which columns the modifying query will affect, but that is unlikely to be an overall win. HOWEVER -- I don't see this feature as something that will appeal to, say, 75% of PgSQL users. If the table in question is being modified on a regular basis, or if a wide variety of queries are being issued, this cache isn't a good idea. Nevertheless, I think there are certainly some situations in which this cache is useful -- and furthermore, these kinds of "mostly read-only" situations are often where MySQL is chosen over PostgreSQL. Anyway, just putting this on the table -- if the consensus is that this isn't a very worthwhile feature, I won't bother with it. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC -
Re: eWeek Poll: Which database is most critical to your
Thomas Swan <tswan-lst@ics.olemiss.edu> — 2002-02-27T00:16:00Z
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title></title> </head> <body> Dann Corbit wrote:<br> <blockquote type="cite" cite="midD90A5A6C612A39408103E6ECDD77B82920CC1F@voyager.corporate.connx.com"> <pre wrap="">-----Original Message-----<br>From: Neil Conway [<a class="moz-txt-link-freetext" href="mailto:nconway@klamath.dyndns.org">mailto:nconway@klamath.dyndns.org</a>]<br>Sent: Tuesday, February 26, 2002 3:04 PM<br>To: <a class="moz-txt-link-abbreviated" href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a><br>Subject: Re: [HACKERS] eWeek Poll: Which database is most critical to<br>your<br><br><br>On Tue, 2002-02-26 at 15:30, Zak Greant wrote:<br></pre> <blockquote type="cite"> <pre wrap="">Good Day All,<br><br>eWeek has posted a poll that asks which database server is most<br></pre> </blockquote> <pre wrap=""><!---->critical<br></pre> <blockquote type="cite"> <pre wrap="">to your organization.<br></pre> </blockquote> <pre wrap=""><!----><br>The article mentions a MySQL feature which apparently improved<br>performance considerably:<br><br>//<br>MySQL 4.0.1's new, extremely fast query cache is also quite notable, as<br>no other database we tested had this feature. If the text of an incoming<br>query has a byte-for-byte match with a cached query, MySQL can retrieve<br>the results directly from the cache without compiling the query, getting<br>locks or doing index accesses. This query caching will be effective only<br>for tables with few updates because any table updates that clear the<br>cache to guarantee correct results are always returned.<br>//<br><br>My guess is that it would be relatively simple to implement. Any<br>comments on this?<br><br>If I implemented this, any chance this would make it into the tree? Of<br>course, it would be:<br><br> - disabled by default<br> - enabled on a table-by-table basis (maybe an ALTER TABLE command)<br></pre> <pre wrap=""><!---->I don't see how it will do any good. There is no "prepare" in<br>Postgresql<br>and therefore you will simply be reexecuting the queries every time any<br>way. Also, parameter markers only work in embedded SQL and that is a <br>single tasking system.<br><br>I think it would be a major piece of work to do anything useful along<br>those lines.<br><br>If you look at how DB/2 works, you will see that they store prepared<br>statements. Another alternative would be to keep some point in the<br>parser marked and somehow jump to that point, but you would have to<br>be able to save a parse tree somewhere and also recognize the query.<br><br>Here is where problems come in...<br>-- Someone wants blue and blue-green, etc shirts that are backordered<br>SELECT shirt, color, backorder_qty FROM garments WHERE color like<br>"BLUE%"<br><br>Now, another query comes along:<br><br>-- Someone else wants reddish, etc shirts that are backordered:<br>SELECT shirt, color, back order_qty FROM garments WHERE color like "RED%"<br><br>It's the same query with different data. Without parameter markers you<br>will never know it. And yet this is exactly the sort of caching that is<br>useful.</pre> </blockquote> However, an exact match is still not a bad idea. You might have a different execution plan depending on the statistics of the data in your column.<br> <br> If there were a way to store the execution plan and start executing from there it still might not be a bad idea.<br> <br> <blockquote type="cite" cite="midD90A5A6C612A39408103E6ECDD77B82920CC1F@voyager.corporate.connx.com"> <pre wrap=""><br><br><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<br><<<br><br>---------------------------(end of broadcast)---------------------------<br>TIP 5: Have you checked our extensive FAQ?<br><br><a class="moz-txt-link-freetext" href="http://www.postgresql.org/users-lounge/docs/faq.html">http://www.postgresql.org/users-lounge/docs/faq.html</a><br></pre> </blockquote> <br> <br> </body> </html> -
Re: eWeek Poll: Which database is most critical to your
Rod Taylor <rbt@barchord.com> — 2002-02-27T00:21:47Z
If you were to expire the cache when the table was updated it wouldn't be so bad, and you certainly don't want to cache everything under the assumption something may come along -- as it probably won't. But if you're interested in speeding this up I can't see any harm in adding a stat into statistics that stores query frequency. If the frequency goes above a certain notch (without the results changing -- static table data) then cache results. However, I think 99% of these cases could be easily rectified with a php interface which caches the results into memory at the users request as I really don't see hundreds of machines making the same queries frequently -- rather 1 or 2 poorly written ones :) Then again, I regularly hit one row database tables which act as markers for data changes to see if the program should reload the entire data set. (Parallel machines for interface purposes cache table data in ram normally, but test for changes with every request -- goal was that they shouldn't have to communicate to eachother). But a 1 row table is very quick to select from. Can this make it faster, if so it'll be useful. Anyway, write it up. If you can speed up some without making a hit against others it'll be a large advantage. -- Rod Taylor This message represents the official view of the voices in my head ----- Original Message ----- From: "Neil Conway" <nconway@klamath.dyndns.org> To: "Dann Corbit" <DCorbit@connx.com> Cc: <pgsql-hackers@postgresql.org> Sent: Tuesday, February 26, 2002 6:42 PM Subject: Re: [HACKERS] eWeek Poll: Which database is most critical to your > On Tue, 2002-02-26 at 18:20, Dann Corbit wrote: > > I don't see how it will do any good. There is no "prepare" in > > Postgresql > > and therefore you will simply be reexecuting the queries every time any > > way. Also, parameter markers only work in embedded SQL and that is a > > single tasking system. > > Perhaps I wasn't clear. The feature I'm proposing is this: > > When processing SELECT queries but before any real work has been > done, lookup the query in a hash table. If it already exists, return the > cached result. If it doesn't exist, execute the query and cache the > result in the hash table. Optionally, we could not immediately cache the > query, just increment a "frequency" counter stored in the hash table. If > the counter goes above a certain constant, we decide that the query is > worth caching, so we cache the full result in the hash table. > > When processing INSERTs, UPDATEs and DELETEs, check if the query > would affect any of the tables for which we are maintaing this cache. If > so, flush the cache. This ensures that we will never return invalid > results. We could perhaps be fancy and keep stats on which columns our > cached queries utilize and which columns the modifying query will > affect, but that is unlikely to be an overall win. > > HOWEVER -- I don't see this feature as something that will appeal to, > say, 75% of PgSQL users. If the table in question is being modified on a > regular basis, or if a wide variety of queries are being issued, this > cache isn't a good idea. Nevertheless, I think there are certainly some > situations in which this cache is useful -- and furthermore, these kinds > of "mostly read-only" situations are often where MySQL is chosen over > PostgreSQL. > > Anyway, just putting this on the table -- if the consensus is that this > isn't a very worthwhile feature, I won't bother with it. > > Cheers, > > Neil > > -- > Neil Conway <neilconway@rogers.com> > PGP Key ID: DB3C29FC > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
-
Experimental Feature development in PostgreSQL
Justin Clift <justin@postgresql.org> — 2002-02-27T02:20:41Z
Neil Conway wrote: <snip> Hi everyone, This is getting me to think : We don't have a really established process for PostgreSQL development which allows for "experimental features". aka the Linux kernel "EXPERIMENTAL", and so forth. aka ./configure --experimental-querycache --experimental-pertablecostings --expermental-something? This way, people can still use PostgreSQL as per normal, but it also allows for development of code which might or might not actually see the light of day, depending on if it turns out to really be useful. By default, things should only become "experimental features" after the correct consideration, not by default. There are situations where it would be beneficial (perhaps in Neil's example here). If Neil were to split off a project and do it on Sourceforge, then only some people would check it out when kind of ready, etc, etc and it wouldn't really be as available to a wider audience. But if's it part of the present source tree, I think the experimental features would see a wider audience and we'd get a better indication of what's good/bad/etc. Don't know how to support those features, but pretty much developers and hard-core-users should be the only ones using them and therefore should be able to figure most of the stuff out for themselves (i.e. we'd only get the real questions). Does this seems like a worthwhile viewpoint to consider, then perhaps we can think about starting it implement it for 7.3? (Wish I could code better). :) Regards and best wishes, Justin Clift > > Perhaps I wasn't clear. The feature I'm proposing is this: > > When processing SELECT queries but before any real work has been > done, lookup the query in a hash table. If it already exists, return the > cached result. If it doesn't exist, execute the query and cache the > result in the hash table. Optionally, we could not immediately cache the > query, just increment a "frequency" counter stored in the hash table. If > the counter goes above a certain constant, we decide that the query is > worth caching, so we cache the full result in the hash table. > > When processing INSERTs, UPDATEs and DELETEs, check if the query > would affect any of the tables for which we are maintaing this cache. If > so, flush the cache. This ensures that we will never return invalid > results. We could perhaps be fancy and keep stats on which columns our > cached queries utilize and which columns the modifying query will > affect, but that is unlikely to be an overall win. > > HOWEVER -- I don't see this feature as something that will appeal to, > say, 75% of PgSQL users. If the table in question is being modified on a > regular basis, or if a wide variety of queries are being issued, this > cache isn't a good idea. Nevertheless, I think there are certainly some > situations in which this cache is useful -- and furthermore, these kinds > of "mostly read-only" situations are often where MySQL is chosen over > PostgreSQL. > > Anyway, just putting this on the table -- if the consensus is that this > isn't a very worthwhile feature, I won't bother with it. > > Cheers, > > Neil > > -- > Neil Conway <neilconway@rogers.com> > PGP Key ID: DB3C29FC > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
-
Re: eWeek Poll: Which database is most critical to your
Hannu Krosing <hannu@krosing.net> — 2002-02-27T05:32:21Z
On Wed, 2002-02-27 at 10:39, Tom Lane wrote: > Neil Conway <nconway@klamath.dyndns.org> writes: > > When processing INSERTs, UPDATEs and DELETEs, check if the query > > would affect any of the tables for which we are maintaing this cache. If > > so, flush the cache. This ensures that we will never return invalid > > results. > > Note that this would imply that the cache is *global* across all > backends; therefore it is a shared data structure and hence an access > bottleneck. (Not to mention a memory-management headache, since the > size of shared memory can't easily be varied on-the-fly.) I think that it would be enough if the hashes and bookkeeping info (tmin,tmax,filename) were in a global table. We could also purge all data more than a few minutes old. We also need an inverse lookup from changed table to cached query for cache invalidation on insert/update/delete The result could be even saved in temp files and be mostly faster than doing the full parse/plan/execute, both for complex queries returning a few rows (saves planning time) or many rows (saves execute time). The format used for saving should be exact wire protocol, so that efficient system calls could be used where available (linux 2.4+ has a system call that will transfer a whole file to a socket in one call bypassing all copying and cacheing) The lookup part will be pretty trivial - lookup using hash, check for tmin/tmax, if ok push cached result out to client.This will make us as fast or faster than MySQL for trivial_benchmark/busy_website case. The cache creation/maintenance part will be much trickier - When creating cache * the tables affected can be determined only from fully built plans because of possible rule expansions. * if there is a trigger on select for this query it can't be cached * put some temporary insert/update/delete triggers on all real tables used in query that will invalidate cache - as an alternatively we could always run the invalidate-query-cache code for affected table on insert/update/delete on a table if exact caching is enabled * invalidate cache on schema changes * run a periodic check and invalidate old cache entries. Some of the above could also be needed for caching query plans. > I cannot believe that caching results for literally-identical queries > is a win, except perhaps for the most specialized (read brain dead) > applications. Actually a web app that looks up contents of a 5 row combobox is not really brain-dead. Doing all the caching (and cache invalidation) on client side is hard and fragle - what happens when someone adds a trigger in backend ? > Has anyone looked at the details of the test case that > MySQL uses to claim that this is a good idea? Has it got any similarity > to your own usage patterns? Yes - for content management / web apps. No - for bean-counting apps. > We have talked about caching query plans for suitably-parameterized > queries, but even that seems unlikely to be a huge win; at least I'd > not think it useful to try to drive the cache totally automatically. > If an application could say "here's a query I expect to use a lot, > varying these specific parameters" then caching a plan for that would > make sense. > > Now, there are notions of "prepared statements" in many access APIs > that fit this description, and in fact the underlying capability exists > in the backend --- we've just not gotten around to building the > interfaces to tie it all together. *That* would be worth working on. Sure. It would a) make many queries faster b) make client libs (ODBC/JDBC/ECPG) faster and simpler by not forcing them to fake it. But there is also a big class of applications that would benefit much more from caching exact queries. And it will make us as fast as MySQL for 100000 consecutive calls of SELECT MAX(N) FROM T ;) --------------- Hannu
-
Re: eWeek Poll: Which database is most critical to your
Tom Lane <tgl@sss.pgh.pa.us> — 2002-02-27T05:39:16Z
Neil Conway <nconway@klamath.dyndns.org> writes: > When processing INSERTs, UPDATEs and DELETEs, check if the query > would affect any of the tables for which we are maintaing this cache. If > so, flush the cache. This ensures that we will never return invalid > results. Note that this would imply that the cache is *global* across all backends; therefore it is a shared data structure and hence an access bottleneck. (Not to mention a memory-management headache, since the size of shared memory can't easily be varied on-the-fly.) I cannot believe that caching results for literally-identical queries is a win, except perhaps for the most specialized (read brain dead) applications. Has anyone looked at the details of the test case that MySQL uses to claim that this is a good idea? Has it got any similarity to your own usage patterns? We have talked about caching query plans for suitably-parameterized queries, but even that seems unlikely to be a huge win; at least I'd not think it useful to try to drive the cache totally automatically. If an application could say "here's a query I expect to use a lot, varying these specific parameters" then caching a plan for that would make sense. Now, there are notions of "prepared statements" in many access APIs that fit this description, and in fact the underlying capability exists in the backend --- we've just not gotten around to building the interfaces to tie it all together. *That* would be worth working on. regards, tom lane
-
Re: eWeek Poll: Which database is most critical to your
Neil Conway <nconway@klamath.dyndns.org> — 2002-02-27T06:21:44Z
On Wed, 2002-02-27 at 00:39, Tom Lane wrote: > I cannot believe that caching results for literally-identical queries > is a win, except perhaps for the most specialized (read brain dead) > applications. According to MySQL: "The query cache is extremely useful in an environment where (some) tables don't change very often and you have a lot of identical queries. This is a typical situation for many web servers that use a lot of dynamic content." Would people agree with the MySQL guys on this? In particular, that this is a "typical situation" for many webapps? > Has anyone looked at the details of the test case that > MySQL uses to claim that this is a good idea? I emailed the author of the eWeek benchmarks asking for more information. My guess is that the benchmark results benefit from the query cache because it executes exactly the same query over and over again (e.g. selecting the same product from the database every time, not simulating typical user behavior by selecting random products). If that is the case, the results are clearly irrelevant. > Has it got any similarity to your own usage patterns? I would be very interested to hear about this as well. > Now, there are notions of "prepared statements" in many access APIs > that fit this description, and in fact the underlying capability exists > in the backend --- we've just not gotten around to building the > interfaces to tie it all together. *That* would be worth working on. Okay, I'll take a look at this... Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
-
Re: eWeek Poll: Which database is most critical to your
Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-02-27T06:50:29Z
> According to MySQL: "The query cache is extremely useful in an > environment where (some) tables don't change very often and you have a > lot of identical queries. This is a typical situation for many web > servers that use a lot of dynamic content." > > Would people agree with the MySQL guys on this? In particular, that this > is a "typical situation" for many webapps? Hmmm. We have a lot of repeated _parameterised_ queries, but the recurrence of identical queries is quite small. It'd be an interesting thing to try and measure. > > Now, there are notions of "prepared statements" in many access APIs > > that fit this description, and in fact the underlying capability exists > > in the backend --- we've just not gotten around to building the > > interfaces to tie it all together. *That* would be worth working on. > > Okay, I'll take a look at this... This is the more general solution, compared to MySQL's query cache - and can speed up paramaterised queries as well as identical queries... Chris
-
Re: eWeek Poll: Which database is most critical to your
Michael Meskes <meskes@postgresql.org> — 2002-02-28T07:04:18Z
On Wed, Feb 27, 2002 at 12:39:16AM -0500, Tom Lane wrote: > I cannot believe that caching results for literally-identical queries > is a win, except perhaps for the most specialized (read brain dead) I don't think they are brain dead. Well that is at first I thought so too, but then thinking some more it made sense. After all MySQL is used mostly for web pages and even your dynamic content doesn't change that often. But in between there are thousands of concurrent access that all execute the very same statement. This feature makes no sense IMO for the "normal" use we both probably had in mind when first reading, but for this web usage I see a benefit if it's implementable. Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
-
Re: eWeek Poll: Which database is most critical to your
Andrew McMillan <andrew@catalyst.net.nz> — 2002-03-01T11:20:36Z
On Wed, 2002-02-27 at 18:39, Tom Lane wrote: > > I cannot believe that caching results for literally-identical queries > is a win, except perhaps for the most specialized (read brain dead) > applications. We have an application which we developed, and which we added specifically added query caching of the type being discussed. This was added within the application, rather than within the database, and provided huge gains for the client. One difficulty is in knowing when the results of the exact same query will result in a different result set (i.e. invalidating your cached resultsets), but as far as I can see this is just as knowable at a database level as it was for us in the application code. If this were available I wouldn't want it applied to _all_ queries, however - I'd want to be able to enable it against particular tables and/or queries (po: a 'WITH RESULTCACHE' option on the end of a SELECT) where I knew there was going to be a dramatic win. The reason there was such a large win for our application was that when people go to a job website on Monday morning to look for a better place to work, there is a strong tendency for them to be searching for much the same sort of thing (as each other) - especially if the site is getting in the millions of page views. The application I talk about here was written in Progress (WebSpeed), and we have found it much harder to do this sort of thing more recently in Oracle or PostgreSQL. This would be nice. Regards, Andrew. -- -------------------------------------------------------------------- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 Are you enrolled at http://schoolreunions.co.nz/ yet?