High/Continous memory usage on ProxySQL 2.3.2

[Ann Minnu Sam]

Hi,

We are observing continuous increase in memory utilization  for both standalone (vm deployment) and Kubernetes deployment for proxySQL. Version used : 2.3.2
The growth trend is 30-50 MB per day. And when the utilization crosses 90% of the max_limit ,here 512 MB. the proxysql container are restarted.

Similarly in vm based deployments also , only restart is releasing the memory.

In vm based deployment as we are not restricting the memory , it is going up to 6 GB in 14 days of uptime. 

Attaching the screen shot of mem utilization trend of both k8 and vm deplyments

[Anish Kumar A K]

Is any way to control the memory utilisation of ProxySQL ...day by day it is increasing around 30 - 40MB . Is it really a memory leak ?

Please help . We configured proxysql as side car container and configured max limit as 500MB and restarting in every 7 days duration . 

[Gregor Fritz]

Hi,

We are observing a similar behaviour on our k8s clusters.

There are 3 ProxySQL instances configured as cluster in each of these environments.

On our staging cluster we see high memory usage but the memory is released periodically.

On our production cluster this looks totally different and we only see an increase but no release until we force a restart:

Of course we have tried imposing memory limits on the pods and although restart of ProxySQL is reasonably quick (~15 seconds until all three pods are restarted) this leads to errors in our applications which we would rather not have.

With a MALLOC_CONF of abort_conf:true,narenas:1,tcache:false,xmalloc:false,background_thread:true,dirty_decay_ms:0,muzzy_decay_ms:0 (following the documentation on jemalloc and how to use the least amount of memory possible) we were able to reduce the amount of memory the pods use at start and the peak usage (down from 20-30GB to 11GB).

Using ulimit to set limits on memory consumption did seem to be ignored.

We suspect the issue might come from the fact that ProxySQL/jemalloc sees the total memory capacity of the nodes.

Those same observations are not made on the ProxySQL instances we have deployed in our VM setups or OpenShift.

If anyone has any input we would be glad to hear it.

Kind regards,

Gregor Fritz

[Kevin Thompson]

Have you checked your query digest usage over time?  I debugged a proxysql memory growth issue some time ago, and was able to attribute it to that feature.  (I'm on 2.0.15-20 if that matters.)

I now use the following command in the crontab to clear the query digest once a day, and have not had to worry about uptime since:

mysql --defaults-group-suffix=proxysql -e "select 1 from stats_mysql_query_digest_reset limit 1" >/dev/null 2>&1

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/e580cf84-4315-4a5b-b7e1-38c7e53f93c5n%40googlegroups.com.

[René Cannaò]

SELECT * FROM stats_memory_metrics : this can help identify what is using memory, even if not all modules are tracked there (yet).

In 2.0.7, as an alternative to query stats_mysql_query_digest_reset we introduced:

TRUNCATE TABLE stats_mysql_query_digest;

To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/CAFn2N8vxfFvBgPzSkbqeNB3rj18sgdprrJDBKAS2itZ3HQwN1g%40mail.gmail.com.

[Ann Minnu Sam]

Hello René,

We tried truncating the  stats_mysql_query_digest and  'PROXYSQL FLUSH QUERY CACHE ' .   But no memory was released' .
Please find our observation when we ran proxysql with valgrind utility and it is showing memory leak. Attaching the screenshot reference. The proxysql (2.2.0) which we were monitoring reached 10GB in 4 days.

top command o/p - process id : 2908450



proxysql instance



leak summary from valgrind



We tested with proxysql 2.2.0 version. Also proxysql restart was releasing the memory. We have observed this kind of memory utilization trend in 2.4.2 also. 

Thanks & Regards

Ann Minnu Sam

6D Technologies.

You received this message because you are subscribed to a topic in the Google Groups "proxysql" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/proxysql/3MlSfFDYZ0U/unsubscribe.
To unsubscribe from this group and all its topics, send an email to proxysql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/CACJpxOZKVoGY0VDcfTtVJqAp_0TW-NaEJRY52q4%3Doun%2BTrxB4Q%40mail.gmail.com.

[Gregor Fritz]

Hi René,

from one of the ProxySQL pods in production environment (looks similar on all 3)

+------------------------------+----------------+
| SQLite3_memory_bytes         | 6015864        |
| jemalloc_resident            | 3758882816     |
| jemalloc_active              | 3695378432     |
| jemalloc_allocated           | 3533467352     |
| jemalloc_mapped              | 3845324800     |
| jemalloc_metadata            | 23472336       |
| jemalloc_retained            | 875888640      |
| Auth_memory                  | 1540           |
| query_digest_memory          | 356584         |
| mysql_query_rules_memory     | 17160          |
| mysql_firewall_users_table   | 0              |
| mysql_firewall_users_config  | 0              |
| mysql_firewall_rules_table   | 0              |
| mysql_firewall_rules_config  | 329            |
| stack_memory_mysql_threads   | 67108864       |
| stack_memory_admin_threads   | 33554432       |
| stack_memory_cluster_threads | 25165824       |
+------------------------------+----------------+

We are currently on v2.4.1. Tested v2.4.3 in our staging environment but did not see any change in memory usage.

TRUNCATE TABLE stats_mysql_query_digest; did not release any memory.

[René Cannaò]

Ann: I don't understand why you think that stats_mysql_query_digest and  'PROXYSQL FLUSH QUERY CACHE ' are somehow related ... They are two completely different things.

Gregor: before any further troubleshooting, you should try to upgrade (after proper testing). Several memory leaks have been fixed in the past, I don't know if you are affected by any of them, but upgrading should be the first step.

Thanks,

René

To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/7a7c4427-80d5-458b-9f3c-6e45b2648321n%40googlegroups.com.

[Gregor Fritz]

Hi René,

yes, I have seen that v2.4.4 was released. Will update and report back. Might take some days to a week until it reaches our production environment though.

Thanks,

Gregor

[Gregor Fritz]

Hi René

After running on v2.4.4 for almost a week we did initially see a vast improvement (1GB memory usage for all 3 instances, increasing to 3GB over a few days).

During the past weekend (more load than during the week but nowhere near peak load) memory usage increased to 9GB.

Please see attached screenshot for details.

Best regards,

Gregor

[Anish Kumar A K]

Is anything need to set or final tuning required from ProxySQL side to control the memory growth . We have disabled the query digest , but daily 10mb growth we can see in our staging environment . 

[Ann Minnu Sam]

We are still observing increase in memory utilization for proxysql in the kubernetes cluster deployment. We have disabled query_digests in the existing setup.
 
proxysql version: 2.4.2
mysql version version: 8.0.30

Attaching the proxysql configuration and the trend of jemalloc variables for reference.

Can you please suggest any other parameter has to set/disabled to avoid this continuous memory growth for production deployments.

[Anish Kumar A K]

Is anyone has faced this issue before and any permanent fix for this ?  We are facing this issue on Production environment and the last option is need to go back to MySQL Router .  

[Gregor Fritz]

We have deployed v2.5.1 to our production cluster today. We will know in a few days if issues with memory consumption have been resolved or are still persistent.

[Peter Lyoo]

Is there any update on v2.5.1?

[Gregor Fritz]

While 2.5.1 is doing better the issue still exists. Versions after that one contain some more bug fixes relating to memory issues but we have not gotten around to test them.