A better way to init stats

[xuyifangreeneyes]

Enhancement

Currently, when a tidb-server starts, one thing to do is to init stats. It loads stats meta and indexes' Histogram and TopN(It doesn't load columns' Histogram and TopN in order to reduce stats mem usage. Columns' Histogram and TopN are loaded when they are needed by the optimizer). If there are many tables and partitions, it may take minutes even one hour to init stats, especially when the cluster is under high pressure and the tidb-server restarts. During the period of init stats, no table stats can be used by the optimizer. The optimizer uses pseudo stats to generate plans, which are probably inefficient and put more pressure to the cluster(Besides, the wrong plans due to pseudo stats may be added into plan cache and the optimizer would reuse them even after real stats are loaded, which is another story).

We have the on-demand stats loading mechanism, which have two kinds, async loading and sync loading(turn on by default in v6.5). However, neither of the two kinds can work in the issue. Async loading cannot start until init stats(https://github.com/pingcap/tidb/blob/master/domain/domain.go#L2041-L2083) is finished. Sync loading would not trigger if table stats meta doesn't exist in stats cache(https://github.com/pingcap/tidb/blob/master/statistics/handle/handle_hist.go#L237-L262).

I add time.Sleep(20 * time.Minute) into (*Handle).InitStats to simulate slow init stats. Here is an example to demonstrate the issue:

mysql> create table t1(a int, b int, index idx(a));
Query OK, 0 rows affected (0.02 sec)

-- insert data into t1

mysql> analyze table t1;
Query OK, 0 rows affected, 1 warning (0.05 sec)

Then restart the tidb-server.

mysql> explain select * from t1 where a > 1;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 3333.33  | root      |               | data:Selection_6               |
| └─Selection_6           | 3333.33  | cop[tikv] |               | gt(test.t1.a, 1)               |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> show stats_meta;
Empty set (0.00 sec)

mysql> show stats_histograms;
Empty set (0.00 sec)

@winoros comes up with a better way to init stats. When init stats, we only read stats meta. Specifically, we only read mysql.stats_meta and mysql.stats_histograms. The two system tables are much smaller than other stats-related system tables such as mysql.stats_buckets and mysql.stats_top_n. There are some details:

1. The major advantage is that init stats will be much faster. TiDB can wait for init stats to finish before processing queries, which can avoid the optimizer using pseudo stats to make decisions during the period of init stats.
2. For partitioned tables, if tidb_partition_prune_mode is dynamic(which is the default value), the optimizer only uses global stats and doesn't use partition stats. Currently, parts of partition stats(e.g., indexes' Histogram and TopN) are loaded into memory when init stats, which is unused and takes up much memory. If we use the new way to init stats, unused partition stats won't be loaded into memory.
3. The lite init stats may help us workaround wired bugs such as interface conversion: interface {} is nil, not *memory.bytesLimits #42302.

[chrysan]

Close this enhancement since it's done.