InnoDB, IO-bound OLTP, 2-disk server

I have run many tests on mid-size servers to show how patches make InnoDB faster for IO bound workloads. The servers could do more than 1000 IOPs and the patches make a big difference. But do the patches help on small servers? I ran tpcc-mysql on my home server with 2 SATA disks, 2 CPU cores and 2GB RAM. There were two interesting results:

1. The v4 Google patch makes things faster.
2. Only the v4 Google patch enforced innodb_max_dirty_pages_pct=20. InnoDB is not good at enforcing this limit on write-intensive workloads. This problem has not received much press. If you are running a critical OLTP server and want it to recover quickly after a crash then this limit must be enforced. Code has been added to the v4 Google patch to delay user sessions by making them flush dirty pages prior to making more pages dirty if the limit has been exceeded. This is enabled by the my.cnf parameter innodb_check_max_dirty_foreground. Nobody has reviewed this code (hint, hint).
   

I tested the v4 Google patch (5.0.37), Percona highperf b13 (5.0.77) and unmodified MySQL 5.0.77 using tpcc-mysql. The server uses 2 disks with SW RAID 0, 1 MB RAID stripe, XFS and the SATA write cache disabled.

tpcc-mysql was run with 20 warehouses and 8 users with a 600 second warmup and 3600 second measurement period.

Results

The 5077 binaries were faster than the v4 Google patch at innodb_max_dirty_pages_pct=20 because they did not enforce that limit. I have included a result for the v4 Google patch at innodb_max_dirty_pages_pct=50 to provide an additional reference point. 

Binary	TpmC	innodb_max_dirty_pages_pct	Avg %dirty pages
v4-5037	1294	20	25.1
percona-5077	2112	20	60.0
mysql-5077	2836	20	72.1
-
v4-5037	3024	50	52.8
-
v4-5037	3854	80	72.0
percona-5077	2930	80	73.7
mysql-5077	3373	80	82.8

Throughput over time for v4-5037:

Configuration

Notes:

• tests were run twice for each binary with innodb_max_dirty_pages_pct set to 20 and 80
• the Percona highperf b13 5.0.77 binary uses more memory for the same value of innodb_buffer_pool_size, so I had to reduce that value to 1G
  

The my.cnf settings for the v4 Google patch:

innodb_buffer_pool_size=1200M
innodb_log_file_size=1900M
innodb_flush_log_at_trx_commit=2
innodb_io_capacity=250
innodb_read_io_threads=2
innodb_write_io_threads=2
innodb_max_dirty_pages_pct=80 or 20
innodb_ibuf_max_pct_of_buffer=10
skip_innodb_ibuf_reads_sync
innodb_doublewrite=0
innodb_file_per_table
allow_view_trigger_sp_subquery
skip_innodb_readahead_random
skip_innodb_readahead_sequential
innodb_flush_method=O_DIRECT
innodb_check_max_dirty_foreground

The my.cnf settings for Percona highperf b13 5.0.77:

innodb_log_file_size=1900M
innodb_buffer_pool_size=1000m
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_io_capacity=250
innodb_read_io_threads=2
innodb_write_io_threads=2
innodb_max_dirty_pages_pct=80
innodb_ibuf_max_size=120M
innodb_ibuf_active_contract=1
innodb_ibuf_accel_rate=200
innodb_doublewrite=0
innodb_read_ahead=0
innodb_adaptive_checkpoint=1

The my.cnf settings for 5.0.77:

innodb_buffer_pool_size=1200M
innodb_log_file_size=1900M
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=80
innodb_doublewrite=0
innodb_file_per_table
innodb_flush_method=O_DIRECT