Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Won't Fix
-
N/A
-
None
Description
The newly added Key_read_misses in USER_STATISTICS and CLIENT_STATISTICS behaves in such a strange way that I can't see how it can be meaningful for users. Apparently it reflects some internal logic which the user has no control of.
MariaDB [test]> create table t (a int) engine=MyISAM; |
Query OK, 0 rows affected (1.727 sec) |
|
|
MariaDB [test]> flush user_statistics;
|
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> insert into t values (1); |
Query OK, 1 row affected (0.001 sec)
|
|
|
MariaDB [test]> select Key_read_hits, Key_read_misses from information_schema.user_statistics; |
+---------------+-----------------+ |
| Key_read_hits | Key_read_misses |
|
+---------------+-----------------+ |
| 0 | 2 |
|
+---------------+-----------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> insert into t values (2),(3); |
Query OK, 2 rows affected (0.000 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select Key_read_hits, Key_read_misses from information_schema.user_statistics; |
+---------------+-----------------+ |
| Key_read_hits | Key_read_misses |
|
+---------------+-----------------+ |
| 0 | 2 |
|
+---------------+-----------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> flush tables;
|
Query OK, 0 rows affected (0.001 sec) |
|
|
MariaDB [test]> insert into t values (4),(5),(6); |
Query OK, 3 rows affected (0.002 sec) |
Records: 3 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select Key_read_hits, Key_read_misses from information_schema.user_statistics; |
+---------------+-----------------+ |
| Key_read_hits | Key_read_misses |
|
+---------------+-----------------+ |
| 0 | 4 |
|
+---------------+-----------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> select Key_read_hits, Key_read_misses from information_schema.user_statistics; |
+---------------+-----------------+ |
| Key_read_hits | Key_read_misses |
|
+---------------+-----------------+ |
| 0 | 4 |
|
+---------------+-----------------+ |
1 row in set (0.001 sec) |
So, it increments the value on the first insert (after opening?) the table (twice, regardless the number of inserted values), then it doesn't, then after flushing (re-opening?) the table increments again.
Even wilder with sequences, it increments the value nine times:
MariaDB [test]> create sequence s engine=MyISAM; |
Query OK, 0 rows affected (0.137 sec) |
|
|
MariaDB [test]> flush user_statistics;
|
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> select nextval(s); |
+------------+ |
| nextval(s) |
|
+------------+ |
| 1 |
|
+------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> select Key_read_hits, Key_read_misses from information_schema.user_statistics; |
+---------------+-----------------+ |
| Key_read_hits | Key_read_misses |
|
+---------------+-----------------+ |
| 0 | 9 |
|
+---------------+-----------------+ |
1 row in set (0.001 sec) |
TABLE_STATISTICS in both case shows the expected zero.
Attachments
Issue Links
- is caused by
-
MDEV-33151 Add more columns to TABLE_STATISTICS and USER STATS
-
- Closed
-