InnoDB Website

This test uses the standard DBT2 benchmark, a commonly-used transactional performance workload that models an order-entry application. DBT2 simulates a wholesale parts supplier where several workers access a database, update customer information and check on parts inventories to place an order. Test results are usually reported in the number of “new order” transactions executed per minute (”NOTPM”). To stress test CPU performance with memory-resident data sets, these transactions are executed continuously, with no “think time” or “keying time”. DBT2 can be downloaded from Sourceforge.

This test is designed specifically to demonstrate the value of the new “adaptive flushing” algorithm introduced with InnoDB Plugin release 1.0.4. This new algorithm estimates the required rate of flushing to avoid I/O bursts at checkpoints, based on the speed of the redo log generation and the current rate of flushing. By eliminating such bursts, I/O write activity is smoothed out, and more consistent transaction throughput and response times are achieved.

The InnoDB Plugin 1.0.4 with adaptive flushing enabled provides much more consistent throughput than when the feature is not enabled, as seen in the chart below. Once steady state was achieved after the ramp-up period (starting after the third checkpoint, marked by the dip when not using adaptive flushing), the total number of transactions processed during the run was almost 10% higher with adaptive flushing enabled than when it was not.

The next chart shows that peaks and valleys in writing to the log file are significantly reduced when the adaptive flushing feature is enabled.

Configuration
Dual quad-core Intel Xeon, 3.00GHz, 32 GB memory, RAID 5
Relevant my.cnf parameters:
query-cache-type = 0
query-cache-size = 0
table-open-cache = 2048
innodb-data-file-path = ibdata1:512M:autoextend
innodb_adaptive_hash_index = ON
innodb_buffer_pool_size = 12G
innodb_checksums = ON
innodb_doublewrite = ON
innodb_file_per_table = ON
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
innodb_locks_unsafe_for_binlog = OFF
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 75
innodb_table_locks = ON
innodb_thread_concurrency = 0
innodb_use_sys_malloc = ON