//
you're reading...
multple mysql instances

Running multiple MySQL instances in parallel

I know, I haven’t been posting much lately. 5.5 upgrades got postponed due to the new storage platform needing my immediate attention and being a speaker at the Percona Live conference in April also needs a lot of attention.

One of the things I want to try out is running multiple MySQL instances on the same machine. The concept remained in the back of my mind ever since I attended Ryan Thiessen’s presentation on the MySQL conference 2011 but we never actually got a proper usecase for it. Well, with the new storage platform it would be really beneficial so an excellent use case to try it out! So what have I been busy with in the past week? That’s right: running multiple instances MySQL on one single server. ;)

Even though it is not well documented and nobody describes the process in depth it is not that complicated to get multiple instances running next to each other. However it does involve a lot of changes in the surrounding tools, scripts and monitoring. For example, this is what I changed so far:

1. MySQL startup script
Yes, you really want this baby to support multiple instances. I’ve learned my lesson with the wildgrowth of copies of the various MMM init scripts.

2. Templating of configs
If you want to maintain the instances well you should definitely start using a fixed template which includes a defaults file. In our case I created one defaults file for all instances and each and every instance will override the settings of the defaults file. Also some tuning parts are now separated from the main config.

3. Automation of adding new instances to a host
Apart from a bunch of config files, data directory you really want to have some intelligence when adding another instance. For example only the innodb_buffer_pool_size needs to be adjusted for each new instance you add.

4. Automation of removing instances from a host
Part of the step above: if you can add instances, they you need to be able to remove an instance. This should be done with care as it will be destructive. ;)

After this there is still a long extensive list of things to be taken care of:
1. Automation of replication setup
The plan is to keep things simple and have two hosts replicate all instances to each other. So the instance 3307 on host 1 will replicate to instance 3307 on host 2 (and back), instance 3308 on host 1 will replicate to instance 3308 on host 2 (and back), etc.

2. HA Monitoring needs thinking/replacement
I haven’t found a HA Monitoring tool that can handle multiple instances on one host.
Why is this a problem?
If only one of the MySQL instances needs maintenance you can’t use the current tools unless you are willing to make all other instances unavailable as well. Also what will you do when the connection pool of one instance gets exhausted? Or if one instance on both servers die?

3. Backup scripts needs some changes
Obviously our backup tools (wrapper scripts around xtrabackup) need some alteration. We are now running multiple instances, so we need to backup more than one database.

4. Cloning scripts need some changes
We have a script that can clone a live database (utilizing xtrabackup) to a new host. Apart from the fact that it assumes it needs to clone only one single database we might also go for full cloning of all instances

5. Monitoring needs to understand multiple instances
Our current (performance) monitoring tools, like Nagios/Cacti/etc, only assume one MySQL instance per host. At best I can implement the templates multiple times, but that also increases the number of other checks with the same factor.

And there obviously a lot more things I haven’t thought of yet. As you can see I’ll be quite occupied in the upcoming period…

About these ads

About Art van Scheppingen

Head of Database Engineering at Spil Games

Discussion

2 thoughts on “Running multiple MySQL instances in parallel

  1. Hi Art,

    As you may or may not know, in previous years I have recorded and distributed conference videos as a volunteer for Technocation, Inc – a not-for-profit providing educational resources to IT professionals.

    I have decided that it’s best to secure permission ahead of time from the speakers and the event, so that there is no confusion about what I am doing.

    I have asked Percona and they are OK with Technocation recording and distributing the content. So now I’m asking you if, as a speaker, you are OK with it. I will not be putting any titles around the videos, and they will be embeddable. If you want to download your own copy of the video to host somewhere, just let me know and I can send you a link to the full-size video. You can always ask me not to publish the talk, if something disastrous happens, or the talk doesn’t go as well as you’d hoped.

    Please let me know if it is OK (or if it is NOT OK) to record your talk. If you’re not sure, remember that we can always delete the video after we record it, if you’re not happy with it, but if you’re happy with how your talk went, we can’t go back in time to record it – so if you’re unsure I’d recommend to say “yes” now but let me know after the fact if you don’t want the video published.

    Thanx!

    Sheeri K. Cabral

    Posted by awfief | April 4, 2012, 1:16 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 426 other followers

Follow

Get every new post delivered to your Inbox.

Join 426 other followers

%d bloggers like this: