Announcement

Announcement Module
Collapse
No announcement yet.

Percona 5.6 InnoDB issue not using indexes correctly [urgent]

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Percona 5.6 InnoDB issue not using indexes correctly [urgent]

    Hey guys, 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.

    SELECT
    i.item_name, i.item_key, i.item_date, f.format_long
    FROM
    items i, formats f
    WHERE
    i.item_format = f.format_id
    AND
    i.item_private = 0
    ORDER BY
    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.
    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.
    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?





    Last edited by mrboon; 11-13-2013, 11:55 AM.

  • #2
    I suggest to try it with STRAIGHT_JOIN or with index hint (USE INDEX or FORCE INDEX) to check if it changes the query execution path first.
    From the docs:
    "STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order."

    Code:
    SELECT
    i.item_name, i.item_key, i.item_date, f.format_long
    FROM
    formats f STRAIGHT_JOIN items i
    ON  f.format_id = i.item_format
    WHERE
    i.item_private = 0
    ORDER BY i.item_id DESC LIMIT 8
    OR

    Code:
    SELECT
    i.item_name, i.item_key, i.item_date, f.format_long
    FROM
    items i, formats f USE INDEX(PRIMARY)
    WHERE
    i.item_format = f.format_id
    AND
    i.item_private = 0
    ORDER BY
    i.item_id DESC LIMIT 8
    As you mentioned the query EXPLAIN output changed between MySQL versions it needs to check for changelog in documentation to verify what exactly changed between the versions. Also did you checked the same query EXPLAIN result on version 5.5 ?

    Comment


    • #3
      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?

      Comment

      Working...
      X