Memory Fragmentation in Primary MariaDB 10.6.18 with ProxySQL 2.5.5

Hello Community Members, We have recently migrated our DB server to Azure from Onprem, and we are struggling with memory fragmentation in MariaDB Database. DB is consuming more than 2 times of buffer pool memory allocated. We see this fragmentation only on primary master. This behaviour is only observed when app connectivity via Proxysql to MariaDB. For sure the culprit is proxysql. We have one master with 2 standby databases in DB topology and 4 proxysql nodes one being hostgroup 1 and rest 3 with hostgroup 2. We have enabled the multiplexing, We initiall have heavy memory fragmentation, which got limited after changing the following variables in proxysql : During Migration : -------------------------- mysql-max_stmts_per_connection = 100; and max_prepared_stmt_count = 500000 Memory fragmentation was quite huge and we have to perform switch over the master database and bounce the DB post sswitchover to release the memory occupied by MariaDB. Post Migration : -------------------- mysql-max_stmts_per_connection = 50; at proxysql and max_prepared_stmt_count = 250000, at mariadb this has slowed down the fragmentation, but still mariadb consuming the memory based on the prepared statements. ProxySQL doesn't automatically close prepared statements unless the client explicitly requests it ? Connection Multiplexing in ProxySQL may be preventing statements from being properly closed. The application might not be explicitly closing prepared statements, relying on ProxySQL to handle cleanup. If multiplexing is enabled, ProxySQL keeps connections open and doesn't send COM_STMT_CLOSE properly. In our case, we have enabled multiplexing. No Query rules in place. sh-4.4$ cat /var/lib/mysql/my.cnf |grep buffer_pool;free -g;top |grep mysqld innodb_buffer_pool_size = 96G total used free shared buff/cache available Mem: 660 378 269 0 13 279 Swap: 0 0 0 1 mysql 20 0 386.8g 374.2g 28312 S 313.3 56.6 13770:26 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 320.0 56.6 13770:36 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 288.7 56.6 13770:44 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 236.0 56.6 13770:51 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 336.3 56.6 13771:02 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 321.9 56.6 13771:11 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 309.3 56.6 13771:21 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 298.0 56.6 13771:30 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 279.7 56.6 13771:38 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 152.0 56.6 13771:42 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 172.1 56.6 13771:48 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 238.0 56.6 13771:55 mysqld\ MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Memory_used';\ +---------------+--------------+\ | Variable_name | Value |\ +---------------+--------------+\ | Memory_used | 117572547280 |\ +---------------+--------------+\ 1 row in set (0.001 sec) MariaDB [(none)]> select 117572547280/1024/1024/1024 'Memory_used in GB';\ +-------------------+\ | Memory_used in GB |\ +-------------------+\ | 109.497967436910 |\ +-------------------+\ 1 row in set (0.000 sec)\ MariaDB [(none)]>\ MariaDB [(none)]> SELECT -> NOW() AS Query_Time,\ -> -> -- Temporary Tables Statistics\ -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_tables') AS Tmp_Tables_Created, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') AS Tmp_Tables_Disk, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_memory_tables') AS Tmp_Tables_Memory, -> -> -- Open Tables & Table Cache -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Open_tables') AS Open_Tables, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Opened_tables') AS Opened_Tables, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'table_open_cache') AS Table_Open_Cache, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'table_definition_cache') AS Table_Definition_Cache, -> -> -- Prepared Statements Memory Usage -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Prepared_stmt_count') AS Active_Prepared_Statements, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_prepare_sql') AS Total_Prepares, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_deallocate_sql') AS Total_Deallocates, -> -> -- Memory Consumed by Prepared Statements (Estimation) -> ROUND((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Prepared_stmt_count') * 512 / 1024, 2) AS Estimated_Memory_MB -> ; +---------------------+--------------------+-----------------+-------------------+---------\----+---------------+------------------+------------------------+----------------------------+----------------+-------------------+---------------------+\ | Query_Time | Tmp_Tables_Created | Tmp_Tables_Disk | Tmp_Tables_Memory | Open_Tables | Opened_Tables | Table_Open_Cache | Table_Definition_Cache | Active_Prepared_Statements | Total_Prepares | Total_Deallocates | Estimated_Memory_MB | +---------------------+--------------------+-----------------+-------------------+---------\----+---------------+------------------+------------------------+----------------------------+----------------+-------------------+---------------------+\ | 2025-02-25 08:49:26 | 17772934 | 1985203 | NULL | 400 | 39233 | 400 | 400 | 240131 | 0 | NULL | 120065.50 |\ +---------------------+--------------------+-----------------+-------------------+-------------+---------------+------------------+------------------------+----------------------------+----------------+-------------------+---------------------+\ 1 row in set (0.003 sec) MariaDB [(none)]> Proxysql : ---------------- mysql> SELECT srv_host, srv_port, ConnUsed, ConnFree, ConnOK, Queries, -> ROUND(Queries / NULLIF(ConnOK, 0), 2) AS MultiplexingEfficiencyRatio -> FROM stats_mysql_connection_pool -> ORDER BY MultiplexingEfficiencyRatio ASC -> LIMIT 10; +-------------------------------------------------------------------------+----------+----------+----------+--------+-----------+-----------------------------+\ | srv_host | srv_port | ConnUsed | ConnFree | ConnOK | Queries | MultiplexingEfficiencyRatio |\ +-------------------------------------------------------------------------+----------+----------+----------+--------+-----------+-----------------------------+\ | 2c_oltp.mariadb | 3052 | 2 | 4 | 1555 | 55156669 | 35470.0 \ | | 2b_oltp.mariadb | 3052 | 2 | 6 | 1933 | 70648384 | 36548.0 \ | | 2a_oltp.mariadb | 3052 | 43 | 4 | 4099 | 150018800 | 36598.0 \ | | 2d_oltp.mariadb | 3052 | 0 | 4 | 2080 | 77161687 | 37096.0 | +-------------------------------------------------------------------------+----------+----------+----------+--------+-----------+-----------------------------+\ 4 rows in set (0.00 sec) mysql> SELECT * FROM stats_mysql_connection_pool;\ +-----------+-------------------------------------------------------------------------+----\------+--------+----------+----------+--------+---------+-------------+-----------+-------\------------ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |\ +-----------+-------------------------------------------------------------------------+----\------+--------+----------+----------+--------+---------+-------------+-----------+-------\------------+ | 1 | 2a_oltp.mariadb | 3052 | ONLINE | 45 | 0 | 4097 | 0 | 62 | 149975306 | 0 | 48544471112 | 187097398415 | 270 | | 2 | 2c_oltp.mariadb | 3052 | ONLINE | 2 | 4 | 1555 | 0 | 10 | 55133002 | 0 | 32800700913 | 20589949783 | 421 | | 2 | 2b_oltp.mariadb | 3052 | ONLINE | 2 | 6 | 1933 | 0 | 14 | 70624781 | 0 | 41584296177 | 27213051703 | 272 | | 2 | 2d_oltp.mariadb | 3052 | ONLINE | 0 | 3 | 2079 | 0 | 13 | 77137911 | 0 | 45755921665 | 31073609951 | 168 | +-----------+-------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+-----------+-------------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM stats_mysql_global WHERE variable_name LIKE '%stmt%'; +---------------------------+----------------+\ | Variable_Name | Variable_Value |\ +---------------------------+----------------+\ | Com_backend_stmt_prepare | 63914669 |\ | Com_backend_stmt_execute | 233922211 |\ | Com_backend_stmt_close | 0 |\ | Com_frontend_stmt_prepare | 233883162 |\ | Com_frontend_stmt_execute | 233923781 |\ | Com_frontend_stmt_close | 233876578 |\ | Stmt_Client_Active_Total | 3 |\ | Stmt_Client_Active_Unique | 2 |\ | Stmt_Server_Active_Total | 93421 |\ | Stmt_Server_Active_Unique | 77596 |\ | Stmt_Max_Stmt_id | 174002 |\ | Stmt_Cached | 82713 |\ +---------------------------+----------------+\ 12 rows in set (0.01 sec) mysql> show variables like "%stmt%"; +--------------------------------+-------+\ | Variable_name | Value |\ +--------------------------------+-------+\ | mysql-max_stmts_cache | 10000 |\ | mysql-max_stmts_per_connection | 50 |\ +--------------------------------+-------+\ 2 rows in set (0.00 sec) mysql> MariaDB [(none)]> show status like 'Prepared_stmt_count';\ +---------------------+--------+\ | Variable_name | Value |\ +---------------------+--------+\ | Prepared_stmt_count | 220071 |\ +---------------------+--------+\ 1 row in set (0.000 sec) MariaDB [(none)]>\ mysql> SELECT\ -> username, schemaname, count(*)\ -> FROM -> stats_mysql_prepared_statements_info\ -> GROUP BY\ -> 1, 2\ -> ORDER BY\ -> 3 DESC;\ +----------+------------+----------+\ | username | schemaname | count(*) |\ +----------+------------+----------+\ | app | appprd | 87573 |\ +----------+------------+----------+\ 1 row in set (0.29 sec)\ mysql> Will proxysql disable multiplexing for all queries that have @ in their query_digest will disable multiplexing ??? We are using 2.5.5 version of proxysql and maridb 10.6.18. How we can stabilize this memory fragmentation permanently without any significant impact on performance?