MySQL performance for version 5.7 vs. 5.6

Question

I have noticed a particular performance issue that I am unsure on how to deal with.

I am in the process of migrating a web application from one server to another with very similar specifications. The new server typically outperforms the old server to be clear.

The old server is running MySQL 5.6.35
The new server is running MySQL 5.7.17

Both the new and old server have virtually identical MySQL configurations. Both the new and old server are running the exact same database perfectly duplicated.

The web application in question is Magento 1.9.3.2.

In Magento, the following function Mage_Catalog_Model_Category::getChildrenCategories() is intended to list all the immediate children categories given a certain category.

In my case, this function bubbles down eventually to this query:

SELECT    `main_table`.`entity_id`
        , main_table.`name`
        , main_table.`path`
        , `main_table`.`is_active`
        , `main_table`.`is_anchor`
        , `url_rewrite`.`request_path`

FROM `catalog_category_flat_store_1` AS `main_table`

LEFT JOIN `core_url_rewrite` AS `url_rewrite`
ON url_rewrite.category_id=main_table.entity_id
AND url_rewrite.is_system=1
AND url_rewrite.store_id = 1
AND url_rewrite.id_path LIKE 'category/%'

WHERE (main_table.include_in_menu = '1')
AND (main_table.is_active = '1')
AND (main_table.path LIKE '1/494/%')
AND (`level` <= 2)
ORDER BY `main_table`.`position` ASC;

While the structure for this query is the same for any Magento installation, there will obviously be slight discrepancies on values between Magento Installation to Magento Installation and what category the function is looking at.

My catalog_category_flat_store_1 table has 214 rows.
My url_rewrite table has 1,734,316 rows.

This query, when executed on its own directly into MySQL performs very differently between MySQL versions.

I am using SQLyog to profile this query.

In MySQL 5.6, the above query performs in 0.04 seconds. The profile for this query looks like this: https://codepen.io/Petce/full/JNKEpy/

In MySQL 5.7, the above query performs in 1.952 seconds. The profile for this query looks like this: https://codepen.io/Petce/full/gWMgKZ/

As you can see, the same query on almost the exact same setup is virtually 2 seconds slower, and I am unsure as to why.

For some reason, MySQL 5.7 does not want to use the table index to help produce the result set.

Anyone out there with more experience/knowledge can explain what is going on here and how to go about fixing it?

I believe the issue has something to do with the way that MYSQL 5.7 optimizer works. For some reason, it appears to think that a full table scan is the way to go. I can drastically improve the query performance by setting max_seeks_for_key very low (like 100) or dropping the range_optimizer_max_mem_size really low to forcing it to throw a warning.

Doing either of these increases the query speed by almost 10x down to 0.2 sec, however, this is still magnitudes slower that MYSQL 5.6 which executes in 0.04 seconds, and I don't think either of these is a good idea as I'm not sure if there would be other implications.

It is also very difficult to modify the query as it is generated by the Magento framework and would require customisation of the Magento codebase which I'd like to avoid. I'm also not even sure if it is the only query that is effected.

I have included the minor versions for my MySQL installations. I am now attempting to update MySQL 5.7.17 to 5.7.18 (the latest build) to see if there is any update to the performance.

After upgrading to MySQL 5.7.18 I saw no improvement. In order to bring the system back to a stable high performing state, we decided to downgrade back to MySQL 5.6.30. After doing the downgrade we saw an instant improvement.

The above query executed in MySQL 5.6.30 on the NEW server executed in 0.036 seconds.

Rousseau

Which specific versions? 5.7.12: "For queries with many OR conditions, the optimizer now is more memory-efficient and less likely to exceed the memory limit imposed by the range_optimizer_max_mem_size system variable. In addition, the default value for that variable has been raised from 1536000 to 8388608. (Bug #79450, Bug #22283790)" – Andrej

And 5.7.9: "For a query with many range conditions, the optimizer would estimate that too much memory would be required for a range scan and fall back to a less optimal plan, such as a full table scan. A new range_optimizer_max_mem_size system variable now controls the limit on memory consumption for the range optimizer. A value of 0 means “no limit.” If an execution ... (See the changelog) – Andrej

Condolences on using a 3rd party software that "gets in the way". The is a "query rewrite" feature in 5.7: mysql.rjweb.org/doc.php/queryrewrite – Andrej

In order to understand how the optimizer thinks different in 5.6 and 5.7, I think I would need to see optimizer trace for both versions. However, as Rick writes, it does not seem you have the optimal index since MySQL is not able to use indexed nested loops join in any of the versions. An index on (category_id, is_system, store_id, id_path), in that order, should speed up your query in both versions. – Catoptrics

Did you file a bug for this? was it fixed in 5.7.19 or later please? – Dees

Answer 1

Wow! This is the first time I have seen something useful from Profiling. Dynamically creating an index is a new Optimization feature from Oracle. But it looks like that was not the best plan for this case.

First, I will recommend that you file a bug at http://bugs.mysql.com -- they don't like to have regressions, especially this egregious. If possible, provide EXPLAIN FORMAT=JSON SELECT... and "Optimizer trace". (I do not accept tweaking obscure tunables as an acceptable answer, but thanks for discovering them.)

Back to helping you...

• If you don't need LEFT, don't use it. It returns NULLs when there are no matching rows in the 'right' table; will that happen in your case?
• Please provide SHOW CREATE TABLE. Meanwhile, I will guess that you don't have INDEX(include_in_menu, is_active, path). The first two can be in either order; path needs to be last.
• And INDEX(category_id, is_system, store_id, id_path) with id_path last.
• Your query seems to have a pattern that works well for turning into a subquery:

(Note: this even preserves the semantics of LEFT.)

SELECT  `main_table`.`entity_id` , main_table.`name` , main_table.`path` ,
        `main_table`.`is_active` , `main_table`.`is_anchor` ,
        ( SELECT  `request_path`
            FROM  url_rewrite
            WHERE  url_rewrite.category_id=main_table.entity_id
              AND  url_rewrite.is_system = 1
              AND  url_rewrite.store_id  = 1
              AND  url_rewrite.id_path LIKE 'category/%' 
        ) as request_path
    FROM  `catalog_category_flat_store_1` AS `main_table`
    WHERE  (main_table.include_in_menu = '1')
      AND  (main_table.is_active = '1')
      AND  (main_table.path like '1/494/%')
      AND  (`level` <= 2)
    ORDER BY  `main_table`.`position` ASC
    LIMIT  0, 1000 

(The suggested indexes apply here, too.)

Andrej

I am accepting this as the correct answer. I also agree with you on your sentiment that tweaking obscure tunables is not the way to proceed on this particular issue. Tweaking such values defeats the purpose of the new Optimizer Algorithm. – Rousseau

Hey everyone, I've been testing this same thing on RDS in 5.6 and 5.7. I don't fully understand the internals but definitely see the affects. Is this bug report the same thing? bugs.mysql.com/bug.php?id=80580 - RDS doesn't yet have 5.7.18 but I'm going to set up locally and see if it helps. – Ophiolatry

Answer 2

THIS is not a ANSWER only for comment for @Nigel Ren

Here you can see that LIKE also use index.

mysql> SELECT *
    -> FROM testdb
    -> WHERE
    -> vals LIKE 'text%';
+----+---------------------------------------+
| id | vals                                  |
+----+---------------------------------------+
|  3 | text for line number 3                |
|  1 | textline 1 we rqwe rq wer qwer q wer  |
|  2 | textline 2 asdf asd fas f asf  wer 3  |
+----+---------------------------------------+
3 rows in set (0,00 sec)

mysql> EXPLAIN
    -> SELECT *
    -> FROM testdb
    -> WHERE
    -> vals LIKE 'text%';
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | testdb | NULL       | range | vals          | vals | 515     | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0,01 sec)

mysql>

sample with LEFT()

mysql> SELECT *
    -> FROM testdb
    -> WHERE
    -> LEFT(vals,4) = 'text';
+----+---------------------------------------+
| id | vals                                  |
+----+---------------------------------------+
|  3 | text for line number 3                |
|  1 | textline 1 we rqwe rq wer qwer q wer  |
|  2 | textline 2 asdf asd fas f asf  wer 3  |
+----+---------------------------------------+
3 rows in set (0,01 sec)

mysql> EXPLAIN
    -> SELECT *
    -> FROM testdb
    -> WHERE
    -> LEFT(vals,4) = 'text';
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | testdb | NULL       | index | NULL          | vals | 515     | NULL |    5 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0,01 sec)

mysql>

Joannjoanna

Misleading example. Since the table has only 2 columns, * refers to only id, vals. The INDEX(vals) implicitly includes the PK, namely id. Hence the index is "covering", hence the misleading "Using index". That does not whether the WHERE used the index for filtering. Instead, "possible_keys" says "vals" to indicate that use of the index for filtering. "NULL" says it needed to do a full scan of something (the covering index, in this case). – Andrej

Bernd -- Suggest you add another column to your testdb sample; it will be included in *, but it will cause the LEFT case to lose "Using index". – Andrej