Re: [HACKERS] Temp tables and LRU-K caching
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Temp tables and LRU-K caching
OK, I will save this for 7.4. Sorry, Gavin. I missed this one for 7.3. --- pgman wrote: > Tom Lane wrote: > > Mike Mascari <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] | (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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Temp tables and LRU-K caching
Tom Lane <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Temp tables and LRU-K caching
Bruce Momjian <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Temp tables and LRU-K caching
Tom Lane wrote: > Mike Mascari <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Temp tables and LRU-K caching
Tom Lane wrote: > Mike Mascari <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Temp tables and LRU-K caching
Mike Mascari <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]