TiDB OOM requires analyzing all tables upon restart

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

Original topic: tidb oom重启都要analyze所有表

| username: cy6301567

Does TiDB need to analyze all tables after an OOM restart? Currently, there is only one node in TiDB. How can I avoid analyzing all tables after a restart?

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

You can set the tidb_enable_auto_analyze parameter to off to disable automatic statistics collection. However, theoretically, an OOM causing TiDB to restart should not restart all analyze tasks.

| username: Kongdom | Original post link

You can analyze both in the morning and evening, otherwise the execution plan will not be accurate.

| username: Inkjade | Original post link

I haven’t encountered TiDB OOM during a restart. When restarting to analyze all tables, you can temporarily disable tidb_enable_auto_analyze, analyze the operating system logs, and analyze specific SQL statements to find the real cause of the OOM.

  1. Check the TiDB logs to determine the time point:

    grep 'risk os OOM' tidb.log
    
  2. Identify the specific SQL statements, for example, SQL statements using more than 1G of memory on 2023-07-12:

    grep -i 'mem_max' tidb_slow_query-2023-07-12T01-58-23.006.log | awk -F ':' '{if($2 > 1036900000) print $2}'
    
  3. Make corresponding SQL optimization adjustments.

  4. Check which specific table is being analyzed and determine if manual table analysis is needed.

| username: cy6301567 | Original post link

Currently, there is only one TiDB node on one machine, with TiKV and PD deployed on three machines.

| username: cy6301567 | Original post link

I think so too, but every time I restart, I have to manually trigger the table statistics, otherwise the query plan gets messed up.

| username: cy6301567 | Original post link

It is now automatic.

| username: cy6301567 | Original post link

Okay, I’ll give it a try.

| username: zhanggame1 | Original post link

Did you disable statistics persistence? You can refer to this:

| username: cy6301567 | Original post link

The issue didn’t occur after setting up two TiDB nodes.