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".
Attachments
Activity
I think the idea makes sense. If EITS is needed for the entire table there is still a way to get it, but the attempt of per partition collecftion will not cause a lot of unexpected row reads. Maybe add a warning or note that EITS is not really collected in frames of this change.
... also ran it through on the Optimizer Call yesterday. No objections.
https://github.com/MariaDB/server/commit/7e9ffc69ecd1bddf731391b01e6be221efb6f8ef
This issue was fixed in 10.2 but merging the code to 10.3 disabled the change.
Will be pushed to 10.4 shortly
So how about this solution for versions that are currently GA:
This looks consistent to me, and will solve the customer' complaint.
The patch to implement this is a one-liner:
diff --git a/sql/sql_admin.cc b/sql/sql_admin.ccindex 4afaff58223..7aeedbaa8a2 100644--- a/sql/sql_admin.cc+++ b/sql/sql_admin.cc@@ -729,6 +729,7 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables,}collect_eis=(table->table->s->table_category == TABLE_CATEGORY_USER &&+ !(lex->alter_info.flags &= Alter_info::ALTER_ADMIN_PARTITION) &&(get_use_stat_tables_mode(thd) > NEVER ||For subsequent versions, we could look at collecting statistics for each partition. When that is implemented, we will make ANALYZE PARTITION to collect EITS stats if use_stat_tables setting requires it.
valerii igor what are your thoughts?