Mysql – Reset global MySQL status variables

MySQLmysql-5.7prepared-statement

I'm currently looking into an issue where when running MySQL prepared statement I get an exception:

SQL Exception: Can't create more than max_prepared_stmt_count statements (current value: 16382)

Looking into this it looks like I'm definetely leaking prepared statements somewhere shown by the global status using the following query:

show global status like 'com_stmt%';

Gives me back the following results:

Com_stmt_close          37693
Com_stmt_execute        54079
Com_stmt_fetch          0
Com_stmt_prepare        54079
Com_stmt_reprepare      0
Com_stmt_reset          0
Com_stmt_send_long_data 0

I'm trying to reset these counters so my app can go back to a normal running state, while I investigate where the prepared statements are being closed but I can't find any way to reset these counters.

I've tried being logged in to mysql and running flush status, I've tried sending flush-status from the mysql-admin command line utility and I've tried resetting the DB but I can never get it to reset.

I'm using MySQL 5.7

Best Answer

You cannot reset GLOBAL STATUS counters except by restarting. Your way to put a patch as you said until you know which connection is leaking prepared statements is to kill your connections regularly. Once the connection or connection is dropped, its prepared statements should disappear and will give you that margin. The exact way to kill them will depend on your connection method- if you are using persistent connections/connection pooling, configure it so the connections are refreshed more frequently. If you are doing your own connection handling, make sure to diconnect at the end of your request cycle. Connection reuse and improper cleanup is likely the cause of your issues.

Worse case scenario, start pt-kill and kill all idle connections, whatever takes to drop those ones that created the prepared statements. BTW, unrelated, but pt-kill has a bug when detecting prepared statements.

Related Question