Technology Blog

How To Speed Up MySQL: An Introduction To Optimizing

1

Although there is nothing groundbreaking in this document consider it a bringing together of techniques for your first foray into optimization. We won’t discuss the more esoteric methods of squeezing the very last millisecond out of MySQL. There are a myriad of parameters to tune: here’s what you need to get right first.

That hackneyed phrase ‘one size does [not] fit all’ is worth bearing in mind too. Clearly the costs and benefits to your specific application must be carefully considered. Much of the advice within should be considered the lowest common denominator for any time sensitive MySQL based application whether it’s web based or not.


Low Hanging Fruit: Query Optimization

First turn on your slow query log and make sure it’s logging queries that don’t use indexes.

In your my.cnf you need the following lines:

 log_slow_queries  = /var/log/mysql/mysql-slow.log
 long_query_time  = 1
 log-queries-not-using-indexes

Unfortunately the stock MySQL distribution doesn’t support logging queries that take less than a second. However Percona have released a set of patches that allow you to do this. Certainly one second is fine to begin with; we’re after the lowest hanging fruit after all.

If you’re a web application and any given query is taking over a second to run you should do better, and chances are you can. If you’re caching the query with something like memcached perhaps it doesn’t matter so much. Let’s assume it does, after all some poor user is going to be stuck waiting for that query to complete, even if it’s only once in a while.

 cat /var/log/mysql/mysql-slow.log | more
 # Time: 090804 12:45:44
 # [email protected]: stephen[stephen] @ localhost []
 # Query_time: 11  Lock_time: 0  Rows_sent: 1  Rows_examined: 91687
 use stephen_drupal;
 SELECT uid,fid
 FROM profile_values
 WHERE uid NOT IN
   (SELECT uid FROM profile_values WHERE fid = 13 AND value = 'no')
   AND value REGEXP 'ert' AND uid != 4145
   AND uid NOT IN
     (SELECT uid FROM users WHERE status = 0)
 ORDER BY RAND()
 LIMIT 1;

This query was taken from a client’s slow query log.

Explain Yourself

Firstly does your query make use of indexes? Here’s how to find out how MySQL has decided to parse and execute your query:

 EXPLAIN EXTENDED
 SELECT uid,fid
 FROM profile_values
 WHERE uid NOT IN
   (SELECT uid FROM profile_values WHERE fid = 13 AND value = 'no')
   AND value REGEXP 'ert' AND uid != 4145
   AND uid NOT IN
   (SELECT uid FROM users WHERE status = 0)
 ORDER BY RAND()
 LIMIT 1
 G;

Use the parameter ‘extended’ to get any additional information MySQL has on the query. The ‘G’ will format the output into a more readable form.

*************************** 1. row ***************************
            id: 1
   select_type: PRIMARY
         table: profile_values
          type: range
 possible_keys: uid
           key: uid
       key_len: 5
           ref: NULL
          rows: 91137
         Extra: Using where; Using temporary; Using filesort
 *************************** 2. row ***************************
            id: 3
   select_type: DEPENDENT SUBQUERY
         table: users
          type: unique_subquery
 possible_keys: PRIMARY
           key: PRIMARY
       key_len: 4
           ref: func
          rows: 1
         Extra: Using where; Full scan on NULL key
 *************************** 3. row ***************************
            id: 2
   select_type: DEPENDENT SUBQUERY
         table: profile_values
          type: index_subquery
 possible_keys: uid,fid
           key: uid
       key_len: 5
           ref: func
          rows: 6
         Extra: Using where; Full scan on NULL key
 3 rows in set, 1 warning (0.00 sec)

Indexes vs. Table Scanning

Something to look out for is a high number in the rows column. What should be considered high depends on the size of your database and to some extent the nature of the query. If you’re not using an index for whatever reason MySQL is scanning through each row and stress is being placed on your disks and I/O subsystem. It’s true that sometimes it’s more efficient for MySQL to scan a table rather than use an index, especially if the table is small, however you should be wary.

Memory

If your database fits neatly into memory or resides on a solid-state drive then the difference between using an index and a table scan may be less noticeable but it should still be fixed. Having plentiful amounts of memory is always a good thing but it can serve to mask serious underlying problems.

Dependent Subqueries

Performing a dependent subquery within an [NOT] IN clause is prone to trouble with MySQL. Generally speaking it’s better if you steer clear of them, or at least be very wary of using them.

The query above can be re-written without using any subqueries making vast time savings. Don’t be afraid to split your queries into two or more parts either. Especially if you’re using MyISAM and are worrying about lock contention you’ll find that two or three very fast queries are preferable to one long one.

Splitting Queries: Transactional Safety And ACID Compliance

Splitting your queries into parts has data consistency ramifications. On MyISAM this may have consequences you need to consider since transactions aren’t available. With InnoDB, transactions, and row-level locking, this is less of an issue. More on this later however.

Making Your Application Do The Work

My philosophy has always been to ask the database to do as little as is reasonably possible and move processing into the application. The less work you give it the less resource it needs.

The fact of the matter is that it’s almost always harder to scale your database server/system than to pop in another web server. That’s if you’re web serving of course, but I imagine it might apply to any application. Web servers are relatively independent: they don’t rely on other web servers for their function in the way a database running across multiple servers does.

Neither do they get out of sync and have to catch up before they become available again. Or need battery-backed caches to remain consistent. Or highly skilled and expensive DBAs. They’re a walk in the park compared to any traditional RDBMS frankly. And they can make much better use of all those CPUs than InnoDB or a lock-contended MyISAM can.

