Re: Query meltdown: caching results
От | Richard Huxton |
---|---|
Тема | Re: Query meltdown: caching results |
Дата | |
Msg-id | 47C40029.3060307@archonet.com обсуждение исходный текст |
Ответ на | Query meltdown: caching results (Gordon <gordon.mcvey@ntlworld.com>) |
Список | pgsql-general |
Gordon wrote: > I need to find a way of not running queries that I don't need to, > either in the PHP script, or in the Postgres database. What I need is > for a result set to be cached somewhere, either by Postgres or PHP, so > when it sees the same query again in a given session it just returns > the previously fetched result set. The cache also needs to be able to > disregard its cached result sets when an event that changes a table > occurs (insert, update, delete, etc). It's the second part that's fiddly (in the general case) if you do it in PHP. If you're looking for a large-scale cache then memcached would suit your needs. There's an add-on for PG that can keep it notigi http://pgfoundry.org/projects/pgmemcache/ > On the PHP side I've written a simple Database class that extends PDO > and that I use in its place. It's a simple class that basically I use > to allow me to nest calls to beginTransaction(), commit () and > rollback () (It only starts an actual transaction of a counter is 0. > Otherwide it just increments the counter. Commit only actually > commits when the counter is 1, and decrements it otherwise. Rollback > sets an error flag and decrements the counter, and only rolls back > when the counter is 1. If the error flag is set then commit will > actually roll back instead. ) > > My options are, as far as I can tell, > > 1) replace the Database PDO extending class with something else that > provides query results caching in PHP, or There are a whole bunch of Pear classes for caching - Cache_Lite is simple to plug into an existing structure. > 2) get Postgres itself to cache the results of queries to avoid > running them repeatedly during a session. > > I seem to remember MySQL providing some kind of results caching, can > Postgres do the same? Has anyone else run into similar problems and > how did they overcome them? No, but if you're serious about the caching you'll want to do it well above the data-access layer. The main gains I've seen with a simple caching system have been: 1. Big, static lookup lists (countries, catalogue sections etc). 2. Whole pages / sections of pages The trick with both is to cache as close to rendering as possible. So, the HTML in the case of pages/controls. Make sure your data-access layer invalidates any relevant cache entries and you'll be fine (as long as you don't do any database manipulation outside your app - always have an "invalidate whole cache" function / script available for this). Oh, and *do* make sure you've identified real gains first. It's distressing to spend two days optimising your caching only to realise you've gained 2% because you've missed the real bottle-neck. -- Richard Huxton Archonet Ltd
В списке pgsql-general по дате отправления: