If you’ve ever setup a web-app on a Linux server, chances are that you’ve setup a MySQL server at least once. On the off chance that you haven’t, we’ll do a quick crash course now:
It might seem like I’m being a bit sarcastic. I am, though not by much. I suspect that most of the instances that I’ve seem running have had no configuration past what comes stock. For a small site, this might be acceptable. For anything of any substance, however, we need a bit more thought regarding our configuration. To that end, most of the articles I’ve seen provide recommendations about increasing the key_buffer, the query_cache_size, or even to swapping the underlying engine between MyISAM or InnoDB. Some of the posts go as far as to suggest rewriting the schema for the data [^1]. While all of these options are likely to provide some speed increase, that list isn’t quite complete: we can also move the data directory to a separate drive.
Unless extra attention was paid to the OS setup,
/var/lib/mysql would likely be on the same physical drive as the OS and the swap partition. Thus, I/O for our database likely has contention with every other application on our system. Simply reducing the competing I/O requests should give us an increase in performance. 1
At this point, I’m going to assume that a second drive is already installed on the system and that it is mounted at
/mnt/data. We’ll start by opening the MySQL configuration file for editing.
Find the line reading datadir = /var/lib/mysql and change it to be a subfolder of our external drive: datadir = /mnt/data/mysql. Now we’ll need to update AppArmor so that it knows to allow the mysqld process to access the new location.
Replace references to /var/lib with /mnt/data.
At this point we need to stop the database, move the data from the old data-store, and start the database again.
All of our applications should now continue working the way they were, so no more configuration should be needed.
###Update (July 2, 2014)### The line “Replace references to /var/lib with /mnt/data.” original read “This file should have four references to /var/lib/mysql. Change them all to /mnt/data/mysql.”
###Footnote### [^1]: Assuming you are actively developing the application in question, I suppose that could be a viable option. Assuming you haven’t already created the application. In which case, I think it might be a bit premature to look at database optimization. Really, “change your schema” sounds more like a veiled “I told you so” than an actual suggestion.
In the event that the server is virtualized, we probably wont get the performance increase, though we will gain the ability to easily take a minimal snapshot of the database that could be stood up or archived as necessary without tying ourselves to the underlying OS installation. ↩