github / github/gh-ost

bad index stats after table cut-over

Open
#875 3 comments 0 reactions 1 assignee Claimed by @timvaillancourt View on GitHub
enhancement performance
Dominant language
Go
Stars
13.6k
Forks
1.4k
Avg merge
2h 31m
Merged PRs (30d)
4

Description

we are facing an issue lately that query plan maybe go wrong in a short time after alter a small table (180,000 rows).

After in-depth analysis, we found a _gho table cut-over before the statistics persisted, the new table initial opening would read persistent data in mysql.innodb_index_stats / mysql.innodb_table_stats, but there is no statistics at this time , and execution plan will go wrong in a short time until persistent recalculation requested.

```
/* Persistent recalculation requested, called from
1) ANALYZE TABLE, or
2) the auto recalculation background thread, or
3) open table if stats do not exist on disk and auto recalc
is enabled */
```

`repro:`

after row-copy complete,query 2 sqls, `show index from _xx_gho` and `mysql.innodb_index_stats`, and after cut-over , query again.

` first stage < after row-copy complete >: `
```
2020-08-17 10:31:48 INFO Row copy complete
2020-08-17 10:31:48 INFO show index from `sthdb`.`_prd_sth_unit_gho`
Key_name| Column_name|Cardinality|
PRIMARY| ID| 11|
idx_DataChange_LastTime| DataChange_LastTime| 11|
2020-08-17 10:31:48 INFO query innodb_index_stats
table_name| index_name| last_update| stat_name|stat_value|sample_size|
_prd_sth_unit_gho| PRIMARY| 2020-08-17 10:31:40| n_diff_pfx01| 0| 1|
_prd_sth_unit_gho| PRIMARY| 2020-08-17 10:31:40| n_leaf_pages| 1| 1|
_prd_sth_unit_gho| PRIMARY| 2020-08-17 10:31:40| size| 1| 1|
_prd_sth_unit_gho| idx_DataChange_LastTime| 2020-08-17 10:31:40| n_diff_pfx01| 0| 1|
_prd_sth_unit_gho| idx_DataChange_LastTime| 2020-08-17 10:31:40| n_diff_pfx02| 0| 1|
_prd_sth_unit_gho| idx_DataChange_LastTime| 2020-08-17 10:31:40| n_leaf_pages| 1| 1|
_prd_sth_unit_gho| idx_DataChange_LastTime| 2020-08-17 10:31:40| size| 1| 1|
```

` second stage < after row-copy complete >: `
```
2020-08-17 10:31:50 INFO query innodb_index_stats
table_name| index_name| last_update| stat_name|stat_value|sample_size|
prd_sth_unit| PRIMARY| 2020-08-17 10:31:40| n_diff_pfx01| 0| 1|
prd_sth_unit| PRIMARY| 2020-08-17 10:31:40| n_leaf_pages| 1| 1|
prd_sth_unit| PRIMARY| 2020-08-17 10:31:40| size| 1| 1|
prd_sth_unit| idx_DataChange_LastTime| 2020-08-17 10:31:40| n_diff_pfx01| 0| 1|
prd_sth_unit| idx_DataChange_LastTime| 2020-08-17 10:31:40| n_diff_pfx02| 0| 1|
prd_sth_unit| idx_DataChange_LastTime| 2020-08-17 10:31:40| n_leaf_pages| 1| 1|
prd_sth_unit| idx_DataChange_LastTime| 2020-08-17 10:31:40| size| 1| 1|
2020-08-17 10:31:50 INFO show index from `sthdb`.`prd_sth_unit`
Key_name| Column_name|Cardinality|
PRIMARY| ID| 0|
idx_DataChange_LastTime| DataChange_LastTime| 0|
```

`solution:`
Add analyze table operation (on the _gho table) after row copy complete, the statistics would be persisted then cut-over table.

Thanks.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.