proxysql memory leak issue

[TokuiNico]

Report

Hi, I used helm to install percona/pxc-operator and percona/pxc-db version 1.14, and enabled the built-in proxysql as the proxy for the cluster.

Recently, I noticed that the memory usage of proxysql is increasing, indicating a memory leak issue. The version of proxysql being used is 2.5.5-percona-1.1, with the original settings in place and query digest turned off.

More about the problem

jemalloc_resident increasing to 583 MB

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              | 629         |
| jemalloc_resident            | 583         |
| jemalloc_active              | 545         |
| jemalloc_allocated           | 462         |
| SQLite3_memory_bytes         | 428         |
| jemalloc_retained            | 134         |
| stack_memory_admin_threads   | 40          |
| stack_memory_cluster_threads | 30          |
| stack_memory_mysql_threads   | 20          |
| jemalloc_metadata            | 10          |
| 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           |
+------------------------------+-------------+
17 rows in set (0.01 sec)

Steps to reproduce

1. install helm chat percona/pxc-operator, percona/pxc-db
2. disable query digest
3. wait 2 days

Versions

1. Kubernetes: 1.25.16
2. Operator: 1.14
3. Database: 1.14

Anything else?

No response

[spron-in]

@TokuiNico I discussed it briefly with our MySQL experts, they are asking if there is any load on the database.

could you please run

SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;
SELECT * FROM stats_mysql_errors_reset LIMIT 1;
SELECT * FROM stats_mysql_connection_pool_reset LIMIT 1;
SELECT * FROM stats_mysql_client_host_cache_reset LIMIT 1;

and show the memory stats once again?

[TokuiNico]

Hi @spron-in, After 3 weeks, the memory usage of ProxySQL has increased to 3GB and did not decrease after running the SQL query mentioned above.

 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              | 3362        |
| jemalloc_resident            | 3308        |
| jemalloc_active              | 3241        |
| jemalloc_allocated           | 3154        |
| jemalloc_retained            | 265         |
| stack_memory_admin_threads   | 50          |
| jemalloc_metadata            | 43          |
| stack_memory_cluster_threads | 30          |
| stack_memory_mysql_threads   | 20          |
| 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           |
| SQLite3_memory_bytes         | -1119       |
+------------------------------+-------------+
17 rows in set (0.00 sec)

[TokuiNico]

Hi @spron-in https://github.com/sysown/proxysql/issues/4491

There is a known Memory Leak issue in proxysql 2.5.5, which has been fixed in version 2.6.3.

[alvinsxh]

Hi @spron-in I have the same problem. I use kubernetes to run percona-xtradb-cluster. Memory usage of proxysql increases continuously, and finally it crashes with OOM error. The result of stats_memory_metrics indicates that almost all memory is used by SQLite3_memory_bytes. Additionally, I found that when I shutdown percona-xtradb-cluster-operator, memory usage of proxysql stops increasing immediately. And when I redeploy percona-xtradb-cluster-operator, memory usage of proxysql start to increase again.

[alvinsxh]

Hi @spron-in I have the same problem. I use kubernetes to run percona-xtradb-cluster. Memory usage of proxysql increases continuously, and finally it crashes with OOM error. The result of stats_memory_metrics indicates that almost all memory is used by SQLite3_memory_bytes. Additionally, I found that when I shutdown percona-xtradb-cluster-operator, memory usage of proxysql stops increasing immediately. And when I redeploy percona-xtradb-cluster-operator, memory usage of proxysql start to increase again.

I think I have found the key point of my problem:

1. percona-xtradb-cluster-operator loops Reconcile PerconaXtraDBCluster for every 5 seconds.
2. In the Reconcile function, operator call resyncPXCUsersWithProxySQL function and finally does command execution towards pods of proxysql, which command is proxysql-admin --syncusers --add-query-rule.
3. In the proxysql-admin shell script, command SAVE MYSQL USERS FROM RUNTIME may cause the suspicious "memory leak".

I have tested almost all sub commands of proxysql-admin --syncusers --add-query-rule, and only SAVE MYSQL USERS FROM RUNTIME increases memory permanently.

[alvinsxh]

Hi @spron-in I have the same problem. I use kubernetes to run percona-xtradb-cluster. Memory usage of proxysql increases continuously, and finally it crashes with OOM error. The result of stats_memory_metrics indicates that almost all memory is used by SQLite3_memory_bytes. Additionally, I found that when I shutdown percona-xtradb-cluster-operator, memory usage of proxysql stops increasing immediately. And when I redeploy percona-xtradb-cluster-operator, memory usage of proxysql start to increase again.

I think I have found the key point of my problem:

1. percona-xtradb-cluster-operator loops Reconcile PerconaXtraDBCluster for every 5 seconds.
2. In the Reconcile function, operator call resyncPXCUsersWithProxySQL function and finally does command execution towards pods of proxysql, which command is proxysql-admin --syncusers --add-query-rule.
3. In the proxysql-admin shell script, command SAVE MYSQL USERS FROM RUNTIME may cause the suspicious "memory leak".

I have tested almost all sub commands of proxysql-admin --syncusers --add-query-rule, and only SAVE MYSQL USERS FROM RUNTIME increases memory permanently.

My problem has been located: https://github.com/sysown/proxysql/issues/4567