1

We just migrated our web site database from a server running MySQL 5.5.11 to one running MariaDB 5.5.41. Everything is working mostly fine, except there's at least one query from our storefront that's generating pretty terrible execution plans on the new server, causing scans of a table that's around 6 GB.

It's a query with two InnoDB tables and a relatively simple join between them, on two columns that are indexed (PK in catalog_category_flat_store_1, but not a covering index on core_url_rewrite):

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.product_id IS NULL
        AND url_rewrite.store_id='1'
        AND url_rewrite.id_path LIKE 'category/%'
WHERE (main_table.is_active = '1') AND (main_table.include_in_menu = '1') AND (main_table.path like '1/2/5/204/225/%') AND (`level` <= 5) ORDER BY `main_table`.`position` ASC

catalog_category_flat_store_1 has 627 rows, and core_url_rewrite has 8,687,266 rows (kill me).

The EXPLAIN shows me that catalog_category_flat_store_1 is "Using index condition; Using where; Using filesort", and core_url_rewrite (the huge table) is "Range checked for each record (index map: 0xF4)", despite allegedly using the index on category_id as its key.

On the old server, catalog_category_flat_store_1 is "Using where; Using filesort", and core_url_rewrite doesn't have anything listed for "Extra", and appears to be using the correct index.

I noticed MariaDB has a lot of additional options for optimizer_switch, and I'm wondering if any of these options are causing the different optimizer behavior, so I figure it's worth testing. The problem is that I don't know what the values should be to make MariaDB act like MySQL.

Here's the old server, where the query runs just fine:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on

And here's the new server, where the exact same query can end up running for ages:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

P.S. Greetings from Magento core_url_rewrite hell.

1

Well, this is a bit of voodoo/shotgun debugging, but I've got it functioning okay for the time being. I set the extended_keys option to on, and also created a covering index on the 8-million-row table in question. Now I'm getting an execution plan that's even better than the acceptable plan the old server was coming up with (which was only using Magento's default indexes).

Still, it all seems a bit hand-wavey, so I'll leave this open for now in case anybody has a better recommendation or explanation.

1

well, this post is very old, but I just went through almost the same after migrating a database from MySQL 5.6 to MariaDB 10.2. On the old server, a query on a table (which was not optimized for indexes) took no longer than 1 second. The same query on the new server with MariaDB takes 1 minute 50 seconds! In the server with MariaDB I added the missing indexes and the problem was solved. the consutla step to delay less than 1 second. But the strange thing was that MySQL with the same lack of indexes did not generate a problem and was able to perform the query much faster than MariaDB.

Well, after investigating a bit, I modified the variable optimizer_switch so that the parameter optimize_join_buffer_size was "ON" (by default it is "off") and it is enough to do that now MariaDB could also quickly solve the query even though it does not have indexes (obviously with indices is still faster, but the lack of indexes is no longer such a serious problem)

0

You should set optimizer_switch in MariaDB back to the old MySQL 5.5.11 value in my.cnf

[mysqld]
optimizer-switch = index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on

You could login to MariaDB abd set is as well

set global optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on';
  • But the docs suggest that it would only change the options specified, and all the MariaDB extras would be unaffected. That being the case, I don't know whether the extra options in MariaDB are all configured with default values that make it behave like MySQL, but I doubt that. – db2 May 4 '15 at 15:41

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.