Anyone who runs a web server with database intensive web sites and applications knows how hard it can be to tame this beast and end up with just the right MySQL configuration for your circumstances. This is especially true if you’re not a full time Linux server administrator, or a database expert, but do run your own web sites on your own server.
There are many variables to consider each of which can have an effect on your database performance. A badly configured database server can often be the number one performance bottleneck on your system.
For a long time you had two basic options. First is to hire an expert to take a look at your configuration and help you tailor it to your needs and circumstances, which is a combination of your available resources, the types of sites you run, the traffic you get, the number of databases and their tables, and so on.
The second is to continuously tweak and test until you reach the optimal configuration. There are some tuning scripts that can help you with that, like MySQLTuner-perl, but they aren’t perfect and they are only effective after you’ve had your MySQL running and under typical load for a while.
However, there are a couple of relatively quick and easy things you can do to significantly optimize your database performance. One is to switch to a MySQL compatible drop-in replacement like MariaDB and Percona Server, and the other is to use the Percona Configuration Wizard for MySQL to produce what is likely to be the most optimal configuration that you can get without spending a lot of time tweaking and testing.
Switch to MariaDB or Percona Server
Since Oracle acquired Sun Microsystems and with it MySQL (originally an independent company) the MySQL community, including some of its original developers, worried about the direction Oracle will take with it, and whether it will preserve its open source licensing. This led to the founding of MariaDB by the same developer who founded the original MySQL. MariaDB is meant to be a fully API compatible drop-in replacement while adding some important enhancement.
Since then many server administrators switched to MariaDB motivated both by its arguably more trustworthy stewardship and its enhancements. MariaDB contains more storange engines, a number of additional features, and most importantly performance improvements.
Percona was founded in 2006 by two MySQL support engineers to offer MySQL consulting services. They maintain the MySQL Performance Blog that is chok full of very useful insights and benchmarks. They offer their own fork of MySQL called the Percona Server which is basically MySQL Community Edition with their own optimizations added and including their XtraDB storage engine.
Both MariaDB and Percona Server will likely offer greater performance than stock MySQL, and both are fully compatible with MySQL so you can easily switch to either. Probably the main differences between them is that Percona Server is more focused on improving InnoDB table performance whereas MariaDB makes improvements to both InnoDB and InnoDB tables. MariaDB also includes XtraDB storage engine from Percona so it might be the best bet for most people who don’t have the needs that specifically match what Percona is focusing on.
Installing MariaDB is pretty easy. Some Linux distributions are even starting to include it by default. Otherwise you can use the MariaDB Repositories tool to get all the instructions you need to get it installed. Once you have it just restarting your MySQL server will be all you need to start using MariaDB.
Getting Percona Server is similarly simple. Check the Percona Software Repositories page for information and links to how to get the repository added and install Percona Server. Alternatively you can go to Percona Downloads, click the Download button on the latest version and choose your distribution. Then you can download and install each package.
Use The Percona Configuration Wizard
Now that you’ve got a speedier version of MySQL you can use The Percona Configuration Wizard to help you create the most optimal configuration.
It is a great and elegant tool that will ask you some key questions about your server resources, workload, use cases and so on. Based on the information you provide it will generate an optimal my.cnf configuration file that you can simply copy to your server, besides any custom modifications you might have (like directory paths). The configuration values it generates are tuned to your specific needs while utilizing the Percona’s well acclaimed best practices.
The configuration file it generates is very elegant and easy to read, and will probably be way better than whatever you ended up with after endless customizing and testing of your own configuration.
It’s possible that some of the questions it asks will mention concepts you might not be familiar with, but it briefly explains each of them in a relatively easy to understand way.
To get your configuration file you will need to register, but that has the benefit of saving the generated configuration for future reference.
And that’s the fastest and easiest way to fix up your database server performance. A better more optimized version of MySQL + a clean, optimized, and tailored configuration file generated in accordance to best practices and expertise.