Throttle slow SQL requests
This topic describes how to throttle slow SQL requests by using effective methods.
In specific scenarios, a large number of slow SQL requests exist in database sessions or slow query logs. These slow SQL requests consume a large number of database resources. This results in high values of one or more monitoring metrics such as the number of active sessions, CPU utilization, IOPS, and memory usage. After you analyze these slow SQL requests, resource-intensive SQL statements are identified. These SQL statements are not executed to provide core services and need to be optimized. To provide stable core services, we recommend that you throttle slow SQL requests that contain resource-intensive SQL statements.
For more information about the syntax for SQL throttling, see SQL throttling.
O\&M procedure for throttling SQL requests
On the Instance session page in the PolarDB-X console, execute the following statement to identify slow SQL requests. For more information, see Session management.
select * from information_schema.processlist where COMMAND!= 'SLEEP' and TIME>= 1000 order by TIME DESC;
Analyze slow SQL requests. For more information, see Analyze and optimize slow SQL queries.
Run SQL commands or perform GUI operations to create throttling rules. For more information, see Session management.
Check the effect of throttling rules on slow SQL requests based on the following information:
Changes in the values of monitoring metrics.
Feedback from users.
The statistical information about the SQL requests for each throttling rule that takes effect. You can execute the
SHOW CCL_RULESstatement to view the statistical information.Database sessions and SQL logs.
Create indexes, modify SQL statements, add database resources, or perform other operations.
Execute the
DROP CCL_RULEstatement to disable one or more specified throttling rules or theCLEAR CCL_RULESstatement to disable all throttling rules.
The following use cases provide examples on how to throttle slow SQL requests that are identified based on the specified throttling rules. You can use the methods in the use cases for reference and configure throttling rules based on your business requirements.
Case 1: The SQL statements created based on the same SQL template are identified from slow SQL requests
A database administrator receives an alert on the high value of a resource metric. The database administrator identifies the following slow SQL requests in database sessions and slow query logs:
+--------+---------------+---------------------+--------------------+---------+------+-------+----------------------------------------------+-----------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | SQL_TEMPLATE_ID |
+--------+---------------+---------------------+--------------------+---------+------+-------+----------------------------------------------+-----------------+
| 951494 | userxxxxxxxxx | 47.100.XX.XX:33830 | analy_db | Query | 40 | | select * from bmsql_oorder where `o_id` > 12 | 65c92c88 |
| 952468 | userxxxxxxxxx | 47.100.XX.XX:33517 | analy_db | Query | 43 | | select * from bmsql_oorder where `o_id` > 10 | 65c92c88 |
| 953468 | userxxxxxxxxx | 47.100.XX.XX:33527 | analy_db | Query | 43 | | select * from bmsql_oorder where `o_id` > 23 | 65c92c88 |
| 954468 | userxxxxxxxxx | 47.100.XX.XX:33537 | analy_db | Query | 43 | | select * from bmsql_oorder where `o_id` > 25 | 65c92c88 |
| 955468 | userxxxxxxxxx | 47.100.XX.XX:33547 | analy_db | Query | 43 | | select * from bmsql_oorder where `o_id` > 27 | 65c92c88 |
+--------+---------------+---------------------+--------------------+---------+------+-------+----------------------------------------------+-----------------+
The SQL statements identified from these slow SQL requests are created based on the same SQL template of which the ID is 65c92c88.
select * from bmsql_oorder where `o_id` > ?
The bmsql_oorder table contains a large volume of data. In the table, no indexes are created on the o_id column. All database resources are consumed to execute the unoptimized SQL statement. This affects the execution of other SQL statements that are important for core services in a negative manner. In this case, a suitable solution is to perform SQL throttling based on the template ID.
Create throttling rules
For example, the SQL statements that are created based on a specified template need to be executed during off-peak hours. You can use the following sample code to create a throttling rule that prevents the execution of these SQL statements.
CREATE CCL_RULE `KILL_CCL` // Set the name of the throttling rule to KILL_CCL. ON `analy_db`.`*` // Use the throttling rule for all SQL requests that are sent to perform operations on tables in the analy_db database. TO 'userxxxxxxxxx'@'%' // Use the throttling rule for SQL requests that are sent from userxxxxxxxxx. FOR SELECT // Use the throttling rule for SQL requests that contain the SELECT statement. FILTER BY TEMPLATE '65c92c88' // Use the throttling rule for SQL requests that contain the statements created based on the specified template. The ID of the template is 65c92c88. WITH MAX_CONCURRENCY = 0; // Set the number of SQL statements that can be concurrently executed on a single node to 0. This way, the SQL statements that match the throttling rule are not executed.
When a client executes the SQL statements that match the throttling rule, the following error message is returned:
ERROR 3009 (HY000): [13172dbaf2801000][10.93.159.222:3029][analy_db]Exceeding the max concurrency 0 per node of ccl rule KILL_CCL
For example, you want to execute a small number of SQL statements created based on a specified template and ensure that all database resources are not consumed to execute the SQL statements. You can create a throttling rule to specify the number of SQL statements that can be concurrently executed on a single node. The following code provides an example:
CREATE CCL_RULE `KILL_CCL_2` // Set the name of the throttling rule to KILL_CCL_2. ON `analy_db`.`*` // Use the throttling rule for all SQL requests that are sent to perform operations on tables in the analy_db database. TO 'userxxxxxxxxx'@'%' // Use the throttling rule for SQL requests that are sent from userxxxxxxxxx. FOR SELECT // Use the throttling rule for SQL requests that contain the SELECT statement. FILTER BY TEMPLATE '65c92c88' // Use the throttling rule for SQL requests that contain the statements created based on the specified template. The ID of the template is 65c92c88. WITH MAX_CONCURRENCY = 2; // Allow two SQL statements that match the throttling rule to be concurrently executed on a single node.
You can also use the SQL throttling feature on the Instance session page in the PolarDB-X console. The following figure shows the dialog box in which you can create a throttling rule. For more information, see Session management.
For example, you want to execute all SQL statements that are created based on a specified template and do not want error messages to return even if the execution process requires a long period of time. You can use the following sample code to create a throttling rule in which the maximum number of message bodies in the waiting queue and the timeout period for SQL requests are specified. The default timeout period for SQL requests is 600 seconds.
CREATE CCL_RULE `QUEUE_CCL_2` // Set the name of the throttling rule to QUEUE_CCL_2. ON `analy_db`.`*` // Use the throttling rule for all SQL requests that are sent to perform operations on tables in the analy_db database. TO 'userxxxxxxxxx'@'%' // Use the throttling rule for SQL requests that are sent from userxxxxxxxxx. FOR SELECT // Use the throttling rule for SQL requests that contain the SELECT statement. FILTER BY TEMPLATE '65c92c88' // Use the throttling rule for SQL requests that contain the statements created based on the specified template. The ID of the template is 65c92c88. WITH MAX_CONCURRENCY = 2, WAIT_QUEUE_SIZE=20, WAIT_TIMEOUT=500; // Allow two SQL statements that match the throttling rule to be concurrently executed on a single node. Set the maximum number of message bodies in the waiting queue on a single node to 20. Set the timeout period for SQL requests to 500. The unit for the timeout period is seconds.
After you create the throttling rule, you can execute SHOW CCL_RULES to obtain the statistical information about the SQL statements that match the throttling rule. The statistical information includes the number of matched SQL statements that are being executed, the number of matched SQL statements for which error messages are reported, and the total number of matched SQL statements. This way, you can check the effect of the throttling rule on slow SQL requests. If you want to disable a throttling rule after you created an index to process the throttled SQL requests in an efficient manner, you can execute DROP CCL_RULE to disable one or more specified throttling rules or execute CLEAR CCL_RULES to disable all throttling rules.
You can use keywords in the preceding SQL statements for throttling. The following keywords are included in the SQL statements:
select
from
bmsql_oorder
where
`o_id`
Create a throttling rule:
CREATE CCL_RULE `KILL_CCL` // Set the name of the throttling rule to KILL_CCL.
ON `analy_db`.`*` // Use the throttling rule for all SQL requests that are sent to perform operations on tables in the analy_db database.
TO 'userxxxxxxxxx'@'%' // Use the throttling rule for SQL requests that are sent from userxxxxxxxxx.
FOR SELECT // Use the throttling rule for SQL requests that contain the SELECT statement.
FILTER BY KEYWORD('select','from','bmsql_oorder','where','`o_id`') // Use the throttling rule for SQL requests in which keywords are used.
WITH MAX_CONCURRENCY = 0; // Set the number of SQL statements that can be concurrently executed on a single node to 0. This way, the SQL statements that match the throttling rule are not executed.
If you can obtain the template ID, we recommend that you create a throttling rule based on the template ID. To obtain the template ID, check the SQL logs, results returned by the EXPLAIN statement, or session information.
You can use the SQL throttling feature on the Instance session page in the PolarDB-X console. The following figure shows the dialog box in which you can create a throttling rule. For more information, see Session management.
Case 2: The same SQL statements are identified from slow SQL requests
A database administrator receives an alert on the high value of a resource metric. The database administrator identifies the following slow SQL requests in database sessions and slow query logs:
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | SQL_TEMPLATE_ID |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| 951494 | userxxxxxxxxx | 47.100.XX.XX:33830 | analy_db | Query | 40 | | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4 |
| 952468 | userxxxxxxxxx | 47.100.XX.XX:33517 | analy_db | Query | 43 | | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4 |
| 953468 | userxxxxxxxxx | 47.100.XX.XX:33527 | analy_db | Query | 43 | | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4 |
| 954468 | userxxxxxxxxx | 47.100.XX.XX:33537 | analy_db | Query | 43 | | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4 |
| 955468 | userxxxxxxxxx | 47.100.XX.XX:33547 | analy_db | Query | 43 | | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4 |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
The bmsql_oorder table contains a large number of records in which the value of o_carrier_id is 2. This results in slow SQL requests. If you create a throttling rule based on the template ID, the throttling rule takes effect on the template-based SQL statements in which the value of o_carrier_id is not 2. If you perform throttling based on keywords, the throttling rules take effect on SQL statements such as those provided in the following examples:
select * from bmsql_oorder where o_carrier_id = 2 limit 1;
select * from bmsql_oorder where o_carrier_id = 2 and o_c_id = 1;
In this case, we recommend that you create a throttling rule based on the template ID and the keyword. You can run the following code:
CREATE CCL_RULE `KILL_CCL` // Set the name of the throttling rule to KILL_CCL.
ON `analy_db`.`*` // Use the throttling rule for all SQL requests that are sent to perform operations on tables in the analy_db database.
TO 'userxxxxxxxxx'@'%' // Use the throttling rule for SQL requests that are sent from userxxxxxxxxx.
FOR SELECT // Use the throttling rule for SQL requests that contain the SELECT statement.
FILTER BY TEMPLATE '438b00e4' // Use the throttling rule for SQL requests that contain the statements created based on the specified template. The ID of the template is 438b00e4.
FILTER BY KEYWORD('o_carrier_id','2') // Use the throttling rule for SQL requests in which the keyword is included.
WITH MAX_CONCURRENCY = 0; // Set the number of SQL statements that can be concurrently executed on a single node to 0. This way, the SQL statements that match the throttling rule are not executed.
In PolarDB-X, if the kernel version of compute nodes is V5.4.11 or later and you do not execute the prepared SQL statements, you can use the following advanced syntax to create a throttling rule:
CREATE CCL_RULE `KILL_CCL` // Set the name of the throttling rule to KILL_CCL.
ON `analy_db`.`*` // Use the throttling rule for all SQL requests that are sent to perform operations on tables in the analy_db database.
TO 'userxxxxxxxxx'@'%' // Use the throttling rule for SQL requests that are sent from userxxxxxxxxx.
FOR SELECT // Use the throttling rule for SQL requests that contain the SELECT statement.
FILTER BY QUERY 'select * from bmsql_oorder where o_carrier_id = 2' // Use the throttling rule for SQL requests in which the specified SQL statement is used.
WITH MAX_CONCURRENCY = 0; // Set the number of SQL statements that can be concurrently executed on a single node to 0. This way, the SQL statements that match the throttling rule are not executed.
Case 3: The SQL statements created based on multiple templates are identified from slow SQL requests
A database administrator receives an alert on the high value of a resource metric. The database administrator identifies the following slow SQL requests in database sessions and slow query logs:
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | SQL_TEMPLATE_ID |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| 951494 | userxxxxxxxxx | 47.100.XX.XX:33830 | analy_db | Query | 40 | | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4 |
| 952468 | userxxxxxxxxx | 47.100.XX.XX:33517 | analy_db | Query | 43 | | select * from bmsql_order_line where ol_o_id = 2 | 57a572f9 |
| 953468 | userxxxxxxxxx | 47.100.XX.XX:33527 | analy_db | Query | 43 | | select * from bmsql_new_order where no_w_id = 2 | de6eefdb |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
In this case, slow SQL requests can occur due to different causes. If an SQL statement in a large number of slow SQL requests is generally executed in an efficient manner and the SQL statement is identified from a large number of slow SQL requests, you can consider causes such as network jitter or service node failures that result in a lower efficiency of execution. Another possible cause is that resource-intensive SQL statements consume all database resources. To identify the exact cause, you must analyze the related SQL statements. The SQL analysis feature is not described in this topic. After you identify the slow SQL requests that you want to throttle, you can create a throttling rule based on each template ID. The increase in the number of throttling rules decreases the matching efficiency. If the kernel version of compute nodes in PolarDB-X is V5.4.11 or later, we recommend that you create a throttling rule based on multiple templates.
CREATE CCL_RULE `KILL_CCL` // Set the name of the throttling rule to KILL_CCL.
ON `analy_db`.`*` // Use the throttling rule for all SQL requests that are sent to perform operations on tables in the analy_db database.
TO 'userxxxxxxxxx'@'%' // Use the throttling rule for SQL requests that are sent from userxxxxxxxxx.
FOR SELECT // Use the throttling rule for SQL requests that contain the SELECT statement.
FILTER BY TEMPLATE('438b00e4','57a572f9','de6eefdb') // Use the throttling rule for SQL requests in which one of the specified templates is matched.
WITH MAX_CONCURRENCY = 0; // Set the number of SQL statements that can be concurrently executed on a single node to 0. This way, the SQL statements that match the throttling rule are not executed.
For example, slow SQL requests in database sessions contain resource-intensive SQL statements and require throttling. If the kernel version of compute nodes in PolarDB-X is V5.4.11 or later, we recommend that you enable slow SQL throttling. For more information, see Throttle slow SQL queries. You can also use the SQL throttling feature on the Instance session page in the PolarDB-X console. The following figure shows the dialog box in which you can create a throttling rule. For more information, see Session management.
Summary
SQL throttling is an emergency measure. If the execution efficiency of a database is low due to the resource-intensive SQL statements in slow SQL requests, you can use this measure to increase the rate at which SQL requests are processed. After you throttle SQL requests that contain resource-intensive SQL statements, you can optimize these resource-intensive SQL statements and disable throttling rules when you no longer need to throttle SQL requests.