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
-
Activity
Regarding second example:
When using @@use_stat_tables="NEVER" we have 0 key reads.
With stat tables enabled, it will try to read once every column from the stat tables and one lookup in the index stats= 9 reads.
As all key_read and key_misses statistics collected for user are global, it is hard to separate system table and user table key read/key_misses without having to add a lot of 'if's in the handler code to keep things separate.
We decided to keep things as it is for now (and document how key_read_hits/key_read_misses really works) and at a later date try to separate the internal key read/key misses from the output.
In the first case, the key_read_misses comes from you having use-stat-tables active.
This will 2 key read hits or 2 key misses every time the stat tables are not in the cache (flush tables will remove them from the cache)
Here is an example that explains this:
MariaDB [test]> set @@use_stat_tables="NEVER";
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> set @@global.userstat=1;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> drop table if exists t;
Query OK, 0 rows affected (0.034 sec)
MariaDB [test]> create table t (a int) engine=MyISAM;
Query OK, 0 rows affected (0.024 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;
--------------
----------------+--------------
----------------+--------------
----------------+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;
--------------
----------------+--------------
----------------+--------------
----------------+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.001 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [test]> select Key_read_hits, Key_read_misses from information_schema.user_statistics;
--------------
----------------+--------------
----------------+--------------
----------------+1 row in set (0.001 sec)
MariaDB [test]> set @@use_stat_tables="PREFERABLY";
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> set @@global.userstat=1;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> drop table t;
Query OK, 0 rows affected (0.021 sec)
MariaDB [test]> create table t (a int) engine=MyISAM;
Query OK, 0 rows affected (0.024 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;
--------------
----------------+--------------
----------------+--------------
----------------+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;
--------------
----------------+--------------
----------------+--------------
----------------+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;
--------------
----------------+--------------
----------------+--------------
----------------+1 row in set (0.001 sec)
MariaDB [test]> analyze table t;
-------
-------------------------------------------------------+-------
-------------------------------------------------------+-------
-------------------------------------------------------+2 rows in set (0.002 sec)
MariaDB [test]> insert into t values (7);
Query OK, 1 row affected (0.000 sec)
MariaDB [test]> select Key_read_hits, Key_read_misses from information_schema.user_statistics;
--------------
----------------+--------------
----------------+--------------
----------------+1 row in set (0.001 sec)
One way to fix this would be to not collect status for system tables, but that would require a lot of if's all over the place in the handler code.