Re: [HACKERS] Autovacuum Improvements

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
Cc: Russell Smith <mr-russ(at)pws(dot)com(dot)au>, Darcy Buskermolen <darcyb(at)commandprompt(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>, Pavan Deolasee <pavan(at)enterprisedb(dot)com>, Christopher Browne <cbbrowne(at)acm(dot)org>, pgsql-general(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Autovacuum Improvements
Date: 2007-01-22 17:18:46
Message-ID: 200701221718.l0MHIkH17524@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Heikki Linnakangas wrote:
> Bruce Momjian wrote:
> > Heikki Linnakangas wrote:
> >> Russell Smith wrote:
> >>> 2. Index cleanup is the most expensive part of vacuum. So doing a
> >>> partial vacuum actually means more I/O as you have to do index cleanup
> >>> more often.
> >> I don't think that's usually the case. Index(es) are typically only a
> >> fraction of the size of the table, and since 8.2 we do index vacuums in
> >> a single scan in physical order. In fact, in many applications the index
> >> is be mostly cached and the index scan doesn't generate any I/O at all.
> >
> > Are _all_ the indexes cached? I would doubt that.
>
> Well, depends on your schema, of course. In many applications, yes.
>
> > Also, for typical
> > table, what percentage is the size of all indexes combined?
>
> Well, there's no such thing as a typical table. As an anecdote here's
> the ratios (total size of all indexes of a table)/(size of corresponding
> heap) for the bigger tables for a DBT-2 run I have at hand:
>
> Stock: 1190470/68550 = 6%
> Order_line: 950103/274372 = 29%
> Customer: 629011 /(5711+20567) = 8%
>
> In any case, for the statement "Index cleanup is the most expensive part
> of vacuum" to be true, you're indexes would have to take up 2x as much
> space as the heap, since the heap is scanned twice. I'm sure there's
> databases like that out there, but I don't think it's the common case.

I agree it index cleanup isn't > 50% of vacuum. I was trying to figure
out how small, and it seems about 15% of the total table, which means if
we have bitmap vacuum, we can conceivably reduce vacuum load by perhaps
80%, assuming 5% of the table is scanned.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tony Caduto 2007-01-22 17:30:03 Re: MSSQL/ASP migration
Previous Message Ted Byers 2007-01-22 17:13:29 Re: MSSQL/ASP migration

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2007-01-22 17:41:06 Re: [HACKERS] Autovacuum Improvements
Previous Message Merlin Moncure 2007-01-22 17:06:00 Re: savepoint improvements