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

Page 3 of 3

By contrast, there's a tendency to alert way too much. People often alert on things like the buffer hit ratio or the number of temporary tables created per second. The problem is that there is no good threshold for such a ratio. The right threshold is not only different from server to server, but from hour to hour as your workload changes.

As a result, alert sparingly and only on conditions that indicate a definite, actionable problem. A low buffer hit ratio isn't actionable, nor does it indicate a real issue, but a server that doesn't respond to a connection attempt is an actual problem that needs to be solved.

MySQL performance tip No. 9: Learn the three rules of indexing

Indexing 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:

  1. They let the server find groups of adjacent rows instead of single rows. Many people think the purpose of an index is to find individual rows, but finding single rows leads to random disk operations, which is slow. It's much better to find groups of rows, all or most of which are interesting, than to find rows one at a time.
  2. They let the server avoid sorting by reading the rows in a desired order. Sorting is costly. Reading rows in the desired order is much faster.
  3. They let the server satisfy entire queries from the index alone, avoiding the need to access the table at all. This is variously known as a covering index or an index-only query.

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

MySQL performance tip No. 10: Leverage the expertise of your peers

Don't try to go it alone. If you're puzzling over a problem and doing what seems logical and sensible to you, that's great. This will work about 19 times out of 20. The other time, you'll go down a rabbit hole that will be very costly and time-consuming, precisely because the solution you're trying seems to make a lot of sense.

Build a network of MySQL-related resources -- and this goes beyond toolsets and troubleshooting guides. There are some extremely knowledgeable people lurking on mailing lists, forums, Q&A websites, and so on. Conferences, trade shows, and local user group events provide valuable opportunities for gaining insights and building relationships with peers who can help you in a pinch.

For those looking for tools to complement these tips, you can check out my 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.

| 1 2 3 Page 3