Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.1.28, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
-
CentOS 7
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
- duplicates
-
-
- Open
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
- links to