Is there a corresponding metadata table for the information shown by stats_healthy?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: show stats_healthy的信息有对应的元信息表吗?

| username: DBRE

Is there a corresponding metadata table for the information shown by show stats_healthy? Or can show stats_healthy support order by?

| username: weixiaobing | Original post link

In the mysql database, there is a stats_meta table. It does not support order by, but you can use where conditions to filter.

| username: xingzhenxiang | Original post link

This works too

show stats_healthy where Healthy <100

| username: TiDBer_pkQ5q1l0 | Original post link

ORDER BY is not supported. The health calculation method is based on the comparison of the number of modified rows to the total number of rows.

| username: DBRE | Original post link

How can I get the specific table name from the table_id in the stats_meta table?

| username: 我是咖啡哥 | Original post link

select table_name, TIDB_TABLE_ID from information_schema.tables

There are in tables

| username: 我是咖啡哥 | Original post link

select t.TABLE_SCHEMA, t.table_name, m.* from mysql.stats_meta m, information_schema.tables t where table_id = TIDB_TABLE_ID and TABLE_SCHEMA='test' and table_name='t';
| username: DBRE | Original post link

:+1: :+1: :+1:

| username: DBRE | Original post link

Table Health Information

You can check the health of table statistics and roughly estimate the accuracy of the statistics on the table using SHOW STATS_HEALTHY. When modify_count >= row_count, the health is 0; when modify_count < row_count, the health is (1 - modify_count/row_count) * 100.

According to the official documentation, for non-partitioned tables, you can sort the health of the tables using the following SQL:

SELECT t.TABLE_SCHEMA, t.table_name, m.table_id, FLOOR(IF(m.modify_count >= m.count, 0, (1 - m.modify_count/m.count) * 100)) AS healthy 
FROM mysql.stats_meta m, information_schema.tables t 
WHERE table_id = TIDB_TABLE_ID AND TABLE_SCHEMA = 'xxxxxxxxxx' 
ORDER BY healthy;

It is still unclear how to obtain the health of partitioned tables.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.