If you’ve been tuning your MySQL database and have wondered what effect theÂ innodb_flush_method settings have on write performance, then this information might help. I’ve recently been doing a lot of baseline load tests to show performance differences between localdisk and the new SAN we’re deploying. Since we run InnoDB for everything in production, and writes are very heavy, I decided to run comparison tests between two identical servers to find the best setting for innodb_flush_method. We have the following specs for the hardware:
- Dell R610
- 24 core Intel Xeon X5670 @ 2.93ghz
- 72GB ECC RAM
- Brocade 825 HBA
- Local disk: RAID-10 15K SAS Ext3 (ugh)
- SAN: Oracle 7420 with four Intel Xeon X7550 @ 2.00GHz, 512GB RAM, 2TB read-cache(MLC-SSD), 36GB write cache (SLC-SSD),Â 3 disk shelves populated with 60x2TB 7200RM SATA drives setup in mirrored format with striped logs, dual 8Gb FC links to redundant fabric, connected to BrocadeÂ DCX 8510-4.
- The my.cnf file being used for the tests: click-click
I’m using the following sysbench command to run the tests. On each server the same commands are used. I ran a 1B row prepare prior to the 1B row test.
sysbench –db-driver=mysql –num-threads=64 –max-requests=1000000000 –max-time=3600 –test=oltp –verbosity=3 –validate=off –oltp-test-mode=complex –oltp-read-only=off –oltp-table-name=sbtest –oltp-table-size=1000000000 –oltp-dist-type=special –mysql-host=localhost –mysql-port=3306 Â –mysql-table-engine=innodb run
On the server that is utilizing SAN paths there are two LUNS presented for MySQL use. /db/data01 for InnoDB data files, /db/logs01 for InnoDB logs. These filesystems are both formatted as XFS. The server running local-disk tests is running Ext3. I might run some more tests later with the local-disk setup as XFS if time allows.
Here are the results. Clearly a well designed SAN infrastructure is superior to even RAID-10 15K SAS drives. And of course you can see the different performance values from using O_DIRECT for the innodb_flush_method for the different data storage mediums.
1B Row Complex Transactional Test, 64 threads
- SAN O_DIRECT: read/write requests: 31560140 (8766.61 per sec.)
- SAN O_DSYNC: read/write requests: 5179457 (1438.52 per sec.)
- SAN fdatasync: read/write requests: 9445774 (2623.66 per sec.)
- Local-disk O_DIRECT: read/write requests: 3258595 (905.06 per sec.)
- Local-disk O_DSYNC: read/write requests: 3494632 (970.65 per sec.)
- Local-disk fdatasync:Â read/write requests: 4223757 (1173.04 per sec.)