Top MySQL performance tips

mysql1600
© Oracle

Our top tips for getting the most out of your MySQL database

Share

MySQL, the open source database designed by a group of developers including 'Monty' Widenius and acquired by Oracle as part of the Sun Microsystems deal in 2010, has become the de facto relational database option in the enterprise.

With that popularity comes a huge range of hints and tips to squeeze the most out of the technology, and we've rounded up some of the best ones for you.

Configure, but not too much

The defaults for MySQL are ancient and almost never sufficient, but some DBAs can get too focused on configuration when it comes to performance issues.

Peter Zaitsev, co-founder and CEO at Percona, a consultancy with MySQL expertise, explains: "MySQL does not auto scale to take advantage of all system resources, so running with default settings can cause very poor performance.

"Make sure you adjust them to match your system size. One common mistake you'll want to avoid is using the same configuration options on your small development system as on your large scale production system. The good news is that there are just a few settings you need to adjust to get close to optimal MySQL performance."

Also, despite being tempting, a server tuning tool probably won't fit your needs, so put the initial time in configuring your server to avoid issues downstream.

Gabriel Ciciliani, internal principal consultant at Pythian adds that its important to keep an eye on defaults because MySQL adjusts them with versions.

"A good example is innodb_log_file_size, which was increased from 5Mb to 50Mb in version 5.6.8 although something closer to 512Mb would probably work better," he says. "And by better I mean that larger files will reduce checkpoint frequency, thus avoiding unnecessary disk writes.

"Defaults changes between versions could also have a negative impact on your application: sql_mode was made stricter in version 5.7 and that could literally break your apps if you were abusing of the relaxed, legacy value."

Get indexes right

Zaitsev says: "Indexes are not needed for running queries, but they are required to run queries fast. Ensure your most performance-critical queries are using indexes, by using EXPLAIN or a monitoring tool.

"Also be mindful of having too many indexes, as the more indexes you have the slower database writes tend to be. Unnecessary indexes also make the database larger and therefore slower." 

Get your database in memory

"Some consider it cheating, but getting your database, or at least all frequently accessed data, into the main memory can be magical for your performance and is increasingly affordable," Zaitsev says.

"While you correctly heard that SSD drives are much faster than HDDs, memory is still several orders of magnitude faster than the fastest SSDs for typical database access patterns. How do you know all the important database bits are in memory? While writes will still need to happen, you should not have any significant disk reads coming from the database."

Analyse your workload

If you want to know why your database has slowed to a crawl it's vital to understand your server's workload. By mapping your workload you can start to identify the most cumbersome queries and start to re-tune accordingly.

There are various tools available for profiling your MySQL database, including the native MySQL Enterprise Monitor's query analyser or Foglight from Quest.

Upgrade your hardware

Your MySQL database is only as good as the hardware it is running on. So if you are suffering from performance issues adding memory is a quick and cheap way to boost performance. Also ensuring that MySQL is running on the best possible CPUs will ensure better query performance.

Alert sparingly

Monitoring and alerting your server workload is vital best practice, but with alerting come false positives and that can create more work than necessary.

As a result, alert sparingly and only for problems that are immediately actionable. For example, a low buffer hit ratio isn't actionable, but an unresponsive server is.

Stick to the InnoDB storage engine

Gabriel Ciciliani from Pythian identifies the InnoDB storage engine as the best fit for most use cases: "It offers a great balance between performance, data consistency, and became MySQL's default in version 5.5.5. It is also receiving most of the attention and development effort."

However, it's important to ensure your InnoDB buffer pool is large enough. Ciciliani adds: "If it's too big, that probably won't be a problem but if your hit ratio is not between 98 and 100 percent, then some more memory could improve performance.

"Estimating the size is not straightforward: if your dataset is small, then you could just allocate enough buffer pool to keep it entirely on memory. For larger datasets, estimating the "active" portion of it (hint: unique pages touched in a day) would give you a good starting point."

Leverage community

There are a range of free resources available for tuning your MySQL instance, events and the forums over at Percona's website. Don't be shy to ask your peers a question if you are struggling with a performance issue, being open source by design, community is at the heart of MySQL anyway, so why not use it?