Is there a setting I’m missing?
My understanding is that the maximum number of prepared statements is . In my case, that should be a theoretical maximum of about 8K prepared statements, but I’m often almost double that number. In fact, my active connections are much lower than the max.
Here are some stats - All taken at the same time from my proxysql node:
proxysql> show variables like “%stmt%”;
±-------------------------------±------+
| Variable_name | Value |
±-------------------------------±------+
| mysql-max_stmts_per_connection | 20 |
| mysql-max_stmts_cache | 10000 |
±-------------------------------±------+
2 rows in set (0.00 sec)
proxysql> SELECT * FROM stats_mysql_connection_pool;
±----------±---------------±---------±-------------±---------±---------±-------±--------±--------±----------------±----------------±-----------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
±----------±---------------±---------±-------------±---------±---------±-------±--------±--------±----------------±----------------±-----------+
| 10 | XXX.XXX.XX.173 | 3306 | ONLINE | 79 | 5 | 84 | 0 | 576172 | 82951515 | 454089261 | 772 |
| 10 | XXX.XXX.XX.174 | 3306 | OFFLINE_SOFT | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 732 |
| 11 | XXX.XXX.XX.173 | 3306 | ONLINE | 46 | 3 | 49 | 0 | 498427 | 33312388 | 30330862 | 772 |
| 11 | XXX.XXX.XX.174 | 3306 | ONLINE | 27 | 13 | 40 | 0 | 297624 | 24769155 | 26670950 | 732 |
±----------±---------------±---------±-------------±---------±---------±-------±--------±--------±----------------±----------------±-----------+
4 rows in set (0.00 sec)
proxysql> SELECT * FROM stats_mysql_global WHERE variable_name LIKE ‘%stmt%’;
±--------------------------±---------------+
| Variable_Name | Variable_Value |
±--------------------------±---------------+
| Com_backend_stmt_prepare | 402827 |
| Com_backend_stmt_execute | 899605 |
| Com_backend_stmt_close | 0 |
| Com_frontend_stmt_prepare | 900848 |
| Com_frontend_stmt_execute | 900035 |
| Com_frontend_stmt_close | 899332 |
| Stmt_Client_Active_Total | 11 |
| Stmt_Client_Active_Unique | 11 |
| Stmt_Server_Active_Total | 16551 |
| Stmt_Server_Active_Unique | 14014 |
| Stmt_Max_Stmt_id | 16273 |
| Stmt_Cached | 14736 |
±--------------------------±---------------+
12 rows in set (0.00 sec)
So, it looks like I have a total of 173 connections open. 173 * 20 = 3,460. Why are there >16K active statements?
The number of active statements show my proxysql matches up with why my percona DB is telling me as well.