Why does TiDB crash and restart when querying something that can be queried in MySQL?

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

Original topic: 在mysql能查出来,在tidb查询,tidb直接崩溃重启,是什么原因?

| username: czxin788

[Test Environment for TiDB]
[TiDB Version] v7.3
[Reproduction Path]
[Encountered Problem: Problem Phenomenon and Impact]
We have a super complex SQL query that returns results in 20 seconds in MySQL. However, when we run it in TiDB, after waiting for about 5 minutes, TiDB crashes and automatically restarts. What could be the general cause of this?
[Resource Configuration]

| username: WalterWj | Original post link

If deploying in a mixed environment, perform resource isolation and enable result persistence for tidb-server.
You can try adding a few machines for TiFlash.

| username: 像风一样的男子 | Original post link

TiDB ran out of memory, please post the SQL execution plan.

| username: Miracle | Original post link

Please provide the TiDB cluster topology and resource configuration.

| username: Inkjade | Original post link

Attach the cluster configuration resource deployment, topology, SQL execution plan, and OOM log information. Only then can we analyze the specific reasons.

| username: zhanggame1 | Original post link

What are the hardware requirements for TiDB?

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

Monitoring and further analysis of logs are required.

| username: czxin788 | Original post link

Execution plans are in the attachments.

Machine configuration: 256GB memory, 64 CPUs.
TiDB and MySQL are on the same machine, with TiDB and this MySQL keeping data synchronized.
TiDB is deployed as a pseudo-cluster on one machine to test whether TiDB’s performance is really better than MySQL’s.
The actual situation is not entirely so; some SQL queries execute much faster on TiDB, while others become slower or even fail to execute. For example, the one in the attachment can directly cause TiDB to restart.

| username: 托马斯滑板鞋 | Original post link

What do you mean?
MySQL and TiDB on the same physical machine?
How is TiDB deployed? Three TiKV on different ports? Or 1 PD, 1 TiKV, and 1 TiDB like this?

| username: 托马斯滑板鞋 | Original post link

Are the table structures the same on both sides?
In TiDB, there is a full table scan:

TableFullScan_1907                       | 26477481.00 | cop[tikv] | table:m  
TableFullScan_1910                           | 1147772.00  | cop[tikv] | table:w  

In MySQL, is there a primary key?

|  1 | PRIMARY     | m            | NULL       | ref    | idx_NumericalOrder,idx_NumericalOrder_PigID,idx_NumericalOrder_BatchID_PigID,idx_NumericalOrder_PigHouseUnitID_PigID,idx_NumericalOrderDetail_IsIn,idx_NumericalOrder_IsIn_Abstract | idx_NumericalOrderDetail_IsIn                | 8       | zhongguo_qla_business.w.NumericalOrderDetail                                  |    1 |   100.00 | Using index condition; Using where                     |
|  1 | PRIMARY     | w            | NULL       | ref    | wm_warehousestockpigextend_NumericalOrderDetail_index,idx_Guid                                                                                                                      | idx_Guid                                     | 108     | zhongguo_qla_business.s.Guid                                                  |    1 |   100.00 | NULL                                                   |
| username: Kongdom | Original post link

Take a look at the TiDB cluster display.

| username: 像风一样的男子 | Original post link

Your SQL has too many full table scans, please optimize it.

| username: wangccsy | Original post link

It seems that TiDB needs to improve its handling of complex SQL. MySQL is written in C++, right? TiDB isn’t written in Java, is it?

| username: DBRE | Original post link

Written in Go

| username: czxin788 | Original post link

Yes, MySQL and TiDB are deployed on a physical machine with 256GB of memory.

See the topology in the image below:

| username: 托马斯滑板鞋 | Original post link


  1. I recommend using version 7.5 (7.3 is DMR).
  2. If it’s a single machine, 1 TiDB, 1 PD, and 1 TiKV should be enough.
  3. Are the table structures the same? Why does one side use the primary key while the other side does a lot of full table scans?
| username: 连连看db | Original post link

If the SQL index used in TiDB is different from that in MySQL, you can try adding a hint in the SQL and re-executing it.

| username: wangccsy | Original post link

Makes sense. Whether the indexes and primary-foreign key settings of tables in two different databases are consistent is very crucial.

| username: TiDBer_小阿飞 | Original post link

Check the index and Foreign Key.

| username: TiDBer_lBAxWjWQ | Original post link

Are the two tables built the same way? One side is doing a full scan, while the other is fine.