Percona Server with XtraDB

Wrong result from table_statistics

Reported by Peter Zaitsev on 2010-07-05
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Server
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_x_#indexes |
+--------------+------------+-----------+--------------+-------------------------+
| test | y | 4 | 0 | 0 |
+--------------+------------+-----------+--------------+-------------------------+
1 row in set (0.00 sec)

Download full text (3.8 KiB)

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_statistics;
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
mysql [localhost] {msandbox} (test) > select * from information_schema.index_statistics;
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 !
mysql [localhost] {msandbox} (test) > select * from information_schema.index_statistics;
+--------------+------------+------------+-----------+
| 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!
mysql [localhost] {msandbox} (test) > select * from information_schema.index_statistics;
+--------------+------------+------------+-----------+
| 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 :(
mysql [localhost] {msandbox} (test) > select * from information_schema.index_statistics;
+--------------+------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+------------+-----------+
| test | test | data | 4 |...

Read more...

Changed in percona-server:
importance: Undecided → Critical
importance: Critical → High
Vadim Tkachenko (vadim-tk) wrote :

provide test-case

Changed in percona-server:
assignee: nobody → Valentine Gostev (core-longbow)

My initial comment included CREATE and INSERT statements for the sample data that I used in the examples I posted. If that is not sufficient test case, I can upload additional samples, just let me know.

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
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers