Is InnoDB sorting really THAT slow?

Question

I had all my tables in myISAM but the table level locking was starting to kill me when I had long running update jobs. I converted my primary tables over to InnoDB and now many of my queries are taking over 1 minute to complete where they were nearly instantaneous on myISAM. They are usually stuck in the Sorting result step. Did I do something wrong?

For example :

SELECT * FROM `metaward_achiever` 
 INNER JOIN `metaward_alias` ON (`metaward_achiever`.`alias_id` = `metaward_alias`.`id`) 
 WHERE `metaward_achiever`.`award_id` = 1507  
 ORDER BY `metaward_achiever`.`modified` DESC 
 LIMIT 100  

Takes about 90 seconds now. Here is the describe :

+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+
| id | select_type | table             | type   | possible_keys                                         | key                        | key_len | ref                             | rows  | Extra                       |
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+
|  1 | SIMPLE      | metaward_achiever | ref    | metaward_achiever_award_id,metaward_achiever_alias_id | metaward_achiever_award_id | 4       | const                           | 66424 | Using where; Using filesort | 
|  1 | SIMPLE      | metaward_alias    | eq_ref | PRIMARY                                               | PRIMARY                    | 4       | paul.metaward_achiever.alias_id |     1 |                             | 
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+

It seems that now TONS of my queries get stuck in the "Sorting result" step :

mysql> show processlist;
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id     | User | Host      | db   | Command | Time | State          | Info                                                                                                 |
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 460568 | paul | localhost | paul | Query   |    0 | NULL           | show processlist                                                                                     | 
| 460638 | paul | localhost | paul | Query   |    0 | Sorting result | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met | 
| 460710 | paul | localhost | paul | Query   |   79 | Sending data   | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met | 
| 460722 | paul | localhost | paul | Query   |   49 | Updating       | UPDATE `metaward_alias` SET `modified` = '2009-09-15 12:43:50', `created` = '2009-08-24 11:55:24', ` | 
| 460732 | paul | localhost | paul | Query   |   25 | Sorting result | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met | 
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

Any why is that simple update stuck for 49 seconds?

If it helps, here are the schemas :

| metaward_alias | CREATE TABLE `metaward_alias` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` datetime NOT NULL,
  `created` datetime NOT NULL,
  `string_id` varchar(255) DEFAULT NULL,
  `shortname` varchar(100) NOT NULL,
  `remote_image` varchar(500) DEFAULT NULL,
  `image` varchar(100) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `type_id` int(11) NOT NULL,
  `md5` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `string_id` (`string_id`),
  KEY `metaward_alias_user_id` (`user_id`),
  KEY `metaward_alias_type_id` (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=858381 DEFAULT CHARSET=utf8 | 

| metaward_award | CREATE TABLE `metaward_award` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` datetime NOT NULL,
  `created` datetime NOT NULL,
  `string_id` varchar(20) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` longtext NOT NULL,
  `owner_points` int(11) NOT NULL,
  `url` varchar(500) NOT NULL,
  `remote_image` varchar(500) DEFAULT NULL,
  `image` varchar(100) NOT NULL,
  `parent_award_id` int(11) DEFAULT NULL,
  `slug` varchar(110) NOT NULL,
  `true_points` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `string_id` (`string_id`),
  KEY `metaward_award_owner_id` (`owner_id`),
  KEY `metaward_award_parent_award_id` (`parent_award_id`),
  KEY `metaward_award_slug` (`slug`),
  KEY `metaward_award_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=122176 DEFAULT CHARSET=utf8 | 

| metaward_achiever | CREATE TABLE `metaward_achiever` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` datetime NOT NULL,
  `created` datetime NOT NULL,
  `award_id` int(11) NOT NULL,
  `alias_id` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `metaward_achiever_award_id` (`award_id`),
  KEY `metaward_achiever_alias_id` (`alias_id`)
) ENGINE=InnoDB AUTO_INCREMENT=77175366 DEFAULT CHARSET=utf8 | 

And these in my my.cnf

innodb_file_per_table
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 16M
innodb_flush_method=O_DIRECT

Quilt

Answer 1

That is a large result set (66,424 rows) that MySQL must manually sort. Try adding an index to metaward_achiever.modified.

There is a limitation with MySQL 4.x that only allows MySQL to use one index per table. Since it is using the index on metaward_achiever.award_id column for the WHERE selection, it cannot also use the index on metaward_achiever.modified for the sort. I hope you're using MySQL 5.x, which may have improved this.

You can see this by doing explain on this simplified query:

SELECT * FROM `metaward_achiever` 
 WHERE `metaward_achiever`.`award_id` = 1507  
 ORDER BY `metaward_achiever`.`modified` DESC 
 LIMIT 100

If you can get this using the indexes for both the WHERE selection and sorting, then you're set.

You could also create a compound index with both metaward_achiever.award_id and metaward_achiever. If MySQL doesn't use it, then you can hint at it or remove the one on just award_id.

Alternatively, if you can get rid of metaward_achiever.id and make metaward_achiever.award_id your primary key and add a key on metaward_achiever.modified, or better yet make metaward_achiever.award_id combined with metaward.modified your primary key, then you'll be really good.

You can try to optimize the file sorting by modifying settings. Unfortunately, I'm not experienced with this, as our DBA handles the configuration, but you might want to check out this great blog: http://www.mysqlperformanceblog.com/

Here's an article about filesort in particular: http://s.petrunia.net/blog/?p=24

Gris

Is filesort faster with myISAM than with InnoDB? Shouldn't they be they same? – Quilt

