Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.7
-
None
-
None
Description
The tables :
|
|
|
|
MariaDB [pmacontrol]> SHOW CREATE TABLE ts_value_general_int\G
|
*************************** 1. row *************************** |
Table: ts_value_general_int
|
Create Table: CREATE TABLE `ts_value_general_int` (
|
`id` bigint(20) NOT NULL AUTO_INCREMENT, |
`id_mysql_server` int(11) NOT NULL, |
`id_ts_variable` int(11) NOT NULL, |
`date` datetime NOT NULL,
|
`value` bigint(20) unsigned DEFAULT NULL, |
PRIMARY KEY (`id`,`date`),
|
KEY `id_mysql_server` (`id_mysql_server`,`id_ts_variable`,`date`)
|
) ENGINE=ROCKSDB AUTO_INCREMENT=19785579712 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
PARTITION BY RANGE (to_days(`date`))
|
(PARTITION `p739373` VALUES LESS THAN (739373) ENGINE = ROCKSDB, |
PARTITION `p739374` VALUES LESS THAN (739374) ENGINE = ROCKSDB, |
PARTITION `p739375` VALUES LESS THAN (739375) ENGINE = ROCKSDB, |
PARTITION `p739376` VALUES LESS THAN (739376) ENGINE = ROCKSDB, |
PARTITION `p739377` VALUES LESS THAN (739377) ENGINE = ROCKSDB, |
PARTITION `p739378` VALUES LESS THAN (739378) ENGINE = ROCKSDB, |
PARTITION `p739379` VALUES LESS THAN (739379) ENGINE = ROCKSDB, |
PARTITION `p739380` VALUES LESS THAN (739380) ENGINE = ROCKSDB, |
PARTITION `p739381` VALUES LESS THAN (739381) ENGINE = ROCKSDB, |
PARTITION `p739382` VALUES LESS THAN (739382) ENGINE = ROCKSDB, |
PARTITION `p739383` VALUES LESS THAN (739383) ENGINE = ROCKSDB, |
PARTITION `p739384` VALUES LESS THAN (739384) ENGINE = ROCKSDB, |
PARTITION `p739385` VALUES LESS THAN (739385) ENGINE = ROCKSDB, |
PARTITION `p739386` VALUES LESS THAN (739386) ENGINE = ROCKSDB, |
PARTITION `p739387` VALUES LESS THAN (739387) ENGINE = ROCKSDB, |
PARTITION `p739388` VALUES LESS THAN (739388) ENGINE = ROCKSDB) |
1 row in set (0,000 sec) |
|
|
|
|
-- yeah this table is big : 19 785 579 712 rows |
|
|
|
|
MariaDB [pmacontrol]> SHOW CREATE TABLE ts_max_date\G
|
*************************** 1. row *************************** |
Table: ts_max_date
|
Create Table: CREATE TABLE `ts_max_date` (
|
`id` int(11) NOT NULL AUTO_INCREMENT, |
`id_mysql_server` int(11) NOT NULL, |
`date` datetime NOT NULL,
|
`date_p1` datetime NOT NULL,
|
`date_p2` datetime NOT NULL,
|
`date_p3` datetime NOT NULL,
|
`date_p4` datetime NOT NULL,
|
`id_ts_file` int(11) NOT NULL, |
`last_date_listener` datetime DEFAULT current_timestamp(),
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `id_mysql_server_2` (`id_mysql_server`,`id_ts_file`),
|
UNIQUE KEY `id_mysql_server` (`id_mysql_server`,`id_ts_file`,`date`),
|
UNIQUE KEY `date` (`date`,`id_ts_file`,`id_mysql_server`),
|
UNIQUE KEY `id_mysql_server_3` (`id_mysql_server`,`id_ts_file`,`date`,`last_date_listener`),
|
KEY `id_ts_file` (`id_ts_file`),
|
KEY `id_mysql_server_4` (`id_mysql_server`,`date`),
|
CONSTRAINT `ts_max_date_ibfk_2` FOREIGN KEY (`id_mysql_server`) REFERENCES `mysql_server` (`id`) ON DELETE CASCADE,
|
CONSTRAINT `ts_max_date_ibfk_3` FOREIGN KEY (`id_ts_file`) REFERENCES `ts_file` (`id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=338 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
|
|
|
|
-- many index for testing =) |
|
|
|
|
MariaDB [pmacontrol]> show create table ts_variable\G
|
*************************** 1. row *************************** |
Table: ts_variable
|
Create Table: CREATE TABLE `ts_variable` (
|
`id` int(11) NOT NULL AUTO_INCREMENT, |
`id_ts_file` int(11) NOT NULL DEFAULT 3, |
`name` varchar(100) NOT NULL, |
`type` enum('INT','DOUBLE','TEXT','JSON','') NOT NULL COMMENT '1 => int, 2 => double, 3 => text, 4=> json', |
`from` varchar(64) NOT NULL, |
`radical` char(10) NOT NULL, |
`is_derived` int(11) DEFAULT 1, |
`is_dynamic` int(11) DEFAULT 1 COMMENT 'only for global variables', |
PRIMARY KEY (`id`),
|
UNIQUE KEY `name` (`name`,`from`)
|
) ENGINE=InnoDB AUTO_INCREMENT=2332 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
1 row in set (0,000 sec) |
|
|
|
|
SELECT
|
'int' as 'type', |
a.`id_mysql_server`,
|
a.`id_ts_variable`,
|
'N/A' as `connection_name`, |
a.`date`,
|
a.`value` as value
|
FROM
|
`ts_value_general_int` a
|
INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server AND a.date = b.date
|
INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id
|
AND b.`id_ts_file` = c.`id_ts_file`
|
WHERE
|
id_ts_variable = 2241 |
AND a.id_mysql_server IN (
|
1, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, |
64, 65, 66, 67, 68, 69, 70, 71, 72, 73, |
74, 75, 76, 77, 78, 79, 80, 81 |
)
|
|
|
|
|
+------+-----------------+----------------+-----------------+---------------------+-------+
|
| type | id_mysql_server | id_ts_variable | connection_name | date | value |
|
+------+-----------------+----------------+-----------------+---------------------+-------+
|
| int | 1 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 54 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 55 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 56 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 57 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 58 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 59 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 60 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 61 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 62 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 63 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 64 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 65 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 66 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 67 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 68 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 69 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 70 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 71 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 72 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 73 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 74 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 75 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 76 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 77 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 78 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 79 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 80 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 81 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
+------+-----------------+----------------+-----------------+---------------------+-------+
|
29 rows in set (0,473 sec) |
|
|
|
|
|
|
--really big time and I don't understand why, by the way only the explain take also the same time, that look like a problem from somewhere else : |
|
|
|
|
|
|
|
MariaDB [pmacontrol]> explain partition SELECT
|
-> 'int' as 'type', |
-> a.`id_mysql_server`,
|
-> a.`id_ts_variable`,
|
-> 'N/A' as `connection_name`, |
-> a.`date`,
|
-> a.`value` as value
|
-> FROM
|
-> `ts_value_general_int` a
|
-> INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server AND a.date = b.date
|
-> INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id
|
-> AND b.`id_ts_file` = c.`id_ts_file`
|
-> WHERE
|
-> id_ts_variable = 2241 |
-> AND a.id_mysql_server IN (
|
-> 1, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, |
-> 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, |
-> 74, 75, 76, 77, 78, 79, 80, 81 |
-> );
|
+------+-------------+-------+---------------------------------------------------------------------------------------------------------------------------------+-------+---------------------------------------------------------------------------------------+-----------------+---------+------------------------------------------------------+------+--------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+---------------------------------------------------------------------------------------------------------------------------------+-------+---------------------------------------------------------------------------------------+-----------------+---------+------------------------------------------------------+------+--------------------------+
|
| 1 | SIMPLE | c | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | | |
| 1 | SIMPLE | b | NULL | range | id_mysql_server_2,id_mysql_server,date,id_mysql_server_3,id_ts_file,id_mysql_server_4 | id_mysql_server | 8 | NULL | 29 | Using where; Using index | |
| 1 | SIMPLE | a | p739373,p739374,p739375,p739376,p739377,p739378,p739379,p739380,p739381,p739382,p739383,p739384,p739385,p739386,p739387,p739388 | ref | id_mysql_server | id_mysql_server | 13 | pmacontrol.b.id_mysql_server,const,pmacontrol.b.date | 1 | | |
+------+-------------+-------+---------------------------------------------------------------------------------------------------------------------------------+-------+---------------------------------------------------------------------------------------+-----------------+---------+------------------------------------------------------+------+--------------------------+
|
3 rows in set (0,473 sec) |
look like it's not normal, or maybe because it's join between RocksDB and InnoDB ?
As you can see only the date : 2024-05-15, so optmizer should look only on partition p739387, why open all partitions ?
of course when I force the partition :
|
|
MariaDB [pmacontrol]> SELECT
|
-> 'int' as 'type', |
-> a.`id_mysql_server`,
|
-> a.`id_ts_variable`,
|
-> 'N/A' as `connection_name`, |
-> a.`date`,
|
-> a.`value` as value
|
-> FROM
|
-> `ts_value_general_int` PARTITION(p739387) a
|
-> INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server
|
-> AND a.date = b.date
|
-> INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id
|
-> AND b.`id_ts_file` = c.`id_ts_file`
|
-> WHERE
|
-> id_ts_variable = 2241 |
-> AND a.id_mysql_server IN (
|
-> 1, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, |
-> 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, |
-> 74, 75, 76, 77, 78, 79, 80, 81 |
-> );
|
+------+-----------------+----------------+-----------------+---------------------+-------+
|
| type | id_mysql_server | id_ts_variable | connection_name | date | value |
|
+------+-----------------+----------------+-----------------+---------------------+-------+
|
| int | 1 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 54 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 55 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 56 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 57 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 58 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 59 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 60 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 61 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 62 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 63 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 64 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 65 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 66 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 67 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 68 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 69 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 70 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 71 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 72 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 73 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 74 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 75 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 76 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 77 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 78 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 79 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 80 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
| int | 81 | 2241 | N/A | 2024-05-15 01:20:01 | 1 | |
+------+-----------------+----------------+-----------------+---------------------+-------+
|
29 rows in set (0,002 sec) |
If I prefetch the partitions I got better performance, so I am sure there a way directly to add this upgrade directly in the optymizer.