I just installed Percona 5.6 on my new CentOS 6.4 server. It's a fast machine 32 core xenon, 72GB ram, 8x SAS RAID 10 setup. So far so good

My old server is a bit less powerful, and was running MySQL 5.1 still. So this was quite an upgrade. But I'm having some issues with InnoDB, it is not using the indexes correctly on some tables it seems. Where on my old machine the same queries were running fine.

Both servers have the same database. I did a mysqldump on the old machine and imported it onto the new Percona 5.6 server. Indexes stayed the same. Both servers use the same my.cnf config settings.

Table items has indexes on: item_id, item_format, item_private and contains about 40 million rows. Table formats has index on: format_id and contains about 250 rows.

i.item_name, i.item_key, i.item_date, f.format_long
items i, formats f
i.item_format = f.format_id
i.item_private = 0 
i.item_id DESC LIMIT 8

On my old server this query takes about 0.0003 seconds. On the new server it takes over 100 seconds.

Query with EXPLAIN on OLD server.

| id | select_type | table |  type  | possible_keys |   key   | key_len |         ref          | rows |    Extra    |
|  1 | SIMPLE      | i     | index  | item_format   | PRIMARY |       4 | NULL                 |    8 | Using where |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY |       4 | dbname.i.item_format |    1 |             |

Query with EXPLAIN on NEW [problem] server.

| id | select_type | table | type | possible_keys |     key     | key_len |        ref         | rows |              Extra              |
|  1 | SIMPLE      | f     | ALL  | PRIMARY       | NULL        | NULL    | NULL               |  219 | Using temporary; Using filesort |
|  1 | SIMPLE      | i     | ref  | item_format   | item_format | 4       | dbname.f.format_id | 3026 | Using where                     |

You can see that it's using temporary and filesort. This seems to be the reason for the slowness.

Any idea how I could resolve this issue?

  • Side note.. try to always use ANSI JOIN syntax better to read that you mean INNER JOIN.. – Raymond Nijland Nov 13 '13 at 23:36
  • Looks like Percona 5.6 chooses to access the table on the wrong order Try if SELECT STRAIGHT_JOIN (rest off your query)... triggers an new plan (most likely it will remove the need for "Using temporary; Using filesort") .. note STRAIGHT_JOIN can have some issues in some special cases when there are no values or with an always false where condition.. use this only to test and post the results.. – Raymond Nijland Nov 13 '13 at 23:52
  • you should check if there are MYD and/or an MYI file(s) when you run that offending query.. and also an post an create table statement that would also be handy.. because Percona 5.6 Optimzer also chooses it can't use the PRIMARY key index on table f... it seems can you also run an PROFILE if that is possible with Percona 5.6?? – Raymond Nijland Nov 14 '13 at 0:07
  • We have gotten a little further, but we are very very confused. As you can see in my question, it seems indexes aren't used correctly. So we removed, and readded the indexes, but that changed nothing. Then we removed the index on item_format and item_private again, and ran the same query (so with out the correct indexes). To our surprise it was very fast. Just as fast as on our old server (with indexes). As far as we understand all columns in a WHERE and ORDER BY clause should always have indexes. Yet on this percona server on this table it seems the other way around. Any ideas why? – Mr.Boon Nov 14 '13 at 8:54
  • Please add SHOW CREATE TABLE <x> for both tables. I've noticed this problem a couple of times: facebook.com/notes/mysql-at-facebook/… - When there are a number of indexes, MySQL 5.6 may choose cardinality over index dives. – Morgan Tocker Nov 15 '13 at 20:48

First of all: This is not because of Percona, the differences you are getting are because of the upgrade to 5.6. Percona Server rarely modifies the SQL optimiser from upstream. The changes come from the new optimiser in MySQL 5.6, which Percona Server 5.6 uses and that was heavily optimised between the two version (usually, for the best).

The reason why the execution is slower is because with an index on (item_format, item_private), it thinks that the query will be faster by getting the results using that index, but in reality, that would cause the join to be done in the "wrong" order. The best index here is (item_private) -assuming it is selective enough, if not it would be the primary key-, as it can use item_private for filtering and the hidden PRIMARY KEY inside the secondary key for ordering, while it uses the PRIMARY KEY of format for the join. Please note that (item_filtering) or (item_filtering, item_private) are not good indexes in this case.

By looking at the optimiser trace and the handler status, the problem seems to come by the predicted number of rows: the old method, in 5.6, seems to predict a full table scan, while the actual number of rows read is -more or less- the number of rows in the LIMIT clause. This seems to be a regression in the query optimiser, and it should be reported if you confirm that it is not due to any special personal configuration. It is specially bad, as it prefers the creation of a temporary table for the join (potentially on disk, so it may be very slow in some cases) over a very light scan.

You have been already told several ways of avoiding this problem for the time being: not creating an index containing item_filtering, using STRAIGHT_JOIN or forcing the usage of item_filtering (or PRIMARY).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.