How to find The TiDB address that handles the query in information_schema

TiDB
1

Application environment:

TiDB version: 6.5.1

2

You can find the TiDB address that handles the query in the processlist table of the information_schema database. The processlist table contains information about the threads executing within the server. The processlist table has a column named HOST which gives the address of the TiDB instance that handles the query.

You can use the following SQL statement to extract the slow queries and the TiDB address that handles the query:

SELECT
    s.query,
    s.time,
    s.user,
    p.host,
    s.query_time,
    s.compile_time / 1000000 AS compile_time_seconds,
    s.compile_time,
    s.db
FROM
    information_schema.cluster_slow_query AS s
    JOIN information_schema.processlist AS p ON s.connection_id = p.id
ORDER BY s.time DESC 
LIMIT 100;

This SQL statement joins the cluster_slow_query table with the processlist table on the connection_id column to get the TiDB address that handles the query. The processlist table has a column named HOST which gives the address of the TiDB instance that handles the query.

You can find more information about the processlist table in the [TiDB official documentation].

: https://docs.pingcap.com/tidb/stable/information-schema#processlist

3

Same query, but with the correct column name:

SELECT
    s.query,
    s.time,
    s.user,
    p.host,
    s.query_time,
    s.compile_time / 1000000 AS compile_time_seconds,
    s.compile_time,
    s.db
FROM
    information_schema.cluster_slow_query AS s
    JOIN information_schema.processlist AS p ON s.Conn_ID = p.id
ORDER BY s.time DESC 
LIMIT 100;
2 Likes