[MDEV-36210] Optimizer improvement or bug ? - Jira

Details

Type: Task
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Fix Version/s: None
Component/s: Optimizer, Partitioning
Labels:
None

Description

In some particular case MariaDB should know witch partition it should adress it:

Original query :

MariaDB [pmacontrol]>

SELECT

  a.`id_mysql_server`,

  a.`id_ts_variable`,

  '' as connection_name,

  a.`date`,

  a.`value` as value

FROM

  `ts_value_general_text` 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 = 1263

  AND a.id_mysql_server IN (

    1, 2, 3, 4, 14, 15, 16, 17, 18, 28, 5, 6, 7,

    8, 9, 10, 11, 12, 13, 19, 20, 21, 22, 24,

);

+-----------------+----------------+-----------------+---------------------+-------------------------------------------+

| id_mysql_server | id_ts_variable | connection_name | date                | value                                     |

+-----------------+----------------+-----------------+---------------------+-------------------------------------------+

|               1 |           1263 |                 | 2025-03-03 00:02:14 | 10.11.11-MariaDB-deb12-log                |

|               2 |           1263 |                 | 2025-03-03 00:02:04 | 10.6.20-MariaDB-deb11-log                 |

|               3 |           1263 |                 | 2025-03-03 00:02:15 | 10.6.20-MariaDB-deb11-log                 |

|               4 |           1263 |                 | 2025-03-03 00:02:05 | 10.6.20-MariaDB-deb11-log                 |

|               5 |           1263 |                 | 2025-03-03 02:48:31 | 10.6.19-15-MariaDB-enterprise-log         |

|               6 |           1263 |                 | 2025-03-03 02:48:42 | 10.6.19-15-MariaDB-enterprise-log         |

|               7 |           1263 |                 | 2025-03-03 02:48:32 | 10.6.19-15-MariaDB-enterprise-log         |

|               8 |           1263 |                 | 2025-03-03 02:48:22 | 10.6.19-15-MariaDB-enterprise-log         |

|               9 |           1263 |                 | 2025-03-03 02:48:33 | 10.6.19-15-MariaDB-enterprise-log         |

|              10 |           1263 |                 | 2025-03-03 02:48:23 | 10.6.17-MariaDB-1:10.6.17+maria~deb11-log |

|              11 |           1263 |                 | 2025-03-03 02:48:34 | 10.6.17-MariaDB-1:10.6.17+maria~deb11-log |

|              12 |           1263 |                 | 2025-03-03 02:48:24 | 10.6.17-MariaDB-1:10.6.17+maria~deb11-log |

|              13 |           1263 |                 | 2025-03-03 02:49:05 | 10.6.17-MariaDB-1:10.6.17+maria~deb11-log |

|              14 |           1263 |                 | 2025-03-03 00:02:16 | 10.6.21-MariaDB-ubu2204-log               |

|              15 |           1263 |                 | 2025-03-03 00:02:06 | 10.6.21-MariaDB-ubu2204-log               |

|              16 |           1263 |                 | 2025-03-03 00:02:47 | 10.6.21-MariaDB-ubu2204-log               |

|              17 |           1263 |                 | 2025-03-03 00:02:07 | 10.6.21-MariaDB-ubu2204-log               |

|              18 |           1263 |                 | 2025-03-03 00:02:27 | 10.6.21-MariaDB-ubu2204-log               |

|              19 |           1263 |                 | 2025-03-03 12:18:29 | 10.6.20-16-MariaDB-enterprise-log         |

|              20 |           1263 |                 | 2025-03-03 12:13:37 | 10.6.20-16-MariaDB-enterprise-log         |

|              21 |           1263 |                 | 2025-03-03 12:18:09 | 10.6.20-16-MariaDB-enterprise-log         |

|              22 |           1263 |                 | 2025-03-03 12:13:38 | 10.6.20-16-MariaDB-enterprise-log         |

|              24 |           1263 |                 | 2025-03-03 00:02:03 | 10.6.14-9-MariaDB-enterprise-log          |

