A reason to use 5.1

We now have 2 great storage engines for 5.1 -- InnoDB 1.0.4 and XtraDB. We need more performance results to understand InnoDB 1.0.4, but it looks excellent from the code I have reviewed. This describes some of the changes based on a brief review. All of this make my work easier as I can reduce the size of the patch I need to maintain extreme performance with MySQL.

Kudos to InnoDB for delivering these features in 5.1 and to Percona and Google for contributing patches.

1. support for more background IO threads - InnoDB and XtraDB support a configurable number of background IO threads for prefetch reads and dirty page writes. The my.cnf parameters are innodb_read_io_threads and innodb_write_io_threads. Prefetch read requests are generated during queries and when insert buffer entries must be merged. For InnoDB, IO requests are hashed by extent number (64 16kb pages per extent) to the per thread request queues although when a request queue is full, then a request will use any queue. Each queue can hold 256 pending requests. I assume the code in XtraDB is the same. The Google patch uses one queue for all read or write threads which should provide better throughput when there are hot extents, but also requires many more changes to the current source.
2. support for group commit - not only does this fix an old regression, I think that it also fixes bug 46459 which degrades performance when autocommit insert statements are used on tables with an auto increment column.
3. adaptive flushing - one of the things that makes InnoDB is the use of adaptive algorithms to keep the server balanced. Many of these are not documented because they work and we don't need to know about them. They may have added a new one with support for adaptive flushing. I hope to see performance results from Percona for this, but I think this is another thing in InnoDB we can soon forget as it will work without problems.
   
4. readahead - prior to 1.0.4, InnoDB could generate read prefetch requests when it detected sequential or random access to most pages in an extent. For 1.0.4, the use of readahead for random access to pages within an extent appears to have been removed. The use of readahead for sequential access to pages within an extent has been changed to use a new my.cnf parameter, innodb_read_ahead_threshold, that sets the number of pages that must be accessed sequentially within an extent before all of the pages in the physically adjacent extent will be prefetched. I am still not fond of this feature because:

• I am not aware of any performance counters that report on the success of readahead (#fetched versus #fetched_and_used). But you can disable readahead now and measure the impact on your application.
  
• Prefetch requests for the pages in the next extent are generated late. For example, if innodb_read_ahead_threshold=56, then requests are generated when the 56th (out of 64) page in the current extent is used.
• If request merging is done for all of the pages in the next extent, then a 1MB read will be used and none of the pages can be accessed until the read completes.