Ensuring the performance of your database system is essential for the efficient functioning of any application. One of the tools at your disposal for performance tuning in TiDB, a distributed SQL database, is the slow query log. This log records SQL queries that exceed a specific execution time threshold, helping identify queries that might be causing performance issues. Configuring the slow query log properly is crucial in order to diagnose and optimize potentially troublesome queries. This article provides a step-by-step guide on how to configure the slow query log in TiDB.

Understanding the Slow Query Log

The slow query log in TiDB works by tracking SQL statements that take longer than a predefined time to execute. These entries can then be used to identify inefficient queries and other database issues. The details logged can include the SQL text, query start time, duration, user, and other critical information that helps in debugging and optimization.

Steps to Configure the Slow Query Log

1. Enable Slow Query Log

By default, the slow query log is enabled in TiDB. However, if you need to confirm or change this setting, you can modify it using the TiDB server system variable called tidb_slow_log_threshold.

SET GLOBAL tidb_slow_log_threshold = 200;

This command sets the slow log threshold to 200 milliseconds. Any query taking longer than this duration will be logged.

2. Configure Log File Location

TiDB stores the slow query log in a separate file, which is usually named tidb_slow_query.log. You can specify a different log path by setting the slow-query-file parameter in the TiDB configuration file (tidb.toml):

[log.file]
slow-query-file = "logs/tidb_slow_query.log"

3. Adjust the Log Threshold

You might need to adjust the threshold based on your environment and requirements. This is achieved by setting tidb_slow_log_threshold. Lowering the threshold will capture more queries, potentially generating a large amount of log data but providing more detailed insight into your query performance.

4. Analyze Slow Query Log

Once configured, you can analyze the slow query logs using various utilities or even manually. TiDB also provides a helpful SQL command to query the slow log data:

SELECT * FROM INFORMATION_SCHEMA.CL_SLOW_QUERY
WHERE time > '2023-01-01 00:00:00' AND time < '2023-01-02 00:00:00';

This SQL command allows you to retrieve slow query logs directly through the SQL interface, focusing on a specific time interval.

Additional Tips

• Setting Threshold Per Session: You can set slow query thresholds at the session level to override global settings, which can be useful for debugging specific sessions without affecting global logging behavior.

  SET SESSION tidb_slow_log_threshold = 100;

• Automating Log Rotation: Ensure your log rotation is properly configured to prevent the slow query log from consuming excessive disk space. This can typically be managed through external tools like logrotate.
• Regular Monitoring: Regularly monitor the slow query log to identify recurring slow queries. Optimizing these queries or their underlying tables through indexes or other means can significantly improve overall performance.

Summary Table

By following these directions and utilizing the slow query log, you can maintain and enhance the performance of your TiDB database, ensuring smoother operations and an improved user experience.