Wrong result from table_statistics
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
High
|
Unassigned |
Bug Description
It looks like something is wrong with how table_statistics is counted
In this case we have provided 5 rows in result so at least 5 rows should have been locked at
and yet only 4 are shown.
mysql> set global userstat_running=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from y limit 5;
+------+
| i |
+------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+------+
5 rows in set (0.00 sec)
mysql> show table_statistics;
+------
| Table_schema | Table_name | Rows_read | Rows_changed | Rows_changed_
+------
| test | y | 4 | 0 | 0 |
+------
1 row in set (0.00 sec)
Related branches
- Alexey Kopytov (community): Approve
-
Diff: 31 lines (+22/-0)2 files modifiedmysql-test/userstat_bug602047.result (+13/-0)
mysql-test/userstat_bug602047.test (+9/-0)
- Percona developers: Pending requested
-
Diff: 31 lines (+22/-0)2 files modifiedmysql-test/userstat_bug602047.result (+13/-0)
mysql-test/userstat_bug602047.test (+9/-0)
- Alexey Kopytov (community): Approve
- Valentine Gostev (community): Approve (qa)
- Percona developers: Pending requested
-
Diff: 525 lines (+62/-50)16 files modifiedinnodb_adjust_defaults.patch (+4/-4)
innodb_admin_command_base.patch (+1/-1)
innodb_buffer_pool_shm.patch (+2/-2)
innodb_extend_slow.patch (+4/-4)
innodb_fast_checksum.patch (+2/-2)
innodb_fast_shutdown.patch (+2/-2)
innodb_files_extend.patch (+3/-3)
innodb_fix_misc.patch (+1/-1)
innodb_lru_dump_restore.patch (+2/-2)
innodb_pass_corrupt_table.patch (+12/-12)
innodb_purge_thread.patch (+2/-2)
innodb_recovery_patches.patch (+2/-2)
innodb_separate_doublewrite.patch (+2/-2)
innodb_show_lock_name.patch (+4/-4)
innodb_show_sys_tables.patch (+1/-1)
innodb_stats.patch (+18/-6)
Changed in percona-server: | |
status: | New → Confirmed |
Changed in percona-server: | |
status: | Confirmed → Triaged |
assignee: | Valentine Gostev (core-longbow) → Yasufumi Kinoshita (yasufumi-kinoshita) |
Changed in percona-server: | |
milestone: | none → 5.1-12.6 |
Changed in percona-server: | |
status: | Triaged → Fix Committed |
Changed in percona-server: | |
status: | Fix Committed → Fix Released |
This bug is much more serious than it sounds -- it causes all single-row lookups to not get counted by userstats patch at all. This is because userstats is incrementing counters by "(number rows read) - 1" each time, and when only 1 row is read, then counter is not incremented. This makes userstats patch useless for identifying unused indexes because it does not count any "SELECT ... WHERE column = $value" queries that match only one row.
I have tested this in latest releases: 5.1.53-11.7, 5.1.53-12.4, and 5.1.54-12.5.
Problem does not affect MyISAM, only InnoDB/XtraDB.
Here are some examples demonstrating this. I have attached table create and insert statement at end of comment.
### start with innodb table and clean counters
mysql [localhost] {msandbox} (test) > select * from information_ schema. index_statistic s;
Empty set (0.00 sec)
### read single row
mysql [localhost] {msandbox} (test) > select id from test where data='abd';
+----+
| id |
+----+
| 3 |
+----+
1 row in set (0.00 sec)
### counter does not change schema. index_statistic s;
mysql [localhost] {msandbox} (test) > select * from information_
Empty set (0.00 sec)
### switch to myisam, then read single row
mysql [localhost] {msandbox} (test) > alter table test engine=myisam;
Query OK, 10 rows affected (0.23 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > select id from test where data='abd';
+----+
| id |
+----+
| 3 |
+----+
1 row in set (0.00 sec)
### counter increases by 1 as expected ! schema. index_statistic s; ------- -+----- ------- +------ ------+ ------- ----+ ------- -+----- ------- +------ ------+ ------- ----+ ------- -+----- ------- +------ ------+ ------- ----+
mysql [localhost] {msandbox} (test) > select * from information_
+------
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+------
| test | test | data | 1 |
+------
1 row in set (0.00 sec)
### read 2 rows now
mysql [localhost] {msandbox} (test) > select id from test where data='abc';
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
### and counter increases by 2, as expected! schema. index_statistic s; ------- -+----- ------- +------ ------+ ------- ----+ ------- -+----- ------- +------ ------+ ------- ----+ ------- -+----- ------- +------ ------+ ------- ----+
mysql [localhost] {msandbox} (test) > select * from information_
+------
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+------
| test | test | data | 3 |
+------
1 row in set (0.00 sec)
### switch back to innodb
mysql [localhost] {msandbox} (test) > alter table test engine=innodb;
Query OK, 10 rows affected (0.31 sec)
Records: 10 Duplicates: 0 Warnings: 0
### read same 2 rows again
mysql [localhost] {msandbox} (test) > select id from test where data='abc';
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
### but now counter only increases by 1 :( schema. index_statistic s; ------- -+----- ------- +------ ------+ ------- ----+ ------- -+----- ------- +------ ------+ ------- ----+
mysql [localhost] {msandbox} (test) > select * from information_
+------
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+------
| test | test | data | 4 |...