I've strange performance difference between a MariaDB and a MySQL servers

I've 2 servers :

1° On the first one I'm running the standard MariaDB package (10.1.26-0+deb9u1) distributed with Debian 9.1 2° On the seconde one I'm running the standard MySQL package (mysql-community-server-5.6.26-2.el7.x86_64) distributed with CentOS 7.1.1503

Both have exactly the same hardware and the same workload (actually nothing). On both, I've imported a 1,5 GB database from third server (by a dump, tables are using InnoDB).

On basic queries, the performance are identical. Unfortunately, on more complex queries (with several jointures using correctly indexed tables), I have a huge difference.

For instance on a query : MariaDB 1,293 seconds <> 0.389 seconds MySQL

On MariaDB

Query_time: 1.293323 Lock_time: 0.001307 Rows_sent: 1 Rows_examined: 1063076

I've tried with default settings on both server ... MySQL always got better results.

I've also tried to tune some cache settings on MariaDB ... but MySQL with default settings remains better

On this query, I've seen the used indexes are different

MySQL index type : "index_merge" <> MariaDB index type "range" and only ONE index is used on MariaDB

I'm sure it's just a question of a missing setting ... but I don't know where to search.

I'm looking for tips or ideas to investigate. Could someone help me ?

Best Regards



I've finally discovered my issue was linked to a difference in optimization method. This is described in https://mariadb.com/kb/en/library/fair-choice-between-range-and-index_merge-optimizations/

On problematic queries, 2 indexes could be used but only one was chosen by the optimization engine

I've first improved the performance by removing the used index (to force the usage of the other index). I've then created a new index with the combinaison of the needed fields

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.