Re: WAL Bypass for indexes

On 4/2/06, Martin Scholes <marty(at)iicolo(dot)com> wrote:
> I have long believed that the bottleneck in transaction-oriented systems is
> the writing of the indexes, complete with splits and merges. A single update
> to one field of a heavily-indexed table could cause dozens of index writes
> to cascade.

This is not the case with *most* OLTP systems. It is, however, an
issue with systems that use a MVCC system which employ an MVTO-like
algorithm where indexes must be updated because row locations are
never the same.

> Running again pgbench with 75 users and 100 transactions, I received a
> consistent rate of 132 tps, a 40% increase in throughput.

Less I/O = increased throughput. Generally, when you use less CPU or
less I/O, you're going to get better performance. The question is, at
what cost?

> Upon recovery, the index gets rebuilt. The only downside is potentially long
> rebuild times during recovery.

Yes, this is unacceptable for large systems.

While retaining the main MVCC implementation PostgreSQL currently has,
we're working on a prototype to reduce WAL I/O and index updates in a
large percentage of OLTP situations by employing an update-in-place
under *safe* conditions. However, I don't see how your recommendation
would actually be feasable. Similarly, from my point of view, a
database should NEVER become corrupt.

Likewise, I think your tests may have been a little biased... you may
want to look through the xlog and index code to see how temp tables
are handled (or not).

In my opinion, I don't think we should have an option to allow the
indexes to become corrupt.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324