docs/design/2025-05-26-refresh-stats-command.md
This document proposes a new SQL command to refresh the statistics for the specified tables. This command is useful for the BR tool to refresh the statistics for the tables that have been restored. It also can be used to debug in-memory statistics issues.
Recently, we have a project that aims to speed up statistics backup and restore for some key customers. In the proposal, we want to perform the physical backup and restore for the stats-related tables; it would significantly reduce the time for reading and writing the stats by using JSON files.
If we implement this solution, it would introduce another problem. The backup and restore operations happen after the cluster has started, which means they occur after the statistics are initialized. Therefore, we still need a mechanism to ensure that TiDB nodes load the latest statistics into memory and are ready to serve user queries after the restore operation. Otherwise, it wouldn't be able to use the latest statistics in time. One workaround is to ask users to perform a full restart of the TiDB nodes, which would trigger stats initialization again during startup. However, this is obviously not ideal for users. Therefore, we plan to introduce a new SQL command that the BR tool can use to trigger stats refreshment for the tables that have been restored.
Another useful use case for this command is when we need to debug in-memory statistics issues. We can leverage this command to force a refresh of the in-memory stats without restarting the cluster or re-analyzing the table.
This command looks as follows:
REFRESH STATS <TARGETS> [FULL | LITE] [CLUSTER];
REFRESH STATS db1.tbl1, tbl2, db2.*, *.* FULL CLUSTER;
TARGETS takes a list of targets separated by comma, which can be any of the following formats.
FULL | LITE names the type of refreshing statistics.
FULL refers to lite-init-stats=falseLITE refers to lite-init-stats=trueCLUSTER is set, refreshing statistics happens for all TiDB instances. Otherwise, refreshing statistics happens to the TiDB instance connected only.Users with the SELECT privilege on the target tables, as well as the BR tool with the ADMIN_RESTORE privilege, can use this command to refresh the statistics for the specified tables.
Implement the Command: Utilize the existing statistics initialization functionality to support the specified syntax while enforcing the necessary permission checks.Cluster-Wide Execution: Ensure that running the command on a single node can successfully update statistics across all nodes in the cluster.To implement this basic feature, we can extend the current functionality for both Lite and non-Lite stats initialization. Then, we can retrieve all table information specified in the target list and pass it to the respective lite and non-lite stats refreshing functions.
The main challenge is how to support the CLUSTER flag, as the statistics are stored in the memory of each TiDB node. Since users, including BR, may connect to only a single TiDB node, we need to find a way to refresh the statistics on other nodes, even if the command is sent to just one node. So, we will mainly focus on this challenge and explore the available ways to achieve it.
In TiDB, the KILL statement allows users to terminate a connection on any TiDB instance within the current cluster. It achieves this by leveraging the TiDB coprocessor framework to distribute the KILL command across different TiDB nodes.
We can adopt a similar approach to implement the REFRESH STATS command. Essentially, we need to implement a new coprocessor operator to send and handle the coprocessor request across different TiDB nodes.
The plan cache module has similar requirements, as it needs to flush the plan cache for tables across all TiDB nodes. Therefore, when implementing the REFRESH STATS command, we should design the infrastructure to be reusable for both statistics refreshing and plan cache flushing operations.
First, we need to define a new type of executor in the protobuf file:
enum ExecType {
...
TypeBroadcastQuery = 17;
}
message Executor {
...
optional BroadcastQuery broadcast_query = 21;
}
message BroadcastQuery { optional string query = 1; }
We need to use this set of protobuf messages to pass the SQL statement between different TiDB nodes.
To enable execution of the REFRESH STATS command in TiDB, we first need to define the REFRESH STATS statement syntax to support parsing this new command.
RefreshStatsStmt:
"REFRESH" "STATS" RefreshObjectList
{
$$ = &ast.RefreshStatsStmt{
RefreshObjects: $3.([]*ast.RefreshObject),
}
}
RefreshObjectList:
RefreshObject
{
$$ = []*ast.RefreshObject{$1.(*ast.RefreshObject)}
}
| RefreshObjectList ',' RefreshObject
{
$$ = append($1.([]*ast.RefreshObject), $3.(*ast.RefreshObject))
}
RefreshObject:
'*' '.' '*'
{
$$ = &ast.RefreshObject{
RefreshObjectScope: ast.RefreshObjectScopeGlobal,
}
}
| Identifier '.' '*'
{
$$ = &ast.RefreshObject{
RefreshObjectScope: ast.RefreshObjectScopeDatabase,
DBName: ast.NewCIStr($1),
}
}
| Identifier '.' Identifier
{
$$ = &ast.RefreshObject{
RefreshObjectScope: ast.RefreshObjectScopeTable,
DBName: ast.NewCIStr($1),
TableName: ast.NewCIStr($3),
}
}
| Identifier
{
$$ = &ast.RefreshObject{
RefreshObjectScope: ast.RefreshObjectScopeTable,
TableName: ast.NewCIStr($1),
}
}
Every time we receive a REFRESH STATS statement, it is parsed as an ast.RefreshStatsStmt, and we use the simple executor to handle and execute it.
func (e *SimpleExec) Next(ctx context.Context, _ *chunk.Chunk) (err error) {
...
case *ast.InitStatsStmt:
err = e.executeInitStats(ctx, x)
...
}
func (e *SimpleExec) executeRefreshStats(ctx context.Context, s *ast.RefreshStatsStmt) error {
if e.IsFromRemote {
// TODO: do the real refresh stats
return nil
}
return broadcast(ctx, e.Ctx(), s.Text())
}
func broadcast(ctx context.Context, sctx sessionctx.Context, sql string) error {
broadcastExec := &tipb.Executor{
Tp: tipb.ExecType_TypeBroadcastQuery,
BroadcastQuery: &tipb.BroadcastQuery{
Query: &sql,
},
}
dagReq := &tipb.DAGRequest{}
...
dagReq.Executors = []*tipb.Executor{broadcastExec}
var builder distsql.RequestBuilder
kvReq, err := builder.
SetDAGRequest(dagReq).
SetFromSessionVars(sctx.GetDistSQLCtx()).
SetFromInfoSchema(sctx.GetInfoSchema()).
SetStoreType(kv.TiDB).
SetTiDBServerID(0).
SetStartTS(math.MaxUint64). // To make check visibility success.
Build()
if err != nil {
return err
}
resp := sctx.GetClient().Send(ctx, kvReq, sctx.GetSessionVars().KVVars, &kv.ClientSendOption{})
...
for {
subset, err := resp.Next(ctx)
if err != nil {
return errors.Trace(err)
}
if subset == nil {
break // all remote tasks finished cleanly
}
}
logutil.BgLogger().Info("Broadcast query", zap.String("sql", sql))
return nil
}
When TiDB executes this statement, it first checks whether the statement comes from a remote source. If it does, TiDB performs the actual stats refreshing and returns the result. Otherwise, it indicates that the command is issued by a user or BR, so we need to broadcast it to all TiDB nodes, including the current node, using the coprocessor framework.
To achieve this, we set the store type to TiDB, which ensures the coprocessor only sends the request to TiDB nodes. Additionally, we set the TiDB server ID to 0, which means a coprocessor task will be generated for each TiDB node.
When a TiDB node receives a coprocessor request, it constructs a DAG executor and converts the protobuf message into an actual physical plan, specifically a PhysicalSimpleWrapper. During this process, the protobuf message is translated into an REFRESH STATS statement, which is then executed using the above executor. Since we know this command originates from a remote source, we also set the IsFromRemote flag accordingly during the conversion.
case tipb.ExecType_TypeBroadcastQuery:
p, err = b.pbToBroadcastQuery(e)
func (b *PBPlanBuilder) pbToBroadcastQuery(e *tipb.Executor) (base.PhysicalPlan, error) {
pa := parser.New()
stmt, err := pa.ParseOneStmt(*e.BroadcastQuery.Query, "", "")
if err != nil {
return nil, errors.Trace(err)
}
simple := Simple{Statement: stmt, IsFromRemote: true, ResolveCtx: resolve.NewContext()}
return &PhysicalSimpleWrapper{Inner: simple}, nil
}
Then, it will be executed by the SimpleExec executor by calling the executeRefreshStats function.
The overall workflow is as follows:
Since we use the coprocessor framework, we don't need to introduce any new concurrency control mechanism. We can simply leverage the concurrency control capabilities provided by the coprocessor framework.
Specifically, we can reuse the tidb_distsql_scan_concurrency variable to decide how many tasks should be handled simultaneously, or we can introduce a new variable to control how many requests should be sent.
We should thoroughly evaluate the performance impact of this command on TiKV's online workload through comprehensive testing.
We can also leverage the timeout capabilities provided by the coprocessor framework. By overriding MaxExecutionTime, we can set a custom timeout for the task. The default timeout is 0, which means there is no timeout. If you want to specify a timeout, you can use the following formula as a reference:
Timeout = min(
((Total Tables * Number of Nodes) / 1M tables per minute) + 1 minute buffer,
15 minutes
)
The timeout for a batch of tasks is calculated based on the number of tables and nodes. In this formula, we assume that initializing statistics for 1 million tables takes approximately 1 minute. This estimate should be validated through testing. And we add a buffer of 1 minute to account for minor delays. However, to prevent excessively long waits, the maximum timeout is capped at 15 minutes, as anything beyond that would be considered unacceptable.
We should test the following scenarios:
We will implement comprehensive functional tests to verify the core functionality. These tests will:
There are two main scenarios we should test:
No compatibility tests are needed for this feature.
We need to conduct two key performance tests:
The results of these tests will help us determine appropriate default values for concurrency limits and timeout settings.
This feature may have some performance impacts on the online workload in TiKV. We should thoroughly evaluate the performance impact through comprehensive testing.
To workaround the issue of stats initialization after the BR restore operation, we can ask users to perform a full restart of the TiDB nodes, which would trigger stats initialization again during startup.