@Paul - Correct. There may be some perceived differences in some cases because presorted data is faster to sort (and InnoDB physically maintains rows ordered by primary key), but internally it always works the same because it's not handled at the storage engine level. – Entrechat

Answer 2

As written in Should you move from MyISAM to Innodb ? (which is pretty recent):

Innodb Needs Tuning As a final note about MyISAM to Innodb migration I should mention about Innodb tuning. Innodb needs tuning. Really. MyISAM for many applications can work well with defaults. I’ve seen hundreds of GB databases ran with MyISAM with default settings and it worked reasonably. Innodb needs resources and it will not work well with defaults a lot. Tuning MyISAM from defaults rarely gives more than 2-3 times gain while it can be as much as 10-50 times for Innodb tables in particular for write intensive workloads. Check here for details.

So, about MySQL Innodb Settings, the author wrote in Innodb Performance Optimization Basics:

The most important ones are:

innodb_buffer_pool_size 70-80% of memory is a safe bet. I set it to 12G on 16GB box.

UPDATE: If you’re looking for more details, check out detailed guide on tuning innodb buffer pool

innodb_log_file_size – This depends on your recovery speed needs but 256M seems to be a good balance between reasonable recovery time and good performance

innodb_log_buffer_size=4M 4M is good for most cases unless you’re piping large blobs to Innodb in this case increase it a bit.

innodb_flush_log_at_trx_commit=2 If you’re not concern about ACID and can loose transactions for last second or two in case of full OS crash than set this value. It can dramatic effect especially on a lot of short write transactions.

innodb_thread_concurrency=8 Even with current Innodb Scalability Fixes having limited concurrency helps. The actual number may be higher or lower depending on your application and default which is 8 is decent start

innodb_flush_method=O_DIRECT Avoid double buffering and reduce swap pressure, in most cases this setting improves performance. Though be careful if you do not have battery backed up RAID cache as when write IO may suffer.

innodb_file_per_table – If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace growth which you can’t reclaim. This option was added in MySQL 4.1 and now stable enough to use.

Also check if your application can run in READ-COMMITED isolation mode – if it does – set it to be default as transaction-isolation=READ-COMMITTED. This option has some performance benefits, especially in locking in 5.0 and even more to come with MySQL 5.1 and row level replication.

Just for the record, the people behind mysqlperformanceblog.com ran a benchmark comparing Falcon, MyISAM and InnoDB. The benchmark was really supposed to be highlighting Falcon, except it was InnoDB that won the day, topping both Falcon and MyISAM in queries per second for almost every test: InnoDB vs MyISAM vs Falcon benchmarks – part 1.

Electrokinetics

Thank you for the post. I do have many of those options already set (see bottom of question), and the mysql instance is indeed taking up around 50% of my memory, but still performing horribly with InnoDB vs MyISAM. – Quilt

Answer 3

My guess is that you probably haven't configured your InnoDB settings beyond the defaults. You should do a quick google for setting up your InnoDB options.

The one that caused me the most noticeable performance issues out of the box was innodb_buffer_pool_size. This should be set to 50-80% of your machine's memory. By default it's often only a few MB. Crank it way up, and you should see a noticeable performance increase.

Also take a look at innodb_additional_mem_pool_size.

Start here, but also google around for "innodb performance tuning".

Donelladonelle

I have these set-up : innodb_file_per_table innodb_buffer_pool_size = 2048M innodb_additional_mem_pool_size = 16M innodb_flush_method=O_DIRECT – Quilt

Answer 4

MySQL's query optimizer is not good, from my memory. Try a subselect instead of a straight join.

SELECT * FROM (SELECT * FROM `metaward_achiever` 
               WHERE `metaward_achiever`.`award_id` = 1507) a
INNER JOIN `metaward_alias` ON (a.`alias_id` = `metaward_alias`.`id`) 
ORDER BY a.`modified` DESC 
LIMIT 100

Or something like that (untested syntax above).

Acetabulum

your query takes only 10 seconds, but myISAM was in the millis. Why? – Quilt

Answer 5

Sorting is something done by the database server, not the storage engine, in MySQL.

If in both cases, the engine was not able to provide the results in already-sorted form (it depends on the index used), then the server needs to sort them.

The only reason that MyISAM / InnoDB might be different is that the order the rows come back could affect how sorted the data are already - MyISAM could give the data back in "more sorted" order in some cases (and vice versa).

Still, sorting 60k rows is not going to take long as it's a very small data set. Are you sure you've got your sort buffer set big enough?

Using an on-disc filesort() instead of an in-memory one is much slower. The engine should however, not make any difference to this. filesort is not an engine function, but a MySQL core function. filesort does, in fact, suck in quite a lot of ways but it's not normally that slow.

Finstad

+1 I commented to this effect above, but missed your comment. Right on! – Entrechat

Answer 6

Try adding a key for fields: metaward_achiever.alias_id, metaward_achiever.award_id, and metaward_achiever.modified this will help alot. And do not use keys on varchar fields it will increase time for inserts and updates. Also it seems you have 77M records in achiever table, you may want to care about innodb optimizations. There are lots of good tutors around how to set memory limits for it.

Chaschase

I have 2 of the keys : KEY metaward_achiever_award_id (award_id), KEY metaward_achiever_alias_id (alias_id) . Should you really put keys on a datetime field? As for the varchar keys, I'm really not updating those tables too often, but is that why I'm stuck with that 49 second update? I select on the varchar fields a lot, so I thought a key would help. – Quilt

Try indexing the fields together: metaward_achiever_combined (award_id, alias_id, modified). Did wonders for my lookup time. – Oliana