2

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

Improve this question
5
  • Are you using persistent connections? What is the current value of max_prepared_stmt_count?
    – danblack
    Nov 28, 2018 at 22:38
  • 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
    – Boardy
    Nov 28, 2018 at 22:41
  • 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).
    – danblack
    Nov 28, 2018 at 22:55
  • 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
    – Boardy
    Nov 28, 2018 at 23:00
  • 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.
    – danblack
    Nov 28, 2018 at 23:08

2 Answers 2

Reset to default
0

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.

Improve this answer
5
  • I've tried restarting mysql systemctl mysqld restart but the counters don't seem to reset oddly
    – Boardy
    Nov 28, 2018 at 23:11
  • 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.
    – jynus
    Nov 28, 2018 at 23:19
  • 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
    Nov 28, 2018 at 23:50
  • 1
    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
    – Boardy
    Nov 29, 2018 at 0:13
  • 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.
    – Wilson Hauck
    Dec 8, 2018 at 18:01
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.