Re: Vacuum I/O throttling]

On Tue, Sep 02, 2003 at 12:17:28AM -0400, Tom Lane wrote:
> Guy Thornley <guy(at)esphion(dot)com> writes:
> > What sort of performance numbers are you looking for? Without the throttle,
> > I/O is nuked and other database activity takes an age, and with it, its much
> > happier?
>
> Some people say that VACUUM nukes their performance, and some don't
> find it to be a problem. AFAICT, it's only an issue if you have little
> reserve disk bandwidth, which in itself is a dangerous situation for a
> database that you don't want to pay attention to.

Well, I finally got chance to take some performance numbers.

The numbers were taken for a set of queries typical for our app on a test
dataset we actually do our testing with. One of the tables involved is
(rows=35805 width=356) [from explain select * from ...] and the other is
larger, (rows=5407836 width=136).

Test box is a dual Athlon MP2400+ with 512MB of ram. Disk is a bit lacking,
it is a single 40GB 7200rpm IDE disk.

Vacuum Actual User System Find
------------------------------------------------------
No vacuum 3:26.11 0:00.31 0:00.09 0:08.48
Vacuum throttled 3:31.84 0:00.27 0:00.10 0:09.58
Vacuum 167:22.36 0:00.24 0:00.09 2:11.18

Actual,User,System should be self-explanatory; Find is the actual time taken
to perform a "find /usr /var -type f > /dev/null"

For the throttled test, i used set vacuum_throttle = 20.

Theres a box turned up that has dual 10k rpm scsi disks, but it will be a
few days until I can test the dataset on that one.