|
There is a big table in a statement-based parallel replication setup, like this:
CREATE TABLE `t` (
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`action` enum('update','create','delete','query') NOT NULL,
|
`status` enum('unclaimed','claimed','error','completed','paused','cancelled','exemptable','violation','blocked') NOT NULL,
|
... more columns here ...
|
`last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
`error_message` text CHARACTER SET utf8,
|
`partition_key` int(11) NOT NULL DEFAULT '0',
|
PRIMARY KEY (`id`),
|
... more multiple-column indexes here ...
|
) ENGINE=InnoDB AUTO_INCREMENT=3184963639 DEFAULT CHARSET=latin1
|
|
select * from information_schema.tables where TABLE_SCHEMA='test' and TABLE_NAME = 't'\G
|
*************************** 1. row ***************************
|
TABLE_CATALOG: def
|
TABLE_SCHEMA: test
|
TABLE_NAME: t
|
TABLE_TYPE: BASE TABLE
|
ENGINE: InnoDB
|
VERSION: 10
|
ROW_FORMAT: Compact
|
TABLE_ROWS: 271226130
|
AVG_ROW_LENGTH: 227
|
DATA_LENGTH: 61576822784
|
MAX_DATA_LENGTH: 0
|
INDEX_LENGTH: 93808164864
|
DATA_FREE: 27502051328
|
AUTO_INCREMENT: 3184963639
|
CREATE_TIME: 2016-08-05 13:08:55
|
UPDATE_TIME: NULL
|
CHECK_TIME: NULL
|
TABLE_COLLATION: latin1_swedish_ci
|
CHECKSUM: NULL
|
CREATE_OPTIONS:
|
TABLE_COMMENT:
|
1 row in set (0.00 sec)
|
Usually status is updated for several ids in a single UPDATE like this:
mysql> EXPLAIN UPDATE test.t SET status = 'claimed' WHERE id IN (3191531520, 3191531548, 3191675779, 3191675794, 3191849444, 3191879324, 3192120630, 3192120649, 3192120666, 3192120681, 3192120694, 3192120710, 3192120722, 3192120732, 3192120742, 3192120754, 3192120765, 3192152806, 3192177807, 3192252972, 3192284688, 3192284434, 3192284713, 3192284461, 3192284485, 3192284748, 3192284506, 3192284763, 3192284777, 3192284528, 3192284790, 3192284556, 3192284574, 3192284590, 3192284615, 3192284368, 3192284634, 3192284383, 3192284651, 3192284400, 3192284415, 3192284671, 3192440124, 3192570421, 3192570439, 3192570456, 3192570471, 3192570487)\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: t
|
type: range
|
possible_keys: PRIMARY
|
key: PRIMARY
|
key_len: 8
|
ref: NULL
|
rows: 48
|
Extra: Using where
|
1 row in set (0.00 sec)
|
So, we have range access and reasonable estimation of rows in range. We may get this plan hundreds of times in a row, but sometimes we end up with a totally different plan while this same query is executed by slave:
2339259 system user test Connect 2594 updating UPDATE t SET status = 'claimed' WHERE id IN (3191531520, 3191531548, 3191675779, 3191675794, 3191849444, 3191879324, 3192120630, 3192120649, 3192120666, 3192120681, 3192120694, 3192120710, 3192120722, 3192120732, 3192120742, 3192120754, 3192120765, 3192152806, 3192177807, 3192252972, 3192284688, 3192284434, 3192284713, 3192284461, 3192284485, 3192284748, 3192284506, 3192284763, 3192284777, 3192284528, 3192284790, 3192284556, 3192284574, 3192284590, 3192284615, 3192284368, 3192284634, 3192284383, 3192284651, 3192284400, 3192284415, 3192284671, 3192440124, 3192570421, 3192570439, 3192570456, 3192570471, 3192570487) 0.000
|
|
mysql> SHOW EXPLAIN FOR 2339259\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: t
|
type: index
|
possible_keys: PRIMARY
|
key: PRIMARY
|
key_len: 8
|
ref: NULL
|
rows: 1
|
Extra: Using where
|
1 row in set, 1 warning (0.01 sec)
|
Optimizer used full index scan for the PRIMARY key (and estimated rows as 1), and this caused a very slow execution of the query and millions of locks set in the process that stalled all other parallel replication threads eventually in a "deadlock".
I suspect there is a bug/corner case somewhere in the optimizer when it decides about indexes available for range access. Maybe this is caused by bad InnoDB statistics etc, but in any case I think we should prevent such an execution path.
|
10.0-debug-printouts.diff
mdev10649-log.txt
