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.

In this test, several of the new features in Plugin Release1.0.4 are enabled, including adaptive flushing, user-specified I/O capacity and number of read I/O threads (4), as well as the new adaptive read ahead algorithm. The performance gains due to using the new parameter innodb_use_sys_malloc (instead of the internal memory allocator) were not enabled. However, these tests should give you an overall idea of the performance gains to be had when enabling all or most of the new performance features in this release.

We conducted one test using a configuration designed to be CPU-bound (with a 16GB buffer pool, a 128MB log buffer and a 2047MB log file size, and innodb_flush_log_at_trx_commit set to 1). Another test was designed to reflect an I/O-bound environment (with an 18GB buffer pool, 16MB log buffer and 128MB log file size, and innodb_flush_log_at_trx_commit set to 2). Although the buffer pool sizes were comparable, the configuration was constrained particularly with respect to log writes. Thus, this test demonstrates the effectiveness of the new adaptive flushing algorithm. Both tests were run on Linux with a dual quad-core Intel Xeon, 3.00GHz, 32 GB memory, RAID 5 configuration. The relevant MySQL/InnoDB configuration parameters are shown below.

For the CPU-bound test, as the following chart shows, InnoDB Plugin release 1.0.4 produces about 20% more throughput than the built-in InnoDB distributed with MySQL, and yields over 7% more throughput than InnoDB Plugin 1.0.3, with 8 concurrent users.

For the I/O bound configuration, InnoDB Plugin Release1.0.4 produces about 15% more throughput than the built-in InnoDB in MySQL, with 128 users.

MySQL / InnoDB configuration (my.cnf parameters)
query-cache-type = 0
query-cache-size = 0
table-open-cache = 2048
innodb-data-file-path = ibdata1:500M:autoextend
innodb_adaptive_flushing = ON
innodb-additional-mem-pool-size = 500M
innodb_adaptive_hash_index = ON
innodb_buffer_pool_size = 16GB (18GB in I/O bound test)
innodb_checksums = ON
innodb_doublewrite = ON
innodb_file_per_table = ON
innodb_flush_log_at_trx_commit = 1 (2 in I/O bound test)
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_lock_wait_timeout = 50
innodb_locks_unsafe_for_binlog = OFF
innodb_log_buffer_size = 128M (16M in I/O bound test)
innodb_log_file_size = 2047M (128M in I/O bound test)
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 75
innodb_read_ahead_threshold = 56
innodb_read_io_threads = 4
innodb_table_locks = ON
innodb_thread_concurrency = 0
innodb_use_sys_malloc = OFF
innodb_write_io_threads = 4