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

MariaDB is not picking up encompassing index at all for ref query on TokuDB table

    XMLWordPrintable

Details

    Description

      While table has an WHERE encompassing index (ref index), server is not picking it for update or select queries on large TokuDB table. This started from 10.1.24 and persists in 10.1.25-26.

      SHOW CREATE TABLE `radacct`;

      | radacct | CREATE TABLE `radacct` (
        `RadAcctId` bigint(21) NOT NULL AUTO_INCREMENT,
        `AcctSessionId` varchar(64) DEFAULT NULL,
        `AcctUniqueId` varchar(32) NOT NULL DEFAULT '',
        `UserName` varchar(64) NOT NULL DEFAULT '',
        `Realm` varchar(64) DEFAULT '',
        `NASIPAddress` varchar(15) NOT NULL DEFAULT '',
      ... other fields ...
        PRIMARY KEY (`RadAcctId`),
        KEY `UserName` (`UserName`),
        KEY `FramedIPAddress` (`FramedIPAddress`),
        KEY `AcctSessionId` (`AcctSessionId`),
        KEY `AcctUniqueId` (`AcctUniqueId`),
        KEY `AcctStartTime` (`AcctStartTime`),
        KEY `AcctStopTime` (`AcctStopTime`),
        KEY `NASIPAddress` (`NASIPAddress`),
        KEY `RadCheckProductID` (`ProductID`),
        KEY `AcctSessionId_UserName` (`AcctSessionId`,`UserName`),
        KEY `user_stop_start_ip` (`UserName`,`AcctStopTime`,`AcctStartTime`,`FramedIPAddress`),
        KEY `AcctStartTime_AcctUniqueId` (`AcctStartTime`,`AcctUniqueId`),
        KEY `CallingStationId` (`CallingStationId`),
        KEY `LastUpdated` (`LastUpdated`),
        KEY `AcctSessionId_2` (`AcctSessionId`,`UserName`,`NASIPAddress`),
       * KEY `updates` (`UserName`,`NASIPAddress`,`AcctSessionId`),*
        KEY `radacct` (`NASIPAddress`,`AcctSessionId`)
      ) ENGINE=TokuDB AUTO_INCREMENT=192662679 DEFAULT CHARSET=latin1 `compression`=tokudb_lzma |
      

      Query:

      UPDATE `radacct` SET `acctsessiontime` = 43570, `acctinputoctets` = 288561856, `acctinputoctets64` = 0<<32|288561856, `acctinputgigawords` = 0>>32, `acctoutputoctets` = 1946791036, `acctoutputoctets64` = 0<<32|1946791036, `acctoutputgigawords` = 0>>32, `framedipaddress` = '5.6.7.8', `lastupdated` = NOW() WHERE ((`UserName` = "username") AND (`NASIPAddress` = "1.2.3.4") AND (`AcctSessionId` = "ether 0/0/4:4096.224 0/0/0/0/0/0_00CE9067"));
      

      Given the WHERE condition, it should pick up index 'updates' because it's encompassing (a full static ref), but it does not. What's worse, query behavior is seemingly random. It either picks single field index or uses index merge.

      Explains:

      | id   | select_type | table   | type        | possible_keys                                                                                                 | key                   | key_len | ref  | rows | filtered | Extra                                               |
      |    1 | SIMPLE      | radacct | index_merge | UserName,AcctSessionId,NASIPAddress,AcctSessionId_UserName,user_stop_start_ip,AcctSessionId_2,updates,radacct | AcctSessionId,radacct | 67,84   | NULL |    1 |   100.00 | Using intersect(AcctSessionId,radacct); Using where |
      

      Here it used intersect between AcctSessionId and the other index containing the other two fields.

      | id   | select_type | table   | type  | possible_keys                                                                                                 | key           | key_len | ref  | rows | filtered | Extra       |
      |    1 | SIMPLE      | radacct | range | UserName,AcctSessionId,NASIPAddress,AcctSessionId_UserName,user_stop_start_ip,AcctSessionId_2,updates,radacct | AcctSessionId | 67      | NULL |    1 |   100.00 | Using where |
      

      And here it used just AcctSessionId index with a single field for the same query. Of course index can be unique for AcctSessionId, but query takes a whole lot of time showing 'Queried XXXXXX rows' where XXXXXX eventually goes to more than 30% of a table.

      Sometimes it also uses UserName index.

      *SHOW INDEX FROM `radacct`;*
       
      | Table   | Non_unique | Key_name                   | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      | radacct |          0 | PRIMARY                    |            1 | RadAcctId        | A         |    33259825 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | UserName                   |            1 | UserName         | A         |       38674 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | FramedIPAddress            |            1 | FramedIPAddress  | A         |       57245 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | AcctSessionId              |            1 | AcctSessionId    | A         |    33259825 |     NULL | NULL   | YES  | BTREE      |         |               |
      | radacct |          1 | AcctUniqueId               |            1 | AcctUniqueId     | A         |    33259825 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | AcctStartTime              |            1 | AcctStartTime    | A         |    33259825 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | AcctStopTime               |            1 | AcctStopTime     | A         |    33259825 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | NASIPAddress               |            1 | NASIPAddress     | A         |         467 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | RadCheckProductID          |            1 | ProductID        | A         |          61 |     NULL | NULL   | YES  | BTREE      |         |               |
      | radacct |          1 | AcctSessionId_UserName     |            1 | AcctSessionId    | A         |    33259825 |     NULL | NULL   | YES  | BTREE      |         |               |
      | radacct |          1 | AcctSessionId_UserName     |            2 | UserName         | A         |    33259825 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | user_stop_start_ip         |            1 | UserName         | A         |       49939 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | user_stop_start_ip         |            2 | AcctStopTime     | A         |    33259825 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | user_stop_start_ip         |            3 | AcctStartTime    | A         |    33259825 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | user_stop_start_ip         |            4 | FramedIPAddress  | A         |    33259825 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | AcctStartTime_AcctUniqueId |            1 | AcctStartTime    | A         |    33259825 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | AcctStartTime_AcctUniqueId |            2 | AcctUniqueId     | A         |    33259825 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | CallingStationId           |            1 | CallingStationId | A         |       79568 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | LastUpdated                |            1 | LastUpdated      | A         |     1446079 |     NULL | NULL   | YES  | BTREE      |         |               |
      | radacct |          1 | AcctSessionId_2            |            1 | AcctSessionId    | A         |    33259825 |     NULL | NULL   | YES  | BTREE      |         |               |
      | radacct |          1 | AcctSessionId_2            |            2 | UserName         | A         |    33259825 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | AcctSessionId_2            |            3 | NASIPAddress     | A         |    33259825 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | updates                    |            1 | UserName         | A         |       51645 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | updates                    |            2 | NASIPAddress     | A         |       83567 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | updates                    |            3 | AcctSessionId    | A         |    33259825 |     NULL | NULL   | YES  | BTREE      |         |               |
      | radacct |          1 | radacct                    |            1 | NASIPAddress     | A         |         230 |     NULL | NULL   |      | BTREE      |         |               |
      | radacct |          1 | radacct                    |            2 | AcctSessionId    | A         |    33259825 |     NULL | NULL   | YES  | BTREE      |         |               |
      

      I have done recount rows ANALYZE and a full scale ANALYZE on the table (by allowing analyze to run for ages), it does not have any effect.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              Alex/AT Alex/AT
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.