Checking Slow SQL Statements in ClickHouse
Scenarios
The SQL statement query in ClickHouse is slow because the conditions such as partitions, where conditions, and indexes of SQL statements are set improperly. As a result, the overall performance of the database is affected. To solve this problem, MRS provides the function of monitoring slow ClickHouse query statements.
Ongoing Slow Queries
You can query information about slow SQL statements that are being executed but do not return any result.
- Log in to FusionInsight Manager.
For details, see Accessing FusionInsight Manager.
- For versions earlier than MRS 3.2.0-LTS, choose Cluster > Services > ClickHouse. On the displayed page, click Query Management and then Ongoing Slow Queries.
- For MRS 3.2.0-LTS or later, choose Cluster > Services > ClickHouse. On the displayed page, click Logic Cluster, and click the name of the target logical cluster. On the displayed page, choose Query Management > Ongoing Slow Queries.
- Slow query parameters
Table 1 Slow query parameters Parameter
Description
Server Node IP Address
IP address of the ClickHouseServer instance. To view the IP address, log in to FusionInsight Manager and choose Cluster > Services > ClickHouse. On the displayed page, click the Instance tab.
Query ID
Unique ID generated internally.
Query
Slow query SQL statement.
Start Time
Time when the execution of a slow query SQL statement starts.
End Time
Time when the execution of a slow query SQL statement ends.
Duration (s)
Total execution time of a slow query SQL statement, in seconds.
User
ClickHouse user who executes a slow query SQL statement.
Client IP Address
IP address of the client that submits a slow query SQL statement.
Memory Used (MB)
Memory used by a slow query SQL statement, in MB.
Operation
You can click Terminate to terminate the slow query using a slow query SQL statement.
- Slow query filter conditions
Select the query condition as required and filter the query results.
Table 2 Filter conditions Condition
Description
Slow query duration exceeding
Filters the slow queries based on the duration.
The value can be 3(s), 9(s), 15(s), or 25(s).
By Query ID
Filters the slow queries based on the query ID.
Fuzzy search based on the query ID is supported. For example, if the query ID is 111-222-333-444-555, you can enter part of it such as 111-222 or -222-333 to query the needed information.
By User
Filters the slow queries based on the ClickHouse user.
Fuzzy search based on part of a username is supported.
By Client IP Address
Filters the slow queries based on the IP address of the client that submits slow query SQL statements.
Fuzzy search based on part of the client IP address is supported. For example, if the client IP address is 192.168.0.1, you can enter part of it such as 192.168 or 192.168.0 to query the information you need.
- Slow query parameters
Completed Queries
You can query information about slow SQL statements that have been executed and returned results.
UI path:
- For versions earlier than MRS 3.2.0-LTS, log in to FusionInsight Manager, choose Cluster > Services > ClickHouse. On the displayed page, click Query Management and then Completed Queries.
- For MRS 3.2.0-LTS or later, log in to FusionInsight Manager, choose Cluster > Services > ClickHouse. On the displayed page, click Logic Cluster, and click the name of the target logical cluster. On the displayed page, choose Query Management > Completed Queries.
For details about slow query parameters and filter conditions, see Table 1 and Table 2, respectively.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot