10 essential performance tips for MySQL
– 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. 1: Profile your workloadThe best way to understand how your server spends its time is to profile the server’s workload. By profiling your workload, you can expose the most expensive queries for further tuning. Here, time is the most important metric because when you issue a query against the server, you care very little about anything except how quickly it completes.
The best way to profile your workload is with a tool such as MySQL Enterprise Monitor’s query analyzer or the pt-query-digest from the Percona Toolkit. These tools capture queries the server executes and return a table of tasks sorted by decreasing order of response time, instantly bubbling up the most expensive and time-consuming tasks to the top so that you can see where to focus your efforts.
Workload-profiling tools group similar queries together into one row, allowing you to see the queries that are slow, as well as the queries that are fast but executed many times.
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.