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, Pennsylvania 19026
"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?
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, Pennsylvania 19026