Neelakanth Nadgir on MySQL and ZFS: A File System Revolution

MySQL and ZFS - A File System Revolution

A year ago this past week (on January 16, 2008), Sun Microsystems announced a historic deal to acquire MySQL AB for a billion dollars. Since then, MySQL-ers (aka Sun Dolphins) have settled in with Sun folks. While Sun has had an influence on MySQL, MySQL has also had an influence on several projects within "Sun Classic". Over this week, we hope to explore some of the projects around Sun which use MySQL and how the acquisition has helped bring us all closer together.

Today we talk to Neelakanth Nadgir in the Sun/MySQL Performance Team about MySQL and ZFS, the revolutionary file system included with Solaris and OpenSolaris operating systems from Sun.

Hi Neel, tell us a bit about yourself, your background and about your role with ZFS?

I have been working at Sun for around nine years now. Sun was my first job after graduating from Texas Tech University. Most of my time at Sun has been spent in the performance organization solving performance problems — benchmark specials as well as customer issues. Until Sun's acquisition of MySQL, I had been focusing on Oracle performance, now I am a part of the Sun/MySQL performance team focusing exclusively on MySQL. The group that I belong to, Performance Applications Engineering or PAE, has considerable experience with databases. We have a lot of experience in improving database performance and scalability. So when Sun acquired MySQL and was looking to improve its performance and scalability, we were a natural choice!

I have been involved with ZFS since 2005 (a few months before it was unveiled to the world). Given my background in databases, I look at ZFS from a database centric viewpoint. We have done quite a bit of work with ZFS to make it perform better for databases and much of it has been distilled into best practices and code fixes. We continue to work on making databases work efficiently with ZFS.

Sun's Neelakanth Nadgir

What's the coolest thing about ZFS from your perspective?

I keep finding cooler things about ZFS every day. As a consumer, The one thing I am really impressed with ZFS is the flexibility it provides to customers. It does not matter if you have expensive storage or cheap JBODs ("Just a Bunch of Disks"), ZFS works just fine with them. Combine that with features like self healing, multiple levels of data redundancy, support for Solid State Drives (SSDs), compression, encryption, etc. In short, it is unrivaled. And most importantly, its all free (as in the GNU 'free').

As a programmer, there are lots of cool techniques used by ZFS that really impress me. For example, checkout Ditto blocks, Driveby scheduling, deadline scheduling, gang blocks, IO prioritization, file holes, adaptive endianess, filesystem level io queuing and pipelined IO execution to name a few things.

What are some of the neat things about ZFS that a MySQL user on Linux or Windows might want to consider?

Like I mentioned before, the features provided by ZFS are unrivaled. Let me give you an example. Lets say that you are interested in using SSDs with your database. Many people do not realize that many kinds of SSDs are biased i.e. they are either faster for reads or writes. For a read-biased SSD, the read performance is much better than write performance. If you are using SSDs as a regular disk, you need to be aware of this limitation and plan your data layout carefully. Now with filesystems other than ZFS, you have to use SSDs as a regular disk — you have no choice. You have to figure out which tablespaces are IO heavy and assign them to your SSDs accordingly. ZFS allows you the choice of either using the SSDs as a regular disk, or as a second level cache. If your database does not fit in your SSD, the second option could be very attractive!

For databases, it is well known that write performance is very important, however, not all writes are equal — log writes are more important to performance than tablespace updates. With ZFS you have the option of boosting write performance using special log devices. As an example, you could use your write-biased SSD to process synchronous writes. The key idea here is always flexibility.

How has the acquisition of MySQL into Sun influenced/changed/helped your work with ZFS and MySQL over the last year?

The biggest benefit to Sun having acquired MySQL is that it gives our team access to the engineering geniuses behind MySQL. The level of support we have received so far has been phenomenal. It is now very easy for us to ask why a particular feature was implemented in a specific way and get fast answers. It also allows us the opportunity to showcase our tools and techniques and help the MySQL engineers understand what it means to scale.

On a more personal note, logging on to IRC, MySQL's internal communication method, is so nostalgic :-)

What do you see for the future of ZFS and anything specific that might excite those in the MySQL world?

ZFS was not designed with databases in mind. However its been quite good for use with databases. We are working to make database specific performance enhancements so that ZFS will perform better with databases. We are also looking at new ways of using ZFS in the stack to solve issues like replication using snapshots, etc. All I can say is "Stay Tuned".

Oh I hate not knowing what's about to happen, I guess I'll be patient :) You're giving a talk with Allan Packer at the 2009 MySQL Users Conference, can you tell us a bit about it?

A lot of people are excited about using ZFS with MySQL. Our talk will focus on the lessons we have learnt with using MySQL and ZFS and the current state of MySQL/ZFS performance. We will also be comparing ZFS with other filesystems and provide performance estimates if you are moving from one filesystem to other. If you are considering using ZFS with MySQL, you dont want to miss this :-)

Thanks Neel, certainly I'll be at your talk at the 2009 MySQL Users Conference, see you then!

This interview was performed in January, 2009.