How should the tidb_stmt_summary_max_stmt_count parameter be set?

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

Original topic: tidb_stmt_summary_max_stmt_count参数应该怎么设置?

| username: 帅的掉渣

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Encountered Problem] The default value of tidb_stmt_summary_max_stmt_count is 3000, and I found that the statements_summary_evicted table always has evictions (maximum 2300). I changed it to 6000, but there are still evictions (maximum 2120). The official documentation suggests that there should be no evictions. Can I change it to a larger number like 100000? Will it have any impact? Why doesn’t the official default to a larger number? What settings do you guys use? Thanks.
[Resource Configuration] ti-db1 machine with 60G memory, 27G used. ti-db2 machine with 60G memory, 20G used.
[Attachments: Screenshots/Logs/Monitoring]


image
image

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

How many rows of data are in this table now?

| username: 帅的掉渣 | Original post link

image

| username: 小龙虾爱大龙虾 | Original post link

Why do you need to record everything? This stuff takes up memory. Just remember the important parts.

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

This modification mainly takes up some memory. If 6000 is still small, you can change it to 10000, but storing so many SQLs doesn’t seem to be very useful…

| username: 帅的掉渣 | Original post link

Mainly for statement analysis in tidb-dashboard, otherwise, the “other” category will appear.
The official documentation also states that if this situation occurs, it will have some impact on performance, so it is recommended to increase it:


It will only retain the most recent 24 statements under the tidb_stmt_summary_max_stmt_count limit. If there is enough memory, can it be increased indefinitely?

| username: redgame | Original post link

It can also be changed to one hundred thousand…

| username: TiDBer_aaO4sU46 | Original post link

You can add until satisfied.

| username: 帅的掉渣 | Original post link

What are your TiDB configurations? Do you use the statement analysis feature in TiDB Dashboard?

| username: 帅的掉渣 | Original post link

@tidb team, do you have any suggestions if there is enough memory?

| username: Soysauce520 | Original post link

Don’t be afraid of OOM when using SQL analysis, you can increase it :bomb:

| username: 小于同学 | Original post link

It can be increased.

| username: 帅的掉渣 | Original post link

Are there any other impacts, such as the SQL analysis OOM mentioned above? How does TiDB recommend configuring it? Currently, the memory of the TiDB server is sufficient.

| username: TiDBer_小阿飞 | Original post link

Current Limitations

Since statement summary tables are stored in memory by default, the statement summary will be lost after the TiDB server restarts.

To address this issue, TiDB v6.6.0 experimentally introduced the statement summary persistence feature, which is disabled by default. When this feature is enabled, historical data is no longer stored in memory but is directly written to disk. After the TiDB server restarts, historical data remains available.

Persisting Statements Summary

Warning

The statement summary persistence feature is currently experimental and is not recommended for use in production environments. This feature may change or be removed without prior notice. If you encounter any bugs, please report them by submitting an issue on GitHub.

As described in the current limitations section, by default, the statements summary is maintained only in memory, and all statements summary data will be lost if the TiDB server restarts. Starting from v6.6.0, TiDB experimentally provides the configuration option tidb_stmt_summary_enable_persistent to allow users to control whether to enable statements summary persistence.

To enable statements summary persistence, you can add the following configuration to the TiDB configuration file:

[instance]
tidb_stmt_summary_enable_persistent = true
# The following configurations are default values and can be adjusted as needed.
# tidb_stmt_summary_filename = "tidb-statements.log"
# tidb_stmt_summary_file_max_days = 3
# tidb_stmt_summary_file_max_size = 64 # MiB
# tidb_stmt_summary_file_max_backups = 0

After enabling statements summary persistence, only the current real-time data is maintained in memory, and historical data is no longer maintained in memory. Historical data is directly written to disk files after generation, with the write cycle referenced in the parameter configuration section’s tidb_stmt_summary_refresh_interval. Subsequent queries against the statements_summary_history or cluster_statements_summary_history tables will return results combining data from both memory and disk.

| username: TiDBer_小阿飞 | Original post link

The experimental feature of statement summary tables can be written to disk, so there’s no need to worry about OOM anymore. However, since it is an experimental feature, there are still risks of instability and bugs. Use with caution!

| username: kelvin | Original post link

Based on actual production, whatever works best.

| username: zhang_2023 | Original post link

Adjusting it too much will significantly affect performance.

| username: 帅的掉渣 | Original post link

If the statistics are incomplete (exceeding the quantity will be allocated to others), what is the purpose of SQL statement analysis?

| username: 有猫万事足 | Original post link

In fact, most problems follow the 20/80 principle. The key issue is not how much you count, but whether these statistics can identify the 20% that cause 80% of the impact. If not, then just adjust it further.

| username: buddyyuan | Original post link

It’s not recommended to change this, as increasing it could pose risks. The reason is that if too many SQL statements are stored, sometimes when you query and need to decode this plan, it could potentially cause the database to OOM. There is a corresponding issue for this. Just set it to 3000; the extra ones are not very active anyway, so it doesn’t really matter.