At Garmin we were getting too many mysql servers to maintain. We decided to try consolidating MySQL instances onto fewer physical servers. We didn’t want to use server virtualization due to the overhead and memory ballooning that come with VMWare. We had trouble finding much information about running and managing multiple MySQL instances together on a server.
We now have a standard repeatable build for all MySQL servers, including the OS and MySQL instances. We currenly have two people managing 80 MySQL instances. We are using Puppet to configure the servers. We are able to rapidly deploy new instances in as little as 15 minutes.
Each MySQL instance has its own IP, data directory, log directory, start/stop scripts, proxy, and is part of a load balanced pool for planned maintenance and failover. We are able to have multiple versions of MySQL, and rapidly switch between them for testing. We are able to upgrade a single instance without affecting the other instances on the same server. All production instances are paired with a slave for backups and redundancy. Some backups use mysqldump and some use LVM snapshots and scp. We use an F5 load balancer. Each F5 pool usually has four entries, the master proxy and database and the slave proxy and database. We route traffic through the master proxy first so we get query analyzer information. It will automatically failover to the master database if the proxy fails. The standard is manual failover to the slave, but we have a few systems that are able to failover and failback automatically. By utilizing the SAN with LVM, we can add disk space while the databases are still running.
There are also some tricks you can do with LVM and multiple instances, like running a slave and taking a writable snapshot then starting a MySQL instance against the snapshot so that developers can test applications against up to date data. The slave stays up to date and you just stop the second instance, remove the snapshot, take another snapshot, and start the slave periodically to instantly refresh.
Ben has been a DBA at Garmin since 2001.
Mark is a DBA at Garmin.
For information on exhibition and sponsorship opportunities at the conference, contact Yvonne Romaine at firstname.lastname@example.org
For media-related inquiries, contact Maureen Jennings at email@example.com
To stay abreast of conference news and to receive email notification when registration opens, please sign up for the O'Reilly MySQL Conference newsletter (login required).
View a complete list of O'Reilly MySQL Conference contacts.