background

One day, I was enjoying one of my few off-duty playtime. Suddenly received frequent alarms, a look is the ya database error, direct denial of service. My first reaction was no good, the database might be down. Say sorry to your game friends and turn on logging and monitoring.

Boy, the database CPU was 100% and THEN I followed my lead and looked at the slow log, but that wasn’t quite right either. The middle process was very tortuous. After our team mates restarted the service, we found the real error cause in the logs that were reported by a large number of people:

ERROR 1461 (42000): Can’t create more than max_prepared_stmt_count statements (current value: 16382)

Start with Google & Stackoverflow Debug.

At this time there is a way to stop loss is:

  • Temporarily increase the value of max_prepareD_STMT_count

We have a hunch that the MySQL CPU is 100%, and this particular period is really heavy traffic, so this change can not solve the fundamental problem. And then we started analyzing what the hell it was.

Easy to understand precompilation

As an efficient database, MySQL naturally has a set of methods to improve the operating efficiency. In addition to the usual automatic optimization of SQL, there is our old method, caching.

In the actual scenario, we can also see that there are many statements where only the value of the condition in the WHERE substatement changes, while the rest of the statement is expected to remain unchanged until the item is taken offline. Such as

SELECT id, name FROM users WHERE sex = "male"; 
SELECT id, name FROM users WHERE sex = "female";
Copy the code

This time a large number of repeated strings will cause the database to recompile and parse a lot of useless work, so can not deduct this variable, keep a template, only accept variables each time, do not re-parse the whole statement.

Like this:

SELECT id, name FROm users WHERE sex =? ;Copy the code

Then the database does not need to do a lot of work, directly executed results, is not beautiful.

Easy to use precompilation

Paste the prepare method to paste the request to the server. Paste the prepared method to paste the request to the server. This precompilation is automatically closed when the results are available, or can be manually closed by calling rows.close ().

// use prepare
rows, err := db.Query("SELECT id, name FROm users WHERE sex = ? ;"."female")
// without prepare statement
rows, err := db.Query("SELECT id, name FROm users WHERE sex = female;")
Copy the code

SQL – Official documentation

And then the line just popped

Back that night, we searched and found that 16382 was the default value for MySQL and generally didn’t need to be changed. If this value is full, our code is using the wrong precompiled template.

There are basically two debug ideas:

  1. Precompile is not closed after use;
  2. Precompiled templates have dynamic values in them;

First of all, the Golang SQL package has the automatic shutdown function, which is the package we used directly. We precompile the template with dynamic values, such as the following statement when precompiled to MySQL:

SELECT id, name FROm users WHERE id = 13 and sex =? ;SELECT id, name FROm users WHERE id = 73 and sex =? ;SELECT id, name FROm users WHERE id = 86 and sex =? ;Copy the code

After screening, we found that although we directly used Golang SQL package, there was also a layer of encapsulation on the upper layer, which sent all SQL as pre-compiled to MySQL. We had a complex SQL writing method which contained a dynamic value, resulting in full pre-compiled parameters, and MySQL directly refused service.

Hey, why was everything okay before

Every time something goes wrong, I always have this idea in my mind, and I want to understand it this time:

  1. Usually flow is not large, that complex SQL execution soon shut down, will not hit full default values;
  2. There were several optimizations, and the rest of the SQL with dynamic values was actually optimized away, so this problem was not exposed before;
  3. No pressure test!!