Comparing InnoDB performance on HDD, SSD, in-memory

The chart shows benchmark results taken using sysbench.  Rough understanding would be that (for this scenario) the performance ratio is HDD:SSD:in-memory = 1:10:50.

	transactions/sec.	read/write reqs./sec.
buffer_pool=8M, HDD	19.93	378.59
buffer_pool=8M, SSD (Intel X25-M)	207.70	3946.29
buffer_pool=2048M, HDD	998.82	18977.51

Details:

The benchmark was taken using MySQL 5.1.41 using innodb_plugin running on linux 2.6.31/x86_64 (Ubuntu 9.10 server).  Options passed to sysbench were: --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --mysql-db=test --oltp-table-name=test_t --num-threads=20 .  My.cnf was set as follows.

max_allowed_packet=16777216
query_cache_size=0
default-storage-engine=INNODB
innodb_buffer_pool_size=2048M
innodb_read_io_threads=4           # set to 10 for X25-M
innodb_log_file_size=64M
innodb_flush_method=O_DIRECT
innodb_file_per_table
innodb_flush_log_at_trx_commit=2   # sufficient for our task
key_buffer_size=64M
myisam_sort_buffer_size=32M
slow-query-log
long_query_time=1

Comments

[Kevin Burton]

The options on your SSD are important.

Use readahead=256 and deadline IO scheduler.

Also, you might want to disable readahead in InnoDB if possible in your version ....

[kazuho]

Deadline is the default I/O scheduler of Ubuntu.

Also there were no readaheads occurring when running this benchmark (average read I/O request size observed using iostat -x was just above 16KB when running the benchmark).