Обсуждение: 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, Pennsylvania19073
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, Pennsylvania19073
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, Pennsylvania19073
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
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: > > 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 -- 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, Pennsylvania19073
On Mon, 23 Sep 2002, Bruce Momjian wrote: > > OK, I will save this for 7.4. Sorry, Gavin. I missed this one for 7.3. Such is life. Gavin