InnoDB at 10,000 IOPS
How does InnoDB do on a server that supports 10,000 IOPS? I am sure this wasn't the first thing on Heikki's mind as he was designing InnoDB 10 to 15 years ago. On hardware that can do 35,000 reads/second of 16kb pages I can get ~11,000 reads per second from InnoDB in MySQL 5.0 and ~17,000 reads per second from the InnoDB plugin. Mutex contention within InnoDB is the limiting factor.
For 16kb page reads, the maximum IOPS on the server depends on the number of concurrent requests. These are results from sysbench fileio for an 8-core server. I don't have the command line, but used this configuration with my sysbench branch.
- ran for 180 seconds
- used 1 64GB file
- used the rndrd mode to do random reads
- file opened with O_DIRECT
- test repeated for 1, 2, 4, 8, 16, 32, 64 and 128 threads
- used 4kb, 8kb and 16kb reads
- test run using these page checksum algorithms: none, crc32, innodb_byte, innodb_word
- I think I used XFS
This lists IOPS by the number of concurrent threads. The maximum IOPS was somewhere between 32 and 128 threads.
1 thread 2 threads 4 threads 8 threads 16 threads 32 threads 64 threads 128 threads
3271 8185 16392 16841 17497 27593 35618 35889
I then ran sysbench OLTP to determine what performance is possible with InnoDB. MySQL clients and server ran on the same 8-core server. The InnoDB datafile for this configuration is ~30G and the buffer pool is ~2G. The test is configured to fetch 1 row by primary key using a HANDLER statement per transaction.
The my.cnf values for this test:
The sysbench command line uses the --oltp-point-select-mysql-handler option that is in the launchpad branch.
sysbench --seed-rng=1 --test=oltp --oltp-table-size=125000000 --max-time=180 --max-requests=0 \
--mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --oltp-read-only --oltp-skip-trx \
--oltp-test-mode=simple --oltp-point-select-mysql-handler --oltp-dist-type=uniform --num-threads=X run
Results are transactions per second from sysbench. As this test uses 1 query per transaction and fetches 1 row by primary key with a database that is much larger than the buffer cache, the results also are similar to the max IOPS that InnoDB can perform. The server is saturated at ~11k IOPS for MySQL 5.0 and ~17k IOPS for MySQL 5.1 with the InnoDB plugin. I think the results are limited by mutex contention on the InnoDB buffer pool mutex.
binary 1 thread 2 threads 4 threads 8 threads 16 threads 32 threads 64 threads 128 threads
5.0.84 4207 6539 9771 11688 11394 10918 10152 8919
1.0.4-innodb 4359 7054 11289 13928 16765 17371 16937 16036