ProxySQL opens up a lot of backend connections
maria....@gmail.com
I am testing proxySQL for my production environment. I have configured proxySQL on elasticbeanstalk with 4 min and 8 max instances, each with c3.4xlarge instance size. I can see proxySQL with 4 instances up atm created a lot of backend connections to RDS. Each instance has created more than 500 connections.
My understanding according to this documentation (https://github.com/sysown/proxysql/wiki/Detail-Answer's-On-FAQ) is that proxysql can have mysql-free_connections_pct * mysql_servers.max_connections / 100 idle connections in pool, which according to my settings should be 100.
Here is the output of some of the queries to debug the issue.
SHOW
VARIABLES LIKE 'mysql-free_connections_pct';
+----------------------------+-----------------+
| Variable_name | Value |
+----------------------------+----------------+
| mysql-free_connections_pct | 10 |
+----------------------------+-----------------+
SELECT hostgroup_id, max_connections FROM mysql_servers;
+--------------+---------------------------+
| hostgroup_id | max_connections |
+--------------+---------------------------+
| 0 | 1000 |
+--------------+---------------------------+
SHOW MYSQL STATUS;
+------------------------------+-------------+
| Variable_name | Value |
+------------------------------+-------------+
| Active_Transactions | 0 |
| Backend_query_time_nsec | 80330120849 |
| Client_Connections_aborted | 135771 |
| Client_Connections_connected | 459 |
| Client_Connections_created | 311873 |
| Client_Connections_non_idle | 459 |
| Com_autocommit | 1292650 |
| Com_autocommit_filtered | 1292650 |
| Com_commit | 627130 |
| Com_commit_filtered | 627004 |
| Com_rollback | 0 |
| Com_rollback_filtered | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 180 |
| Com_stmt_prepare | 180 |
| ConnPool_get_conn_failure | 0 |
| ConnPool_get_conn_immediate | 0 |
| ConnPool_get_conn_success | 37191 |
| ConnPool_memory_bytes | 7488 |
| MySQL_Monitor_Workers | 16 |
| MySQL_Thread_Workers | 10 |
| Queries_backends_bytes_recv | 1816493238 |
| Queries_backends_bytes_sent | 565848112 |
| Query_Cache_Entries | 0 |
| Query_Cache_Memory_bytes | 0 |
| Query_Cache_Purged | 0 |
| Query_Cache_bytes_IN | 0 |
| Query_Cache_bytes_OUT | 0 |
| Query_Cache_count_GET | 0 |
| Query_Cache_count_GET_OK | 0 |
| Query_Cache_count_SET | 0 |
| Query_Processor_time_nsec | 4074597829 |
| Questions | 4941793 |
| SQLite3_memory_bytes | 1344000 |
| Server_Connections_aborted | 0 |
| Server_Connections_connected | 459 |
| Server_Connections_created | 37191 |
| Servers_table_version | 1 |
| Slow_queries | 11 |
| Stmt_Active_Total | 0 |
| Stmt_Active_Unique | 0 |
| Stmt_Max_Stmt_id | 1 |
| mysql_backend_buffers_bytes | 32548608 |
| mysql_frontend_buffers_bytes | 30081024 |
| mysql_session_internal_bytes | 965232 |
+------------------------------+-------------+
select ThreadID, command, time_ms from stats.stats_mysql_processlist order by time_ms desc; (which shows that all connections are in sleep mode since so long)
+----------+---------+----------+
| ThreadID | command | time_ms |
+----------+---------+----------+
| 3 | Sleep | 27974629 |
| 9 | Sleep | 27974618 |
| 0 | Sleep | 27974604 |
| 2 | Sleep | 27974589 |
| 3 | Sleep | 27974578 |
| 7 | Sleep | 27974568 |
| 1 | Sleep | 27974555 |
(Truncated)
May I know the reason of so many open
connections? Or am I missing something?
Thanks in advance J
René Cannaò
--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.