Okay, so it’s true, this is a generalisation. It of course depends on your application. Perl, PHP and Python are just as competent at doing somersaults with your data as MySQL is, perhaps more so. Besides which you put yourself in control of potential bottlenecks and that’s always worth something. You’d rather tinker with your code base than MySQL’s surely?

That Query: Optimized

The initial query has been broken down into three. The first two queries are run and their results are fed into the third. Importantly we’ve changed REGEXP to a LIKE and in the second stage you’ll see we remove the ORDER BY RAND() LIMIT 1 construct.

The SQL_NO_CACHE ensures the queries are run without the aid of the cache that will confuse our profiling. We acknowledge that the OS disk cache has been warmed up however.

That original query:

 SELECT SQL_NO_CACHE uid,fid
 FROM profile_values
 WHERE uid NOT IN
   (SELECT uid FROM profile_values WHERE fid = 13 AND value = 'no')
   AND value REGEXP 'ert' AND uid != 4145
   AND uid NOT IN
   (SELECT uid FROM users WHERE status = 0)
 ORDER BY RAND()
 LIMIT 1;
 1 row in set (6.01 sec)

The optimized query (Stage 1):

 x =
   SELECT SQL_NO_CACHE uid
   FROM profile_values
   WHERE fid = 13 AND value = 'no';
   444 rows in set (0.00 sec)
 y =
   SELECT SQL_NO_CACHE uid
   FROM users WHERE status = 0;
   464 rows in set (0.00 sec)
   SELECT SQL_NO_CACHE uid,fid
   FROM profile_values
   WHERE uid NOT IN (4145,{x},{y})
   AND value LIKE '%ert%'
   ORDER BY RAND()
   LIMIT 1;
   1 row in set (0.52 sec)

We can do better however. The construct “ORDER BY RAND() LIMIT 1” is less than optimal. There are a number of ways around this but I’m happy to break it into two queries. They will run in O(n) time rather than O(1) — that is to say the time taken is dependent on the size of the table — but in this case I happen to know the table size is always going to be very modest.

The optimized query (Stage 2):

 rows_in_set = 
   SELECT SQL_NO_CACHE COUNT(uid)
   FROM profile_values
   WHERE uid NOT IN (4145,{x},{y})
   AND value LIKE '%ert%'
   1 row in set (0.13 sec)
   random_number = a random number between 1 and rows_in_set
   SELECT SQL_NO_CACHE uid,fid
   FROM profile_values
   WHERE uid NOT IN (4145,{x},{y})
   AND value LIKE '%ert%'
   LIMIT {random_number}, 1
   1 row in set (0.13 sec)

The query went from 6.01 seconds to 0.52 seconds to finally a set of queries that took ~0.26 seconds. Admittedly there is some extra overhead with the additional bandwidth generated and processing the application must do to produce the third query from the first two but, in this case at least, it’s negligible.

It might seem counterintuitive but decreasing the number of queries you send MySQL doesn’t necessarily equate to a faster application. Once you’ve bought into the idea it’s easier to scale web servers — and not MySQL — you might consider letting your application handle more of the work.

Even if we hadn’t managed to decrease the overall computation time and had simply moved some of it from MySQL to your application it would still be a win. Your web server is much better at concurrency than MySQL is.

Lock Contention: To MyISAM or InnoDB

Most MySQL databases out there run on MyISAM. I don’t have the figures and I wouldn’t know how to go about acquiring them but since it’s the default MySQL table type I’d wager that it is the case.

MyISAM is fine at many things and for many situations. You don’t have to worry about queries that COUNT(*) for example. It’s a very useful query to perform but on InnoDB it isn’t fast: you’ll need to find an alternative. MyISAM is more efficient in terms of disk space, in part due to it making physically smaller indexes which gives you all the more chance of having them fit into memory. On databases where inserts and updates rarely feature it’s faster. But you better make sure that’s the case.

 mysql> show status like 'Table_locks_waited';
 +--------------------+-------+
 | Variable_name      | Value |
 +--------------------+-------+
 | Table_locks_waited | 23412 | 
 +--------------------+-------+

A large or growing Table_locks_waited value is the sign of a serious concurrency bottleneck.

Any long running select will block an insert or update from happening. That will in turn prevent any further selects from occurring until after the queued insert or update is complete. Your computer — with all its wonderful parallelism — will suddenly begin working in serial, at least with respect to MySQL. It doesn’t matter how many CPUs you throw at it, only one will be used. A performance nightmare. MyISAM is for the most part fantastic and probably powers much of the web but it — like everything else — has costs and benefits that must be weighed.

Replication and Sharding

If you’re not suffering from lock contention, your queries are using the appropriate indexes (which you’ve made if they didn’t exist) and you’ve optimised your queries then perhaps you need to consider replicating or sharding your data.

Replication

Replication is an excellent way to distribute your database to more than one location. It’s used by some as a way to scale the number of reads that can be performed. However the number of writes the entire system can handle is limited to that of the weakest server. Replication is a very wasteful way to scale your application as exactly the same data appears on each server: hardly an efficient use of resources. However it’s very simple to implement and thus remains a favoured method of scaling: in the first instance. Beyond that you’ll have to think about sharding.

Sharding, Splintering or Partitioning Your Data

Sharding your data is a superior way to scale your application. It’s efficient in the sense that the same data doesn’t appear in more than one location. Rather you split your data into ‘shards’ which are placed on different disks or servers with individual resources dedicated to each shard.

In terms of implementation it is more difficult than replication, there’s no question of that. It very much depends on your data and whether there is a way to divide it methodically. Ideally it’s something that you would build into your application during its initial design. If you wish to scale your writes you must shard: replication will only take you so far.