By default, a SQL query that takes more than one second is a slow query, and the corresponding statement is a slow query statement. The process where a database administrator (DBA) analyzes slow query statements and finds out the reasons why slow queries occur is known as slow query analysis.
You can log in to the TencentDB for PostgreSQL console, click an instance ID in the instance list to enter the instance management page, and select the Performance Optimization tab to analyze slow queries, as shown below:
There are two monitoring views in the console, visually and conveniently illustrating the monitoring data of database slow queries.
Combined View (Slow Log and Other Metrics): This view shows and compares the monitoring data of the slow query metric and another metric in the same chart. Supported metrics include CPU utilization, QPS, requests, read requests, write requests, other requests, buffer cache hit rate, and average execution latency.
Slow SQL Execution Time Distribution: This view shows in what time period slow queries mainly occur.
The slow SQL list shows slow query statements of the database in real time. The list is arranged in descending order by time, that is, the latest slow query statement is automatically displayed in the first row.
The slow SQL list has the following fields: the execution time, the slow SQL statement, the total time, the client IP, the database name, and the account executing the statement.
- By default, the slow SQL list displays slow SQL data over the past seven days. The slow SQL data is stored in a log, and the oldest data is automatically deleted from the log to ensure that the log only stores data within the past seven days and the log size does not exceed 50 GiB.
- Slow SQL queries larger than 20 KB in size cannot be viewed in the console. To view them, submit a ticket.
The slow SQL statistics and analysis page shows the slow query statements with abstract parameter values within the specified time range and their aggregated statistical analysis results. The page has the following fields:
begin_timeof statement execution is logged as the last execution time.