Re: eWeek Poll: Which database is most critical to your - Mailing list pgsql-hackers
From | Rod Taylor |
---|---|
Subject | Re: eWeek Poll: Which database is most critical to your |
Date | |
Msg-id | 110101c1bf24$be7aa0c0$6a02000a@jester Whole thread Raw |
In response to | Re: eWeek Poll: Which database is most critical to your ("Dann Corbit" <DCorbit@connx.com>) |
List | pgsql-hackers |
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 >
pgsql-hackers by date: