GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Queries performing worse in 5.6 with optimizations

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

  • Queries performing worse in 5.6 with optimizations

    We have a set of Percona Server slaves performing read-only queries, one of which we've upgraded to Percona Server 5.6 to test production usage against our existing 5.5 slaves. All the slaves receive production traffic equally via a load balancer, so the 5.6 slave is receiving the same type of queries as the other slaves.

    I've noticed that with 5.6, certain queries perform much more poorly due to the use of index condition pushdown and take a couple of orders of magnitude longer to complete. Here are some example EXPLAIN statements:

    Percona Server 5.5
    Code:
    1  SIMPLE  inbox          ref     PRIMARY,idx_participants,reverse_read_status  idx_participants  4  const        387  Using index; Using temporary
    1  SIMPLE  inbox_message  eq_ref  PRIMARY,imt                                   PRIMARY           4  inbox.im_id  1    Using where
    Percona Server 5.6
    Code:
    1  SIMPLE  inbox_message  range  PRIMARY,imt                                   imt      6  NULL                       40  Using index condition; Using where; Using temporary
    1  SIMPLE  inbox          ref    PRIMARY,idx_participants,reverse_read_status  PRIMARY  8  const,inbox_message.im_id  1   Using index; Distinct
    On 5.5, the query takes ~10ms to execute. On 5.6, it takes in the order of 30-60 seconds to complete using the altered query plan. The only way so far that I've found to solve this is to add FORCE INDEX (PRIMARY) to the query which makes the query plan on 5.6 match the one on 5.5 (and 5.5 stay the same), and then the query will complete in a similar amount of time. I've also tried disabling optimizations completely in my.cnf and restarting the server, but I still get "Using index condition" in the EXPLAIN output in this instance.

    I've also noticed that there are some queries that aren't using the optimizer but that still seem to suffer from a bad query plan. I'm not sure if this is to do with the changes in the way that statistics are calculated for a table:

    Percona Server 5.5 (query time ~300ms)
    Code:
    1  SIMPLE  im     range   PRIMARY,idx_sentdate,imt,im_recipient         idx_sentdate  4  NULL               102856  Using where; Using filesort
    1  SIMPLE  mr     eq_ref  PRIMARY                                       PRIMARY       4  im.im_recipient    1       Using where
    1  SIMPLE  inbox  ref     PRIMARY,idx_participants,reverse_read_status  PRIMARY       8  mr.me_id,im.im_id  1       Using where
    Percona Server 5.6 (query time ~60-120s)
    Code:
    1  SIMPLE  im     range   PRIMARY,idx_sentdate,imt,im_recipient         PRIMARY  4  NULL                      174862225  Using where
    1  SIMPLE  mr     eq_ref  PRIMARY                                       PRIMARY  4  im.im_recipient           1          Using where
    1  SIMPLE  inbox  ref     PRIMARY,idx_participants,reverse_read_status  PRIMARY  8  im.im_recipient,im.im_id  1          Using where
    Again, the only way to solve this has been to use FORCE INDEX (idx_sentdate) on all queries, which isn't a particularly long-term solution in case the tables and data change in such a way that forcing those particular indexes no longer constitutes a good query plan.

    Are there any tips for MySQL 5.6 that allow you to work around situations like that, such as changing server variables that calculate statistics better?

  • #2
    On further investigation, it appears that the query planner is just picking a different index in 5.6 that's resulting in a much worse performing query. I've managed to disable the optimizations completely, and now get the following EXPLAIN for the first query in 5.6:

    Code:
    1  SIMPLE  inbox_message  range  PRIMARY,imt                                   imt      6  NULL                           40  Using where; Using temporary
    1  SIMPLE  inbox          ref    PRIMARY,idx_participants,reverse_read_status  PRIMARY  8  const,wld.inbox_message.im_id  1   Using index; Distinct
    It seems to simple be down to the selection of imt in place of PRIMARY as the key for inbox_message that's causing the problem. I've tried using ANALYZE TABLE to recreate the index statistics for all the tables concerned, to no avail. Seems like 5.6 just doesn't like generating query plans for the tables we have, which means we're probably going to have to stick with 5.5 for the time being.

    Comment


    • #3
      ...i'm seeing similar issues except I hit table scans. When I look at the cardinailty of the index in information_schema.statistics, it's just warped.

      Anyway - did you ever solve the issue or did u stay on 5.5?
      thx

      Comment


      • #4
        Never managed to solve it; I could never find a combination of configuration values that would cause the statistics for the indexes not to be crazy and pick a bad query plan. Ended up using index hinting (FORCE INDEX) for every query that was affected.

        Comment

        Working...
        X