Re: [COMMITTERS] pgsql: Reduce the size of memory allocations by lazy vacuum when - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: [COMMITTERS] pgsql: Reduce the size of memory allocations by lazy vacuum when
Date
Msg-id 1190626102.4181.54.camel@ebony.site
Whole thread Raw
In response to Re: [COMMITTERS] pgsql: Reduce the size of memory allocations by lazy vacuum when  ("Heikki Linnakangas" <heikki@enterprisedb.com>)
Responses Re: [COMMITTERS] pgsql: Reduce the size of memoryallocations by lazy vacuum when
List pgsql-hackers
On Mon, 2007-09-24 at 10:02 +0100, Heikki Linnakangas wrote:
> Alvaro Herrera wrote:
> > Log Message:
> > -----------
> > Reduce the size of memory allocations by lazy vacuum when processing a small
> > table, by allocating just enough for a hardcoded number of dead tuples per
> > page.  The current estimate is 200 dead tuples per page.
> 
> 200 sounds like a badly chosen value. With a 8k block size, that's a bit
> less than MaxHeapTuplesPerPage, which means that in the worst case you
> don't allocate enough space to hold all dead tuples, and you end up
> doing 2 index cleanups, no matter how large you set
> maintenance_work_mem.

Agreed. Tables with 2-4 columns easily fall into that category.
Assoication tables are often like this and can have 2 indexes on them.

> Note that as the patch stands, the capping is not limited to small
> tables. Doing extra index passes on a relatively big table with lots of
> indexes might be cause a lot of real extra I/O.
> 
> How about just using MaxHeapTuplesPerPage? With the default 8K block
> size, it's not that much more than 200, but makes the above gripes
> completely go away. That seems like the safest option at this point.

It would be much better to use a value for each table. Any constant
value will be sub-optimal in many cases. 

Let's use our knowledge of the table to calculate a sensible value. We
often have average row length available from last VACUUM, don't we? Use
that, plus 10%. 

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com



pgsql-hackers by date:

Previous
From: Gregory Stark
Date:
Subject: Re: curious regression failures
Next
From: "Heikki Linnakangas"
Date:
Subject: Re: [COMMITTERS] pgsql: Reduce the size of memoryallocations by lazy vacuum when