ProxySQL critical memory spike

[AnishKuma]

We are still observing increase in memory utilization for proxysql in the kubernetes cluster deployment. We have disabled query_digests in the existing setup based on suggestion mentioned in one of the site . While analysing stats_memory_metrics we are suspecting the memory hike on jemalloc. And finally leading to ProxySQL pod restart which is affecting our production live traffic .

proxysql version: 2.4.2 mysql version version: 8.0.30 Environment : K8 cluster / EL8 ( RHEL)

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. Detailed information has attached .

Thank you! proxysql_cnf.txt proxysqlAnalysis.xlsx

[renecannao]

We have disabled query_digests in the existing setup based on suggestion mentioned in one of the site .

Please don't . It is a terrible idea. ProxySQL won't understand when it needs to disable multiplexing.

According to the output you provided, on day 5 ProxySQL is using 83MB , and jemalloc has 146 MB memory mapped. This is NOT critical memory usage, at all.

This doesn't match at all with the graph you attached. Thus something else is using memory.

Closing

[AnishKuma]

Thank you for the information that related to query digest . we will take care that . And in production , it is running with enabled mode only .

Please ignore jemalloc result as of now which we shared in the last thread . It was taken from our standalone test setup. The issue is faced in our production environment where we have kubernetes cluster and the max memory memory that can be allocated to proxysql is 1.5GB. Our observation is once proxy is started it is consuming avg 30MB every day and when it reaches 1.5GB (the limit given for the proxysql pod) the proxysql pod restarts hence affecting the live traffic.

The daily growth can be verifed from the Grafana output shared. We are unable to identify the factor/parameter that is causing this unlimited memory cons Please refer the ProxySQL configuration attached earlier .

1. Last 7 days Trend
2. Last 30 days - last restart happened on 28-03-2023 , started with memory of 154mb and on 14th day (11-04-2023) it is taking 614mb and still growing and daily growth of 30mb .

4. Stats_memory_matrics

5. ProxySQL.cnf proxy_cnf.txt

Please let me know , anymore information is required .

[renecannao]

1 GB (the limit given for the proxysql pod)

Why 1 GB ?

[AnishKuma]

Generally in a micro service kind of deployment , there is some limitation to allocate huge memory for each pod similar to Application module level pod . While considering ProxySQL as a lightweight component we have allocated 1.5 GB limitation .

Can you please suggest here , normally how much we can allocate memory for ProxySQL as a POD level deployment (micro service ) in a high OLTP application environment . In vm based deployments we never used to give any memory limit and seeing memory increase going unlimited .

[renecannao]

in a high OLTP application environment

Do you run MySQL with a 1 GB limit too? ProxySQL is not a lightweight proxy, but a complex server that is protocol aware, performs routing, tracking, multiplexing. ProxySQL can uses 50MB of memory for very simple workloads, or tens of GB for workload with a high QPS, complex prepared statements, hundreds of thousands of client connections, etc. It has a cost in both CPU and memory utilization. It really depends from the tasks it needs to perform.

If you want a lightweight proxy I would recommend HAProxy . If you want a proxy that can give you a lot of features and optimize traffic, use ProxySQL .

Assigning 1GB limit to ProxySQL seems pretty random, especially "in a high OLTP application environment" . You first need to understand how much memory it needs for your workload and environment .

Unrelated: please fix how you reply to this github issue, as each of your reply is creating a new issue.

[AnishKuma]

Then how ProxySQL has deployed side car container in a microservice environment ? For MySQL , it is a database and working as non micro service and configured 120 GB RAM .

As per your input , it is not a lightweight component and cannot deployed in a microservice environment . And we never treated ProxySQL like a DB server to allocate > 100GB of RAM . Thanks for your detailed input and will just think once again ProxySQL before deploying it in a microservice cluster environment .

[renecannao]

As mentioned previously ProxySQL can run less than 100MB of memory, but it really depends from the tasks it needs to perform and from your workload: I know nothing about your workload and query pattern.

ProxySQL is often deployed as a sidecar and in microservice environments, with appropriate memory constraints. But again: the amount of memory it needs to use depends a lot from configuration, workload and query pattern. You first need to identify how much memory it needs for your workload and environment .