AnishKuma
commented
1 year ago We are also facing the same issue related to Jemalloc . Is any permanent fix ?
Open aakashmuthuramalingam opened 3 years ago
AnishKuma
commented
1 year ago We are also facing the same issue related to Jemalloc . Is any permanent fix ?
dis1
commented
1 year ago Have same problem. We need to restart proxysql every night.
nikopavlica
commented
11 months ago We're facing the exact same issue using proxysql/proxysql:2.5.4 container image and our remediation is to restart the service twice a day - we're running proxy as a sidecar container in kubernetes cluster, where i don't want to have proxy sidecar consuming more memory than main app.
Our stats are:
MySQL [(none)]> SELECT Variable_Name, Variable_Value / 1024 / 1024 as "Usage in MB" FROM stats.stats_memory_metrics ORDER BY 2 DESC;
+------------------------------+-------------+
| Variable_Name | Usage in MB |
+------------------------------+-------------+
| jemalloc_mapped | 1353 |
| jemalloc_resident | 1311 |
| jemalloc_active | 1290 |
| jemalloc_allocated | 1278 |
| jemalloc_retained | 260 |
| stack_memory_mysql_threads | 32 |
| jemalloc_metadata | 16 |
| stack_memory_admin_threads | 8 |
| SQLite3_memory_bytes | 3 |
| Auth_memory | 0 |
| query_digest_memory | 0 |
| mysql_query_rules_memory | 0 |
| mysql_firewall_users_table | 0 |
| mysql_firewall_users_config | 0 |
| mysql_firewall_rules_table | 0 |
| mysql_firewall_rules_config | 0 |
| stack_memory_cluster_threads | 0 |MySQL [(none)]> SELECT * FROM stats_mysql_global;
+----------------------------------------------+----------------+
| Variable_Name | Variable_Value |
+----------------------------------------------+----------------+
| ProxySQL_Uptime | 202852 |
| Active_Transactions | 0 |
| Client_Connections_aborted | 0 |
| Client_Connections_connected | 2 |
| Client_Connections_created | 5232048 |
| Server_Connections_aborted | 20 |
| Server_Connections_connected | 6 |
| Server_Connections_created | 31845 |
| Server_Connections_delayed | 0 |
| Client_Connections_non_idle | 2 |
| mysql_backend_buffers_bytes | 70904 |
| mysql_frontend_buffers_bytes | 131072 |
| mysql_session_internal_bytes | 20104 |
| Com_autocommit | 0 |
| Com_autocommit_filtered | 0 |
| Com_commit | 0 |
| Com_commit_filtered | 0 |
| Com_rollback | 0 |
| Com_rollback_filtered | 0 |
| Com_backend_change_user | 39347 |
| Com_backend_init_db | 0 |
| Com_backend_set_names | 33119 |
| Com_frontend_init_db | 0 |
| Com_frontend_set_names | 5231596 |
| Com_frontend_use_db | 0 |
| Com_backend_stmt_prepare | 1032806 |
| Com_backend_stmt_execute | 3800590 |
| Com_backend_stmt_close | 0 |
| Com_frontend_stmt_prepare | 3800590 |
| Com_frontend_stmt_execute | 3800590 |
| Com_frontend_stmt_close | 3800138 |
| Questions | 51516193 |
| Slow_queries | 783 |
| GTID_consistent_queries | 0 |
| GTID_session_collected | 0 |
| Queries_backends_bytes_recv | 47757619971 |
| Queries_backends_bytes_sent | 62963684248 |
| Queries_frontends_bytes_recv | 65898557177 |
| Queries_frontends_bytes_sent | 122552706640 |
| Query_Processor_time_nsec | 60545226190 |
| Backend_query_time_nsec | 1846762272719 |
| ConnPool_get_conn_latency_awareness | 0 |
| ConnPool_get_conn_immediate | 57320 |
| ConnPool_get_conn_success | 38687680 |
| ConnPool_get_conn_failure | 0 |
| mysql_killed_backend_connections | 0 |
| mysql_killed_backend_queries | 0 |
| hostgroup_locked_set_cmds | 0 |
| hostgroup_locked_queries | 0 |
| mysql_unexpected_frontend_com_quit | 0 |
| mysql_unexpected_frontend_packets | 0 |
| queries_with_max_lag_ms__total_wait_time_us | 0 |
| queries_with_max_lag_ms__delayed | 0 |
| queries_with_max_lag_ms | 0 |
| backend_lagging_during_query | 0 |
| backend_offline_during_query | 0 |
| get_aws_aurora_replicas_skipped_during_query | 0 |
| automatic_detected_sql_injection | 0 |
| whitelisted_sqli_fingerprint | 0 |
| max_connect_timeouts | 0 |
| generated_error_packets | 0 |
| client_host_error_killed_connections | 0 |
| Client_Connections_hostgroup_locked | 0 |
| Mirror_concurrency | 0 |
| Mirror_queue_length | 0 |
| Selects_for_update__autocommit0 | 0 |
| Servers_table_version | 1 |
| MySQL_Thread_Workers | 2 |
| Access_Denied_Wrong_Password | 0 |
| Access_Denied_Max_Connections | 0 |
| Access_Denied_Max_User_Connections | 0 |
| MySQL_Monitor_Workers | 0 |
| MySQL_Monitor_Workers_Aux | 0 |
| MySQL_Monitor_Workers_Started | 0 |
| MySQL_Monitor_connect_check_OK | 0 |
| MySQL_Monitor_connect_check_ERR | 0 |
| MySQL_Monitor_ping_check_OK | 0 |
| MySQL_Monitor_ping_check_ERR | 0 |
| MySQL_Monitor_read_only_check_OK | 0 |
| MySQL_Monitor_read_only_check_ERR | 0 |
| MySQL_Monitor_replication_lag_check_OK | 0 |
| MySQL_Monitor_replication_lag_check_ERR | 0 |
| MySQL_Monitor_dns_cache_queried | 31845 |
| MySQL_Monitor_dns_cache_lookup_success | 31845 |
| MySQL_Monitor_dns_cache_record_updated | 3 |
| MyHGM_myconnpoll_get | 38630360 |
| MyHGM_myconnpoll_get_ok | 38630360 |
| MyHGM_myconnpoll_push | 38639496 |
| MyHGM_myconnpoll_destroy | 21 |
| MyHGM_myconnpoll_reset | 0 |
| SQLite3_memory_bytes | 3593728 |
| ConnPool_memory_bytes | 361872 |
| Stmt_Client_Active_Total | 0 |
| Stmt_Client_Active_Unique | 0 |
| Stmt_Server_Active_Total | 38 |
| Stmt_Server_Active_Unique | 27 |
| Stmt_Max_Stmt_id | 4045 |
| Stmt_Cached | 4044 |
| Query_Cache_Memory_bytes | 0 |
| Query_Cache_count_GET | 0 |
| Query_Cache_count_GET_OK | 0 |
| Query_Cache_count_SET | 0 |
| Query_Cache_bytes_IN | 0 |
| Query_Cache_bytes_OUT | 0 |
| Query_Cache_Purged | 0 |
| Query_Cache_Entries | 0 |
| new_req_conns_count | 0 |
+----------------------------------------------+----------------+
107 rows in set (0.003 sec)We'll try to test it with v2.5.5 to see if it fixes it.
renecannao
commented
11 months ago Are you saying that a process consuming 1.3GB of memory is a lot?
i don't want to have proxy sidecar consuming more memory than main app.
I understand that.
It is worth to note that ProxySQL is caching metadata for 4044 prepared statements, filtered 5.6M COM_STMT_ requests out of 11.4M (almost 50%) , and reduced the number of connections to the backend by 99.4% .
Yes, the proxy sidecar is consuming more memory then the app, but it is way way way more efficient than the app in managing connections and running queries.
The app seems completely stateless, it connects, and on average it disconnects after less than 10 requests. Considering it is also using prepared statements, maybe each connections run on average 5-6 queries only. Very basic application.
That said, it your application is extremely simple, you should consider moving ProxySQL to a middle layer to serve multiple applications at the same time.
renecannao
commented
11 months ago We'll try to test it with v2.5.5 to see if it fixes it.
Unless you are pointing to a memory leak (and so far there is no evidence of a memory leak), there is nothing to be fixed. ProxySQL is using memory based on the actions it needs to perform. As I explained in my previous reply, it is performing a lot of tasks that your application is not performing, like connection pool and the re-use of prepared statement. This has a cost. Please do not confuse ProxySQL with a TCP load balancer: a TCP loadbalancer will consume less memory and probably less CPU too , but will not optimize your workload. With ProxySQL you get less queries, less network traffic, less load to the backends, faster response time, faster connect time, and much more.
nikopavlica
commented
11 months ago Are you saying that a process consuming 1.3GB of memory is a lot?
This depends on the context. In our context, where main app is consuming 1.5GB, yes. (and we run between 10 and 200 of such pods, autoscaled based on traffic).
Without proxysql, we got very unpredictable number of connections (since some "long" requests made additional fpm workers spin up and establish new db connections), while with proxysql, the number of opened connections is constant.
All that said - I have no problem of having 1.3GB of memory for this, as long as i know this is the limit - but its not. Its just growing. And the fact that I don't know where it will stop is bothering me.
It is worth to note that ProxySQL is caching metadata for 4044 prepared statements
Is it possible that this is the main usage of the memory? Bellow i'm attaching memory/stats from the same pod as above, but from earlier time where memory didn't yet grow that big.
MySQL [(none)]> SELECT Variable_Name, Variable_Value / 1024 / 1024 as "Usage in MB" FROM stats.stats_memory_metrics ORDER BY 2 DESC;
+------------------------------+-------------+
| Variable_Name | Usage in MB |
+------------------------------+-------------+
| jemalloc_mapped | 257 |
| jemalloc_resident | 221 |
| jemalloc_active | 211 |
| jemalloc_allocated | 206 |
| jemalloc_retained | 74 |
| stack_memory_mysql_threads | 32 |
| stack_memory_admin_threads | 8 |
| jemalloc_metadata | 6 |
| SQLite3_memory_bytes | 2 |
| Auth_memory | 0 |
| query_digest_memory | 0 |
| mysql_query_rules_memory | 0 |
| mysql_firewall_users_table | 0 |
| mysql_firewall_users_config | 0 |
| mysql_firewall_rules_table | 0 |
| mysql_firewall_rules_config | 0 |
| stack_memory_cluster_threads | 0 |
+------------------------------+-------------+
17 rows in set (0.003 sec)
MySQL [(none)]> SELECT * FROM stats_mysql_global;
+----------------------------------------------+----------------+
| Variable_Name | Variable_Value |
+----------------------------------------------+----------------+
| ProxySQL_Uptime | 10770 |
| Active_Transactions | 0 |
| Client_Connections_aborted | 0 |
| Client_Connections_connected | 2 |
| Client_Connections_created | 251986 |
| Server_Connections_aborted | 0 |
| Server_Connections_connected | 7 |
| Server_Connections_created | 1251 |
| Server_Connections_delayed | 0 |
| Client_Connections_non_idle | 2 |
| mysql_backend_buffers_bytes | 141808 |
| mysql_frontend_buffers_bytes | 131072 |
| mysql_session_internal_bytes | 20896 |
| Com_autocommit | 0 |
| Com_autocommit_filtered | 0 |
| Com_commit | 0 |
| Com_commit_filtered | 0 |
| Com_rollback | 0 |
| Com_rollback_filtered | 0 |
| Com_backend_change_user | 2169 |
| Com_backend_init_db | 0 |
| Com_backend_set_names | 1860 |
| Com_frontend_init_db | 0 |
| Com_frontend_set_names | 251964 |
| Com_frontend_use_db | 0 |
| Com_backend_stmt_prepare | 53908 |
| Com_backend_stmt_execute | 177577 |
| Com_backend_stmt_close | 0 |
| Com_frontend_stmt_prepare | 177577 |
| Com_frontend_stmt_execute | 177577 |
| Com_frontend_stmt_close | 177555 |
| Questions | 2408822 |
| Slow_queries | 9 |
| GTID_consistent_queries | 0 |
| GTID_session_collected | 0 |
| Queries_backends_bytes_recv | 2297049603 |
| Queries_backends_bytes_sent | 2973306733 |
| Queries_frontends_bytes_recv | 3104116457 |
| Queries_frontends_bytes_sent | 5796356765 |
| Query_Processor_time_nsec | 3037872561 |
| Backend_query_time_nsec | 91608859639 |
| ConnPool_get_conn_latency_awareness | 0 |
| ConnPool_get_conn_immediate | 2238 |
| ConnPool_get_conn_success | 1802829 |
| ConnPool_get_conn_failure | 0 |
| mysql_killed_backend_connections | 0 |
| mysql_killed_backend_queries | 0 |
| hostgroup_locked_set_cmds | 0 |
| hostgroup_locked_queries | 0 |
| mysql_unexpected_frontend_com_quit | 0 |
| mysql_unexpected_frontend_packets | 0 |
| queries_with_max_lag_ms__total_wait_time_us | 0 |
| queries_with_max_lag_ms__delayed | 0 |
| queries_with_max_lag_ms | 0 |
| backend_lagging_during_query | 0 |
| backend_offline_during_query | 0 |
| get_aws_aurora_replicas_skipped_during_query | 0 |
| automatic_detected_sql_injection | 0 |
| whitelisted_sqli_fingerprint | 0 |
| max_connect_timeouts | 0 |
| generated_error_packets | 0 |
| client_host_error_killed_connections | 0 |
| Client_Connections_hostgroup_locked | 0 |
| Mirror_concurrency | 0 |
| Mirror_queue_length | 0 |
| Selects_for_update__autocommit0 | 0 |
| Servers_table_version | 1 |
| MySQL_Thread_Workers | 2 |
| Access_Denied_Wrong_Password | 0 |
| Access_Denied_Max_Connections | 0 |
| Access_Denied_Max_User_Connections | 0 |
| MySQL_Monitor_Workers | 0 |
| MySQL_Monitor_Workers_Aux | 0 |
| MySQL_Monitor_Workers_Started | 0 |
| MySQL_Monitor_connect_check_OK | 0 |
| MySQL_Monitor_connect_check_ERR | 0 |
| MySQL_Monitor_ping_check_OK | 0 |
| MySQL_Monitor_ping_check_ERR | 0 |
| MySQL_Monitor_read_only_check_OK | 0 |
| MySQL_Monitor_read_only_check_ERR | 0 |
| MySQL_Monitor_replication_lag_check_OK | 0 |
| MySQL_Monitor_replication_lag_check_ERR | 0 |
| MySQL_Monitor_dns_cache_queried | 1251 |
| MySQL_Monitor_dns_cache_lookup_success | 1251 |
| MySQL_Monitor_dns_cache_record_updated | 3 |
| MyHGM_myconnpoll_get | 1800591 |
| MyHGM_myconnpoll_get_ok | 1800591 |
| MyHGM_myconnpoll_push | 1801126 |
| MyHGM_myconnpoll_destroy | 0 |
| MyHGM_myconnpoll_reset | 0 |
| SQLite3_memory_bytes | 2833696 |
| ConnPool_memory_bytes | 361888 |
| Stmt_Client_Active_Total | 0 |
| Stmt_Client_Active_Unique | 0 |
| Stmt_Server_Active_Total | 49 |
| Stmt_Server_Active_Unique | 27 |
| Stmt_Max_Stmt_id | 1124 |
| Stmt_Cached | 1123 |
| Query_Cache_Memory_bytes | 0 |
| Query_Cache_count_GET | 0 |
| Query_Cache_count_GET_OK | 0 |
| Query_Cache_count_SET | 0 |
| Query_Cache_bytes_IN | 0 |
| Query_Cache_bytes_OUT | 0 |
| Query_Cache_Purged | 0 |
| Query_Cache_Entries | 0 |
| new_req_conns_count | 0 |
+----------------------------------------------+----------------+I did noticed a slight increase in cached statements (1123 => 4044), but this increase didn't match the increase in memory (211MB => 1290MB). But now that you mention it, I'll try to limit this down to 2000 and see if it helps me stabilize the memory usage. Given more thought, it would explain the constant "leak". Will report back!
nikopavlica
commented
11 months ago Ok, looks like this really was the "culprit" 😅. After limiting mysql-max_stmts_cache, we now see memory being reclaimed when it reaches the limit, without any loss of functionality.
Our case is then solved and is probably not the same as the OP is having (given that he doesn't have any prepared statements cached).
Sorry for hijacking the issue, but thanks for your help finding our "issue" @renecannao 🍻
We are running proxysql instance(2.0.7) for 3-node Percona XtraDB Cluster. Currently proxysql is using 95% of the total memory(16GB) of the system and it is keep on increasing. As checked with the stats memory tables, neither query cache nor query digest is consuming memory(only 10MB), it is completely used by Jemalloc_* about 13GB. The below is the split up.
The below is the output of show mysql status
It is ubuntu 16 and the package We have used for the installation is https://github.com/sysown/proxysql/releases/download/v2.0.7/proxysql_2.0.7-ubuntu16_amd64.deb
Here the question is
Please let me know for any further details required.