Query Lag While Inserting Data in Bulk into a Single Table with TiFlash Replica

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

Original topic: 具有tiflash副本单表批量插入数据的同时查询卡顿

| username: TiDBer_idvN8NKU

With the default parameters of version 7.1.1, during performance testing with a single table containing 200 fields, 3 TiKV replicas, and 3 TiFlash replicas, while continuously importing data in bulk, the PROGRESS in information_schema.tiflash_replica shows 1. At this point, using /*+ read_from_storage(tiflash[a]) */ for query statistics is frequently extremely slow. What could be wrong here? This shouldn’t be happening.

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

Is there a mixed deployment situation? Are TiFlash instances on separate systems and disks? Check the IO and network conditions when it’s slow…

| username: TiDBer_idvN8NKU | Original post link

TiKV and TiFlash use the same storage disk, could it be because of this? Using iotop to check the disk IO situation, there isn’t much throughput, and there’s no issue with network IO either.

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

The fact that the PROGRESS in information_schema.tiflash_replica is 1 indicates that synchronization is not an issue. If TiFlash queries are slow, it could be due to slow IO or network on the TiFlash nodes. You can also use EXPLAIN ANALYZE on the slow SQL to see which specific step is causing the delay.

| username: zhanggame1 | Original post link

Don’t just look at IO throughput; you need to check the disk usage percentage. Even with low throughput, it could still be at 100%. If you install Grafana, you can view disk statistics in the overview section, as well as check CPU and network conditions.

| username: TiDBer_idvN8NKU | Original post link

Data is being imported, is something wrong because high IO is causing query lag?

| username: TiDBer_idvN8NKU | Original post link

During the data import process, the IOUtil ratio of the TiFlash node is the highest, but in fact, there is no data storage because the data import uses insert ignore. The high IO ratio causes query lag. How can this be optimized?

| username: 裤衩儿飞上天 | Original post link

  1. Perform data import during off-peak business hours.
  2. Switch to higher-end storage, but this won’t fundamentally solve the impact of intensive writes on the business; it can only mitigate it.
| username: 有猫万事足 | Original post link

The ioUtil is almost full, and if you want to solve the import or query speed issue, there is no way.

However, if you want to avoid mutual interference, there is a way, which is to use resource control, which has been supported since version 7.1. But it may require some development modifications, at least the import users and other users need to be separated.

| username: redgame | Original post link

Query plan issue

| username: cassblanca | Original post link

There might be read hotspot contention issues if TiKV and TiFlash share the same disk.