Reset global MySQL status variables

Question

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

• danblack: Are you using persistent connections? What is the current value of max_prepared_stmt_count?
• Boardy: max_prepared_stmt_count is 16382 as is prepared_stmt_count. What do you mean persistent, is it a flag in the connection, or do you mean I am reusing the same connection? I use several connections within the app for different sections, so it tends to open them and close as required in some parts of the app, and other parts, it keeps the connection open for the lifetime the app is running
• danblack: 54079 -ひく 37693 =わ 16k. So if there is a leak, as opposed to just using a lot, its probably in the connections that are open for the lifetime of the app (which is what I loosely meant as persistent).
• Boardy: Yea its definetely a leak somewhere, in the status counts, you can see the the create and execute goes up, but the close doesn't match, but want to reset it the counters to get the app going again while I look into the problem. Ideally don't want to increase the max as it will just fill again and I'll back to square one
• danblack: Doesn't appear to be a reset mechanism. Odd that the prepared is the same as the exec - so no reuse? Overall the limit on max_prepared_stmt_count is extremely old and won't consume too much memory if increased. I don't know if explicitly killing a connection causes Com_stmt_close to increase. Worth a test on a test server. If safe to kill off long term connections that might be able to identify which connection one is leaking.

Answer 1

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.

• Boardy: I've tried restarting mysql systemctl mysqld restart but the counters don't seem to reset oddly
• jynus: Just to be clear- max_prepared_stmt_count doesn't limit the amount of total statements you can create, just how many you are created simultaneously. Either you didn't restart or you create statements so fast that they get blocked instantly. Prepared statements don't survive a restart.
• Boardy: I am an idiot, the restart did work, I was connecting to the wrong server after I did the restart. That's what I get for trying to work late at night
• Boardy: Now I've figured that resetting the server does in fact reset the counters, I've managed to identify what app it was that was causing the issue
• Wilson Hauck: Boardy, Please confirm about 17,000 prepared statements after execution were missing the DEALLOCATE PREPARE activity that would have kept com_stmt_close in order.

Answer 2

After you are finished with a prepared statement, and executing it, be sure to deallocate_prepare_statement. I think this will keep the counter from ever climbing to the limit.