Don't tell us, show us with realistic benchmark results and customer case studies. A post about using OpenSolaris at SmugMug was hugely popular. We need more of the same. The post was popular because the content was very technical.
DTrace is great. But examples seem to be limited to producing information I can already get from MySQL. Show me what I can't get from community MySQL branches such as OurDelta and Percona that have user, index and table monitoring. Where is the repository of DTrace scripts for MySQL?
I think that ZFS can make some MySQL installations run better and faster. The features I am most interested in are compression and the ability to disable the InnoDB doublewrite buffer. Copy-on-write filesystems make it more likely that compression does not kill performance, so ZFS compression can be useful in the real world. Write throughput on a busy Innodb server should be much higher when the doublewrite buffer is disabled. When it is enabled, writes are done in two phases (write up to 128 16kb pages to a log file, sync, write up to 128 16kb pages in place using random IO, sync). But I have a few questions.
- ZFS does not support O_DIRECT. Nor does it support the hints provided by directio. It supports O_DSYNC and O_SYNC so that synchronous writes can be done. When direct IO is used, filesystem prefetching on reads is not done and bad decisions about what to cache in the filesystem buffer cache are avoided. Linux has had problems for MySQL using InnoDB and a large buffer cache. Is Solaris is smarter about managing the ZFS buffer cache? Are there other ways to limit filesystem read prefetching?
- ZFS uses copy-on-write rather than update-in-place. This can fragment files. InnoDB generates prefetch requests with the assumption that logically adjacent blocks within a 1MB extent are physically adjacent. Most of the benchmarks I have seen for ZFS + InnoDB are on files that have not been aged and are unlikely to have fragmentation. Is this a problem? Are there tools to measure and fix fragmentation?
- Group commit is broken for Innodb when the binlog (replication log) is enabled. If the master is run with innodb_flush_trx_log_at_commit=1, then the Innodb commit log is flushed to disk per commit so the writes for the log will be smaller. What value should be used for recordsize and the Innodb transaction log?
Who wants to go through the hassle of building MySQL on OpenSolaris? I have seen too many blog posts that describe too many problems.
Is anyone running MySQL and Solaris in a public cloud? Sun's service isn't open to new users at this time. OpenSolaris can be used with Amazon EC2, but you have to register to get the OpenSolaris AMI. MySQL is extremely popular in private clouds and there is a huge opportunity to provide services and tools that make it easier to run MySQL in this setup.