Table Statistics

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

Original topic: 表的统计信息

| username: 等一分钟

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5.1

[Encountered Problem: Problem Phenomenon and Impact]

The cluster has a large amount of data and many tables, and there are often slow SQL queries. Is there any way to find out which tables have problematic statistics?

| username: 等一分钟 | Original post link

Is there a table to check the health of the tables?

| username: tidb菜鸟一只 | Original post link

SHOW stats_healthy

| username: DBRE | Original post link

There are no tables to check, but non-partitioned tables can be calculated.

You can check the health of table statistics and roughly estimate the accuracy of the statistics on the table through 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 with 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: 我是咖啡哥 | Original post link

Directly use SQL :joy:
| username: 我是咖啡哥 | Original post link

So early, huh? :joy:

| username: DBRE | Original post link

The early bird catches the worm :face_with_peeking_eye:

| username: Alex920 | Original post link

This is excellent. Reaching the goal so early.

| username: 等一分钟 | Original post link

Thank you, everyone.

| username: jansu-dev | Original post link

Partition tables can treat partitions as tables, using a function to calculate their health – stats: support show stats for partition table by lamxTyler · Pull Request #8023 · pingcap/tidb (github.com)

| username: system | Original post link

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