Re: WAL Bypass for indexes

Ok Tom, I stand corrected.

I downloaded the latest snapshot and both scenarios (normal and WAL bypass for indexes) produced between 185 and 230 tps on my machine.

The lesson here is that whatever WAL magic has been performed on the latest release gives over 100% speedup, and the speedup is so good that skipping WAL for indexes does basically nothing.

Kudos.

Cheers,
M
_____ Original message _____
Subject: Re: [HACKERS] WAL Bypass for indexes
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: 02nd April 2006 5:17:50 PM

"Martin Scholes" <marty(at)iicolo(dot)com> writes:
> I did some informal testing using pgbench on v8.07. First, I ran pgbench =
> normally with 75 users doing 100 transactions, full vacuuming between runs. =
> My machine consistently gave me 92 tps.

> As an experiment, I commented out of the btree index source all of the XLOG =
> code I could find. I basically replaced the test for a temp table with "if =
> (0)" and then recompiled.

It'd be more interesting if you'd done this testing on 8.1, or better
CVS HEAD, as we took several steps to improve WAL performance in 8.1
(notably, abandoning the 64-bit CRC code). Also, when you don't say
what configuration you were testing, the test results don't mean a lot.
The cost of WAL logging is *very* heavily influenced by things such as
checkpoint frequency, whether you have a separate drive for WAL, etc.

> It seems to me that major performance gains can be had by allowing some =
> indexes to be created with some "UNSAFE-FAIL" flag,

This might be worth doing, but I'd want to see a more convincing
demonstration before putting effort into it ...

regards, tom lane