Moving MySQL from RHEL (CentOS) to Solaris

After quite successful evaluation we have finally decided to move our production MySQL Database servers to Solaris. Why Solaris? Well, there are couple of good reasons for it.

First of all about database part of system architecture design. If your system is designed according to SOA principles you probably have one (or more) relatively small databases for each service app. Let’s assume that small database term describes a database up to 200-300GB of the overall size and up to 50 millions rows in its largest table (no more than 1-2 such tables in a db). This is where MySQL is performing quite well. If your database is much bigger and/or it cannot be segmented because of application nature, then, perhaps, MySQL is not the right choice.

Traffic pattern is also important factor in the system architecture design. In most common, Web oriented application, pattern of accessing data usually doesn’t not much differs from the read/write ratio value of 90/10. In practice, many systems that I saw are dealing with 95% of reads against 5% of write requests. For example, if we are designing simple Users Directory service, we would expect a lot of logins (reads) and modest amount of new user registrations (writes).

Scalability. This is one of most popular arguments of almost every technical discussion about system architecture of an application backed by database engine. The truth is that database scalability issue in most cases exists only for read requests to the database. Read-only database traffic scales out quite good using simple technique of separating read and write queries on different servers using MySQL replication. Using this approach, application is querying MySQL slave(s) when reading data, and all data modification statements go to the master. All the complicity of software design is that application code should distinguish between read and write database connection. And, of course there system overhead of setting up the MySQL replication. Pros and cons of this modus operandi is beyond the scope of the discussion. The only fact that matters is that master database instance has much higher SLA than slave. And one more important requirement for the MySQL server, is that in sake of maintainability of DB servers, the only database(s) that belongs to the application should exist on master and slave(s) instances of MySQL. This limitation comes up as MySQL defines replication per instance and not per database. This requirement restricts putting any additional (even micro-small) on the master server.

Hardware. Normally, we prefer cheap and small servers in our system. Standard server is 1U Dell PowerEdge R410 like machine. We made exception for database hosts and set it up on bigger Dell PowerEdge R710 with 12 cores and 64GB of RAM. We equipped database servers with redundant power supplies and remote access (DRAC) cards. Each database host is running multiple instances of MySQL. Currently, master database is the only piece of system that must not have any downtime and requires immediate attention in case of failure.

Platforms and versions. The system is 100% Linux. We love Debian. For the database server we made exception again, and installed CentOS on it. The reason for this is MySQL. Packages shipped with Debian are MySQL 5.0 wich is as stable as Debian. But 5.0 lacks many features that modern web development demands. Newer, 5.1 packages are not maintained by the Debian team. Respecting well known fact that MySQL 5.1 is relatively buggy piece of software, it has been decided to stick with official binary builds provided by Sun (Oracle now). Today prebuilt  packages are provided for the following Linux Distros: RedHat, Suse Enterprise and Generic Linux, which is basically an archive that should be manually unpacked and placed in right places. We have chosen CentOS as most popular, binary compatible with RedHat system.

MySQL setup layout. We configured our server with CentOS for running multiple instances of MySQL. Each instance has its own data directory, listening on its own TCP port and UNIX socket. In order to achieve this there should be created customized versions of init script (/etc/init.d/mysql.<instance_name>), configuration file (/etc/my.<instance_name>.cnf) and actual data and log directories for each instance.

Having the system running more than a year now, I can confirm that it is working fairly well with the following exceptions:

• Maintenance is somewhat complicated. Configuration of each instance is overhead.
• Scripting, tracing and debugging requires custom/parametrized scripts. Even console mysql client requires connection parameters for the right instance. Funny and annoying fact is that command history of the mysql client is shared for all instances. Sometimes it drives me crazy. 🙂
• No way to isolate system resources. We were needed it one-two times in all the history and I cannot say that this is big problem. But having such a possibility is very nice to have candy.
• RedHat/CentOS software packages are very very outdated. For example, running simple jobs and scripts written in Python (that we use a lot) often becomes a nightmare as CentOS has Python 2.4. Code developed in Python 2.5 and later has no chance to run there without massive refactoring.

So we looked at Solaris as main platform for our database servers. MySQL packages are built by mysql.com and both Solaris and MySQL belong now to the same company, so Solaris seems to be good choice for database server OS. Especially we wanted to evaluate the features described below.

1. Using Solaris Containers
   In the past I have been playing with a few virtualization technologies attempting to use it for running MySQL database. VMWare ESXi was complete disaster for me, IO performance is horrible and it simply kills MySQL. OpenVZ performs much better, as it use similar approach of operating system level virtualization and performance penalty is minimal there. But it still locks us down to RedHat/CentOS. Solaris Containers with its excellent management tools allows to run multiple instances of MySQL without notable performance loss. Containers are very flexible in everything related to resource management. Almost every system aspect can be either shared between zones or reserved to each container. MySQL installation and management is greatly simplified. We have one single version of the my.cnf configuration file, init script, ports and directories layout are simply set to defaults.
2. Using DTrace
   Tracing, monitoring and debugging MySQL is not a trivial task. DTrace is awesome troubleshooting technology. There are DTrace probes support in MySQL. It makes the server problem finding and solving much easier. We just want it. Period.
3. Using ZFS replication and snapshots for DB backup and HA
   Backing up MySQL is always a big headache. Mysqldump is slow and sometimes is not that reliable. There is no binary backup support out of the box. Innodb.com Hot Backup is dead is renamed to MySQL Enterprise Backup and is being sold now as part of commercial editions of MySQL. Percona engineers have developed XtraBackup that does a great job. But it is still interfering with the MySQL engine and quite slow. ZFS replication and snapshots works on much lower level and is extremely fast. Restore is also almost immediate and reliable.

We are running our new Solaris servers now for more than two weeks in production. Everything goes smoothly until now. We will share our future experience further each time we have something new to tell about our MySQL life on Solaris.