From 951750becaf9b3293cf1100b1a93e24f4c5062fa Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Mon, 26 Nov 2001 21:03:34 +0000 Subject: [PATCH] Add LAZY messages. --- doc/TODO.detail/performance | 176 +++++++++++++++++++++++++++++++++++- 1 file changed, 173 insertions(+), 3 deletions(-) diff --git a/doc/TODO.detail/performance b/doc/TODO.detail/performance index 1d3ed185fd..5bfbbb403b 100644 --- a/doc/TODO.detail/performance +++ b/doc/TODO.detail/performance @@ -345,7 +345,7 @@ From owner-pgsql-hackers@hub.org Tue Oct 19 10:31:10 1999 Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id KAA29087 for ; Tue, 19 Oct 1999 10:31:08 -0400 (EDT) -Received: from hub.org (hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.9 $) with ESMTP id KAA27535 for ; Tue, 19 Oct 1999 10:19:47 -0400 (EDT) +Received: from hub.org (hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.10 $) with ESMTP id KAA27535 for ; Tue, 19 Oct 1999 10:19:47 -0400 (EDT) Received: from localhost (majordom@localhost) by hub.org (8.9.3/8.9.3) with SMTP id KAA30328; Tue, 19 Oct 1999 10:12:10 -0400 (EDT) @@ -454,7 +454,7 @@ From owner-pgsql-hackers@hub.org Tue Oct 19 21:25:30 1999 Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id VAA28130 for ; Tue, 19 Oct 1999 21:25:26 -0400 (EDT) -Received: from hub.org (hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.9 $) with ESMTP id VAA10512 for ; Tue, 19 Oct 1999 21:15:28 -0400 (EDT) +Received: from hub.org (hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.10 $) with ESMTP id VAA10512 for ; Tue, 19 Oct 1999 21:15:28 -0400 (EDT) Received: from localhost (majordom@localhost) by hub.org (8.9.3/8.9.3) with SMTP id VAA50745; Tue, 19 Oct 1999 21:07:23 -0400 (EDT) @@ -1006,7 +1006,7 @@ From pgsql-general-owner+M2497@hub.org Fri Jun 16 18:31:03 2000 Received: from renoir.op.net (root@renoir.op.net [207.29.195.4]) by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id RAA04165 for ; Fri, 16 Jun 2000 17:31:01 -0400 (EDT) -Received: from hub.org (root@hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.9 $) with ESMTP id RAA13110 for ; Fri, 16 Jun 2000 17:20:12 -0400 (EDT) +Received: from hub.org (root@hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.10 $) with ESMTP id RAA13110 for ; Fri, 16 Jun 2000 17:20:12 -0400 (EDT) Received: from hub.org (majordom@localhost [127.0.0.1]) by hub.org (8.10.1/8.10.1) with SMTP id e5GLDaM14477; Fri, 16 Jun 2000 17:13:36 -0400 (EDT) @@ -1113,3 +1113,173 @@ Giles +From pgsql-hackers-owner+M1795@postgresql.org Thu Dec 7 18:47:52 2000 +Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id SAA09172 + for ; Thu, 7 Dec 2000 18:47:52 -0500 (EST) +Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) + by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eB7NjFP10612; + Thu, 7 Dec 2000 18:45:15 -0500 (EST) + (envelope-from pgsql-hackers-owner+M1795@postgresql.org) +Received: from thor.tht.net (thor.tht.net [209.47.145.4]) + by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eB7N6BP08233 + for ; Thu, 7 Dec 2000 18:06:11 -0500 (EST) + (envelope-from bright@fw.wintelcom.net) +Received: from fw.wintelcom.net (bright@ns1.wintelcom.net [209.1.153.20]) + by thor.tht.net (8.9.3/8.9.3) with ESMTP id SAA97456 + for ; Thu, 7 Dec 2000 18:57:32 GMT + (envelope-from bright@fw.wintelcom.net) +Received: (from bright@localhost) + by fw.wintelcom.net (8.10.0/8.10.0) id eB7MvWE21269 + for pgsql-hackers@postgresql.org; Thu, 7 Dec 2000 14:57:32 -0800 (PST) +Date: Thu, 7 Dec 2000 14:57:32 -0800 +From: Alfred Perlstein +To: pgsql-hackers@postgresql.org +Subject: [HACKERS] Patches with vacuum fixes available for 7.0.x +Message-ID: <20001207145732.X16205@fw.wintelcom.net> +MIME-Version: 1.0 +Content-Type: text/plain; charset=us-ascii +Content-Disposition: inline +User-Agent: Mutt/1.2.5i +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +Status: ORr + +We recently had a very satisfactory contract completed by +Vadim. + +Basically Vadim has been able to reduce the amount of time +taken by a vacuum from 10-15 minutes down to under 10 seconds. + +We've been running with these patches under heavy load for +about a week now without any problems except one: + don't 'lazy' (new option for vacuum) a table which has just + had an index created on it, or at least don't expect it to + take any less time than a normal vacuum would. + +There's three patchsets and they are available at: + +http://people.freebsd.org/~alfred/vacfix/ + +complete diff: +http://people.freebsd.org/~alfred/vacfix/v.diff + +only lazy vacuum option to speed up index vacuums: +http://people.freebsd.org/~alfred/vacfix/vlazy.tgz + +only lazy vacuum option to only scan from start of modified +data: +http://people.freebsd.org/~alfred/vacfix/mnmb.tgz + +Although the patches are for 7.0.x I'm hoping that they +can be forward ported (if Vadim hasn't done it already) +to 7.1. + +enjoy! + +-- +-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] +"I have the heart of a child; I keep it in a jar on my desk." + +From pgsql-hackers-owner+M1809@postgresql.org Thu Dec 7 20:27:39 2000 +Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id UAA11827 + for ; Thu, 7 Dec 2000 20:27:38 -0500 (EST) +Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) + by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eB81PsP22362; + Thu, 7 Dec 2000 20:25:54 -0500 (EST) + (envelope-from pgsql-hackers-owner+M1809@postgresql.org) +Received: from fw.wintelcom.net (ns1.wintelcom.net [209.1.153.20]) + by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eB81JkP21783 + for ; Thu, 7 Dec 2000 20:19:46 -0500 (EST) + (envelope-from bright@fw.wintelcom.net) +Received: (from bright@localhost) + by fw.wintelcom.net (8.10.0/8.10.0) id eB81JwU25447; + Thu, 7 Dec 2000 17:19:58 -0800 (PST) +Date: Thu, 7 Dec 2000 17:19:58 -0800 +From: Alfred Perlstein +To: Tom Lane +cc: pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] Patches with vacuum fixes available for 7.0.x +Message-ID: <20001207171958.B16205@fw.wintelcom.net> +References: <20001207145732.X16205@fw.wintelcom.net> <28791.976236143@sss.pgh.pa.us> +MIME-Version: 1.0 +Content-Type: text/plain; charset=us-ascii +Content-Disposition: inline +User-Agent: Mutt/1.2.5i +In-Reply-To: <28791.976236143@sss.pgh.pa.us>; from tgl@sss.pgh.pa.us on Thu, Dec 07, 2000 at 07:42:23PM -0500 +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +Status: OR + +* Tom Lane [001207 17:10] wrote: +> Alfred Perlstein writes: +> > Basically Vadim has been able to reduce the amount of time +> > taken by a vacuum from 10-15 minutes down to under 10 seconds. +> +> Cool. What's it do, exactly? + +================================================================ + +The first is a bonus that Vadim gave us to speed up index +vacuums, I'm not sure I understand it completely, but it +work really well. :) + +here's the README he gave us: + + Vacuum LAZY index cleanup option + +LAZY vacuum option introduces new way of indices cleanup. +Instead of reading entire index file to remove index tuples +pointing to deleted table records, with LAZY option vacuum +performes index scans using keys fetched from table record +to be deleted. Vacuum checks each result returned by index +scan if it points to target heap record and removes +corresponding index tuple. +This can greatly speed up indices cleaning if not so many +table records were deleted/modified between vacuum runs. +Vacuum uses new option on user' demand. + +New vacuum syntax is: + +vacuum [verbose] [analyze] [lazy] [table [(columns)]] + +================================================================ + +The second is one of the suggestions I gave on the lists a while +back, keeping track of the "last dirtied" block in the data files +to only scan the tail end of the file for deleted rows, I think +what he instead did was keep a table that holds all the modified +blocks and vacuum only scans those: + + Minimal Number Modified Block (MNMB) + +This feature is to track MNMB of required tables with triggers +to avoid reading unmodified table pages by vacuum. Triggers +store MNMB in per-table files in specified directory +($LIBDIR/contrib/mnmb by default) and create these files if not +existed. + +Vacuum first looks up functions + +mnmb_getblock(Oid databaseId, Oid tableId) +mnmb_setblock(Oid databaseId, Oid tableId, Oid block) + +in catalog. If *both* functions were found *and* there was no +ANALYZE option specified then vacuum calls mnmb_getblock to obtain +MNMB for table being vacuumed and starts reading this table from +block number returned. After table was processed vacuum calls +mnmb_setblock to update data in file to last table block number. +Neither mnmb_getblock nor mnmb_setblock try to create file. +If there was no file for table being vacuumed then mnmb_getblock +returns 0 and mnmb_setblock does nothing. +mnmb_setblock() may be used to set in file MNMB to 0 and force +vacuum to read entire table if required. + +To compile MNMB you have to add -DMNMB to CUSTOM_COPT +in src/Makefile.custom. + +-- +-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] +"I have the heart of a child; I keep it in a jar on my desk." + -- 2.40.0