Memory leak in 2.5.5

[vladzu]

We have upgraded and running ProxySQL version 2.5.5-10-g195bd70, package used - 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

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

It includes 4h of memory dumps, the memory leakage trend can be seen here as well:

[renecannao]

Hi @vladzu .
Before further debugging, can you please also attach the output of SELECT * FROM stats_mysql_global; ?
Thanks

[vladzu]

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)

[JavierJF]

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.

[JavierJF]

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.

[vladzu]

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.

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

[JavierJF]

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.

[vladzu]

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

[vladzu]

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;

[renecannao]

Hi @vladzu .

any updates on that ?

I want to assure you that we are committed to supporting all our users, including both our community participants and our paying subscribers. To ensure efficient and dedicated support, we prioritize direct inquiries from our paying customers through our private support channels. This model allows us to provide them with timely and extensive assistance, as part of the benefits of their subscription plan.
We definitely care about the issues you have raised and want to make sure they are addressed.
Thank you for your understanding.

To be more specific on this issue, we have been working on this and we are making interesting progress.

select 1 from stats_mysql_query_digest_reset limit 1;

Please run TRUNCATE TABLE stats_mysql_query_digest : it is a lot (really a lot) faster as its purpose is just to reset the table.
Any query on stats_mysql_query_digest_reset (even a SELECT ... LIMIT 1) causes the table to be generated before being reset , thus for a short period of time you have an even further memory increase.

select 1 from stats_mysql_connection_pool_reset limit 1;

This only reset some counter, it is not relevant about memory usage.

[vladzu]

Hi @renecannao ,
we totally understand the your model.

Thank you for your proposal, we will test this out and come back with the results

[JavierJF]

Hi @vladzu, good afternoon,

as @renecannao mentioned, we decided to look back to this issue, since the information you provided pointed something to be off. Sadly, probably due to the steady allocation pattern of the instance and the default profiling mode of jemalloc , the provided dumps weren't helpful for the issue detection, so a reproduction scenario was required. After further analysis we were able to identify the specific leak that is likely affecting you, it's one of the two fixed in this PR, specifically the one affecting mysql_hostgroup_attributes. The fixes for this issue in that PR will be available in next ProxySQL release.

Also, we have taken action for improving the granularity we get for jemalloc reports, details in this PR, this will hopefully allow us to identify issues like this one more easily in the future.

Thanks for the report, and for the supplied information, regards, Javier.