Bad execution plan after running pt-osc
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Invalid
|
Undecided
|
Unassigned | |||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Triaged
|
Medium
|
Unassigned |
Bug Description
In PS 5.6 with innodb_
CREATE TABLE `companies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`site_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`custom1` varchar(255) DEFAULT NULL,
`custom2` varchar(255) DEFAULT NULL,
`custom3` varchar(255) DEFAULT NULL,
`custom4` varchar(255) DEFAULT NULL,
`custom5` varchar(255) DEFAULT NULL,
`custom6` varchar(255) DEFAULT NULL,
`custom7` varchar(255) DEFAULT NULL,
`custom8` varchar(255) DEFAULT NULL,
`custom9` varchar(255) DEFAULT NULL,
`custom10` varchar(255) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`custom11` varchar(255) DEFAULT NULL,
`custom12` varchar(255) DEFAULT NULL,
`custom13` varchar(255) DEFAULT NULL,
`custom14` varchar(255) DEFAULT NULL,
`custom15` varchar(255) DEFAULT NULL,
`custom16` varchar(255) DEFAULT NULL,
`custom17` varchar(255) DEFAULT NULL,
`custom18` varchar(255) DEFAULT NULL,
`custom19` varchar(255) DEFAULT NULL,
`custom20` varchar(255) DEFAULT NULL,
`custom21` varchar(255) DEFAULT NULL,
`custom22` varchar(255) DEFAULT NULL,
`custom23` varchar(255) DEFAULT NULL,
`custom24` varchar(255) DEFAULT NULL,
`custom25` varchar(255) DEFAULT NULL,
`ticket_count` mediumint(8) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO companies (id) VALUES (NULL);
INSERT INTO companies (id) VALUES (NULL);
INSERT INTO companies (id) VALUES (NULL);
... up to 30 times for example.
Good execution plan:
# Query_time: 0.000296 Lock_time: 0.000113 Rows_sent: 5 Rows_examined: 5 Rows_affected: 0
# Bytes_sent: 2168 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 1130
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
# InnoDB_
# InnoDB_
SET timestamp=
SELECT `companies`.* FROM `companies` WHERE `companies`.`id` IN (1,2, 3, 4, 5);
Now, add a column with pt-osc:
pt-online-
--nocheck-
--execute \
--chunk-
--max-load Threads_running=50 \
--critical-load Threads_running=500 \
--alter "
ADD COLUMN label_array text
" u=root,
and run the same query:
# Query_time: 0.000575 Lock_time: 0.000390 Rows_sent: 5 Rows_examined: 34 Rows_affected: 0
# Bytes_sent: 2243 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 115C
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
# InnoDB_
# InnoDB_
SET timestamp=
SELECT `companies`.* FROM `companies` WHERE `companies`.`id` IN (1, 2, 3, 4, 5);
FULL TABLE scan.
With innodb_
Changed in percona-server: | |
status: | New → Confirmed |
tags: | added: i58783 |
I can reproduce the same problem on Oracle MySQL, but only using pt-osc. If I run the same commands that pt-osc runs on the background it works well!
SELECT @@SQL_MODE; _lock_wait_ timeout' ; wait_timeout' ; SHOW_CREATE = 1/*!40101, @@SQL_MODE= 'NO_AUTO_ VALUE_ON_ ZERO,STRICT_ TRANS_TABLES, NO_ENGINE_ SUBSTITUTION' */; _lock_wait_ timeout' ; lock_wait_ timeout= 1; wait_timeout' ; timeout= 60; SHOW_CREATE = 1/*!40101, @@SQL_MODE= 'NO_AUTO_ VALUE_ON_ ZERO,STRICT_ TRANS_TABLES, NO_ENGINE_ SUBSTITUTION' */; SHOW_CREATE, @@SQL_QUOTE_ SHOW_CREATE := 1 */; SHOW_CREATE := @OLD_QUOTE */; schema. key_column_ usage WHERE referenced_ table_schema= 'test' AND referenced_ table_name= 'companies' ; SHOW_CREATE, @@SQL_QUOTE_ SHOW_CREATE := 1 */; SHOW_CREATE := @OLD_QUOTE */; `_companies_ new` (
SHOW VARIABLES LIKE 'innodb\
SHOW VARIABLES LIKE 'lock\_
SHOW VARIABLES LIKE 'wait\_timeout';
SET SESSION wait_timeout=10000;
SET @@SQL_QUOTE_
SELECT @@server_id /*!50038 , @@hostname*/;
SELECT @@SQL_MODE;
SHOW VARIABLES LIKE 'innodb\
SET SESSION innodb_
SHOW VARIABLES LIKE 'lock\_
SET SESSION lock_wait_
SHOW VARIABLES LIKE 'wait\_timeout';
SET SESSION wait_timeout=10000;
SET @@SQL_QUOTE_
SHOW VARIABLES LIKE 'wsrep_on';
SHOW VARIABLES LIKE 'version%';
SHOW ENGINES;
SHOW VARIABLES LIKE 'innodb_version';
SELECT @@SERVER_ID;
SHOW GRANTS FOR CURRENT_USER();
SHOW PROCESSLIST;
SHOW SLAVE HOSTS;
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SELECT CONCAT(@@hostname, @@port);
SHOW TABLES FROM `test` LIKE 'companies';
SELECT @@server_id /*!50038 , @@hostname*/;
SHOW TRIGGERS FROM `test` LIKE 'companies';
/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_
USE `test`;
SHOW CREATE TABLE `test`.`companies`;
/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_
EXPLAIN SELECT * FROM `test`.`companies` WHERE 1=1;
SELECT table_schema, table_name FROM information_
SHOW VARIABLES LIKE 'wsrep_on';
/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_
USE `test`;
SHOW CREATE TABLE `test`.`companies`;
/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_
CREATE TABLE `test`.
`id` int(11) NOT NULL AUTO_INCREMENT,
`site_id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`custom1` varchar(255) DEFAULT NULL,
`custom2` varchar(255) DEFAULT NULL,
`custom3` varchar(255) DEFAULT NULL,
`custom4` varchar(255) DEFAULT NULL,
`custom5` varchar(255) DEFAULT NULL,
`custom6` varchar(255) DEFAULT NULL,
`custom7` varchar(255) DEFAULT NULL,
`custom8` varchar(255) DEFAULT NULL,
`custom9` varchar(255) DEFAULT NULL,
`custom10` varchar(255) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`custom11` varchar(255) DEFAULT NULL,
`custom12` varchar(255) DEFAULT NULL,
`custom13` varchar(255) DEFAULT NULL,
`custom14` varchar(255) DEFAULT NULL,
`custom15` varchar(255) DEFAULT NULL,
`custom16` varchar(255) DEFAULT NULL,
`custom17` varchar(255) DEFAULT NULL,
`custom18` varchar(255) DEFAULT NULL,
`custom19` varchar(255) DEFAULT NULL,
`custom20` varchar(255) DEFAULT NULL,
`custom21` varchar(255) DEFAULT NULL,
`custom22` varchar(255) DEFAULT NULL,
`custom23` ...