Re: query cache
От | Billy Earney |
---|---|
Тема | Re: query cache |
Дата | |
Msg-id | CAB1ii-d+Z+rmMP0nk=gTObUzZVsB3E-7uM7_+xd+8ta96yjxoQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: query cache (Tatsuo Ishii <ishii@postgresql.org>) |
Список | pgsql-hackers |
Thanks.. I'll keep those issues in mind.<br /><br /><div class="gmail_quote">On Sat, Mar 24, 2012 at 6:18 PM, Tatsuo Ishii<span dir="ltr"><<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>></span> wrote:<br /><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">>>Well, you'd have to start by demonstrating the benefit of it. The<br /> >> advantage of query cachesin proxies and clients is well-known, because you<br /> >> can offload some of the work of the database ontoother servers, this<br /> >> increasing capacity. Adding a query cache to the database server would<br /> >>require the "query identity recognition" of the cache to be far cheaper (as<br /> >> in 10X cheaper) than planningand running the query, which seems unlikely<br /> >> at best.<br /> >><br /> >> I figured I'd createthe md5 digest of the sourceText of a query, and then<br /> > look that up in a hash. I don't think that will bevery expensive. I'll<br /> > have another hash to keep track of which queries are dependent on which<br /> > relations,so that when a relation is changed somehow (and committed), the<br /> > query is then invalidated and removedfrom the query hash.<br /><br /></div>From the experience of implementing query cache in pgool-II there are<br />some suggestions:<br /><br /> - A query result cache should not be created if the transaction<br /> including the SELECTis not committed.<br /><br /> - Since a transaction could have many SELECTs, you need to keep those<br /> query resultssomewhere in a temporary storage. You could either<br /> discard or register them to the query cache storage dependingon the<br /> transaction's fate, either aborted or committed.<br /><br /> - If a SELECT has non-immutable functions,then the query result<br /> should not be cached.<br /><br /> - If a SELECT uses temporary tables, then the queryresult should not<br /> be cached.<br /><br /> - If a SELECT uses unlogged tables, then the query result should not<br/> be cached because their data could vanish after crash recovery. Of<br /> course this is only applied if you planto use cache storage which<br /> does not survive after crash.<br /> --<br /> Tatsuo Ishii<br /> SRA OSS, Inc. Japan<br/> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br/> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br/></blockquote></div><br />
В списке pgsql-hackers по дате отправления: