[MDEV-29145] Very slow query on partition table joined with other tables Created: 2022-07-20  Updated: 2022-07-27

Status: Open
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.6.7
Fix Version/s: 10.6

Type: Bug Priority: Minor
Reporter: Rob Burrowes Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

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) |


Generated at Thu Feb 08 10:06:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.