Description:
Online-schema-change tool such as gh-ost follows a common pattern. During migration, existing rows are inserted to a conceptual range in the new table, and new rows could be added concurrently beyond the range. For a table with very wide rows, which is common in use cases that store serialized objects in MySQL, migrating existing rows will cause page splitting right before the end of the range, generating many one-row pages.
The sampling algorithm (dict0stats.cc) used by ANALYZE for persistent statistics can not handle these one-row pages, and usually estimates n_diff as zero giving a false illusion that the table is empty or some significantly under-estimated value. Note that n_diff for the primary key is a very important estimation in that it stands for the rows of the table. Such an abnormal estimation has unexpected bad consequences in planning and external tools.
The reason is that the sampling algorithm by design needs some adjustments to get a good NDV estimation across pages, which effectively ignores one-row pages. However, primary-key columns do not need such adjustment, because the number of distinct values is independent on adjacent pages.
In contrast, sampling secondary index does not suffer from this problem, because the limited total width of secondary index columns effectively avoids one-row pages.
How to repeat:
# mtr testcase
create table t1(c1 int primary key, c2 text(8000)) engine=innodb;
--echo # construct table with 9999 records
--let $total_rows_number=9999
--let $iterator=1
begin;
while ($iterator < $total_rows_number)
{
--eval insert into t1 values($iterator, repeat('a', 8000));
--inc $iterator
}
commit;
create table t2 (c1 int primary key, c2 text CHARACTER SET latin1);
# Simulate inserting new rows
insert t2 select 10000, c2 from t1 limit 1;
# Simulate migrating old rows
insert t2 select * from t1;
# Assuming innodb_stats_persistent_sample_pages being 20
analyze table t2;
select TABLE_ROWS from information_schema.tables where table_name ='t2';
select * from mysql.innodb_table_stats where table_name = 't2';
The unexpected result is:
mysql> analyze table t2;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t2 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.04 sec)
mysql> select TABLE_ROWS from information_schema.tables where table_name ='t2';
+------------+
| TABLE_ROWS |
+------------+
| 0 |
+------------+
1 row in set (0.00 sec)
mysql> select * from mysql.innodb_table_stats where table_name = 't2';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test | t2 | 2021-09-15 15:26:19 | 0 | 10027 | 0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)
mysql> select * from mysql.innodb_index_stats where table_name = 't2';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test | t2 | PRIMARY | 2021-09-15 15:26:19 | n_diff_pfx01 | 0 | 20 | c1 |
| test | t2 | PRIMARY | 2021-09-15 15:26:19 | n_leaf_pages | 9997 | NULL | Number of leaf pages in the index |
| test | t2 | PRIMARY | 2021-09-15 15:26:19 | size | 10027 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
3 rows in set (0.00 sec)
Suggested fix:
Avoid the inapplicable adjustments for the primary key. See the enclosed patch in the next comment.
if (n_diff_on_leaf_page > 0) {
n_diff_on_leaf_page--;
}
With the fix, the expected result is:
mysql> analyze table t2;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t2 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.04 sec)
mysql> select TABLE_ROWS from information_schema.tables where table_name ='t2';
+------------+
| TABLE_ROWS |
+------------+
| 9998 |
+------------+
1 row in set (0.00 sec)
mysql> select * from mysql.innodb_index_stats where table_name = 't2';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test | t2 | PRIMARY | 2021-09-15 15:26:19 | n_diff_pfx01 | 9997 | 20 | c1 |
| test | t2 | PRIMARY | 2021-09-15 15:26:19 | n_leaf_pages | 9997 | NULL | Number of leaf pages in the index |
| test | t2 | PRIMARY | 2021-09-15 15:26:19 | size | 10027 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
3 rows in set (0.00 sec)
Description: Online-schema-change tool such as gh-ost follows a common pattern. During migration, existing rows are inserted to a conceptual range in the new table, and new rows could be added concurrently beyond the range. For a table with very wide rows, which is common in use cases that store serialized objects in MySQL, migrating existing rows will cause page splitting right before the end of the range, generating many one-row pages. The sampling algorithm (dict0stats.cc) used by ANALYZE for persistent statistics can not handle these one-row pages, and usually estimates n_diff as zero giving a false illusion that the table is empty or some significantly under-estimated value. Note that n_diff for the primary key is a very important estimation in that it stands for the rows of the table. Such an abnormal estimation has unexpected bad consequences in planning and external tools. The reason is that the sampling algorithm by design needs some adjustments to get a good NDV estimation across pages, which effectively ignores one-row pages. However, primary-key columns do not need such adjustment, because the number of distinct values is independent on adjacent pages. In contrast, sampling secondary index does not suffer from this problem, because the limited total width of secondary index columns effectively avoids one-row pages. How to repeat: # mtr testcase create table t1(c1 int primary key, c2 text(8000)) engine=innodb; --echo # construct table with 9999 records --let $total_rows_number=9999 --let $iterator=1 begin; while ($iterator < $total_rows_number) { --eval insert into t1 values($iterator, repeat('a', 8000)); --inc $iterator } commit; create table t2 (c1 int primary key, c2 text CHARACTER SET latin1); # Simulate inserting new rows insert t2 select 10000, c2 from t1 limit 1; # Simulate migrating old rows insert t2 select * from t1; # Assuming innodb_stats_persistent_sample_pages being 20 analyze table t2; select TABLE_ROWS from information_schema.tables where table_name ='t2'; select * from mysql.innodb_table_stats where table_name = 't2'; The unexpected result is: mysql> analyze table t2; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t2 | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.04 sec) mysql> select TABLE_ROWS from information_schema.tables where table_name ='t2'; +------------+ | TABLE_ROWS | +------------+ | 0 | +------------+ 1 row in set (0.00 sec) mysql> select * from mysql.innodb_table_stats where table_name = 't2'; +---------------+------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+--------+----------------------+--------------------------+ | test | t2 | 2021-09-15 15:26:19 | 0 | 10027 | 0 | +---------------+------------+---------------------+--------+----------------------+--------------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.innodb_index_stats where table_name = 't2'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test | t2 | PRIMARY | 2021-09-15 15:26:19 | n_diff_pfx01 | 0 | 20 | c1 | | test | t2 | PRIMARY | 2021-09-15 15:26:19 | n_leaf_pages | 9997 | NULL | Number of leaf pages in the index | | test | t2 | PRIMARY | 2021-09-15 15:26:19 | size | 10027 | NULL | Number of pages in the index | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 3 rows in set (0.00 sec) Suggested fix: Avoid the inapplicable adjustments for the primary key. See the enclosed patch in the next comment. if (n_diff_on_leaf_page > 0) { n_diff_on_leaf_page--; } With the fix, the expected result is: mysql> analyze table t2; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t2 | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.04 sec) mysql> select TABLE_ROWS from information_schema.tables where table_name ='t2'; +------------+ | TABLE_ROWS | +------------+ | 9998 | +------------+ 1 row in set (0.00 sec) mysql> select * from mysql.innodb_index_stats where table_name = 't2'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test | t2 | PRIMARY | 2021-09-15 15:26:19 | n_diff_pfx01 | 9997 | 20 | c1 | | test | t2 | PRIMARY | 2021-09-15 15:26:19 | n_leaf_pages | 9997 | NULL | Number of leaf pages in the index | | test | t2 | PRIMARY | 2021-09-15 15:26:19 | size | 10027 | NULL | Number of pages in the index | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 3 rows in set (0.00 sec)