Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 在mysql能查出来，在tidb查询，tidb直接崩溃重启，是什么原因？
[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]
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.
TiDB ran out of memory, please post the SQL execution plan.
Please provide the TiDB cluster topology and resource configuration.
Attach the cluster configuration resource deployment, topology, SQL execution plan, and OOM log information. Only then can we analyze the specific reasons.
What are the hardware requirements for TiDB?
Monitoring and further analysis of logs are required.
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.
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?
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 |
Take a look at the TiDB cluster display.
Your SQL has too many full table scans, please optimize it.
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?
Yes, MySQL and TiDB are deployed on a physical machine with 256GB of memory.
See the topology in the image below:
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.
Makes sense. Whether the indexes and primary-foreign key settings of tables in two different databases are consistent is very crucial.
Check the index and Foreign Key.
Are the two tables built the same way? One side is doing a full scan, while the other is fine.
|
username: czxin788
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: