Articles related to real life scenarios involving java, frameworks and libraries configurations and salt-stack. The topics includes Core Java, Hibernate, Spring MySQL, Multithreading, Java NIO, jQuery, c3p0, Connection pooling, salt state, pillar, grain etc.

Monday, June 25, 2018

Solution for MySQL "Can't create more than max_prepared_stmt_count statements"

12:20 AM Posted by Unknown 7 comments
Recently I had a live incident. Support team started getting a lot of customer calls of service being down. The application is built with RoR and MySQL
I checked for usual things from past experience. While doing that I checked logs and found the following log statement.
ActiveRecord::StatementInvalid (Mysql::Error: Can't create more than max_prepared_stmt_count statements (current value: 16382): <some sql query>
The log clearly mentions the problem. The issue was happening because the application could not create prepared statements because limit has been reached. This was the first time I saw this issue, this means that our application has reached a stage where it the current limit is not enough(16382).

What are prepared statements?

I won’t go deep into it since there’s a wealth of information out there on the Internet. The basic idea behind prepared statements and the log message above is that the SQL statement itself is compiled once and cached for future use. Prepared statements are best when a sql query is being ran multiple times only the arguments/parameters changes. It has been introduced in Rails 3.1. It is automatically created whenever you use ActiveRecords to do any db operation.
Prepared statement 3 actions.
  • Prepare (Once)
  • Execute (As many times)
  • Deallocate (Once at the end)

Useful queries and commands(MySQL).

  • Get current max prepared statement limit.
show variables like "%prepared%"; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | max_prepared_stmt_count | 16382 | +-------------------------+-------+
  • Get current prepared statement count. These are the statements which has been Prepared and not yet deallocated. These are cached in memory.
show global status like "%prepared%"; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | Prepared_stmt_count | 15975 | +---------------------+-------+
  • Get stats related to prepared statements.
show global status like "com_stmt%"; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Com_stmt_close | 3248454 |# How many times statements has been `deallocated` since last mysql server started | Com_stmt_execute | 110121011 |# How many times statements has been `executed` since last mysql server started | Com_stmt_fetch | 0 | | Com_stmt_prepare | 3447923 |# How many times statements has been `prepared` since last mysql server started | Com_stmt_reprepare | 22 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | +-------------------------+-----------+


The easiest solution is to increase the limit. If the issue has started happeing quite recently and you suspect any new code changes/application changes could be causing the issue. You should check that first. Number one culprit could be prepared statements being Prepared but not being deallocated once done using it in the code.
Note: Don't increase the limit too much. It not good for your database health. I just doubled it becuase the could was low/defuault. Keep an eye on the count for a while and increase more only if required. This way you can keep track of the application usage growth and also detect if any specific release caused the prepared statement count to increase.
  • Increase prepared statement temporarily(Until mysql restart).
SET GLOBAL max_prepared_stmt_count = 32764; ## OR SET @@global.max_prepared_stmt_count = 32764;
  • Permanently update the limit Change
Edit mysql configuration file(/etc/mysql/my.cnf) and add/edit limit.
[mysqld] .... max_prepared_stmt_count = 32764 ....
Restart MySQL
service mysql restart