Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22054

Very bad performance MariaDB vs MySQL

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.4.12
    • Fix Version/s: 10.4, 10.5
    • Component/s: Optimizer, Query Cache
    • Labels:
      None
    • Environment:
      Alpine Linux 3.11

      Description

      Hello,
      after upgrading from MySQL 5.7.12 to MariaDB 10.4.12 we see a nasty drop in performances which render our software unusable. I experimented with MySQL 5.7.29 and the result is the same.
      I tracked down the issue to a x6 drop in speed for some queries in MariaDB :

      SELECT COUNT(*) FROM `products_views` INNER JOIN `users` ON `users`.`id` = `products_views`.`user_id` WHERE (products_views.created_at between '2020-03-09 00:00:00.000000' and '2020-03-20');
      +----------+
      | COUNT(*) |
      +----------+
      |   185202 |
      +----------+
      1 row in set (11.26 sec)
      

      while with MySQL 5.7.29 I get :

      SELECT COUNT(*) FROM `products_views` INNER JOIN `users` ON `users`.`id` = `products_views`.`user_id` WHERE (products_views.created_at between '2020-03-09 00:00:00.000000' and '2020-03-20');
      +----------+
      | COUNT(*) |
      +----------+
      |   165805 |
      +----------+
      1 row in set (7.96 sec)
      

      The cache helps a bit with MariaDB if I repeat the same query (8.92 sec), while in MySQL the time is constant, always 2.05 sec.

      The EXPLAIN shows both software have a very different way of running this very same query :

      MariaDB

      +------+-------------+----------------+-------+---------------------------------+---------------------------------+---------+--------------------------------+--------+-------------+
      | id   | select_type | table          | type  | possible_keys                   | key                             | key_len | ref                            | rows   | Extra       |
      +------+-------------+----------------+-------+---------------------------------+---------------------------------+---------+--------------------------------+--------+-------------+
      |    1 | SIMPLE      | users          | index | PRIMARY                         | index_users_on_followers_count  | 4       | NULL                           | 226050 | Using index |
      |    1 | SIMPLE      | products_views | ref   | index_products_views_on_user_id | index_products_views_on_user_id | 5       | pf_analytics_int_prod.users.id | 15     | Using where |
      +------+-------------+----------------+-------+---------------------------------+---------------------------------+---------+--------------------------------+--------+-------------+
      

      MySQL

      +----+-------------+----------------+------------+--------+---------------------------------+---------+---------+-----------------------------------+---------+----------+-------------+
      | id | select_type | table          | partitions | type   | possible_keys                   | key     | key_len | ref                               | rows    | filtered | Extra       |
      +----+-------------+----------------+------------+--------+---------------------------------+---------+---------+-----------------------------------+---------+----------+-------------+
      |  1 | SIMPLE      | products_views | NULL       | ALL    | index_products_views_on_user_id | NULL    | NULL    | NULL                              | 6159146 |    11.11 | Using where |
      |  1 | SIMPLE      | users          | NULL       | eq_ref | PRIMARY                         | PRIMARY | 4       | pf_app_pub.products_views.user_id |       1 |   100.00 | Using index |
      +----+-------------+----------------+------------+--------+---------------------------------+---------+---------+-----------------------------------+---------+----------+-------------+
      

      I experimented a bit with some optimizations but to not avail. MariaDB optimizer is not up to par with MySQL but it's mostly the cache that is way better in MySQL.
      Profiling shows all the time is spent in "Sending data" phase, both for MariaDB and MySQL.

        Attachments

          Activity

            People

            Assignee:
            axel Axel Schwenke
            Reporter:
            PascalCL Pascal ROBINET
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.