Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.6.7
-
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) | |