MariaDB Memory Fragmentation with ProxySQL 2.5.5 in Primary Master DB
47 views
Skip to first unread message
gopinath k
Feb 27, 2025, 4:59:45 AMFeb 27
to proxysql
Hello Proxysql Community Members,
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>
We have recently migrated our DB server to Azure from Onprem, and we are struggling with memory fragmentation in MariaDB Database.
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.
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
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)]>
+---------------+--------------+
| 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)]>
-> 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)
-> 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>
+---------------------------+----------------+
| 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?
Thank you,
Gopinath.K
gopinath k
Apr 11, 2025, 4:10:00 AMApr 11
to proxysql
Situation of Memory fragmentation got stabilized after switching from default malloc to Jemalloc, along with DEV code fixes by reducing the unique prepared statements. However, fragmentation is not fully adressed, but positive impact we could see MariaDB mysqld started releasing memory back to OS.
Reply all
Reply to author
Forward
0 new messages