|
In mysql-5.6, the result is 5. The table has actually 5 rows:
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
Moreover, if I rerun the EXPLAIN, I get rows=6 change to rows=5. Sometimes it happens on the second re-run. Sometimes even the first EXPLAIN returns rows=5.
|
|
Debugging, discovered that mysql-5.6 has a background statistics update process. See storage/innobase/dict/dict0stats_bg.cc, dict_stats_thread. This explains why EXPLAIN result changes when one re-runs EXPLAIN.
|
|
On the question of why does statistics says rows=6 when the table has only 5 rows:
(gdb) c
Continuing.
Hardware watchpoint 9: *$a3
Old value = 0
New value = 1
dict_table_n_rows_inc (table=0x7fffa4077d58) at /home/psergey/dev2/10.0-monty/storage/innobase/include/dict0dict.ic:415
(gdb) c
Continuing.
Hardware watchpoint 9: *$a3
Old value = 1
New value = 2
dict_table_n_rows_inc (table=0x7fffa4077d58) at /home/psergey/dev2/10.0-monty/storage/innobase/include/dict0dict.ic:415
(gdb) c
Continuing.
Hardware watchpoint 9: *$a3
Old value = 2
New value = 3
dict_table_n_rows_inc (table=0x7fffa4077d58) at /home/psergey/dev2/10.0-monty/storage/innobase/include/dict0dict.ic:415
(gdb) c
Continuing.
[Switching to Thread 0x7fffc2ffd700 (LWP 27712)]
Hardware watchpoint 9: *$a3
Old value = 3
New value = 4
dict_stats_update_persistent (table=0x7fffa4077d58) at /home/psergey/dev2/10.0-monty/storage/innobase/dict/dict0stats.cc:2065
(gdb) p index->stat_index_size
$101 = 1
(gdb)
(gdb) p table->stat_n_rows
$102 = 4
(gdb) c
Continuing.
[Switching to Thread 0x7fffe4193700 (LWP 27715)]
Hardware watchpoint 9: *$a3
Old value = 4
New value = 5
dict_table_n_rows_inc (table=0x7fffa4077d58) at /home/psergey/dev2/10.0-monty/storage/innobase/include/dict0dict.ic:415
(gdb) c
Continuing.
Hardware watchpoint 9: *$a3
Old value = 5
New value = 6
dict_table_n_rows_inc (table=0x7fffa4077d58) at /home/psergey/dev2/10.0-monty/storage/innobase/include/dict0dict.ic:415
(gdb)
|
|
(gdb) c
Continuing.
[Switching to Thread 0x7fffc2ffd700 (LWP 27712)]
Hardware watchpoint 9: *$a3
Old value = 6
New value = 5
|
|
I can observe the same thing in debugger on mysql-5.6.
I am running the statement
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
and see:
(gdb) set $q=& table->stat_n_rows
(gdb) watch *$q
Hardware watchpoint 5: *$q
(gdb) c
Continuing.
Hardware watchpoint 5: *$q
Old value = 0
New value = 1
0x0886379d in dict_table_n_rows_inc (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/include/dict0dict.ic:412
(gdb) c
Continuing.
Hardware watchpoint 5: *$q
Old value = 1
New value = 2
0x0886379d in dict_table_n_rows_inc (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/include/dict0dict.ic:412
(gdb) c
Continuing.
Hardware watchpoint 5: *$q
Old value = 2
New value = 3
0x0886379d in dict_table_n_rows_inc (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/include/dict0dict.ic:412
(gdb) c
Continuing.
[Switching to Thread 0xa128bb90 (LWP 15090)]
Hardware watchpoint 5: *$q
Old value = 3
New value = 4
0x0899099d in dict_stats_update_persistent (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/dict/dict0stats.cc:2044
(gdb) c
Continuing.
[Switching to Thread 0xa4fffb90 (LWP 15137)]
Hardware watchpoint 5: *$q
Old value = 4
New value = 5
0x0886379d in dict_table_n_rows_inc (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/include/dict0dict.ic:412
(gdb) c
Continuing.
Hardware watchpoint 5: *$q
Old value = 5
New value = 6
0x0886379d in dict_table_n_rows_inc (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/include/dict0dict.ic:412
(gdb) c
Continuing.
-
-
- At this point, the INSERT query is finished. In the client
- I see "Query OK, 5 rows affected"
- Note that the stats is 6. I suppose, if one gets a big enough table, the
- difference can be bigger.
[Switching to Thread 0xa128bb90 (LWP 15090)]
Breakpoint 4, dict_stats_update_persistent (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/dict/dict0stats.cc:2044
(gdb) c
Continuing.
Hardware watchpoint 5: *$q
Old value = 6
New value = 5
0x0899099d in dict_stats_update_persistent (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/dict/dict0stats.cc:2044
(gdb)
|
|
Wondering why mysql-5.6 doesn't have the problem with join_outer_innodb.test...
it seems, the cause is vasil.dimov@oracle.com-20120521133620-glj6l0ntcsrz0wbl ... They added ANALYZE TABLE statements to stabilize the statistics.
|
|
mysql 5.6's use of "-- disable_result_log" in test files was not helpful when analyzing this test failure...
|
|
Fixed by making mtr to run the testsuite without persistent stats.
|
|
Starting with MariaDB Server 10.6.5, we will no longer globally disable innodb_stats_persistent in all mtr tests. Instead, only a few special tests will disable statistics, adjust timeouts or whatever.
|