You can modify the max_prepared_stmt_count parameter on an ApsaraDB RDS for MySQL instance to support stress testing workloads or other scenarios that require a higher prepared statement limit. This topic explains what the parameter controls, how to calculate the right value for sysbench-based stress testing, and how to change the parameter on your RDS instance.
Overview
The max_prepared_stmt_count parameter sets the maximum total number of server-side prepared statements allowed on a MySQL server. Its primary purpose is to guard against denial-of-service (DoS) attacks in which an attacker opens a large number of prepared statements to exhaust the memory of an RDS instance.
| Property | Value |
|---|---|
| Default value | 16382 |
| Valid range | 0 to 1048576 |
| Apply type | Dynamic (takes effect immediately without a restart) |
| Memory cost | At least 8 KB per prepared statement |
For full details on this system variable, see max_prepared_stmt_count in the MySQL documentation.
When to increase the value
The default value of 16382 is sufficient for most production workloads. However, you need to increase max_prepared_stmt_count in the following scenarios:
sysbench-based stress testing: High-concurrency benchmarks create many prepared statements across multiple threads and tables, often exceeding the default limit.
Scenarios with special requirements on the max_prepared_stmt_count parameter: Applications that use extensive prepared statement caching or connection pooling may require a higher limit.
When the limit is reached, MySQL returns the following error:
FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"Calculate the value for sysbench stress testing
In this example, sysbench 1.0.20 is used. The formulas below are derived from analysis of the sysbench source code and show how many prepared statements each testing model creates. Before you run a stress test, make sure the value of max_prepared_stmt_count is greater than or equal to the number calculated for your model.
| Testing model | Number of prepared statements |
|---|---|
oltp_read_only | >= Number of threads x Number of tables x 5 + Number of threads |
oltp_write_only | >= Number of threads x Number of tables x 4 + Number of threads |
oltp_read_write | >= Number of threads x Number of tables x 9 + Number of threads |
oltp_insert | 0. No prepared statements are used. |
Example calculation
For an oltp_read_only test with 256 threads and 100 tables, the number of prepared statements is:
256 x 100 x 5 + 256 = 128,256Before you start the stress test, set max_prepared_stmt_count to a value greater than 128256.
Procedure
To modify the max_prepared_stmt_count parameter, follow the standard parameter modification process for your RDS instance. For detailed steps, see Modify instance parameters.
In brief:
Log on to the ApsaraDB RDS console.
Find your RDS instance and go to the Parameters page.
Search for
max_prepared_stmt_countand modify the value.Click Apply Changes. Because this is a dynamic parameter, the change takes effect immediately without a restart.
After you modify the parameter, run the following SQL statement to verify the new value:
SHOW GLOBAL VARIABLES LIKE 'max_prepared_stmt_count';Monitor prepared statement usage
To check how many prepared statements are currently open on the server, run:
SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count';Monitoring this value helps you determine whether you need to raise or lower max_prepared_stmt_count. If Prepared_stmt_count consistently approaches the limit during normal operations, consider increasing the parameter. If it stays well below the limit, you can keep the default or reduce the value after stress testing.
Risks and recommendations
In sysbench-based stress testing scenarios, you must temporarily increase the value of
max_prepared_stmt_countto 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, be aware of the following risks:
Risk of increasing: Each prepared statement occupies at least 8 KB of memory. Setting a high value allows more prepared statements to accumulate, which increases memory consumption on the RDS instance and makes it more vulnerable to attacks.
Risk of decreasing: Existing prepared statements are not affected. However, once the current number of prepared statements exceeds the new, lower limit, no new prepared statements can be created until previously opened statements are released.