TiDB Cluster with TiFlash Component Has Slow Query Speed

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

Original topic: 包含tiflash组件的tidb集群查询速度慢

| username: TiDBer_djgos04V

[Test Environment for TiDB] Testing
[TiDB Version] 7.1.1
I deployed 3 TiDB nodes, 3 PD nodes, 3 TiKV nodes, and 2 TiFlash nodes on three servers. After importing test data from MySQL, I found that the query performance did not improve much. However, when I previously deployed 3 TiDB nodes, 3 PD nodes, and 3 TiKV nodes on my three virtual machines, the query performance was much higher than MySQL. Is it because TiFlash itself has such a significant impact on performance, or is it because my server storage space is insufficient?

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

Please share your topology diagram. You didn’t place TiFlash and TiKV together, did you…

| username: TiDBer_djgos04V | Original post link

Yes, that’s right. Because of the server quantity limitation.

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

Did you bind the NUMA resources? Are TiKV and TiFlash using the same disk? Check whether the test queries are using TiKV or TiFlash…

| username: TiDBer_djgos04V | Original post link

There is no resource binding, TiKV and TiFlash are using the same disk, and the test statements are all routed to TiKV.

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

Isn’t TiFlash a side effect for you? It can’t speed up queries and still takes up resources to synchronize data… You can test aggregate queries, there should be an improvement.

| username: zhanggame1 | Original post link

According to the official recommendation, TiKV should be deployed independently.

| username: TiDBer_vfJBUcxl | Original post link

Official explanation:
For scenarios where you want to save machine resources and have no isolation requirements, you can deploy TiKV and TiFlash together. It is recommended to allocate enough resources for both TiKV and TiFlash separately and avoid sharing disks.

| username: 大飞哥online | Original post link

TiFlash is causing side effects for you. You can separate TiFlash individually.

| username: ljluestc | Original post link

Data Volume and Distribution: If the data volume is relatively small or the data distribution is fairly even, the performance improvement from TiFlash may be limited. TiFlash is more suitable for handling large-scale OLAP queries. If the dataset is small, it may not significantly improve query performance.

Solution: Ensure that the data volume is large enough and that the complexity of OLAP queries is suitable for acceleration using TiFlash. If the data scale is small, TiFlash may not be necessary, and you can consider using only TiKV.

Data Synchronization Issues: TiFlash needs to stay synchronized with TiKV. If there are delays or failures in data synchronization, it may lead to decreased query performance.

Solution: Ensure that data synchronization between TiFlash and TiKV is running smoothly. Check the status of the TiDB cluster and the logs of TiKV and TiFlash to see if there are any synchronization issues.

Hardware Resources: TiFlash requires sufficient CPU, memory, and storage resources to operate. If hardware resources are insufficient, performance may degrade.

Solution: Ensure that TiFlash nodes have enough CPU and memory resources, and that there is enough storage space to accommodate the data. Monitor the resource usage of TiFlash nodes to ensure they do not reach their limits.

Query Optimization: TiFlash supports various query optimization strategies, but the performance of queries still depends on the complexity of the queries themselves and the data distribution.

Solution: Optimize query statements and try to avoid inefficient operations such as full table scans. Use Explain to analyze the query plan to understand how the query is executed and optimize accordingly.

Network and Communication Latency: Communication between TiFlash and TiKV may be affected by network latency, which can lead to decreased query performance.

Solution: Ensure that the network connection between TiFlash and TiKV is good. Use network performance tools to diagnose and resolve potential network latency issues.

Ultimately, the performance improvement from TiFlash depends on the combined effect of multiple factors. If you encounter performance issues, it is recommended to first check the above factors to identify the root cause of the performance degradation and take appropriate measures to optimize and improve performance. If hardware resources are limited or the data volume is small and OLAP query acceleration is not needed, you may consider not using the TiFlash component.

| username: 昵称想不起来了 | Original post link

TiFlash Resource Planning

| username: Z六月星星 | Original post link

We have the same deployment here, and there are always problems in production. TiFlash and TiKV are together, and the default setting uses TiFlash. When it’s fast, it’s very fast, but when it’s slow, the entire TiDB cluster query is extremely slow, and CPU and memory resources are completely exhausted.

| username: Z六月星星 | Original post link

Your query does not have slow SQL, it might perform better with TiKV.

| username: ShawnYan | Original post link

It is recommended to deploy them separately. Don’t make the resources so tight in a production environment.

| username: Billmay表妹 | Original post link

@heiwandou I noticed that several of your posts did not provide effective suggestions and seem a bit like spam. Please correct this behavior!