Running Sysbench Benchmark on MySQL using Solid State Drives and ZFS
By blueprints on Feb 23, 2009
By Roger Bitar, Systems Technical Marketing
IntroductionAs a follow up to the previous Sysbench benchmark that ran on Solaris UFS, we re-ran the benchmark using ZFS filesystem on the same setup and configuration. Solaris ZFS does not allow the forcedirectio option as with UFS. We followed the “ZFS Best Practices Guide” recommendations. Namely, we limited the size of the Adaptive Replacement Cache (ARC) to 1GB, and we set the ZFS recordsize to 16K as it matches the Innodb page size. We used the same MySQL configuration file “my.cnf” that was used in the previous UFS test.
Results on Solaris ZFSThe following TPS (transactions per seconds) results were obtained for read only operations:
The following latency results were obtained, smaller is better:
- We obtained better results when we used UFS, because of the forcedirectio option used when mounting the filesystem. ZFS does not have this option, instead we limited the ARC cache to 1GB in the /etc/system file.
- Again, SSDs demonstrated a significant advantage (up to 7x) for this read-only workload in environments where memory was constrained when using smaller innodb_buffer_pool_size.
- Database transaction latency is much better (30x) when using SSDs compared to HDDs.
- The best results with this type of workload are obtained on regular disks along with ample main memory. SSDs come a close second, even when main memory is severely constrained. Throughput is significantly worse when regular disks are combined with insufficient buffer memory.