Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.13, 10.5.18, 10.6.14
-
None
Description
MDEV-15101 is supposed to be fixed in 10.5.4+, but if engine-independent statistics is collected by the ANALYZE TABLE we still have to wait for the table flush if there was some long running query in progress when such ANALYZE was executed, thus causing the same problems. Consider this simple table:
MariaDB [test]> show create table t1\G
|
*************************** 1. row ***************************
|
Table: t1
|
Create Table: CREATE TABLE `t1` (
|
`id` int(11) DEFAULT NULL,
|
`c1` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
1 row in set (0.005 sec)
|
|
|
MariaDB [test]> select * from t1;
|
+------+------+
|
| id | c1 |
|
+------+------+
|
| 1 | 0 |
|
| 2 | 0 |
|
| 3 | 0 |
|
| 4 | 0 |
|
+------+------+
|
4 rows in set (0.002 sec)
|
In one connection run a slow query against it, like this:
MariaDB [test]> select sleep(100) from t1;
Now in another one try this:
MariaDB [test]> select @@use_stat_tables;
|
+------------------------+
|
| @@use_stat_tables |
|
+------------------------+
|
| PREFERABLY_FOR_QUERIES |
|
+------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> analyze table t1;
|
+---------+---------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------+---------+----------+----------+
|
| test.t1 | analyze | status | OK |
|
+---------+---------+----------+----------+
|
1 row in set (0.044 sec)
|
|
|
MariaDB [test]> select count(*) from t1;
|
+----------+
|
| count(*) |
|
+----------+
|
| 4 |
|
+----------+
|
1 row in set (0.001 sec)
|
No problem so far, but if we with to 'preferable' to force engine-independent statistics collection even by default:
MariaDB [test]> set session use_stat_tables = 'preferably';
|
Query OK, 0 rows affected (0.005 sec)
|
|
|
MariaDB [test]> analyze table t1;
|
+---------+---------+----------+-----------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------+---------+----------+-----------------------------------------+
|
| test.t1 | analyze | status | Engine-independent statistics collected |
|
| test.t1 | analyze | status | OK |
|
+---------+---------+----------+-----------------------------------------+
|
2 rows in set (0.053 sec)
|
|
|
MariaDB [test]> select count(*) from t1;
|
...
|
we are forced to wait for the query in the first connection to complete, this way:
Yuliyas-Air:maria10.5 Valerii$ bin/mysql test
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 21
|
Server version: 10.5.18-MariaDB MariaDB Server
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [test]> show processlist;
|
+----+---------+-----------+------+---------+------+-------------------------+---------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+---------+-----------+------+---------+------+-------------------------+---------------------------+----------+
|
| 19 | Valerii | localhost | test | Query | 114 | User sleep | select sleep(100) from t1 | 0.000 |
|
| 20 | Valerii | localhost | test | Query | 6 | Waiting for table flush | select count(*) from t1 | 0.000 |
|
| 21 | Valerii | localhost | test | Query | 0 | starting | show processlist | 0.000 |
|
+----+---------+-----------+------+---------+------+-------------------------+---------------------------+----------+
|
3 rows in set (0.003 sec)
|
So, the fix for MDEV-15101 had not taken the engine-independent table statistics collection case into account. I think it's a bug.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-