This topic describes how to use the ApsaraDB for ClickHouse console to monitor slow SQL queries and manage running SQL queries. This helps you find and optimize slow SQL queries or temporarily terminate the processing of requests to release resources.

Background information

The process of writing raw query logs to tables is complex. This process requires you to perform operations such as installing clients, writing SQL statements, and sorting results. The trend of slow SQL queries cannot be displayed in an intuitive manner. This results in high overall costs, poor usability, and low efficiency. ApsaraDB for ClickHouse displays query logs by using graphical interfaces. ApsaraDB for ClickHouse allows you to perform visual retrieval by key dimension and analyze the statistics for slow SQL queries. You can summarize and analyze log data with ease in a matter of clicks. This helps you identify and handle issues.

Monitor slow SQL queries

To monitor slow SQL queries, you can collect statistics for the trend of slow SQL queries and view the details of slow SQL queries over the specified time range.

  1. Log on to the ApsaraDB for ClickHouse console.
  2. In the top navigation bar, select the region where the ApsaraDB for ClickHouse cluster for which you want to monitor slow SQL queries is deployed.
  3. In the list of clusters, click the ID of the cluster. The Cluster Information page appears. In the left-side navigation pane, click Query Management.
  4. Click the Slow Queries tab.
  5. Customize the execution duration of slow SQL queries. By default, queries whose execution durations exceed 3,000 ms are considered slow SQL queries.
    Note The minimum specified period to execute slow SQL queries is 500 ms.
  6. Use the following methods to view the trend or details of slow SQL queries:
    • Click Last 15 Minutes, Last 30 Minutes, or Last 1 Hour to view the trend or details of slow query logs over the last 15 minutes, last 30 minutes, or last hour.
    • Specify the start time and the end time for the query. Then, click Query to view the trend or details of slow query logs within a maximum of seven days.
      Note
      • The start time must be within the last seven days. The end time cannot be later than the current time.
      • The time is accurate to the minute.
  7. View the trend of slow SQL queries.
    • X-axis: indicates time.
    • Left-side y-axis: indicates the number of slow SQL queries.
    • Right-side y-axis in orange: indicates the average execution duration. Unit: milliseconds.
    • Right-side y-axis in blue: indicates the maximum execution duration. Unit: milliseconds.
    178440001
  8. View the details of slow SQL queries.
  9. Export the data of slow SQL queries.

Manage running SQL queries

The Running Queries tab displays running SQL queries based on filter conditions in real time. You can view the details of running SQL queries and terminate selected SQL queries to temporarily release resources.

  1. Log on to the ApsaraDB for ClickHouse console.
  2. In the top navigation bar, select the region where the ApsaraDB for ClickHouse cluster for which you want to manage running SQL queries is deployed.
  3. In the list of clusters, click the ID of the cluster. The Cluster Information page appears.
  4. In the left-side navigation pane, click Query Management.
  5. Click the Running Queries tab.
  6. To filter query results, select User, SQL Keywords, or Initial Query ID from the drop-down list.
    17844001
    Note If you do not set a filter condition, all running SQL queries are displayed by default.
  7. If the cluster fails or the resource usage reaches the configured limit, select required SQL queries that cause the issue, and then click Terminate Selected Queries to terminate selected query requests. You can also click Terminate All Queries to terminate all query requests.
    17844005
    Note After you click Terminate Selected Queries or Terminate All Queries, the confirmation message appears. If you click Cancel, queries continue to run on the backend.