Remember MySQL preparedstatement use overrun problem

"Phenomenon & Background"

This service is an intermediary service that provides routing rules calculation and SQL filtering for database sub-tables. That is, upstream will send the request to the service, the service according to the Sub-database table rules to send the corresponding SQL execution to the corresponding sub-library, sub-table to execute, and the results are returned to upstream.

In the afternoon of 2016-07-14, after the upstream traffic switched, MySQL reported a large number of "Can ' t create more than Max_prepared_stmt_count statements (current value:16382)", MySQL does not work to cause this layer of database routing service to not work and a large number of requests fail.

It probably means that at the same time all sessions in the MYSQLD PreparedStatement statement exceeds the MySQL limit, causing the new preparedstatement to fail and produce an error.

Temporary resolution: Upstream fallback.

"Cause analysis"

• Why do you build so many PreparedStatement?

1) There is an interface because of the bug causes all the library, sub-table full table SQL operations, and each SQL execution will be a new PreparedStatement

2) upstream switching traffic after the use of new libraries to expand the number of libraries and the number of tables, resulting in a request corresponding to the number of sub-tables multiplied, that a request corresponding to the number of preparedstatement multiplied.

• What is PreparedStatement for?

PreparedStatement is to execute a request to the MySQL server to compile the SQL statement and store its precompiled result in the PreparedStatement object when repeated execution of a SQL statement with different parameter values. You can then use the object to efficiently execute the statement multiple times without precompiling, using the database's buffers directly, to improve the efficiency of database access.

"PreparedStatement"

• Principle

Perform the process for MySQL query, including:

1. Transfer SQL to the database

2, the server first check the query cache, if hit, Geely returns results; otherwise go to the next stage

3. Database Validation & Parsing SQL

4. Calculate the execution plan

5. Execute queries based on API execution plan calling the storage engine

6. Return Data

In the above steps, the 4th step is time consuming. To improve performance, the database caches execution statements and their execution plans. However, the SQL statement itself is key, and the execution plan will not hit the cache as long as there is a byte in the Value,sql statement.

Therefore, with PreparedStatement, you can increase the cache hit rate for SQL that has only a different parameter.

When PreparedStatement is created, the parameterized statements are sent to the database for syntax detection and execution of scheduled calculations. SQL statements are precompiled and stored in the PreparedStatement object, and the next time the same SQL statement is executed, the database is no longer precompiled, and the database's buffer is used directly to improve the access efficiency of the database.

• Role

1. Code Readability & maintainability

2. Increase cache Hit rate and improve performance in batch execution

3. Improve security and prevent SQL injection

• Applicable scenarios

There are a large number of SQL structures that are the same, with only a different number of SQL requests. Or you are in security considerations for writing to SQL.

Remember MySQL preparedstatement use overrun problem