On 4/2/06, Martin Scholes <marty@iicolo.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