Horton

Thread

  1. Patches with vacuum fixes available for 7.0.x

    Alfred Perlstein <bright@wintelcom.net> — 2000-12-07T22:57:32Z

    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."

  2. Re: Patches with vacuum fixes available for 7.0.x

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-08T00:42:23Z

    Alfred Perlstein <bright@wintelcom.net> 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?

			regards, tom lane

  3. Re: Patches with vacuum fixes available for 7.0.x

    Alfred Perlstein <bright@wintelcom.net> — 2000-12-08T01:19:58Z

    * Tom Lane <tgl@sss.pgh.pa.us> [001207 17:10] wrote:
> Alfred Perlstein <bright@wintelcom.net> 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."

  4. Re: Patches with vacuum fixes available for 7.0.x

    Tom Samplonius <tom@sdf.com> — 2000-12-08T02:11:29Z

    On Thu, 7 Dec 2000, Alfred Perlstein wrote:

> 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.
...

  What size database was that on?

  I looking at moving a 2GB database from MySQL to Postgres.  Most of that
data is one table with 12 million records, to which we post about 1.5
million records a month.  MySQL's table locking sucks, but as long as are
careful about what reports we run and when, we can avoid the problem.  
However, Postgres' vacuum also sucks.  I have no idea how long our
particular database would take to vacuum, but I don't think it would be
very nice.

  That also leads to the erserver thing.  erserver sounds nice, but I sure
wish it was possible to get more details on it.  It seems rather
intangible right now.  If erserver is payware, where do I buy it?

  This is getting a bit off-topic now...


Tom

  5. Re: Patches with vacuum fixes available for 7.0.x

    Alfred Perlstein <bright@wintelcom.net> — 2000-12-08T03:07:17Z

    * Tom Samplonius <tom@sdf.com> [001207 18:55] wrote:
> 
> On Thu, 7 Dec 2000, Alfred Perlstein wrote:
> 
> > 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.
> ...
> 
>   What size database was that on?

Tables were around 300 megabytes.

>   I looking at moving a 2GB database from MySQL to Postgres.  Most of that
> data is one table with 12 million records, to which we post about 1.5
> million records a month.  MySQL's table locking sucks, but as long as are
> careful about what reports we run and when, we can avoid the problem.  
> However, Postgres' vacuum also sucks.  I have no idea how long our
> particular database would take to vacuum, but I don't think it would be
> very nice.

We only do about 54,000,000 updates to a single table per-month.

>   That also leads to the erserver thing.  erserver sounds nice, but I sure
> wish it was possible to get more details on it.  It seems rather
> intangible right now.  If erserver is payware, where do I buy it?

Contact Pgsql Inc. I think it's free, but you have to discuss terms
with them.

>   This is getting a bit off-topic now...

Scalabilty is hardly ever off-topic. :)

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

  6. Re: Patches with vacuum fixes available for 7.0.x

    Alfred Perlstein <bright@wintelcom.net> — 2001-01-02T23:58:19Z

    * Peter Schmidt <peterjs@home.com> [010102 12:53] wrote:
> Will these patchsets be available to the public?
> I get:
> "You don't have permission to access /~alfred/vacfix/vlazy.tgz on this
> server"
> 
> Thanks.
> Peter
> 
> 
> 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

Oops!  The permissions should be fixed now, if anyone wants to
grab these feel free.

Peter, thanks for pointing it out.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

  7. Re: Patches with vacuum fixes available for 7.0.x

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-23T03:54:48Z

    Vadim, did these patches ever make it into 7.1?

> 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."
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  8. Re: Patches with vacuum fixes available for 7.0.x

    Alfred Perlstein <bright@wintelcom.net> — 2001-01-23T19:15:25Z

    * Bruce Momjian <pgman@candle.pha.pa.us> [010122 19:55] wrote:
> 
> Vadim, did these patches ever make it into 7.1?

According to:
http://www.postgresql.org/cgi/cvsweb.cgi/pgsql/src/backend/parser/gram.y?rev=2.217&content-type=text/x-cvsweb-markup

nope. :(

> 
> > 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."
> > 
> 
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

  9. Re: Patches with vacuum fixes available for 7.0.x

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-24T14:37:50Z

    Here is another open item.  What are we doing with LAZY vacuum?  

> 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."
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  10. Re: Patches with vacuum fixes available for 7.0.x

    Denis Perchine <dyp@perchine.com> — 2001-01-24T16:53:54Z

    On Wednesday 24 January 2001 20:37, Bruce Momjian wrote:
> Here is another open item.  What are we doing with LAZY vacuum?

Sorry for inserting in the middle. I would like to say that when I tried LAZY 
vacuum on 7.0.3, I had a lockup on one of the table which disappeared after I 
did usual vacuum. I have sent an original version of a table from the backup 
to Vadim, but did not get any response. Just for your info.

-- 
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------