Long running queries in TiDB

TiDB
1

Application environment:

Production

TiDB version:v4.0.14

Problem:

Just like we query the innodb_trx table in mysql for finding the long running queries.Can someone please suggest a way to find the long running queries in TiDB?

2

To effectively identify and analyze long-running queries in TiDB, you can leverage several tools and configurations provided by the system. Here’s a detailed guide on how to do this:

1. Configuring the Slow Query Log

The slow query log in TiDB is a crucial tool for identifying queries that take longer than expected to execute. By default, TiDB logs queries that exceed 300 milliseconds. You can adjust this threshold using the tidb_slow_log_threshold system variable.

  • Enable/Disable Slow Query Log: The slow query log is enabled by default. You can control it using the tidb_enable_slow_log variable. To enable or disable it, execute:

    SET GLOBAL tidb_enable_slow_log = ON;  -- or OFF

  • Set Slow Query Threshold: Adjust the threshold for what constitutes a slow query:

    SET GLOBAL tidb_slow_log_threshold = 200;  -- in milliseconds

  • Log File Location: The slow query log is stored in a file specified by the slow-query-file configuration. By default, this is tidb-slow.log.

For more details on configuring the slow query log, refer to the TiDB documentation.

2. Using the Slow Query Log

Once configured, the slow query log provides detailed information about each slow query, including execution time, query text, and execution plan. You can access this information by querying the INFORMATION_SCHEMA.SLOW_QUERY table or by directly examining the log file.

  • Querying Slow Queries: Use SQL to query the slow query table:

    SELECT * FROM INFORMATION_SCHEMA.SLOW_QUERY WHERE query_time > 1;  -- queries longer than 1 second

  • Analyzing Execution Plans: The slow query log includes execution plans, which can be decoded using the tidb_decode_plan function to understand how the query was executed.

3. Understanding the Performance Schema

TiDB provides a performance schema that helps in understanding the resource consumption and execution details of SQL queries. This includes metrics like execution time, memory usage, and more.

  • Key Metrics: Look for metrics such as Query_time, Parse_time, Compile_time, and Optimize_time in the slow query log to identify where time is being spent.

  • Transaction Details: Fields like Txn_start_ts and Commit_time provide insights into transaction behavior and potential bottlenecks.

4. Utilizing Monitoring Tools

TiDB integrates with monitoring tools like Prometheus and Grafana to provide real-time insights into database performance.

  • Prometheus and Grafana: These tools can be used to visualize metrics and set up alerts for long-running queries. They provide dashboards that display query performance, resource usage, and system health.

  • TiDB Dashboard: This tool offers a user-friendly interface to view slow queries, analyze execution plans, and monitor system performance. It integrates with the slow query log and statement summary tables to provide comprehensive insights.

For more information on using these monitoring tools, you can visit the TiDB Monitoring Framework Overview.

By configuring the slow query log, understanding the performance schema, and utilizing monitoring tools, you can effectively identify and analyze long-running queries in TiDB, leading to improved database performance and efficiency.