|              28 |           1263 |                 | 2025-03-03 00:02:08 | N/A                                       |

|              29 |           1263 |                 | 2025-03-03 00:02:04 | 10.6.20-16-MariaDB-enterprise-log         |

+-----------------+----------------+-----------------+---------------------+-------------------------------------------+

25 rows in set (0,220 sec)

explain: (here it's a part of the query normally it's an UNION ALL with different id_ts_variable

MariaDB [pmacontrol]> explain partitions SELECT  a.`id_mysql_server`, a.`id_ts_variable`, '' as connection_name,a.`date`, a.`value` as value    FROM `ts_value_general_text` 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 = 1263 AND a.id_mysql_server IN (1,2,3,4,14,15,16,17,18,28,5,6,7,8,9,10,11,12,13,19,20,21,22,24,29) \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: c

   partitions: NULL

         type: const

possible_keys: PRIMARY,ts_variable_ibfk_1

          key: PRIMARY

      key_len: 4

          ref: const

         rows: 1

        Extra:

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: b

   partitions: NULL

         type: range

possible_keys: id_mysql_server_2,id_mysql_server,date,id_ts_file,id_mysql_server_4

          key: id_mysql_server

      key_len: 8

          ref: NULL

         rows: 25

        Extra: Using where; Using index

*************************** 3. row ***************************

           id: 1

  select_type: SIMPLE

        table: a

   partitions: p739632,p739633,p739634,p739635,p739636,p739637,p739638,p739639,p739640,p739641,p739642,p739643,p739644,p739645,p739646,p739647,p739648,p739649,p739650,p739651,p739652,p739653,p739654,p739655,p739656,p739657,p739658,p739659,p739660,p739661,p739662,p739663,p739664,p739665,p739666,p739667,p739668,p739669,p739670,p739671,p739672,p739673,p739674,p739675,p739676,p739677,p739678,p739679,p739680

         type: ref

possible_keys: id_mysql_server

          key: id_mysql_server

      key_len: 13

          ref: pmacontrol.b.id_mysql_server,const,pmacontrol.b.date

         rows: 2

        Extra:

3 rows in set (0,013 sec)

The goal here :
To made a point select the goal is to register last value in table ts_max_date for each server for each kind of data, if all good will be in last partition if not (if the server is down) can be days or months ago.

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

  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=717 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

MariaDB [pmacontrol]> show create table ts_value_general_text\G

*************************** 1. row ***************************

       Table: ts_value_general_text

Create Table: CREATE TABLE `ts_value_general_text` (

  `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` text NOT NULL,

  PRIMARY KEY (`id`,`date`),

  KEY `id_mysql_server` (`id_mysql_server`,`id_ts_variable`,`date`)

) ENGINE=ROCKSDB AUTO_INCREMENT=3224597377 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

 PARTITION BY RANGE (to_days(`date`))

(PARTITION `p739632` VALUES LESS THAN (739632) ENGINE = ROCKSDB,

 PARTITION `p739633` VALUES LESS THAN (739633) ENGINE = ROCKSDB,

 PARTITION `p739634` VALUES LESS THAN (739634) ENGINE = ROCKSDB,

 PARTITION `p739635` VALUES LESS THAN (739635) ENGINE = ROCKSDB,

 PARTITION `p739636` VALUES LESS THAN (739636) ENGINE = ROCKSDB,

 PARTITION `p739637` VALUES LESS THAN (739637) ENGINE = ROCKSDB,

 PARTITION `p739638` VALUES LESS THAN (739638) ENGINE = ROCKSDB,

 PARTITION `p739639` VALUES LESS THAN (739639) ENGINE = ROCKSDB,

 PARTITION `p739640` VALUES LESS THAN (739640) ENGINE = ROCKSDB,

 PARTITION `p739641` VALUES LESS THAN (739641) ENGINE = ROCKSDB,

 PARTITION `p739642` VALUES LESS THAN (739642) ENGINE = ROCKSDB,

 PARTITION `p739643` VALUES LESS THAN (739643) ENGINE = ROCKSDB,

 PARTITION `p739644` VALUES LESS THAN (739644) ENGINE = ROCKSDB,

 PARTITION `p739645` VALUES LESS THAN (739645) ENGINE = ROCKSDB,

 PARTITION `p739646` VALUES LESS THAN (739646) ENGINE = ROCKSDB,

 PARTITION `p739647` VALUES LESS THAN (739647) ENGINE = ROCKSDB,

 PARTITION `p739648` VALUES LESS THAN (739648) ENGINE = ROCKSDB,

 PARTITION `p739649` VALUES LESS THAN (739649) ENGINE = ROCKSDB,

 PARTITION `p739650` VALUES LESS THAN (739650) ENGINE = ROCKSDB,

 PARTITION `p739651` VALUES LESS THAN (739651) ENGINE = ROCKSDB,

 PARTITION `p739652` VALUES LESS THAN (739652) ENGINE = ROCKSDB,

 PARTITION `p739653` VALUES LESS THAN (739653) ENGINE = ROCKSDB,

 PARTITION `p739654` VALUES LESS THAN (739654) ENGINE = ROCKSDB,

 PARTITION `p739655` VALUES LESS THAN (739655) ENGINE = ROCKSDB,

 PARTITION `p739656` VALUES LESS THAN (739656) ENGINE = ROCKSDB,

 PARTITION `p739657` VALUES LESS THAN (739657) ENGINE = ROCKSDB,

 PARTITION `p739658` VALUES LESS THAN (739658) ENGINE = ROCKSDB,

 PARTITION `p739659` VALUES LESS THAN (739659) ENGINE = ROCKSDB,

 PARTITION `p739660` VALUES LESS THAN (739660) ENGINE = ROCKSDB,

 PARTITION `p739661` VALUES LESS THAN (739661) ENGINE = ROCKSDB,

 PARTITION `p739662` VALUES LESS THAN (739662) ENGINE = ROCKSDB,

 PARTITION `p739663` VALUES LESS THAN (739663) ENGINE = ROCKSDB,

 PARTITION `p739664` VALUES LESS THAN (739664) ENGINE = ROCKSDB,

 PARTITION `p739665` VALUES LESS THAN (739665) ENGINE = ROCKSDB,

 PARTITION `p739666` VALUES LESS THAN (739666) ENGINE = ROCKSDB,

 PARTITION `p739667` VALUES LESS THAN (739667) ENGINE = ROCKSDB,

 PARTITION `p739668` VALUES LESS THAN (739668) ENGINE = ROCKSDB,

 PARTITION `p739669` VALUES LESS THAN (739669) ENGINE = ROCKSDB,

 PARTITION `p739670` VALUES LESS THAN (739670) ENGINE = ROCKSDB,

 PARTITION `p739671` VALUES LESS THAN (739671) ENGINE = ROCKSDB,

 PARTITION `p739672` VALUES LESS THAN (739672) ENGINE = ROCKSDB,

 PARTITION `p739673` VALUES LESS THAN (739673) ENGINE = ROCKSDB,

 PARTITION `p739674` VALUES LESS THAN (739674) ENGINE = ROCKSDB,

 PARTITION `p739675` VALUES LESS THAN (739675) ENGINE = ROCKSDB,

 PARTITION `p739676` VALUES LESS THAN (739676) ENGINE = ROCKSDB,

 PARTITION `p739677` VALUES LESS THAN (739677) ENGINE = ROCKSDB,

 PARTITION `p739678` VALUES LESS THAN (739678) ENGINE = ROCKSDB,

 PARTITION `p739679` VALUES LESS THAN (739679) ENGINE = ROCKSDB,

 PARTITION `p739680` VALUES LESS THAN (739680) ENGINE = ROCKSDB)

1 row in set (0,001 sec)

So i put in all query the prefetch of partition, but I think it's should be made already by MariaDB, the engine got all the information:

SELECT

        c.id AS ts_variable_id,

        group_concat(distinct concat('p',TO_DAYS(b.date) + 1)) AS partition_day

        FROM ts_max_date b

        JOIN ts_variable c ON b.id_ts_file = c.id_ts_file

        WHERE c.id in (1263,1264,1325,606,1324,1607,830,1300,1322,1086,1120,1620)

        GROUP BY c.id;

+----------------+-----------------+

| ts_variable_id | partition_day   |

+----------------+-----------------+

|            606 | p739672,p739679 |

|            830 | p739679         |

|           1086 | p739679         |

|           1120 | p739679         |

|           1263 | p739679         |

|           1264 | p739679         |

|           1300 | p739679         |

|           1322 | p739679         |

|           1324 | p739679         |

|           1325 | p739679         |

|           1607 | p739679         |

|           1620 | p739672,p739679 |

+----------------+-----------------+

12 rows in set (0,001 sec)

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

  KEY `ts_variable_ibfk_1` (`id_ts_file`),

  KEY `from` (`from`),

  CONSTRAINT `ts_variable_ibfk_1` FOREIGN KEY (`id_ts_file`) REFERENCES `ts_file` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=1701 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

Of course when we add the partitions the query time are much better :

MariaDB [pmacontrol]> SELECT  a.`id_mysql_server`, a.`id_ts_variable`, '' as connection_name,a.`date`, a.`value` as value

    ->   FROM `ts_value_general_text` PARTITION (p739672,p739679) 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 = 606 AND a.id_mysql_server IN (1,2,3,4,14,15,16,17,18,28,5,6,7,8,9,10,11,12,13,19,20,21,22,24,29);

+-----------------+----------------+-----------------+---------------------+--------------+

| id_mysql_server | id_ts_variable | connection_name | date                | value        |

+-----------------+----------------+-----------------+---------------------+--------------+

|               1 |            606 |                 | 2025-03-03 15:34:31 | Disconnected |

|               2 |            606 |                 | 2025-03-03 15:34:32 | Primary      |

|               3 |            606 |                 | 2025-03-03 15:34:32 | Primary      |

|               4 |            606 |                 | 2025-03-03 15:34:32 | Primary      |

|               5 |            606 |                 | 2025-03-03 15:34:35 | Primary      |

|               6 |            606 |                 | 2025-03-03 15:34:26 | Primary      |

|               7 |            606 |                 | 2025-03-03 15:34:26 | Primary      |

|               8 |            606 |                 | 2025-03-03 15:34:26 | Primary      |

|               9 |            606 |                 | 2025-03-03 15:34:27 | Primary      |

|              10 |            606 |                 | 2025-03-03 15:34:27 | Primary      |

|              11 |            606 |                 | 2025-03-03 15:34:28 | Primary      |

|              12 |            606 |                 | 2025-03-03 15:34:28 | Primary      |

|              13 |            606 |                 | 2025-03-03 15:34:28 | Primary      |

|              14 |            606 |                 | 2025-03-03 15:34:33 | Primary      |

|              15 |            606 |                 | 2025-03-03 15:34:33 | Primary      |

|              16 |            606 |                 | 2025-03-03 15:34:34 | Primary      |

|              17 |            606 |                 | 2025-03-03 15:34:34 | Primary      |

|              18 |            606 |                 | 2025-03-03 15:34:34 | Primary      |

|              19 |            606 |                 | 2025-03-03 15:34:29 | Primary      |

|              20 |            606 |                 | 2025-03-03 15:34:29 | Primary      |

|              21 |            606 |                 | 2025-03-03 15:34:30 | Primary      |

|              22 |            606 |                 | 2025-03-03 15:34:30 | Primary      |

|              24 |            606 |                 | 2025-03-03 15:23:37 | Primary      |

|              29 |            606 |                 | 2025-03-03 15:34:31 | Disconnected |

+-----------------+----------------+-----------------+---------------------+--------------+

24 rows in set (0,022 sec)

of course times are much more better. My question in some other SQL, the engine get the correct partition, why there we try to open all of them?

Attachments

Activity

People

Assignee:: Unassigned

Reporter:: Aurélien LEQUOY

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2025-03-03 14:36

Updated:: 2025-03-10 02:39

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server