You can modify the max_prepared_stmt_count parameter of an ApsaraDB RDS for MySQL instance to meet your stress testing requirements and business requirements. This topic describes how to configure the max_prepared_stmt_count parameter for an RDS instance in the sysbench-based stress testing scenarios.
Background information
The max_prepared_stmt_count parameter specifies the total number of server-side prepared statements. The max_prepared_stmt_count parameter is used for scenarios in which denial-of-service (DoS) attacks occur. The attackers inject a large number of prepared statements to exhaust the memory resources of an RDS instance. For more information, see max_prepared_stmt_count.
The default value of the max_prepared_stmt_count parameter for an RDS instance is 16382. In some scenarios, the default value is too small to meet your requirements. You must increase the value of the parameter. For example, an error message indicating that the value of the max_prepared_stmt_count parameter is small may be displayed for the sysbench-based stress testing in high-concurrency scenarios.
FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"Scenarios
You need to modify the max_prepared_stmt_count parameter in the following scenarios:
sysbench-based stress testing
Scenarios in which special requirements are imposed on the max_prepared_stmt_count parameter
Usage notes
In sysbench-based stress testing scenarios, you must temporarily increase the value of the max_prepared_stmt_count parameter to meet the testing requirements. After the stress testing is complete, we recommend that you decrease the value of the parameter to prevent memory risks and security risks.
After you increase or decrease the value of the parameter, the following risks may occur:
A prepared statement occupies at least 8 KB of memory. If you increase the value of the parameter, a large amount of memory of the RDS instance is occupied by the prepared statements. This makes your RDS instance more vulnerable to attacks.
If you decrease the value of the parameter, existing prepared statements are not affected. However, after the number of prepared statements exceeds the value of the max_prepared_stmt_count parameter, new prepared statements are not allowed until the previous prepared statements are released.
Parameter settings in sysbench-based stress testing scenarios
In this example, sysbench 1.0.20 is used. The formulas that are used to calculate the number of prepared statements for common stress testing models can be obtained from the analysis results of sysbench source code. Before you use a stress testing model to perform sysbench-based stress testing, make sure that the value of the max_prepared_stmt_count parameter is greater than or equal to the number of prepared statements for the model. The following table describes formulas for different stress testing models.
Testing model | Number of prepared statements |
oltp_read_only | Greater than or equal to the value that is calculated by using the following formula: Number of threads × Number of tables × 5 + Number of threads |
oltp_write_only | Greater than or equal to the value that is calculated by using the following formula: Number of threads × Number of tables × 4 + Number of threads |
oltp_read_write | Greater than or equal to: Number of threads × Number of tables × 9 + Number of threads |
oltp_insert | 0. No prepared statements are used. |
For example, if the number of threads is 256 and the number of tables is 100 for the oltp_read_only model, the number of prepared statements is 128,256. Before you perform sysbench-based stress testing, change the value of the max_prepared_stmt_count parameter to a value that is greater than 128256.
Procedure for parameter settings
For more information, see Modify instance parameters.