Running Sysbench Benchmark on MySQL using Solid State Drives and ZFS

By Roger Bitar, Systems Technical Marketing

Introduction

As 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 ZFS

The following TPS (transactions per seconds) results were obtained for read only operations:

The following latency results were obtained, smaller is better:


Conclusion



  1. 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.

  2. 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.

  3. Database transaction latency is much better (30x) when using SSDs compared to HDDs.

  4. 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. 

Comments:

What about read/write scenarios?

Posted by Behrang on February 23, 2009 at 09:30 AM PST #

Behrang,

Good question! I only published read only results, because you get the most performance with read, and I wanted to highlight the dramatic difference between SSD and HDD in terms of performance and latency, since we will be introducing SSD in our servers. Besides, read operations are much more predominant than write operations.

Now, why you get better results with read, is because with flash and SSD, you need to erase a memory cell before you can write to it. Even with this caveat, you still get better performance than with HDD. In addition, all the SSDs available from Sun also come with a built-in buffer to help speed up the write operations.

Posted by Roger Bitar on February 24, 2009 at 02:54 AM PST #

What about writes and are there any issues with the writes running out on the SDD ?

What would be really nice is if something like SMART would enumerate the erase blocks on the drive, determine the max erase cycles, and read the current number of erase cycles - thus allowing us to have an ETA on failures of the drives...

We are seriously contemplating this - but worried about having them run in production for this reason.

I do think however the many Typo3 databases we are running would FLY !

Posted by Glenn Kelley on March 05, 2010 at 09:51 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

blueprints

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Feeds