Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.1.34, 10.2.31
Description
When EITS is enabled, ALTER TABLE ... ANALYZE PARTITION reads (and locks) all rows in the InnoDB table, even for a tiny partition. As a result, for huge tables it may work for hours and days and does not server the purpose. This does NOT happen without engine-independent persistent statistics enabled.
Consider this table:
MariaDB [test]> show create table tp\G
|
*************************** 1. row ***************************
|
Table: tp
|
Create Table: CREATE TABLE `tp` (
|
`id` int(11) DEFAULT NULL,
|
`c1` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
PARTITION BY RANGE (`id`)
|
(PARTITION `p0` VALUES LESS THAN (10) ENGINE = InnoDB,
|
PARTITION `p1` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
|
1 row in set (0,00 sec)
|
|
|
MariaDB [test]> show table status like 'tp'\G
|
*************************** 1. row ***************************
|
Name: tp
|
Engine: InnoDB
|
Version: 10
|
Row_format: Dynamic
|
Rows: 9731209
|
Avg_row_length: 32
|
Data_length: 318619648
|
Max_data_length: 0
|
Index_length: 0
|
Data_free: 5242880
|
Auto_increment: NULL
|
Create_time: 2020-01-10 17:45:50
|
Update_time: 2020-01-14 10:39:57
|
Check_time: NULL
|
Collation: latin1_swedish_ci
|
Checksum: NULL
|
Create_options: partitioned
|
Comment:
|
1 row in set (0,02 sec)
|
This is how long it takes to get statistics for the partition with just 9 rows:
MariaDB [test]> set session use_stat_tables='preferably';
|
Query OK, 0 rows affected (0,00 sec)
|
|
|
MariaDB [test]> alter table tp analyze partition p0;
|
+---------+---------+----------+-----------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------+---------+----------+-----------------------------------------+
|
| test.tp | analyze | status | Engine-independent statistics collected |
|
| test.tp | analyze | status | OK |
|
+---------+---------+----------+-----------------------------------------+
|
2 rows in set (17,33 sec)
|
|
|
MariaDB [test]> select count(*) from tp;
|
+----------+
|
| count(*) |
|
+----------+
|
| 10000000 |
|
+----------+
|
1 row in set (5,89 sec)
|
|
|
MariaDB [test]> select count(*) from tp partition(p0);
|
+----------+
|
| count(*) |
|
+----------+
|
| 9 |
|
+----------+
|
1 row in set (0,00 sec)
|
In the INNODB STATUS output taken when this query runs we see:
...
|
---TRANSACTION 421521529651464, ACTIVE 10 sec fetching rows
|
mysql tables in use 2, locked 2
|
12335 lock struct(s), heap size 2105552, 6374971 row lock(s)
|
MySQL thread id 10, OS thread handle 140046871947008, query id 59 localhost root
|
alter table tp analyze partition p0
|
...
|
So, the statement that should access single partition with just 9 rows reads and locks all rows in the table. As a result it is totally useless, One can just run ANALYZE for the entire table with the same "costs".