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

Very slow query on partition table joined with other tables

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.6.7
    • 10.6
    • Partitioning
    • None
    • MySQL 5.7 3GHz FreeBSD 11 (amd64), 2 Core, 4G RAM, 2TB SATA
      MariaDB 10.6.7 2GHz arrch64 Ubuntu, 4 Core, 8G RAM, 2TB SATA SSD
      Same Schema and data

    Description

      I just migrated from mysql Ver 14.14 Distrib 5.7.17, for freebsd11 (amd64)
      to mysql Ver 15.1 Distrib 10.6.7-MariaDB, for debian-linux-gnu (Ubuntu aarch64).

      Both databases have exactly the same schema and data.

      Performance is comparable, except for the large partitioned log table.
      The issue occurs only when the log table is joined with another three small tables.

      MySQL 5.7 returns 4409 rows in set (0.14 sec)
      MariaDB 10.6.7 returns 4409 rows in set (8 min 49.504 sec)

      If I split the query into two queries, by removing the JOIN with the Partitioned table
      and then perform the JOIN, SUM and GROUP BY externally, in ruby code,
      I get similar performance from both databases.

      i.e. it is fast selecting the same time range from the partition table, without the JOIN
      MySQL 5.7, returned 13551 rows in set (0.05 sec)
      MariaDB 10.6.7 returned 13551 rows in set (0.097 sec)

      Size of data set in the Partition we select against

      mysql> select count(*) from log_summary PARTITION (p2022_07);
      +----------+
      | count(*) |
      +----------+
      |  6708854 |
      +----------+
      

      Selecting just one hour is very fast for both MySQL and MariaDB

        SELECT log_timestamp, hostname, 
                       bytes_in/(1024*1024.0) AS b_in, 
                       (bytes_in + bytes_out)/(1024*1024.0) AS total 
        FROM log_summary 
        WHERE log_timestamp >= '2022-07-19 15:46:36'
        AND log_timestamp <= '2022-07-19 16:46:36' ;
      

      The query on the other 3 tables, to facilitate the external join
      MySQL 5.7 returns 190 rows in 0.00s.
      MariaDB returns 190 rows in 0.002s.

        SELECT d.site_name, c.site_name
        FROM distribution AS d 
        JOIN customer_distribution USING (distribution_id)
        JOIN customer AS c USING (customer_id);
      

      The query with the JOIN, SUM and GROUP BY, is fast in MySQL
      and 3800 times slower in MariaDB

      mysql 5.7> EXPLAIN SELECT log_timestamp, d.site_name AS site, 
                                                       SUM(bytes_in)/(1024*1024.0) AS b_in,
                                                       SUM(bytes_in + bytes_out)/(1024*1024.0) AS total
       FROM distribution AS d 
       JOIN customer_distribution USING (distribution_id) 
       JOIN customer USING (customer_id), log_summary 
       WHERE log_timestamp >= '2022-07-19 15:46:36'
       AND log_timestamp <= '2022-07-19 16:46:36' 
       AND customer.site_name = log_summary.hostname 
       GROUP BY log_timestamp, d.site_name;
      +----+-------------+-----------------------+------------+--------+-------------------------+-----------+---------+--------------------------------------------+-------+----------+----------------------------------------------+
      | id | select_type | table                 | partitions | type   | possible_keys           | key       | key_len | ref                                        | rows  | filtered | Extra                                        |
      +----+-------------+-----------------------+------------+--------+-------------------------+-----------+---------+--------------------------------------------+-------+----------+----------------------------------------------+
      |  1 | SIMPLE      | log_summary           | p2022_07   | range  | PRIMARY,hostname        | PRIMARY   | 5       | NULL                                       | 26936 |   100.00 | Using where; Using temporary; Using filesort |
      |  1 | SIMPLE      | customer              | NULL       | ref    | PRIMARY,site_name       | site_name | 33      | wikk.log_summary.hostname                  |     1 |   100.00 | Using index                                  |
      |  1 | SIMPLE      | customer_distribution | NULL       | eq_ref | PRIMARY,distribution_id | PRIMARY   | 4       | wikk.customer.customer_id                  |     1 |   100.00 | Using where                                  |
      |  1 | SIMPLE      | d                     | NULL       | eq_ref | PRIMARY                 | PRIMARY   | 4       | wikk.customer_distribution.distribution_id |     1 |   100.00 | NULL                                         |
      +----+-------------+-----------------------+------------+--------+-------------------------+-----------+---------+--------------------------------------------+-------+----------+----------------------------------------------+
      

      MariaDB [wikk]>  EXPLAIN SELECT log_timestamp, d.site_name AS site, 
                                                       SUM(bytes_in)/(1024*1024.0) AS b_in,
                                                       SUM(bytes_in + bytes_out)/(1024*1024.0) AS total
       FROM distribution AS d 
       JOIN customer_distribution USING (distribution_id) 
       JOIN customer USING (customer_id), log_summary 
       WHERE log_timestamp >= '2022-07-19 15:46:36'
       AND log_timestamp <= '2022-07-19 16:46:36' 
       AND customer.site_name = log_summary.hostname 
       GROUP BY log_timestamp, d.site_name;
      +------+-------------+-----------------------+--------+-------------------------+-----------------+---------+----------------------------------------+------+---------------------------------+
      | id   | select_type | table                 | type   | possible_keys           | key             | key_len | ref                                    | rows | Extra                           |
      +------+-------------+-----------------------+--------+-------------------------+-----------------+---------+----------------------------------------+------+---------------------------------+
      |    1 | SIMPLE      | d                     | ALL    | PRIMARY                 | NULL            | NULL    | NULL                                   | 24   | Using temporary; Using filesort |
      |    1 | SIMPLE      | customer_distribution | ref    | PRIMARY,distribution_id | distribution_id | 5       | wikk.d.distribution_id                 | 6    | Using index                     |
      |    1 | SIMPLE      | customer              | eq_ref | PRIMARY,site_name       | PRIMARY         | 4       | wikk.customer_distribution.customer_id | 1    | Using where                     |
      |    1 | SIMPLE      | log_summary           | ref    | PRIMARY,hostname        | hostname        | 32      | wikk.customer.site_name                | 395  | Using where                     |
      +------+-------------+-----------------------+--------+-------------------------+-----------------+---------+----------------------------------------+------+---------------------------------+
      

      The tables

      CREATE TABLE `customer` (
        `customer_id` int(11) NOT NULL AUTO_INCREMENT,
        `name` char(64) DEFAULT NULL,
        `link` int(11) DEFAULT NULL,
        `connected` date DEFAULT NULL,
        `termination` date DEFAULT NULL,
        `site_name` char(32) DEFAULT NULL,
        `email` varchar(255) DEFAULT NULL,
        `telephone` char(16) DEFAULT NULL,
        `mobile` char(16) DEFAULT NULL,
        `billing_address` varchar(255) DEFAULT NULL,
        `billing_name` char(64) DEFAULT NULL,
        `active` tinyint(1) DEFAULT 1,
        `comment` varchar(255) DEFAULT NULL,
        `site_address` varchar(255) DEFAULT NULL,
        `latitude` double DEFAULT NULL,
        `longitude` double DEFAULT NULL,
        `height` double DEFAULT 3,
        `plan` int(11) DEFAULT 1,
        `net_node_interface_id` int(11) DEFAULT NULL,
        `cabled` tinyint(1) DEFAULT 0,
        PRIMARY KEY (`customer_id`),
        UNIQUE KEY `site_name` (`site_name`)
      ) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=latin
       
      CREATE TABLE `customer_distribution` (
        `customer_id` int(11) NOT NULL DEFAULT 0,
        `distribution_id` int(11) DEFAULT NULL,
        PRIMARY KEY (`customer_id`),
        KEY `distribution_id` (`distribution_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
       
      CREATE TABLE `distribution` (
        `distribution_id` int(11) NOT NULL AUTO_INCREMENT,
        `site_name` char(32) DEFAULT NULL,
        `chan` int(11) DEFAULT NULL,
        `active` tinyint(1) DEFAULT 1,
        `dns_subnet_id` int(11) DEFAULT NULL,
        `site_address` varchar(255) DEFAULT NULL,
        `latitude` double DEFAULT NULL,
        `longitude` double DEFAULT NULL,
        `comment` varchar(255) DEFAULT NULL,
        `height` double DEFAULT 0,
        `colour` char(16) DEFAULT NULL,
        `net_node_interface_id` int(11) DEFAULT NULL,
        PRIMARY KEY (`distribution_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1 |
       
      CREATE TABLE `log_summary` (
        `bytes_in` bigint(20) DEFAULT NULL,
        `bytes_out` bigint(20) DEFAULT NULL,
        `hostname` char(32) NOT NULL DEFAULT '',
        `log_timestamp` datetime NOT NULL,
        PRIMARY KEY (`log_timestamp`,`hostname`),
        KEY `hostname` (`hostname`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       PARTITION BY RANGE  COLUMNS(`log_timestamp`)
      (PARTITION `p2014_12` VALUES LESS THAN ('2015-01-01') ENGINE = InnoDB,
       PARTITION `p2015_01` VALUES LESS THAN ('2015-02-01') ENGINE = InnoDB,
       PARTITION `p2015_02` VALUES LESS THAN ('2015-03-01') ENGINE = InnoDB,
       PARTITION `p2015_03` VALUES LESS THAN ('2015-04-01') ENGINE = InnoDB,
       PARTITION `p2015_04` VALUES LESS THAN ('2015-05-01') ENGINE = InnoDB,
       PARTITION `p2015_05` VALUES LESS THAN ('2015-06-01') ENGINE = InnoDB,
       PARTITION `p2015_06` VALUES LESS THAN ('2015-07-01') ENGINE = InnoDB,
       PARTITION `p2015_07` VALUES LESS THAN ('2015-08-01') ENGINE = InnoDB,
       PARTITION `p2015_08` VALUES LESS THAN ('2015-09-01') ENGINE = InnoDB,
       PARTITION `p2015_09` VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
       PARTITION `p2015_10` VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB,
       PARTITION `p2015_11` VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB,
       PARTITION `p2015_12` VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
       PARTITION `p2016_01` VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB,
       PARTITION `p2016_02` VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB,
       PARTITION `p2016_03` VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
       PARTITION `p2016_04` VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB,
       PARTITION `p2016_05` VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB,
       PARTITION `p2016_06` VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB,
       PARTITION `p2016_07` VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB,
       PARTITION `p2016_08` VALUES LESS THAN ('2016-09-01') ENGINE = InnoDB,
       PARTITION `p2016_09` VALUES LESS THAN ('2016-10-01') ENGINE = InnoDB,
       PARTITION `p2016_10` VALUES LESS THAN ('2016-11-01') ENGINE = InnoDB,
       PARTITION `p2016_11` VALUES LESS THAN ('2016-12-01') ENGINE = InnoDB,
       PARTITION `p2016_12` VALUES LESS THAN ('2017-01-01') ENGINE = InnoDB,
       PARTITION `p2017_01` VALUES LESS THAN ('2017-02-01') ENGINE = InnoDB,
       PARTITION `p2017_02` VALUES LESS THAN ('2017-03-01') ENGINE = InnoDB,
       PARTITION `p2017_03` VALUES LESS THAN ('2017-04-01') ENGINE = InnoDB,
       PARTITION `p2017_04` VALUES LESS THAN ('2017-05-01') ENGINE = InnoDB,
       PARTITION `p2017_05` VALUES LESS THAN ('2017-06-01') ENGINE = InnoDB,
       PARTITION `p2017_06` VALUES LESS THAN ('2017-07-01') ENGINE = InnoDB,
       PARTITION `p2017_07` VALUES LESS THAN ('2017-08-01') ENGINE = InnoDB,
       PARTITION `p2017_08` VALUES LESS THAN ('2017-09-01') ENGINE = InnoDB,
       PARTITION `p2017_09` VALUES LESS THAN ('2017-10-01') ENGINE = InnoDB,
       PARTITION `p2017_10` VALUES LESS THAN ('2017-11-01') ENGINE = InnoDB,
       PARTITION `p2017_11` VALUES LESS THAN ('2017-12-01') ENGINE = InnoDB,
       PARTITION `p2017_12` VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,
       PARTITION `p2018_01` VALUES LESS THAN ('2018-02-01') ENGINE = InnoDB,
       PARTITION `p2018_02` VALUES LESS THAN ('2018-03-01') ENGINE = InnoDB,
       PARTITION `p2018_03` VALUES LESS THAN ('2018-04-01') ENGINE = InnoDB,
       PARTITION `p2018_04` VALUES LESS THAN ('2018-05-01') ENGINE = InnoDB,
       PARTITION `p2018_05` VALUES LESS THAN ('2018-06-01') ENGINE = InnoDB,
       PARTITION `p2018_06` VALUES LESS THAN ('2018-07-01') ENGINE = InnoDB,
       PARTITION `p2018_07` VALUES LESS THAN ('2018-08-01') ENGINE = InnoDB,
       PARTITION `p2018_08` VALUES LESS THAN ('2018-09-01') ENGINE = InnoDB,
       PARTITION `p2018_09` VALUES LESS THAN ('2018-10-01') ENGINE = InnoDB,
       PARTITION `p2018_10` VALUES LESS THAN ('2018-11-01') ENGINE = InnoDB,
       PARTITION `p2018_11` VALUES LESS THAN ('2018-12-01') ENGINE = InnoDB,
       PARTITION `p2018_12` VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,
       PARTITION `p2019_01` VALUES LESS THAN ('2019-02-01') ENGINE = InnoDB,
       PARTITION `p2019_02` VALUES LESS THAN ('2019-03-01') ENGINE = InnoDB,
       PARTITION `p2019_03` VALUES LESS THAN ('2019-04-01') ENGINE = InnoDB,
       PARTITION `p2019_04` VALUES LESS THAN ('2019-05-01') ENGINE = InnoDB,
       PARTITION `p2019_05` VALUES LESS THAN ('2019-06-01') ENGINE = InnoDB,
       PARTITION `p2019_06` VALUES LESS THAN ('2019-07-01') ENGINE = InnoDB,
       PARTITION `p2019_07` VALUES LESS THAN ('2019-08-01') ENGINE = InnoDB,
       PARTITION `p2019_08` VALUES LESS THAN ('2019-09-01') ENGINE = InnoDB,
       PARTITION `p2019_09` VALUES LESS THAN ('2019-10-01') ENGINE = InnoDB,
       PARTITION `p2019_10` VALUES LESS THAN ('2019-11-01') ENGINE = InnoDB,
       PARTITION `p2019_11` VALUES LESS THAN ('2019-12-01') ENGINE = InnoDB,
       PARTITION `p2019_12` VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,
       PARTITION `p2020_01` VALUES LESS THAN ('2020-02-01') ENGINE = InnoDB,
       PARTITION `p2020_02` VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB,
       PARTITION `p2020_03` VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB,
       PARTITION `p2020_04` VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB,
       PARTITION `p2020_05` VALUES LESS THAN ('2020-06-01') ENGINE = InnoDB,
       PARTITION `p2020_06` VALUES LESS THAN ('2020-07-01') ENGINE = InnoDB,
       PARTITION `p2020_07` VALUES LESS THAN ('2020-08-01') ENGINE = InnoDB,
       PARTITION `p2020_08` VALUES LESS THAN ('2020-09-01') ENGINE = InnoDB,
       PARTITION `p2020_09` VALUES LESS THAN ('2020-10-01') ENGINE = InnoDB,
       PARTITION `p2020_10` VALUES LESS THAN ('2020-11-01') ENGINE = InnoDB,
       PARTITION `p2020_11` VALUES LESS THAN ('2020-12-01') ENGINE = InnoDB,
       PARTITION `p2020_12` VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB,
       PARTITION `p2021_01` VALUES LESS THAN ('2021-02-01') ENGINE = InnoDB,
       PARTITION `p2021_02` VALUES LESS THAN ('2021-03-01') ENGINE = InnoDB,
       PARTITION `p2021_03` VALUES LESS THAN ('2021-04-01') ENGINE = InnoDB,
       PARTITION `p2021_04` VALUES LESS THAN ('2021-05-01') ENGINE = InnoDB,
       PARTITION `p2021_05` VALUES LESS THAN ('2021-06-01') ENGINE = InnoDB,
       PARTITION `p2021_06` VALUES LESS THAN ('2021-07-01') ENGINE = InnoDB,
       PARTITION `p2021_07` VALUES LESS THAN ('2021-08-01') ENGINE = InnoDB,
       PARTITION `p2021_08` VALUES LESS THAN ('2021-09-01') ENGINE = InnoDB,
       PARTITION `p2021_09` VALUES LESS THAN ('2021-10-01') ENGINE = InnoDB,
       PARTITION `p2021_10` VALUES LESS THAN ('2021-11-01') ENGINE = InnoDB,
       PARTITION `p2021_11` VALUES LESS THAN ('2021-12-01') ENGINE = InnoDB,
       PARTITION `p2021_12` VALUES LESS THAN ('2022-01-01') ENGINE = InnoDB,
       PARTITION `p2022_01` VALUES LESS THAN ('2022-02-01') ENGINE = InnoDB,
       PARTITION `p2022_02` VALUES LESS THAN ('2022-03-01') ENGINE = InnoDB,
       PARTITION `p2022_03` VALUES LESS THAN ('2022-04-01') ENGINE = InnoDB,
       PARTITION `p2022_04` VALUES LESS THAN ('2022-05-01') ENGINE = InnoDB,
       PARTITION `p2022_05` VALUES LESS THAN ('2022-06-01') ENGINE = InnoDB,
       PARTITION `p2022_06` VALUES LESS THAN ('2022-07-01') ENGINE = InnoDB,
       PARTITION `p2022_07` VALUES LESS THAN ('2022-08-01') ENGINE = InnoDB,
       PARTITION `p2022_08` VALUES LESS THAN ('2022-09-01') ENGINE = InnoDB,
       PARTITION `p2022_09` VALUES LESS THAN ('2022-10-01') ENGINE = InnoDB,
       PARTITION `p2022_10` VALUES LESS THAN ('2022-11-01') ENGINE = InnoDB,
       PARTITION `p2022_11` VALUES LESS THAN ('2022-12-01') ENGINE = InnoDB,
       PARTITION `p9999` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) |
      

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            rbur004 Rob Burrowes
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.