Insert Buffer

The InnoDB insert buffer significantly reduces the disk IO required to support a change intensive workload when the database does not fit in the buffer pool. Eventually I must begin calling it the change buffer as it does more in MySQL 5.5.

The insert buffer reduces the disk IO required to maintain secondary indexes. For many servers that I care about it reduces secondary index maintenance IO by a factor of 2 to 5 -- something between 2:1 and 5:1. Someone reported a server today where the savings was 16:1. On the benchmark I describe in this note, the insert buffer makes InnoDB 36X faster and that rate is likely to approach 80X faster if I let the test run to completion. I think it can do even better with smarter management of the LRU.

Benchmarks provide a simple way to demonstrate the performance. I ran the Insert Benchmark on two servers with InnoDB. Both servers used a 16G buffer pool and loaded 1B rows using iibench.py and one connection. Each server can do ~1600 reads/second across 8 SAS disks and ~200 reads/second to 1 disk.

With the insert buffer enabled the test completed in ~65 hours. The row insert rate over the last 1M rows was ~2500/second. The row insert rate over the 1M rows from 595M to 596M was ~4500.

Facebook Patch

I pushed more changes to the Facebook patch for MySQL. These include:

• Add the my.cnf parameter innodb_merge_sort_block_size that can be set per session. This determines the amount of memory used by InnoDB during fast index create. The default is 1MB.
• Add more SHOW STATUS counters for monitoring replication. The counters Relay_log_io_events and Relay_log_io_bytes are the number of events and bytes written by the IO thread. The counters  Relay_log_sql_events and Relay_log_sql_bytes are the number of events and bytes consumed by the SQL thread. The counter Relay_log_sql_wait_seconds is the number of seconds that the SQL thread waits for the IO thread to provide events. This occurs when the master is idle or the network between master/slave is slow.
• Add the column Records_in_range_calls to IS.user_statistics. This column counts the number of calls to handler::records_in_range per account.
• Add log_query_sample_rate and log_error_sample_rate. When log_query_sample_rate is > 0, then 1 / log_query_sample_rate of all queries are written to the slow query log. When log_error_sample_rate is > 0, then 1 / log_error_sample_rate of all queries that raise an error are written to the slow query log. These do not change behavior for the existing parameters that also determine when a statement is logged.
• Add the Innodb my.cnf variable innodb_sync_checkpoint_limit. This is a percentage and is used to override the sync and async checkpoint limits. This is ignored when set to 0. The limits determine when dirty pages are flushed to enforce fuzzy checkpoint constraints.

innodb_sync_checkpoint_limit

When this option is set to 75 it is similar to the previous behavior where the limits could not be modified. Making this greater than 80 might reduce the dirty page flush rate. Assume that max_size is the sum of the transaction log file sizes, then the current code sets the sync checkpoint limit to approximately 0.9 * 0.9 * (15/16) of max_size (about 75% of it) and the async limit to 0.9 * 0.9 * (7/8) of max size (about 70% of it). When innodb_sync_checkpoint_limit is set to a nonzero value, then the sync limit is set to (sync_checkpoint_limit/100) * 0.95 of max_size and the async limit is set to (sync_checkpoint_limit/100) * 0.90 of max_size. The impact of this is that you get the performance benefits of a larger transaction log file without making it larger.

Several SHOW STATUS counters were added to improve monitoring for this. Below, age is Innodb_lsn_current - Innodb_lsn_oldest.

• Innodb_preflush_async_limit - limit at which async page flushes are done for fuzzy checkpoint
• Innodb_preflush_sync_limit - limit at which sync page flushes are done for fuzzy checkpoint
• Innodb_preflush_async_margin - age minus preflush_async_limit
• Innodb_preflush_sync_margin - age minus preflush_sync_limit
• Innodb_checkpoint_lsn - LSN of last checkpoint
• Innodb_lsn_current - current LSN
• Innodb_lsn_current_minus_oldest - current LSN minus oldest LSN for which there is a dirty page in the buffer pool
• Innodb_lsn_current_minus_last_checkpoint
• Innodb_lsn_oldest - min of the oldest modification LSN from all dirty pages
• Innodb_purged_pages - number of pages processed by trx_purge

I ran sysbench for a readwrite workload using 2 x 1900 transaction log files and with innodb_sync_checkpoint_limit=95, the page write rate was 80% of the rate with the default settings.