How to Check Excessive Network Traffic Between TiDB and TiKV

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

Original topic: tidb和tikv之间网络流量过大怎么查

| username: 舞动梦灵

Since last Sunday, I found that one of the TiDB servers has been triggering internal network traffic alarms. After checking, I noticed that the network traffic on the system is frequently interacting between the 6 TiKV servers and the TiDB server. Where should I start to investigate this issue?

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

Check if there have been any new large SQL queries recently, the kind that scan the entire table.

| username: 舞动梦灵 | Original post link

I specifically checked the chat records in the online group, and there were no chat messages on the evening of the 28th, unless the developers secretly went online.

| username: 舞动梦灵 | Original post link

After looking at the SQL in the dashboard for the past hour, it seems there are no issues. The top-ranked SQL query took me 2-3 seconds to check manually.

SELECT
    AM_USER_POINT_DETAIL_ID,
    CM_USER_ID,
    BEFORE_POINTS,
    POINTS,
    AFTER_POINTS,
    OPERATE_DIRECT,
    OPERATE_TYPE,
    OPERATE_DT,
    EXPIRE_POINTS,
    EXPIRE_DT 
FROM
    am_user_point_detail 
WHERE
    EXPIRE_POINTS IS NULL 
    AND EXPIRE_DT IS NOT NULL 
    AND EXPIRE_DT < str_to_date( DATE_FORMAT( NOW(), '%Y-%m-%d' ), '%Y-%m-%d %H:%i:%s' ) 
    AND ROUND( EXPIRE_DT MOD 10, 0 ) = 3 
    LIMIT 500;
| username: tidb菜鸟一只 | Original post link

Check if there are any SQL statements with particularly large return data volumes during this recent period that were not present before.

| username: 舞动梦灵 | Original post link

Your SQL is for versions 6.0 and 7.0, right? I’m using version 4.0.9, and this table doesn’t have the avg_result_rows field.

| username: 舞动梦灵 | Original post link

Brother, let me ask. When TiDB processes SQL and performs a full table scan, does TiKV transfer all the data of these tables to TiDB, and then TiDB processes the data?

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

Yes, so if there are a large number of full table scan SQLs, it is normal for TiKV to transmit a large amount of data to TiDB.

| username: 舞动梦灵 | Original post link

Okay, I just checked the SQL mentioned above, and it hasn’t been executed. Then the traffic recovered. It should be an issue with that SQL. That SQL doesn’t use the index. The entire table has 16 million rows.

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

The AVG_PROCESSED_KEYS field can also be used.

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

It’s good that it’s resolved.

| username: 舞动梦灵 | Original post link

Does this field show which table and which database sent how much traffic data?

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

This is not as accurate as the one above. This represents the amount of data processed by TiKV and does not include MVCC data. You can refer to this link:

| username: 舞动梦灵 | Original post link

Thank you.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.