10 essential performance tips for MySQL

From workload profiling to the three rules of indexing, these expert insights are sure to make your MySQL servers scream

Current Job Listings

As with all relational databases, MySQL can prove to be a complicated beast, one that can crawl to a halt at a moment's notice, leaving your applications in the lurch and your business on the line.

The truth is, common mistakes underlie most MySQL performance problems. To ensure your MySQL server hums along at top speed, providing stable and consistent performance, it is important to eliminate these mistakes, which are often obscured by some subtlety in your workload or a configuration trap.

[ To enhance the performance and health of your MySQL systems, check out our 10 essential MySQL tools for admins. | Learn how to master MySQL in the Amazon cloud. | Keep up to date on the key business tech news and insights with the InfoWorld Daily newsletter. Subscribe today! ]

Luckily, many MySQL performance issues turn out to have similar solutions, making troubleshooting and tuning MySQL a manageable task.

Here are 10 tips for getting great performance out of MySQL.

MySQL performance tip No. 2: Understand the four fundamental resourcesTo function, a database server needs four fundamental resources: CPU, memory, disk, and network. If any of these is weak, erratic, or overloaded, then the database server is very likely to perform poorly.

Understanding the fundamental resources is important in two particular areas: choosing hardware and troubleshooting problems.

When choosing hardware for MySQL, ensure good-performing components all around. Just as important, balance them reasonably well against each other. Often, organizations will select servers with fast CPUs and disks but that are starved for memory. In some cases, adding memory is cheap way of increasing performance by orders of magnitude, especially on workloads that are disk-bound. This might seem counterintuitive, but in many cases disks are overutilized because there isn't enough memory to hold the server's working set of data.

Another good example of this balance pertains to CPUs. In most cases, MySQL will perform well with fast CPUs because each query runs in a single thread and can't be parallelized across CPUs.

When it comes to troubleshooting, check the performance and utilization of all four resources, with a careful eye toward determining whether they are performing poorly or are simply being asked to do too much work. This knowledge can help solve problems quickly.

MySQL performance tip No. 4: Filter results by cheapest firstA great way to optimize MySQL is to do cheap, imprecise work first, then the hard, precise work on the smaller, resulting set of data.

For example, suppose you're looking for something within a given radius of a geographical point. The first tool in many programmers' toolbox is the great-circle (Haversine) formula for computing distance along the surface of a sphere. The problem with this technique is that the formula requires a lot of trigonometric operations, which are very CPU-intensive. Great-circle calculations tend to run slowly and make the machine's CPU utilization skyrocket.

Before applying the great-circle formula, pare down your records to a small subset of the total, and trim the resulting set to a precise circle. A square that contains the circle (precisely or imprecisely) is an easy way to do this. That way, the world outside the square never gets hit with all those costly trig functions.

Queues, point No. 3 above, tend to scale poorly for this reason.

The defaults that ship with MySQL are one-size-fits-none and badly outdated, but you don't need to configure everything. It's better to get the fundamentals right and change other settings only if needed. In most cases, you can get 95 percent of the server's peak performance by setting about 10 options correctly. The few situations where this doesn't apply are going to be edge cases unique to your circumstances.

In most cases, server "tuning" tools aren't recommended because they tend to give guidelines that don't make sense for specific cases. Some even have dangerous, inaccurate advice coded into them -- such as cache hit ratios and memory consumption formulas. These were never right, and they've gotten even less correct as time has passed.

MySQL performance tip No. 9: Learn the three rules of indexingIndexing is probably the most misunderstood topic in databases because there are so many ways to get confused about how indexes work and how the server uses them. It takes a lot of effort to really understand what's going on.

Indexes, when properly designed, serve three important purposes in a database server:

If you can design your indexes and queries to exploit these three opportunities, you can make your queries several orders of magnitude faster.

MySQL configuration tool, Query Advisor tool, and Percona Monitoring Plugins. The configuration tool can help you generate a baseline my.cnf file for a new server that's superior to the sample files that ship with the server. The Query Advisor analyzes your SQL to help detect potentially bad patterns such as pagination queries (No. 7). Percona Monitoring is a set of monitoring and graphing plugins to help you save statistics eagerly and alert reluctantly (No. 8). All three are freely available.

Related articles

This story, "10 essential performance tips for MySQL," was originally published at InfoWorld.com. Follow the latest developments in data management at InfoWorld.com. For the latest developments in business technology news, follow InfoWorld.com on Twitter.

Read more about data management in InfoWorld's Data Management Channel.

This story, "10 essential performance tips for MySQL" was originally published by InfoWorld.

5 collaboration tools that enhance Microsoft Office
  
Shop Tech Products at Amazon