Something I’ve been meaning to do for a while is an audit of key Lyceum database queries to see if there are oportunities to fine-tune the database indexes. Part of the reason I hadn’t gotten around to this yet is because I was waiting for the opportunity to use a large real-life data set when anaylyzing the queries.
Such an opportunity recently presented itself. One of the biggest Lyceum installations is iBlog, “South Africa’s Blogging Community”. The creator/head-honcho, Mark, has been in touch with me for a while now, and has been expressing growing concerns about the performance of the site. Mark was nice enough to give me full access to his server, and boy was I happy to get my eager little hands on all that data. Below is a description of where I went, what I found, and — in the exciting conclusion — how iBlog, and Lyceum, improved.
Throughout this article I will use the MySQL EXPLAIN command. If you haven’t used it before, you may want to go take a quick look at the documenation to familiarize yourself with its purpose.
EXPLAIN provides a variety of information. For our purposes here, we will be focusing on its ‘rows’ output. This information is very useful and also very easy to understand. If you know what a database index is for — getting to data in a table without having to look through the entire table — then you can appreciate the ‘rows’ metric; it is an estimation of how many rows MySQL will have to examine in order to find the data.
Mark presented to me this query as an example of what was giving iBlog trouble. This is a query that is executed on every single viewing of any Lyceum blog, to retrieve the set of posts list on the blog’s front page. Mark said that it was taking around 6 seconds to execute! I will refer to this query as Trouble Query:
mysql> EXPLAIN EXTENDED SELECT DISTINCT * FROM posts INNER JOIN post2cat ON (post2cat.post_id = posts.ID) INNER JOIN categories ON (post2cat.category_id = categories.cat_ID) WHERE 1=1 AND post_date_gmt <='2006-12-13 09:14:59' AND (post_status="publish") AND post_status!="attachment" AND categories.blog='21' GROUP BY posts.ID ORDER BY post_date DESC LIMIT 0,10;
+----+-------------+------------+--------+-------------------+---------+---------+----------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+----------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | posts | ref | PRIMARY,page,main | page | 1 | const | 24350 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | post2cat | ref | post_id | post_id | 4 | iblogcoza_1.posts.ID | 1 | Using index |
| 1 | SIMPLE | categories | eq_ref | PRIMARY,blog | PRIMARY | 4 | iblogcoza_1.post2cat.category_id | 1 | Using where |
+----+-------------+------------+--------+-------------------+---------+---------+----------------------------------+-------+----------------------------------------------+
Wow, 24250 rows scanned. Ick. Becaue of the Lyceum schema, MySQL should be able to pare down from categories pretty quickly, so we know something is amis. In order to root out the problem, let’s pretend we are the database and pare down the data ourselves step-by-step in the same order that MySQL will when executing Trouble Query. Maybe the design flaw will become evident.
mysql> explain select categories.cat_ID from categories where blog = 21;
+----+-------------+------------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | categories | ref | blog | blog | 3 | const | 7 | Using where; Using index |
+----+-------------+------------+------+---------------+------+---------+-------+------+--------------------------+
7 rows. Looks great. In fact, the results of that query are (22,3019,3021,3022,3023,9171,18333), which means that this blog only has 7 categories, which means that our index could not be more optimal. w00t! Let’s check the next level, post2cat:
mysql> explain select post_id from post2cat where category_id in (22,3019,3021,3022,3023,9171,18333);
+----+-------------+----------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+-------+--------------------------+
| 1 | SIMPLE | post2cat | index | NULL | post_id | 8 | NULL | 43102 | Using where; Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+-------+--------------------------+
43102 rows! You know that ain’t right. And don’t let that “index” type fool you, it doesn’t mean that it is actually using an index, only that instead of scanning the main data store it is scanning the index (which is marginally faster due to the (usually) smaller size of the data on disk). ~40k, hmm that number seems familiar, where have I seen it before… wait a second…
mysql> select count(rel_id) from post2cat;
+---------------+
| count(rel_id) |
+---------------+
| 45779 |
+---------------+
It’s scanning almost the entire table! It’s as if the category column does not have an index at all. Wait a second…
CREATE TABLE $wpdb->post2cat (
rel_id int unsigned NOT NULL auto_increment,
post_id int unsigned NOT NULL default '0',
category_id int unsigned NOT NULL default '0',
PRIMARY KEY (rel_id),
KEY post_id (post_id,category_id)
) ENGINE = InnoDB;
(source)
It doesn’t!! Ah ha, we have found our bottleneck! But before we try to fix it, let’s see how the rest of Trouble Query fares (using the set of numbers returned from the previous query):
mysql> EXPLAIN SELECT DISTINCT * FROM posts where post_date_gmt <='2006-12-13 09:14:59' AND (post_status="publish") AND post_status!="attachment" AND ID in (8694, 8775, [SNIP] , 49917, 49963) GROUP BY posts.ID ORDER BY post_date DESC LIMIT 0,10;
+—-+————-+——-+——-+——————-+———+———+——+——+———————————————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+——————-+———+———+——+——+———————————————-+
| 1 | SIMPLE | posts | range | PRIMARY,page,main | PRIMARY | 4 | NULL | 385 | Using where; Using temporary; Using filesort |
+—-+————-+——-+——-+——————-+———+———+——+——+———————————————-+
385 rows. This could probably be tweaked down some more, but it is nothing to be ashamed of.
Not having an index on a column in a WHERE clause, however, is something to be ashamed of. So, back to our dismal post2cat performance. Let’s see what adding an index can do for us:
ALTER TABLE post2cat ADD UNIQUE INDEX category_id (category_id,post_id);
Okay, that went through without any complaints. Now let’s try Trouble Query again and see if our new index makes a difference:
mysql> EXPLAIN EXTENDED SELECT DISTINCT * FROM posts INNER JOIN post2cat ON (post2cat.post_id = posts.ID) INNER JOIN categories ON (post2cat.category_id = categories.cat_ID) WHERE 1=1 AND post_date_gmt <='2006-12-13 09:14:59' AND (post_status="publish") AND post_status!="attachment" AND categories.blog='21' GROUP BY posts.ID ORDER BY post_date DESC LIMIT 0,10;
+----+-------------+------------+--------+---------------------+-------------+---------+-------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------------+-------------+---------+-------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | categories | ref | PRIMARY,blog | blog | 3 | const | 7 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | post2cat | ref | category_id,post_id | category_id | 4 | iblogcoza_1.categories.cat_ID | 6 | Using index |
| 1 | SIMPLE | posts | eq_ref | PRIMARY,page,main | PRIMARY | 4 | iblogcoza_1.post2cat.post_id | 1 | Using where |
+----+-------------+------------+--------+---------------------+-------------+---------+-------------------------------+------+----------------------------------------------+
We went from 24352 rows scanned down to 14! Note that the number of rows scanned for Trouble Query is now fewer than for the no-join query on the posts table we tried just before. This an example of how EXPLAINing through each part of a query “in the same order that MySQL will” does not always give a completely accurate picture of what mysql is doing. But it certainly serves perfectly well for performance audits.
Like a father witnessing his child’s uncontrollable expresions of blissful abandon on Christmas morning after receiving a gift that is sure to bring many years of childhood joy and memories, my heart was warmed when Mark applied this new 1-1 cardinality B-Tree index to his database and reported back that his server load immediately went down by an order of magnitude, allowing iBlog to serve many more requests per second with existing hardware and paving the way for at-worst linear load-hardware scaling as his community grew.
So there you have it folks. With feedback and resources from a community member, a bunch of data, and taking the time to examine a few queries, we made dramatic performance improvements to Lyceum.
Share This (digg, reddit, de.licio.us, email, etc)