Proxysql: Stmt_Cached grow beyond mysql-max_stmts_cache causing ProxySQL to crash

[KlausOvergaard]

We are using ProxySQL 1.4.1. The caching of Prepare statements seems to grow infinitely. Our mysql-max_stmts_cache is set to default 10.000, but Stmt_Cached and Stmt_Max_Stmt_id grow way beyond until all server memory is consumed (12 GB) and eventually ProxySQL crash.
Here is a screnshot of the memory consumption. The busier our servers are, the quicker the stmt cache grows: https://gyazo.com/358232ad78361532eb2131ba2e21c73c

[renecannao]

Issue confirmed

[renecannao]

In 1.4 the handling of prepared statements were rewritten to solve some design limitations.
The code responsible for Stmt_Cached and Stmt_Max_Stmt_id wasn't migrated tho.
Now should be fixed, and this is an example of an application generating a lot of unique PS:

Admin> SELECT * FROM stats_mysql_global WHERE variable_name LIKE '%stmt%' AND variable_value;
+---------------------------+----------------+
| Variable_Name             | Variable_Value |
+---------------------------+----------------+
| Com_backend_stmt_prepare  | 3420716        |
| Com_backend_stmt_execute  | 1852050        |
| Com_frontend_stmt_prepare | 1852837        |
| Com_frontend_stmt_execute | 1852065        |
| Com_frontend_stmt_close   | 1851650        |
| Stmt_Client_Active_Total  | 1588           |
| Stmt_Client_Active_Unique | 1167           |
| Stmt_Server_Active_Total  | 256            |
| Stmt_Server_Active_Unique | 256            |
| Stmt_Max_Stmt_id          | 62660          |
| Stmt_Cached               | 9786           |
+---------------------------+----------------+
11 rows in set (0,01 sec)

[renecannao]

Fixed in current branch 1.4.4

[KlausOvergaard]

Hi Rene

Anything we can do to clear the cache on prepared statements before the
server runs out of memory ?
When do you expect to release 1.4.4 ?

Thanks for your support!

Best regards
Klaus Overgaard

Den 6. okt. 2017 23.58 skrev "René Cannaò" [e-mail]:

Fixed in current branch 1.4.4

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/sysown/proxysql/issues/1198#issuecomment-334880007,
or mute
the thread
https://github.com/notifications/unsubscribe-auth/AfByLLZJSwp04oLmptwpMxsec26g2ZnCks5spqJqgaJpZM4PunwC
.

[eightnoteight]

Hi @renecannao

This fix is not ported to 2.x, also the mysql-max_stmts_cache documentation is missing from
https://github.com/sysown/proxysql/wiki/Global-variables