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

Very bad performance MariaDB vs MySQL

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.12
    • 10.4, 10.5
    • Optimizer, Query Cache
    • None
    • 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 cached queries in MariaDB.
      Here are the initial queries, with no caching involved :

      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)
      

      Afterward the cache is primed and things gets ugly for MariaBD when I repeat the same query :

      • MariaDB (8.92 sec with some variations)
      • 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

            psergei Sergei Petrunia
            PascalCL Pascal ROBINET
            Votes:
            0 Vote for this issue
            Watchers:
            9 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.