Thread: LRU and full table scans
On general a discussion has been taking place regarding cached query plans and how MySQL invented them. Of course, this is totally false. I remembered a nice paragraph in the Oracle docs as to the process by which Oracle uses shared SQL areas to share the execution plan of identical statements, flushing the area whenever a dependent object was modified. In searching for the reference, however, I stumbled an interesting fact. Unlike normal queries where blocks are added to the MRU end of an LRU list, full table scans add the blocks to the LRU end of the LRU list. I was wondering, in the light of the discussion of using LRU-K, if PostgreSQL does, or if anyone has tried, this technique? Mike Mascari mascarm@mascari.
Hannu Krosing wrote: > > On Wed, 2002-02-27 at 13:09, Mike Mascari wrote: > > On general a discussion has been taking place regarding cached query > > plans and how MySQL invented them. > > IMHO the discussion was about cached queries not query plans. You're right, of course. It would be interesting though to compare the speed of a cached query against a cache query plan + cached data blocks. If the cached query got a hit, the cached query plan + cached data blocks would lose by the number of cycles spent in the executor. Alternatively, the cost of a cache miss in the caching of a query means wasted memory that could have been used for cached data blocks... > > > Of course, this is totally false. I > > remembered a nice paragraph in the Oracle docs as to the process by > > which Oracle uses shared SQL areas to share the execution plan of > > identical statements, flushing the area whenever a dependent object was > > modified. In searching for the reference, however, I stumbled an > > interesting fact. Unlike normal queries where blocks are added to the > > MRU end of an LRU list, full table scans add the blocks to the LRU end > > of the LRU list. > > This seems really elegant solution , much better than not caching at all > and much better than flushing the whole cache by a large table scan Yes. And Oracle has a CACHE keyword option on its CREATE TABLE/ALTER TABLE statement to allow full table scans of small lookup tables to follow normal MRU caching, if necessary. > > > I was wondering, in the light of the discussion of > > using LRU-K, if PostgreSQL does, or if anyone has tried, this technique? > > Hannu Mike Mascari mascarm@mascari.com
Mike Mascari wrote: > On general a discussion has been taking place regarding cached query > plans and how MySQL invented them. Of course, this is totally false. I > remembered a nice paragraph in the Oracle docs as to the process by > which Oracle uses shared SQL areas to share the execution plan of > identical statements, flushing the area whenever a dependent object was > modified. In searching for the reference, however, I stumbled an > interesting fact. Unlike normal queries where blocks are added to the > MRU end of an LRU list, full table scans add the blocks to the LRU end > of the LRU list. I was wondering, in the light of the discussion of > using LRU-K, if PostgreSQL does, or if anyone has tried, this technique? Yes, someone from India has a project to test LRU-K and MRU for large table scans and report back the results. He will implement whichever is best. He posted a week ago, see "Implementation Proposal For Add Free Behind Capability For Large Sequential Scan", Amit Kumar Khare <skamit2000@yahoo.com>. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Wed, 2002-02-27 at 13:09, Mike Mascari wrote: > On general a discussion has been taking place regarding cached query > plans and how MySQL invented them. IMHO the discussion was about cached queries not query plans. > Of course, this is totally false. I > remembered a nice paragraph in the Oracle docs as to the process by > which Oracle uses shared SQL areas to share the execution plan of > identical statements, flushing the area whenever a dependent object was > modified. In searching for the reference, however, I stumbled an > interesting fact. Unlike normal queries where blocks are added to the > MRU end of an LRU list, full table scans add the blocks to the LRU end > of the LRU list. This seems really elegant solution , much better than not caching at all and much better than flushing the whole cache by a large table scan > I was wondering, in the light of the discussion of > using LRU-K, if PostgreSQL does, or if anyone has tried, this technique? ------------ Hannu
"Mike Mascari" <mascarm@mascari.com> escribi� en el mensaje news:3C7C9449.B747532B@mascari.com... > On general a discussion has been taking place regarding cached query > plans and how MySQL invented them. Of course, this is totally false. I > remembered a nice paragraph in the Oracle docs as to the process by > which Oracle uses shared SQL areas to share the execution plan of > identical statements, flushing the area whenever a dependent object was > modified. In searching for the reference, however, I stumbled an > interesting fact. Unlike normal queries where blocks are added to the > MRU end of an LRU list, full table scans add the blocks to the LRU end > of the LRU list. I was wondering, in the light of the discussion of > using LRU-K, if PostgreSQL does, or if anyone has tried, this technique? > > Mike Mascari > mascarm@mascari. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html First Hello, Second...Yes, I'm doing some studies on the buffer manager and also studying and implementing different policys, one of them is the LRU-K. By the time I don't have any results, I'm just preparing to run the TPC-H benchmark to test all the policys that I have implemented. I have implemented some policys for the buffer manager, some better and some worst than LRU (FIFO, LFU, LRD, FBR, LRU-K, LRFU, 4 CLOCK policys (or second chance), CORRELATED REFERENCES, and different combinations of them like (LFU+CLOCK+AGING(by division or by substraction)+CORRELATED REFERENCES)), there are seven principal policys and seven "add-on's" that could be applyed to them, resulting in 25 combinations of policys.
Roque Bonilla wrote: > First Hello, Second...Yes, I'm doing some studies on the buffer manager and > also studying and implementing different policys, one of them is the LRU-K. > > By the time I don't have any results, I'm just preparing to run the TPC-H > benchmark to test all the policys that I have implemented. > I have implemented some policys for the buffer manager, some better and some > worst than LRU (FIFO, LFU, LRD, FBR, LRU-K, LRFU, 4 CLOCK policys (or second > chance), CORRELATED REFERENCES, and different combinations of them like > (LFU+CLOCK+AGING(by division or by substraction)+CORRELATED REFERENCES)), > there are seven principal policys and seven "add-on's" that could be applyed > to them, resulting in 25 combinations of policys. We do have someone working on LRU-K but we haven't seen any test results from him yet. Please let us know what you find. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026