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

Re: Index vacuum improvements

From: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Index vacuum improvements
Date: 2006-03-30 18:38:26
Message-ID: Pine.OSF.4.61.0603302117300.228269@kosh.hut.fi (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, 29 Mar 2006, Simon Riggs wrote:

> First off, we need some good timings that show this effect. I believe
> it, but we need some publicly discussable performance test cases to show
> the effect and then show how much we've improved upon it, repeatably.

Yeah, a good vacuum benchmark would be nice, not so much for this specific 
case but in general.

> Initially, I'd suggest just trying to improve this situation by
> pre-scanning the physical index files into OS filesystem cache (only) -
> i.e. dont lock the files at all. That way, all I/O is sequential into
> memory and then after that all random I/O will be logical. But it would
> *all* need to fit in cache.

If the index is small enough to fit in memory, it's not so much of a 
problem anyway...

> We might be able to improve the index FSM allocation algorithm so that
> we improve the locality of logically adjacent blocks. That way a larger
> than memory index would be able to be read with a limited cache. We
> could then replace the full pre-read with just a limited sequential scan
> ahead.

That would be a good thing for index scan performance too.

> Maybe effective_cache_size could be a real parameter after all?
>
> The existing FSM allocation scheme provides this for certain kinds of
> tables, but not others.

Can you elaborate, please? I couldn't find any evidence of that.

- Heikki

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-03-30 18:51:56
Subject: WAL dirty-buffer management bug
Previous:From: Andrew DunstanDate: 2006-03-30 18:27:25
Subject: Re: control pg_hba.conf via SQL

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