Temp tables and LRU-K caching
Hello.
I'm just curious as to the 7.3 status of a couple of things:
1. Back in Feb. I wrote (in regards to Oracle behavior):
"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?"
Bruce wrote:
"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."
Did this make it into 7.3?
2. Gavin Sherry had worked up a patch so that temporary
relations could be dropped automatically upon transaction
commit. Did any of those patches it make it? I notice that
whenever I create a temporary table in a transaction, my HD
light blinks. Is this a forced fsync() causes by the fact that
the SQL standard defines temporary relations as surviving across
transactions? If so, I'd bet those of us who use
transaction-local temporary tables could get few drops more of
performance from an ON COMMIT drop patch w/o fsync.
Any thoughts?
Mike Mascari
mascarm@mascari.com
Mike Mascari <mascarm@mascari.com> writes:
Bruce wrote:
"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."
Did this make it into 7.3?
No, we never heard back from that guy. It is still a live topic though.
One of the Red Hat people was looking at it over the summer, and I think
Neil Conway is experimenting with LRU-2 code right now.
2. Gavin Sherry had worked up a patch so that temporary
relations could be dropped automatically upon transaction
commit. Did any of those patches it make it?
No they didn't; I forget whether there was any objection to his last try
or it was just too late to get reviewed before feature freeze.
I notice that
whenever I create a temporary table in a transaction, my HD
light blinks. Is this a forced fsync() causes by the fact that
the SQL standard defines temporary relations as surviving across
transactions?
A completely-in-memory temp table is not really practical in Postgres,
for two reasons: one being that its schema information is stored in
the definitely-not-temp system catalogs, and the other being that we
request allocation of disk space for each page of the table, even if
it's temp. It might be possible to work around the latter issue (at
the cost of quite unfriendly behavior should you run out of disk space)
but short of a really major rewrite there isn't any way to avoid keeping
temp table catalog info in the regular catalogs. So you are certainly
going to get a disk hit when you create or drop a temp table.
7.3 should be considerably better than 7.1 or 7.2 for temp table access
because it doesn't WAL-log operations on the data within temp tables,
though.
Another thing I'd like to see in the near future is a configurable
setting for the amount of memory space that can be used for temp-table
buffers. The current setting is ridiculously small (64*8K IIRC), but
there's not much point in increasing it until we also have a smarter
management algorithm for the temp buffers. I've asked Neil to look at
making the improved LRU-K buffer management algorithm apply to temp
buffers as well as regular shared buffers.
regards, tom lane
Mike Mascari wrote:
Hello.
I'm just curious as to the 7.3 status of a couple of things:
1. Back in Feb. I wrote (in regards to Oracle behavior):
"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?"Bruce wrote:
"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."Did this make it into 7.3?
That person stopped working on it. It is still on the TODO list.
2. Gavin Sherry had worked up a patch so that temporary
relations could be dropped automatically upon transaction
commit. Did any of those patches it make it? I notice that
whenever I create a temporary table in a transaction, my HD
light blinks. Is this a forced fsync() causes by the fact that
the SQL standard defines temporary relations as surviving across
transactions? If so, I'd bet those of us who use
transaction-local temporary tables could get few drops more of
performance from an ON COMMIT drop patch w/o fsync.
This has me confused. There was an exchange with Gavin Auguest 27/28
which resulted in a patch:
http://archives.postgresql.org/pgsql-patches/2002-08/msg00475.php
and my adding it to the patches list:
http://archives.postgresql.org/pgsql-patches/2002-08/msg00502.php
However, it was never applied. I don't see any discussion refuting the
patch or any email removing it from the queue. The only thing I can
think of is that somehow I didn't apply it.
My only guess is that I said I was putting in the queue, but didn't. I
am concerned if there are any other patches I missed. I see the cube
patch being added to the queue 40 seconds later, and I know that was in
there because I see the message removing it from the queue. I must have
made a mistake on that one.
What do we do now? The author clearly got it in before beta, but we are
in beta now. I think we should apply it.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Tom Lane wrote:
Another thing I'd like to see in the near future is a configurable
setting for the amount of memory space that can be used for temp-table
buffers. The current setting is ridiculously small (64*8K IIRC), but
there's not much point in increasing it until we also have a smarter
management algorithm for the temp buffers. I've asked Neil to look at
making the improved LRU-K buffer management algorithm apply to temp
buffers as well as regular shared buffers.
Speaking of sizing, I wonder if we should query about the amount of RAM
in the machine either during initdb or later and size based on that.
In other words, if we add a GUC variable that shows the amount of RAM,
we could size things based on that value.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Tom Lane wrote:
Mike Mascari <mascarm@mascari.com> writes:
Bruce wrote:
"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."
Did this make it into 7.3?No, we never heard back from that guy. It is still a live topic though.
One of the Red Hat people was looking at it over the summer, and I think
Neil Conway is experimenting with LRU-2 code right now.2. Gavin Sherry had worked up a patch so that temporary
relations could be dropped automatically upon transaction
commit. Did any of those patches it make it?No they didn't; I forget whether there was any objection to his last try
or it was just too late to get reviewed before feature freeze.
I see it going into the patch queue. Here is the full thread:
I don't see why it wasn't applied.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Tom Lane wrote:
Mike Mascari <mascarm@mascari.com> writes:
Bruce wrote:
"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."
Did this make it into 7.3?No, we never heard back from that guy. It is still a live topic though.
One of the Red Hat people was looking at it over the summer, and I think
Neil Conway is experimenting with LRU-2 code right now.
Okay.
2. Gavin Sherry had worked up a patch so that temporary
relations could be dropped automatically upon transaction
commit. Did any of those patches it make it?No they didn't; I forget whether there was any objection to his last try
or it was just too late to get reviewed before feature freeze.
Nuts. Oh well. Hopefully for 7.4...
I notice that
whenever I create a temporary table in a transaction, my HD
light blinks. Is this a forced fsync() causes by the fact that
the SQL standard defines temporary relations as surviving across
transactions?A completely-in-memory temp table is not really practical in Postgres,
for two reasons: one being that its schema information is stored in
the definitely-not-temp system catalogs, and the other being that we
request allocation of disk space for each page of the table, even if
it's temp.
I knew what I was asking made no sense two seconds after
clicking 'Send'. Unfortunately, there's no undo on my mail
client ;-).
Mike Mascari
mascarm@mascari.com
Bruce Momjian <pgman@candle.pha.pa.us> writes:
What do we do now? The author clearly got it in before beta, but we are
in beta now. I think we should apply it.
No. It's a feature addition and we are in feature freeze. Moreover,
it's an unreviewed feature addition (I certainly never had time to look
at the last version of the patch). Hold it for 7.4.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
No, we never heard back from that guy. It is still a live topic though.
One of the Red Hat people was looking at it over the summer, and I think
Neil Conway is experimenting with LRU-2 code right now.
Just to confirm that, I'm working on this, and hope to have something
ready for public consumption soon. Tom was kind enough to send me some
old code of his that implemented an LRU-2 replacement scheme, and I've
used that as the guide for my new implementation. I just got a really
basic version working yesterday -- I'll post a patch once I get
something I'm satisfied with. I also still need to look into the local
buffer management stuff suggested by Tom.
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
OK, I will save this for 7.4. Sorry, Gavin. I missed this one for 7.3.
---------------------------------------------------------------------------
pgman wrote:
Tom Lane wrote:
Mike Mascari <mascarm@mascari.com> writes:
Bruce wrote:
"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."
Did this make it into 7.3?No, we never heard back from that guy. It is still a live topic though.
One of the Red Hat people was looking at it over the summer, and I think
Neil Conway is experimenting with LRU-2 code right now.2. Gavin Sherry had worked up a patch so that temporary
relations could be dropped automatically upon transaction
commit. Did any of those patches it make it?No they didn't; I forget whether there was any objection to his last try
or it was just too late to get reviewed before feature freeze.I see it going into the patch queue. Here is the full thread:
I don't see why it wasn't applied.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Import Notes
Reply to msg id not found: | Resolved by subject fallback
