Repository: sysown/proxysql (language: cpp)

Issue:
Memory leak in 2.5.5
We have upgraded and running ProxySQL version 2.5.5-10-g195bd70, package used - [proxysql_2.5.5-ubuntu20_amd64.deb](https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/focal/proxysql_2.5.5-ubuntu20_amd64.deb). 
Before we used version 2.5.3 
Distro we use: Ubuntu 20.04.6 LTS \n \l

We're having memory leak issues in all our proxysql instances. It was issue v=before with 2.5.3 and with current 2.5.5.

We do truncate `stats_mysql_query_digest` once a day. 

Also from here you can see that it is not the main issue:
```
mysql> select Variable_Name,Variable_Value/1024/1024 as Mem_usage_in_MB from stats_memory_metrics;
+------------------------------+-----------------+
| Variable_Name                | Mem_usage_in_MB |
+------------------------------+-----------------+
| SQLite3_memory_bytes         | 670             |
| jemalloc_resident            | 771             |
| jemalloc_active              | 735             |
| jemalloc_allocated           | 724             |
| jemalloc_mapped              | 839             |
| jemalloc_metadata            | 24              |
| jemalloc_retained            | 170             |
| Auth_memory                  | 0               |
| query_digest_memory          | 0               |
| mysql_query_rules_memory     | 2               |
| mysql_firewall_users_table   | 0               |
| mysql_firewall_users_config  | 0               |
| mysql_firewall_rules_table   | 0               |
| mysql_firewall_rules_config  | 0               |
| stack_memory_mysql_threads   | 64              |
| stack_memory_admin_threads   | 32              |
| stack_memory_cluster_threads | 24              |
+------------------------------+-----------------+
```
this is an example of one instance memory growing. other instances have the same pattern 
![Screenshot 2024-04-01 at 15 48 45](https://github.com/sysown/proxysql/assets/6085951/bf4b737a-36f0-40ec-a241-6c7b88d8945e)

As you can see from the picture - we did 2 restarts during this time

proxysql.cnf 
```
datadir="/mdata01/proxysql"
errorlog="/var/log/proxysql/proxysql.log"

admin_variables=
{
    admin_credentials="proxysql-admin:passwd"
    mysql_ifaces="0.0.0.0:6032;/tmp/proxysql-admin.sock"
    cluster_username="proxysql-admin"
    cluster_password="passwd"
    cluster_check_interval_ms=5000
    cluster_check_status_frequency=10
    cluster_mysql_query_rules_diffs_before_sync=3
    cluster_mysql_servers_diffs_before_sync=3
    cluster_mysql_users_diffs_before_sync=3
    cluster_proxysql_servers_diffs_before_sync=3
    restapi_enabled=true
    restapi_port=6080
}

mysql_variables=
{
    # General
    interfaces="0.0.0.0:6033"
    default_schema="information_schema"

    # Connection handling
    connect_timeout_server=3000
    connect_retries_on_failure=3
    connection_max_age_ms=10000
    default_charset="utf8mb4"
    default_collation_connection="utf8mb4_0900_ai_ci"
    max_connections=2000
    multiplexing=false
    poll_timeout=2000
    query_digests_keep_comment=false
    wait_timeout=181000

    # Internal pings and monitoring
    monitor_username="proxysql-monitor"
    monitor_password="passwd"
    monitor_connect_interval=20000
    monitor_ping_interval=10000
    monitor_read_only_interval=1000
    monitor_read_only_timeout=800
    monitor_replication_lag_interval=1000
    monitor_replication_lag_group_by_host=true
    monitor_writer_is_also_reader=false
    ping_interval_server_msec=10000
    ping_timeout_server=500

    # Queries handling
    enable_client_deprecate_eof=false
    enable_server_deprecate_eof=false
    query_retries_on_failure=0
    set_query_lock_on_hostgroup=true
	verbose_query_error=true
    hostgroup_manager_verbose=0
}

```

To mention inside proxysql we have: 
```
mysql> select count(*) from mysql_replication_hostgroups;
+----------+
| count(*) |
+----------+
| 1094     |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from mysql_servers;
+----------+
| count(*) |
+----------+
| 3282     |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from mysql_query_rules_fast_routing;
+----------+
| count(*) |
+----------+
| 4376     |
+----------+
1 row in set (0.00 sec)

```

We utilise mysql_servers to have custom max_connections limits per hostgroup.

Including memory profile files here - 
[proxysql_memory_dump.tar.gz](https://github.com/sysown/proxysql/files/14826607/proxysql_memory_dump.tar.gz)

It includes 4h of memory dumps, the memory leakage trend can be seen here as well: 
![Screenshot 2024-04-01 at 20 00 41](https://github.com/sysown/proxysql/assets/6085951/1dc6428d-8fde-47f7-9586-d35962d81db7)




Discussion / hints:
Hi @vladzu .
Before further debugging, can you please also attach the output of `SELECT * FROM stats_mysql_global;` ?
Thanks
Hi @renecannao ,
sure 

```
mysql> SELECT * FROM stats_mysql_global;
+----------------------------------------------+----------------+
| Variable_Name                                | Variable_Value |
+----------------------------------------------+----------------+
| ProxySQL_Uptime                              | 1641942        |
| Active_Transactions                          | 0              |
| Client_Connections_aborted                   | 0              |
| Client_Connections_connected                 | 0              |
| Client_Connections_created                   | 54732          |
| Server_Connections_aborted                   | 0              |
| Server_Connections_connected                 | 0              |
| Server_Connections_created                   | 0              |
| Server_Connections_delayed                   | 0              |
| Client_Connections_non_idle                  | 0              |
| mysql_backend_buffers_bytes                  | 0              |
| mysql_frontend_buffers_bytes                 | 0              |
| mysql_session_internal_bytes                 | 20512          |
| 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                      | 0              |
| Com_backend_init_db                          | 0              |
| Com_backend_set_names                        | 0              |
| Com_frontend_init_db                         | 0              |
| Com_frontend_set_names                       | 0              |
| Com_frontend_use_db                          | 0              |
| Com_backend_stmt_prepare                     | 0              |
| Com_backend_stmt_execute                     | 0              |
| Com_backend_stmt_close                       | 0              |
| Com_frontend_stmt_prepare                    | 0              |
| Com_frontend_stmt_execute                    | 0              |
| Com_frontend_stmt_close                      | 0              |
| Questions                                    | 0              |
| Slow_queries                                 | 0              |
| GTID_consistent_queries                      | 0              |
| GTID_session_collected                       | 0              |
| Queries_backends_bytes_recv                  | 0              |
| Queries_backends_bytes_sent                  | 0              |
| Queries_frontends_bytes_recv                 | 0              |
| Queries_frontends_bytes_sent                 | 4269096        |
| Query_Processor_time_nsec                    | 0              |
| Backend_query_time_nsec                      | 0              |
| ConnPool_get_conn_latency_awareness          | 0              |
| ConnPool_get_conn_immediate                  | 0              |
| ConnPool_get_conn_success                    | 0              |
| 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                        | 41320          |
| MySQL_Thread_Workers                         | 4              |
| Access_Denied_Wrong_Password                 | 0              |
| Access_Denied_Max_Connections                | 0              |
| Access_Denied_Max_User_Connections           | 0              |
| MySQL_Monitor_Workers                        | 8              |
| MySQL_Monitor_Workers_Aux                    | 0              |
| MySQL_Monitor_Workers_Started                | 8              |
| MySQL_Monitor_connect_check_OK               | 164172         |
| MySQL_Monitor_connect_check_ERR              | 0              |
| MySQL_Monitor_ping_check_OK                  | 328334         |
| MySQL_Monitor_ping_check_ERR                 | 0              |
| MySQL_Monitor_read_only_check_OK             | 3283590        |
| MySQL_Monitor_read_only_check_ERR            | 0              |
| MySQL_Monitor_replication_lag_check_OK       | 3283589        |
| MySQL_Monitor_replication_lag_check_ERR      | 1              |
| MySQL_Monitor_dns_cache_queried              | 0              |
| MySQL_Monitor_dns_cache_lookup_success       | 0              |
| MySQL_Monitor_dns_cache_record_updated       | 0              |
| MyHGM_myconnpoll_get                         | 0              |
| MyHGM_myconnpoll_get_ok                      | 0              |
| MyHGM_myconnpoll_push                        | 0              |
| MyHGM_myconnpoll_destroy                     | 0              |
| MyHGM_myconnpoll_reset                       | 0              |
| SQLite3_memory_bytes                         | 736426880      |
| ConnPool_memory_bytes                        | 774400         |
| Stmt_Client_Active_Total                     | 0              |
| Stmt_Client_Active_Unique                    | 0              |
| Stmt_Server_Active_Total                     | 0              |
| Stmt_Server_Active_Unique                    | 0              |
| Stmt_Max_Stmt_id                             | 1              |
| Stmt_Cached                                  | 0              |
| 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.00 sec)
```
Hi @vladzu,

based on the provided memory dumps, there is no obvious memory usage increase that can be appreciated as an indication of a memory leak. This could also mean that the memory dump doesn't contains the leak scenario, memory usage didn't seem to increase much during the `4 hours` that this test lasted. The difference that can be appreciated in the graphs is less than `20MB`, which isn't much.

This could also mean that there is no real leak, and that memory is just being rightfully used for `SQLite3` tables. I'm going to provide you a list of queries, if you could provide the results, we will try to figure out what is causing the extra memory consumption. First, the global variables configuration:

```
SELECT * FROM global_variables WHERE variable_name NOT LIKE '%credentials%' AND variable_name NOT LIKE '%passwords%';
```

Then several stats tables:

```
SELECT * FROM stats.stats_memory_metrics;
SELECT COUNT(*) FROM stats.stats_mysql_free_connections; // Because your high number of servers
SELECT COUNT(*) FROM stats_mysql_prepared_statements_info; // Perhaps many unique prepared statements?
SELECT COUNT(*) FROM stats_mysql_client_host_cache; // Just in case you have client-error-limit feature enabled, I don't think its the case because you didn't mention it
```

Also mention that the metrics that you reported from that particular instance are a bit odd, the uptime is high enough (`19 days`) yet, looks like the instance is serving no traffic:

```
| Queries_backends_bytes_recv                  | 0              |
| Queries_backends_bytes_sent                  | 0              |
| Queries_frontends_bytes_recv                 | 0              |
| Queries_frontends_bytes_sent                 | 4269096        |
```

And `LOAD MYSQL SERVERS TO RUNTIME` is executed with high frequency:

```
| Servers_table_version                        | 41320          | // Once each 40s aprox
```

I'm just curious if this is an instance setup with the purpose of reproducing the leak, or it's a real scenario instance in which the grow in memory usage was observed?

To summarize, I don't see a leak being present in this instance, if you could please share more details about this one, or other more affected I would gladly follow up.

Thank you, Javier.
Hi @vladzu,

since it can add more helpful information, could you also paste the output of the following script?

```
for d in main stats monitor; do
  for t in `mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "SHOW TABLES FROM $d" -NB 2>/dev/null`; do
    echo "`mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "SELECT COUNT(*) FROM $d.$t" -NB 2> /dev/null` : $d.$t"
  done
done | sort -n -r | grep -Ev '^0'
```

If there is any table retaining extra info, would be easy to spot.

Thank you, Javier.
Hi @JavierJF, 

First of all some comments on that: 
based on the provided memory dumps, there is no obvious memory usage increase that can be appreciated as an indication of a memory leak. This could also mean that the memory dump doesn't contains the leak scenario, memory usage didn't seem to increase much during the 4 hours that this test lasted. The difference that can be appreciated in the graphs is less than 20MB, which isn't much.

Yes, test was run only for 4 hours but if you look at the 1st graph provided - you can see it is growing constantly (drops are restarts). 

Currently within 10 days memory grown from 350Mb to 700Mb with slow growing pace.
![Screenshot 2024-04-05 at 14 56 08](https://github.com/sysown/proxysql/assets/6085951/9e661bce-94d3-416f-befc-eaefa9378be9)

It can be Sqlite3 footpring but what is causing it ?

Providing requested data:

```
mysql> SELECT * FROM global_variables WHERE variable_name NOT LIKE '%credentials%' AND variable_name NOT LIKE '%passwords%';
+----------------------------------------------------------------------+--------------------------------------------+
| variable_name                                                        | variable_value                             |
+----------------------------------------------------------------------+--------------------------------------------+
| admin-stats_mysql_connections                                        | 60                                         |
| admin-stats_mysql_connection_pool                                    | 60                                         |
| admin-stats_mysql_query_cache                                        | 60                                         |
| admin-stats_mysql_query_digest_to_disk                               | 0                                          |
| admin-stats_system_cpu                                               | 60                                         |
| admin-stats_system_memory                                            | 60                                         |
| admin-telnet_admin_ifaces                                            | (null)                                     |
| admin-telnet_stats_ifaces                                            | (null)                                     |
| admin-refresh_interval                                               | 2000                                       |
| admin-read_only                                                      | false                                      |
| admin-vacuum_stats                                                   | true                                       |
| admin-cluster_mysql_variables_diffs_before_sync                      | 3                                          |
| admin-cluster_admin_variables_diffs_before_sync                      | 3                                          |
| admin-cluster_ldap_variables_diffs_before_sync                       | 3                                          |
| admin-cluster_mysql_query_rules_save_to_disk                         | true                                       |
| admin-cluster_mysql_servers_save_to_disk                             | true                                       |
| admin-cluster_mysql_users_save_to_disk                               | true                                       |
| admin-cluster_proxysql_servers_save_to_disk                          | true                                       |
| admin-cluster_mysql_variables_save_to_disk                           | true                                       |
| admin-cluster_admin_variables_save_to_disk                           | true                                       |
| admin-cluster_ldap_variables_save_to_disk                            | true                                       |
| admin-checksum_mysql_query_rules                                     | true                                       |
| admin-checksum_mysql_servers                                         | true                                       |
| admin-checksum_mysql_users                                           | true                                       |
| admin-checksum_mysql_variables                                       | true                                       |
| admin-checksum_admin_variables                                       | true                                       |
| admin-checksum_ldap_variables                                        | true                                       |
| admin-web_enabled                                                    | false                                      |
| admin-web_port                                                       | 6080                                       |
| admin-web_verbosity                                                  | 0                                          |
| admin-prometheus_memory_metrics_interval                             | 61                                         |
| admin-coredump_generation_interval_ms                                | 30000                                      |
| admin-coredump_generation_threshold                                  | 10                                         |
| admin-ssl_keylog_file                                                |                                            |
| admin-mysql_ifaces                                                   | 0.0.0.0:6032;/tmp/proxysql-admin.sock      |
| admin-cluster_username                                               | proxysql-admin                             |
| admin-cluster_password                                               | ryterwg4wDFSDe4w                           |
| admin-cluster_check_interval_ms                                      | 5000                                       |
| admin-cluster_check_status_frequency                                 | 10                                         |
| admin-cluster_mysql_query_rules_diffs_before_sync                    | 3                                          |
| admin-cluster_mysql_servers_diffs_before_sync                        | 3                                          |
| admin-cluster_mysql_users_diffs_before_sync                          | 3                                          |
| admin-cluster_proxysql_servers_diffs_before_sync                     | 3                                          |
| admin-restapi_enabled                                                | true                                       |
| admin-restapi_port                                                   | 6080                                       |
| admin-version                                                        | 2.5.5-10-g195bd70                          |
| mysql-default_charset                                                | utf8mb4                                    |
| mysql-default_collation_connection                                   | utf8mb4_0900_ai_ci                         |
| mysql-shun_on_failures                                               | 5                                          |
| mysql-shun_recovery_time_sec                                         | 10                                         |
| mysql-unshun_algorithm                                               | 0                                          |
| mysql-query_retries_on_failure                                       | 0                                          |
| mysql-client_host_cache_size                                         | 0                                          |
| mysql-client_host_error_counts                                       | 0                                          |
| mysql-connect_retries_on_failure                                     | 3                                          |
| mysql-connect_retries_delay                                          | 1                                          |
| mysql-connection_delay_multiplex_ms                                  | 0                                          |
| mysql-connection_max_age_ms                                          | 10000                                      |
| mysql-connect_timeout_client                                         | 10000                                      |
| mysql-connect_timeout_server                                         | 3000                                       |
| mysql-connect_timeout_server_max                                     | 10000                                      |
| mysql-enable_client_deprecate_eof                                    | false                                      |
| mysql-enable_server_deprecate_eof                                    | false                                      |
| mysql-enable_load_data_local_infile                                  | false                                      |
| mysql-eventslog_filename                                             |                                            |
| mysql-eventslog_filesize                                             | 104857600                                  |
| mysql-eventslog_default_log                                          | 0                                          |
| mysql-eventslog_format                                               | 1                                          |
| mysql-auditlog_filename                                              |                                            |
| mysql-auditlog_filesize                                              | 104857600                                  |
| mysql-handle_unknown_charset                                         | 1                                          |
| mysql-free_connections_pct                                           | 10                                         |
| mysql-connection_warming                                             | false                                      |
| mysql-session_idle_ms                                                | 1                                          |
| mysql-have_ssl                                                       | false                                      |
| mysql-have_compress                                                  | true                                       |
| mysql-interfaces                                                     | 0.0.0.0:6033                               |
| mysql-log_mysql_warnings_enabled                                     | false                                      |
| mysql-monitor_enabled                                                | true                                       |
| mysql-monitor_history                                                | 600000                                     |
| mysql-monitor_connect_interval                                       | 20000                                      |
| mysql-monitor_connect_timeout                                        | 600                                        |
| mysql-monitor_ping_interval                                          | 10000                                      |
| mysql-monitor_ping_max_failures                                      | 3                                          |
| mysql-monitor_ping_timeout                                           | 1000                                       |
| mysql-monitor_read_only_interval                                     | 1000                                       |
| mysql-monitor_read_only_timeout                                      | 800                                        |
| mysql-monitor_read_only_max_timeout_count                            | 3                                          |
| mysql-monitor_replication_lag_group_by_host                          | true                                       |
| mysql-monitor_replication_lag_interval                               | 1000                                       |
| mysql-monitor_replication_lag_timeout                                | 1000                                       |
| mysql-monitor_replication_lag_count                                  | 1                                          |
| mysql-monitor_groupreplication_healthcheck_interval                  | 5000                                       |
| mysql-monitor_groupreplication_healthcheck_timeout                   | 800                                        |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count         | 3                                          |
| mysql-monitor_groupreplication_max_transactions_behind_count         | 3                                          |
| mysql-monitor_groupreplication_max_transactions_behind_for_read_only | 1                                          |
| mysql-monitor_galera_healthcheck_interval                            | 5000                                       |
| mysql-monitor_galera_healthcheck_timeout                             | 800                                        |
| mysql-monitor_galera_healthcheck_max_timeout_count                   | 3                                          |
| mysql-monitor_username                                               | proxysql-monitor                           |
| mysql-monitor_password                                               | dfe5rEpdosneS3eff                          |
| mysql-monitor_replication_lag_use_percona_heartbeat                  |                                            |
| mysql-monitor_query_interval                                         | 60000                                      |
| mysql-monitor_query_timeout                                          | 100                                        |
| mysql-monitor_slave_lag_when_null                                    | 60                                         |
| mysql-monitor_threads_min                                            | 8                                          |
| mysql-monitor_threads_max                                            | 128                                        |
| mysql-monitor_threads_queue_maxsize                                  | 128                                        |
| mysql-monitor_local_dns_cache_ttl                                    | 300000                                     |
| mysql-monitor_local_dns_cache_refresh_interval                       | 60000                                      |
| mysql-monitor_local_dns_resolver_queue_maxsize                       | 128                                        |
| mysql-monitor_wait_timeout                                           | true                                       |
| mysql-monitor_writer_is_also_reader                                  | false                                      |
| mysql-max_allowed_packet                                             | 67108864                                   |
| mysql-tcp_keepalive_time                                             | 0                                          |
| mysql-use_tcp_keepalive                                              | false                                      |
| mysql-automatic_detect_sqli                                          | false                                      |
| mysql-firewall_whitelist_enabled                                     | false                                      |
| mysql-firewall_whitelist_errormsg                                    | Firewall blocked this query                |
| mysql-throttle_connections_per_sec_to_hostgroup                      | 1000000                                    |
| mysql-max_transaction_idle_time                                      | 14400000                                   |
| mysql-max_transaction_time                                           | 14400000                                   |
| mysql-multiplexing                                                   | false                                      |
| mysql-log_unhealthy_connections                                      | true                                       |
| mysql-enforce_autocommit_on_reads                                    | false                                      |
| mysql-autocommit_false_not_reusable                                  | false                                      |
| mysql-autocommit_false_is_transaction                                | false                                      |
| mysql-verbose_query_error                                            | true                                       |
| mysql-hostgroup_manager_verbose                                      | 0                                          |
| mysql-binlog_reader_connect_retry_msec                               | 3000                                       |
| mysql-threshold_query_length                                         | 524288                                     |
| mysql-threshold_resultset_size                                       | 4194304                                    |
| mysql-query_digests_max_digest_length                                | 2048                                       |
| mysql-query_digests_max_query_length                                 | 65000                                      |
| mysql-query_digests_grouping_limit                                   | 3                                          |
| mysql-query_digests_groups_grouping_limit                            | 0                                          |
| mysql-query_rules_fast_routing_algorithm                             | 1                                          |
| mysql-wait_timeout                                                   | 181000                                     |
| mysql-throttle_max_bytes_per_second_to_client                        | 0                                          |
| mysql-throttle_ratio_server_to_client                                | 0                                          |
| mysql-max_connections                                                | 2000                                       |
| mysql-max_stmts_per_connection                                       | 10                                         |
| mysql-max_stmts_cache                                                | 10000                                      |
| mysql-mirror_max_concurrency                                         | 16                                         |
| mysql-mirror_max_queue_length                                        | 32000                                      |
| mysql-default_max_latency_ms                                         | 1000                                       |
| mysql-default_query_delay                                            | 0                                          |
| mysql-default_query_timeout                                          | 86400000                                   |
| mysql-query_processor_iterations                                     | 0                                          |
| mysql-query_processor_regex                                          | 1                                          |
| mysql-set_query_lock_on_hostgroup                                    | 0                                          |
| mysql-set_parser_algorithm                                           | 1                                          |
| mysql-reset_connection_algorithm                                     | 2                                          |
| mysql-auto_increment_delay_multiplex                                 | 5                                          |
| mysql-auto_increment_delay_multiplex_timeout_ms                      | 10000                                      |
| mysql-long_query_time                                                | 1000                                       |
| mysql-query_cache_size_MB                                            | 256                                        |
| mysql-query_cache_soft_ttl_pct                                       | 0                                          |
| mysql-ping_interval_server_msec                                      | 10000                                      |
| mysql-ping_timeout_server                                            | 500                                        |
| mysql-default_schema                                                 | information_schema                         |
| mysql-poll_timeout                                                   | 2000                                       |
| mysql-poll_timeout_on_failure                                        | 100                                        |
| mysql-server_capabilities                                            | 569899                                     |
| mysql-server_version                                                 | 8.0.32                                     |
| mysql-keep_multiplexing_variables                                    | tx_isolation,transaction_isolation,version |
| mysql-kill_backend_connection_when_disconnect                        | true                                       |
| mysql-client_session_track_gtid                                      | true                                       |
| mysql-sessions_sort                                                  | true                                       |
| mysql-session_idle_show_processlist                                  | true                                       |
| mysql-show_processlist_extended                                      | 0                                          |
| mysql-commands_stats                                                 | true                                       |
| mysql-query_digests                                                  | true                                       |
| mysql-query_digests_lowercase                                        | false                                      |
| mysql-query_digests_replace_null                                     | false                                      |
| mysql-query_digests_no_digits                                        | false                                      |
| mysql-query_digests_normalize_digest_text                            | false                                      |
| mysql-query_digests_track_hostname                                   | false                                      |
| mysql-query_digests_keep_comment                                     | false                                      |
| mysql-parse_failure_logs_digest                                      | false                                      |
| mysql-servers_stats                                                  | true                                       |
| mysql-default_reconnect                                              | true                                       |
| mysql-ssl_p2s_ca                                                     |                                            |
| mysql-ssl_p2s_capath                                                 |                                            |
| mysql-ssl_p2s_cert                                                   |                                            |
| mysql-ssl_p2s_key                                                    |                                            |
| mysql-ssl_p2s_cipher                                                 |                                            |
| mysql-ssl_p2s_crl                                                    |                                            |
| mysql-ssl_p2s_crlpath                                                |                                            |
| mysql-stacksize                                                      | 1048576                                    |
| mysql-threads                                                        | 4                                          |
| mysql-init_connect                                                   |                                            |
| mysql-ldap_user_variable                                             |                                            |
| mysql-add_ldap_user_comment                                          |                                            |
| mysql-default_tx_isolation                                           | READ-COMMITTED                             |
| mysql-default_session_track_gtids                                    | OFF                                        |
| mysql-connpoll_reset_queue_length                                    | 50                                         |
| mysql-min_num_servers_lantency_awareness                             | 1000                                       |
| mysql-aurora_max_lag_ms_only_read_from_replicas                      | 2                                          |
| mysql-stats_time_backend_query                                       | false                                      |
| mysql-stats_time_query_processor                                     | false                                      |
| mysql-query_cache_stores_empty_result                                | true                                       |
| mysql-data_packets_history_size                                      | 0                                          |
+----------------------------------------------------------------------+--------------------------------------------+

mysql> SELECT * FROM stats.stats_memory_metrics;
+------------------------------+----------------+
| Variable_Name                | Variable_Value |
+------------------------------+----------------+
| SQLite3_memory_bytes         | 855238032      |
| jemalloc_resident            | 976580608      |
| jemalloc_active              | 932364288      |
| jemalloc_allocated           | 920965008      |
| jemalloc_mapped              | 1041764352     |
| jemalloc_metadata            | 32129176       |
| jemalloc_retained            | 279965696      |
| Auth_memory                  | 26778          |
| query_digest_memory          | 0              |
| mysql_query_rules_memory     | 2206158        |
| 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   | 25165824       |
| stack_memory_cluster_threads | 25165824       |
+------------------------------+----------------+
17 rows in set (0.01 sec)

mysql> SELECT COUNT(*) FROM stats.stats_mysql_free_connections;
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+
1 row in set (0.01 sec)

mysql>
mysql> SELECT COUNT(*) FROM stats_mysql_prepared_statements_info;
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM stats_mysql_client_host_cache;
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec)
```

Indeed, currently as you mentioned - this host does not server any traffic but memory usage is increasing. But we see the same trend with machines that are serving traffic. 

> And LOAD MYSQL SERVERS TO RUNTIME is executed with high frequency:
> 
> `| Servers_table_version                        | 41320          | // Once each 40s aprox`

This is because we dynamically create and delete mysql schemas and we sync them to proxysql as hostgroups and mysql_servers .

If you want we can run memory profiler for a longer period and on machine where there is a live traffic 

Hi @vladzu,

> Yes, test was run only for 4 hours but if you look at the 1st graph provided - you can see it is growing constantly (drops are restarts).
> 
> Currently within 10 days memory grown from 350Mb to 700Mb with slow growing pace.

Yes, I understand, my comment was regarding the dumps themselves within that time frame. In them, it can't be appreciated something specific as the cause of the memory usage increase. This grow itself isn't usual, specially because looks related to SQLite3, and it's an idle system (outside of the regular configuration updates). In the previous output I can't see any config that could cause this steady memory increase.

>This is because we dynamically create and delete mysql schemas and we sync them to proxysql as hostgroups and mysql_servers .

Thanks for clarifying. Could you please share with us the output of the previous script I have provided? Just in case we are missing a growing table in one particular place.

Thank you, Javier.
Sorry, missed that 

11770 : stats.stats_mysql_query_digest_reset
11770 : stats.stats_mysql_query_digest
9893 : stats.stats_mysql_prepared_statements_info
4304 : main.runtime_mysql_query_rules_fast_routing
4304 : main.mysql_query_rules_fast_routing
3231 : stats.stats_mysql_connection_pool_reset
3231 : stats.stats_mysql_connection_pool
3231 : main.runtime_mysql_servers
3231 : main.mysql_servers
1200 : monitor.mysql_server_replication_lag_log
1200 : monitor.mysql_server_read_only_log
1077 : main.runtime_mysql_replication_hostgroups
1077 : main.mysql_replication_hostgroups
375 : main.mysql_collations
207 : main.runtime_global_variables
207 : main.global_variables
184 : main.runtime_mysql_users
184 : main.mysql_users
120 : monitor.mysql_server_ping_log
107 : stats.stats_mysql_global
92 : stats.stats_mysql_users
60 : monitor.mysql_server_connect_log
54 : stats.stats_mysql_commands_counters
27 : stats.stats_mysql_free_connections
17 : stats.stats_memory_metrics
12 : stats.stats_proxysql_servers_checksums
11 : stats.stats_mysql_processlist
6 : main.runtime_checksums_values
5 : stats.stats_mysql_errors_reset
5 : stats.stats_mysql_errors
2 : stats.stats_proxysql_servers_metrics
2 : stats.stats_proxysql_servers_clients_status
2 : main.runtime_proxysql_servers
2 : main.proxysql_servers
1 : stats.stats_mysql_query_rules
1 : stats.global_variables
1 : main.runtime_mysql_query_rules
1 : main.mysql_query_rules
any updates on that ? 

just to note here - once a day we do:
select 1 from stats_mysql_query_digest_reset limit 1;
select 1 from stats_mysql_connection_pool_reset limit 1;

Fix patch:
diff --git a/lib/MySQL_HostGroups_Manager.cpp b/lib/MySQL_HostGroups_Manager.cpp
index b185416af8..d1f6188ec4 100644
--- a/lib/MySQL_HostGroups_Manager.cpp
+++ b/lib/MySQL_HostGroups_Manager.cpp
@@ -7663,6 +7663,7 @@ void MySQL_HostGroups_Manager::generate_mysql_hostgroup_attributes_table() {
 		}
 	}
 
+	(*proxy_sqlite3_finalize)(statement);
 	delete incoming_hostgroup_attributes;
 	incoming_hostgroup_attributes=NULL;
 }
@@ -7717,6 +7718,7 @@ void MySQL_HostGroups_Manager::generate_mysql_servers_ssl_params_table() {
 		string MapKey = MSSP.getMapKey(rand_del);
 		Servers_SSL_Params_map.emplace(MapKey, MSSP);
 	}
+	(*proxy_sqlite3_finalize)(statement);
 	delete incoming_mysql_servers_ssl_params;
 	incoming_mysql_servers_ssl_params=NULL;
 }


Test patch: