Skip site navigation (1) Skip section navigation (2)

Re: Resumable vacuum proposal and design overview

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Galy Lee <lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp>, "Jim C(dot) Nasby" <jim(at)nasby(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Resumable vacuum proposal and design overview
Date: 2007-02-28 22:14:24
Message-ID: 45E5FEC0.4040401@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Gregory Stark wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> 
>> How much memory would it save during VACUUM on a 1 billion row table
>> with 200 million dead rows? Would that reduce the number of cycles a
>> normal non-interrupted VACUUM would perform?
> 
> It would depend on how many dead tuples you have per-page. If you have a very
> large table with only one dead tuple per page then it might even be larger.
> But in the usual case it would be smaller.

FWIW, there's some unused bits in current representation, so it might 
actually be possible to design it so that it's never larger.

One optimization to the current structure, instead of switching to a 
bitmap, would be to store the block number just once for each block, 
followed by a variable length list of offsets. It'd complicate the 
binary search though.

> Also note that it would have to be non-lossy.

Yep. Or actually, it might be useful to forget some dead tids if it 
allowed you to memorize a larger number of other dead tids. Hmm, what a 
weird thought :).

Another insight I had while thinking about this is that the dead tid 
list behaves quite nicely from a OS memory management point of view. In 
the 1st vacuum phase, the array is filled in sequence, which means that 
the OS can swap out the early parts of it and use the memory for buffer 
cache instead. In the index scan phase, it's randomly accessed, but if 
the table is clustered, it's in fact not completely random access. In 
the 2nd vacuum pass, the array is scanned sequentially again. I'm not 
sure how that works out in practice, but you might want to use a larger 
maintenance_work_mem than you'd think.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-hackers by date

Next:From: Martijn van OosterhoutDate: 2007-02-28 22:26:48
Subject: Re: SOC & user quotas
Previous:From: Andrew DunstanDate: 2007-02-28 22:13:59
Subject: Re: Compilation errors

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group