Not the answer you need?
Register and ask your own question!

ProxySQL Stmt_Server_Active_Total much higher then it should be

ufjohnufjohn EntrantCurrent User Role Novice
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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.