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




Mon, 23 Sep 2002 11:57:14 -0400
Mike Mascari <mascarm@mascari.com>
Temp tables and LRU-K caching
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

Mon, 23 Sep 2002 12:24:06 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Temp tables and LRU-K caching 
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 19073

Mon, 23 Sep 2002 12:34:33 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Temp tables and LRU-K caching
Tom 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 19073

Mon, 23 Sep 2002 12:36:41 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Temp tables and LRU-K caching
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:

	http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=200208272124.g7RLO1L20172%40candle.pha.pa.us&rnum=1&prev=/groups%3Fq%3Dcreate%2Btemp%2Btable%2Bon%2Bcommit%2Bgroup:comp.databases.postgresql.*%26hl%3Den%26lr%3D%26ie%3DUTF-8%26scoring%3Dd%26selm%3D200208272124.g7RLO1L20172%2540candle.pha.pa.us%26rnum%3D1

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

Mon, 23 Sep 2002 12:39:06 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Temp tables and LRU-K caching
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.

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




Mon, 23 Sep 2002 12:40:54 -0400
Mike Mascari <mascarm@mascari.com>
Re: Temp tables and LRU-K caching
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

Mon, 23 Sep 2002 12:52:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Temp tables and LRU-K caching 
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


23 Sep 2002 14:50:09 -0400
Neil Conway <neilc@samurai.com>
Re: Temp tables and LRU-K caching