ProxySQL is a very useful tool for gaining high availability, load balancing, query routing, query caching, query rewriting, multiplexing, and data masking. It is a proven tool and is used largely in production. There can be an instance of ProxySQL using a lot of memory on the server. This post is related to one of the causes that can use memory on the ProxySQL server and how we handle it.
Explanation
How do we know where the memory is getting used?
Proxysql has a stats database which we can see with an admin login. This database contains metrics gathered by ProxySQL concerning its internal functioning.
This post mostly talks about stats in MySQL query digest in ProxySQL.
Memory used by query digest can grow unlimited, and you can query the memory usage with the below query with an admin login. The memory usage is shown in bytes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | MySQL [(none)]> select * from stats_memory_metrics; +------------------------------+----------------+ | Variable_Name | Variable_Value | +------------------------------+----------------+ | SQLite3_memory_bytes | 3732504 | | jemalloc_resident | 171573248 | | jemalloc_active | 123863040 | | jemalloc_allocated | 21793784 | | jemalloc_mapped | 237305856 | | jemalloc_metadata | 10041824 | | jemalloc_retained | 57868288 | | Auth_memory | 835 | | query_digest_memory | 177248 | | mysql_query_rules_memory | 8410 | | 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 | 8388608 | | stack_memory_cluster_threads | 0 | +------------------------------+----------------+ |
This is the test instance. So you don’t see it growing so fast due to idle load. But in the production environment where there are tons of unique queries running from ProxySQL, it can grow fast and be troublesome. Once it restarts, it clears the memory and empties the stats_mysql_query_digest table.
You can check this memory usage using Percona Monitoring and Management (PMM) as well. Let’s see where it shows.
1 2 3 4 5 6 | mysql> select * from stats_memory_metrics where variable_name = 'query_digest_memory'; +---------------------+----------------+ | Variable_Name | Variable_Value | +---------------------+----------------+ | query_digest_memory | 3944 | +---------------------+----------------+ |
Under PMM, you can check “Memory used to store data” under the ProxySQL instance summary → Memory usage pane.
Here is how it released the memory on restarting and also emptied the query_digest as well.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [root@yunus-shaikh-node3 ~]# systemctl restart proxysql [root@yunus-shaikh-node3 ~]# mysql -h 127.0.0.1 -u admin -P 6032 -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MySQL [(none)]> select * from stats_mysql_query_digest; Empty set (0.00 sec) MySQL [(none)]> select * from stats_memory_metrics where variable_name='query_digest_memory'; +---------------------+----------------+ | Variable_Name | Variable_Value | +---------------------+----------------+ | query_digest_memory | 0 | +---------------------+----------------+ |
But restarting ProxySQL is not a good solution for a production environment, so how do we release the memory?
Ways to do it without restarting ProxySQL
1) You can disable mysql_query_digest if you don’t want it. It is not a big problem, so keeping it enabled might benefit from seeing what queries your application is running. ProxySQL table (stats_mysql_query_digest) can reduce the overhead of analyzing slow logs and sorting slow queries. So we don’t recommend it to be disabled as it can be taken care of better.
Below are the steps that can be used to disable it.
1 2 3 | set mysql-query_digests = 0; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK; |
2) ProxySQL has provided a table stats_mysql_query_digest_reset. If we run a select on it, it will display the contents on stats_mysql_query_digest and clears the table. This can be used if you don’t need the history of stats_mysql_query_digest anymore after reading once. This is similar to restarting ProxySQL as it does not backup anything and just deletes the data in stats_mysql_query_digest.
We had two entries in the digest, and when we selected it from stats_mysql_query_digest_reset then, it cleared the stats_mysql_query_digest table and also released the memory for the same.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | MySQL [(none)]> select count(*) from stats_mysql_query_digest; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) MySQL [(none)]> select count(*) from stats_mysql_query_digest_reset; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) MySQL [(none)]> select count(*) from stats_mysql_query_digest; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.01 sec) MySQL [(none)]> select * from stats_memory_metrics where variable_name='query_digest_memory'; +---------------------+----------------+ | Variable_Name | Variable_Value | +---------------------+----------------+ | query_digest_memory | 0 | +---------------------+----------------+ |
NOTE – This does not save your mysql_query_digest. It just deletes everything.
3) There is a variable admin-stats_mysql_query_digest_to_disk which allows you to save the query_digest in the history table history_mysql_query_digest after n number of seconds. N is the value you define to variables in seconds.
1 2 3 | set admin-stats_mysql_query_digest_to_disk = <No of seconds in which you want to move from memory to disk>; LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK; |
Every N seconds, it will move the contents from stats_mysql_query_digest to history_mysql_query_digest. So the digest will then be stored on a disk. Memory will be released.
4) The below step will directly store the MySQL digest on DISK. This will stop using the stats_mysql_query_digest table and will only store all data in history_mysql_query_digest.
1 | SAVE MYSQL DIGEST TO DISK |
Other variables that can be used for reducing the query digest in the memory include:
1) mysql-query_digests_keep_comment – This is by default false, which can help to reduce the digest text, and that means the size as well for the query digest. If you need, you can enable it.
2) mysql-query_digests_max_digest_length – This is by default set to 2048. You can reduce it to a minimum of 16. This will result in the queries like:
1 2 3 | select * from test to select * from te |
The digest text will be limited to 16 characters counting with space and comments in the query. This will result in queries cut down to some extent. Again, it will save memory.
3) mysql-query_digests_grouping_limit – This will convert the queries like;
1 2 3 4 5 6 | set mysql-query_digests_grouping_limit=2; LOAD MYSQL VARIABLES TO RUNTIME; select * from test2 where id IN (1,2,3,4); to select * from test2 where id IN (?,?,...) |
4) mysql-query_digests_normalize_digest_text
When set to FALSE (default), ProxySQL will cache the SQL digest and related information in the table stats.stats_mysql_query_digest by the schema.
When this variable is TRUE, queries statistics store digest_text on a different internal hash table. In this way, ProxySQL will be able to normalize data, digest_text is internally stored elsewhere, and it deduplicates data.
When you query stats_mysql_query_digest, the data is merged. This drastically reduces memory usage on setups with many schemas but similar query patterns.
This will save us from restarting ProxySQL every time if the memory usage is high due to mysql_query_digest.
Conclusion
If you see the ProxySQL memory usage going high, you should look at the memory stats table to define where the memory is getting used. If you find that stats_mysql_query_digest is using the memory, then you can implement one of the abovementioned options to release the memory. You might also want to check why your application generates a lot of unique queries to fill the query digest. That can also help you to minimize the memory used by query digest.
The query digest doesn’t always need to cause a problem. If the memory metrics table shows something else, then you would have to go to troubleshoot in that way. If you think there is a memory leak, then you can refer to the below page on detecting memory leaks
In general, the recommended way of setting up the mysql_query_digest for ProxySQL would be to allow it to store in memory which can be faster than storing it on disk. So at some point in time, you would need to clear your memory before it grows. Here you can decide on your requirement if you need to store your query digest for a more extended period, then you can move it to disk. Query digest works so that it does not repeat unique queries in the digest. It only stores distinct queries when it is in one place.
When you move it from memory to disk (i.e. under history_mysql_query_digest), it empties the stats_mysql_query_digest table. So it will start recording all the queries that were also recorded previously and moved to the history table. When you move that data again to the history table, it will duplicate the query_digest there. So plan accordingly.
It would be best to decide how you want to set up your mysql_query_digest as per the requirement. It is always better to review it and move it to disk later. If not needed after a certain time, you can remove it from the disk too.
https://proxysql.com/documentation/memory-leak-detection/
References:
https://proxysql.com/documentation
Proxysql Major Version 2.0
