MySQL Adventures: Reduce MySQL Memory Utilization With ProxySQL Multiplexing

In our previous post, we explained about how max_prepared_statement_count can bring production down . This blog is the continuity of that post. If you can read that blog from the below link.

How max_prepared_stmt_count bring down the production MySQL system

We had set the max_prepared_stmt_count to 20000. But after that, we were facing the below error continuously.

Can't create more than max_prepared_stmt_count statements (current value: 20000)

We tried to increase it to 25000, 30000 and finally 50000. But unfortunately, we can’t fix it and increasing this value will lead to a memory leak which we explained in our previous blog.

We are using ProxySQL to access the database servers. And the architecture looks like below.

Multiplexing in ProxySQL:

The main purpose of the multiplexing is to reduce the connections to MySQL servers. So we can send thousands of connections to only a hundred backend connections.

We enabled multiplexing while setting up the Database environment. But multiplexing will not be work in all the times. ProxySQL has some sense to track the transactions which are executing in that connection. If any transactions are not committed or rollback then, it’ll never use that connection for the next request. It’ll pick another free connection from the connection pool.

From the ProxySQL Doc, there are few scenarios where multiplexing is disabled.

• active transaction
• Tables are locked.
• Set queries (like SET FOREIGN_KEY_CHECKS)

In our case, the most of the errors are due to prepare statement count. Believe it, this issue made us to reduce the memory utilization also.

Get the currently active prepared statements:

Run the below query which will give tell us the number of active prepare statements in the backend.

SELECT
    *
FROM
    stats_mysql_global
WHERE
    variable_name LIKE '%stmt%';
+---------------------------+----------------+
| Variable_Name             | Variable_Value |
+---------------------------+----------------+
| Com_backend_stmt_prepare  | 168318911      |
| Com_backend_stmt_execute  | 143165882      |
| Com_backend_stmt_close    | 0              |
| Com_frontend_stmt_prepare | 171609010      |
| Com_frontend_stmt_execute | 171234713      |
| Com_frontend_stmt_close   | 171234006      |
| Stmt_Client_Active_Total  | 19983          |
| Stmt_Client_Active_Unique | 4              |
| Stmt_Server_Active_Total  | 84             |
| Stmt_Server_Active_Unique | 23             |
| Stmt_Max_Stmt_id          | 10002          |
| Stmt_Cached               | 2186           |
+---------------------------+----------------+

# You can get the same results in MySQL also
MySQL> show status like 'Prepared_stmt_count';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Prepared_stmt_count | 19983 |
+---------------------+-------+

You can see the number of active prepare statements are 19983. while running the query again and again, I could see a random count but those all are more than 19000. And you can see the Com_backend_stmt_close is 0.

Yes, ProxySQL will never close the prepared statements in the backend. But there is a mechanism in ProxySQL which allocates 20 prepared statements(20 is the default value) to each connection. Once its executed all 20 statements then the connection will come back to the connection pool and close all 20 statements in one shot.

Run the below query to get the default statement count for a connection.

proxysql> show variables like "%stmt%";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| mysql-max_stmts_per_connection | 20    |
| mysql-max_stmts_cache          | 10000 |
+--------------------------------+-------+

There is a great explanation about this variable by René Cannaò who is the founder of ProxtSQL. You can read about that here.

Why this much prepared statements are running?

As mentioned earlier, proxysql will never close the prepared statements in the backend. We realize that we are getting heavy traffic on both ProxySQL servers and its send it to one Master node. And also the Laravel has all the queries with prepared statement format. That's why we are getting this much prepared statements.

Get where the most of the prepared statements are used:

Run the below query in proxySQL and this will give you the total count of executed prepared statements on all the databases and the usernames.

SELECT
    username, schemaname, count(*)
FROM
    stats_mysql_prepared_statements_info
GROUP BY
    1, 2
ORDER BY
    3 DESC;
+----------+---------------+----------+
| username | schemaname    | count(*) |
+----------+---------------+----------+
| DBname   |    user1      | 2850     |
| DBname   |    user2      | 257      |
| DBname   |    user3      | 108      |
| DBname   |    user1      | 33       |
| DBname   |    user2      | 33       |
| DBname   |    user1      | 16       |
| DBname   |    user1      | 15       |
+----------+---------------+----------+
#There is a Bug in this view. The Username column is actually showing the schemaname and the schemaname column is showing usernames. I have reported this bug in proxySQL's github repo.
https://github.com/sysown/proxysql/issues/1720

Force ProxySQL to use multiplex:

There are few cases proxysql will disable the multiplexing. Particularly all queries that have @ in their query_digest will disable multiplexing.

Collect the queries which has @

SELECT
    DISTINCT digest, digest_text
FROM
    stats_mysql_query_digest
WHERE
    digest_text LIKE '%@%' \G;

*************************** 1. row ***************************
     digest: 0xA8F2FFB14312850C
digest_text: SELECT @@max_allowed_packet
*************************** 2. row ***************************
     digest: 0x7CDEEF2FF695B7F8
digest_text: SELECT @@session.tx_isolation
*************************** 3. row ***************************
     digest: 0x2B838C3B5DE79958
digest_text: SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout

Finally, the above statements are executed by prepared statements. These queries are by default disabled the multiplexing. But ProxySQL has another cool feature that we can allow these queries (which has @ ) to use multiplexing.

Run the below query to set proxysql to use multiplexing for these queries. We can insert it by Query pattern or query digest.

# Add multiplexing to a query using query_text
INSERT INTO mysql_query_rules 
(active, match_digest, multiplex)
VALUES
('1', '^SELECT @@session.tx_isolation', 2);

INSERT INTO mysql_query_rules 
(active, match_digest, multiplex)
VALUES
('1', '^SELECT @@max_allowed_packet', 2);
# Add multiplexing to a query using query Diagest
INSERT INTO mysql_query_rules 
(active, digest, multiplex)
VALUES
('1', '0x2B838C3B5DE79958', 2);
# Save it to Runtime and Disk
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Restart ProxySQL:

If we enabled multiplexing in proxySQL then its mandatory to restart ProxySQL service or close all the existing connections and open it as a new one.

service proxysql restart

Lets check the active prepared statements in both ProxySQL and MySQL.

#proxySQL
SELECT
    *
FROM
    stats_mysql_global
WHERE
    variable_name LIKE '%stmt%';
+--------------------------+----------------+
| Variable_Name            | Variable_Value |
+--------------------------+----------------+
| Stmt_Client_Active_Total | 6              |
+--------------------------+----------------+
#mysql
show status like 'Prepared_stmt_count';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Prepared_stmt_count | 166   |
+---------------------+-------+

The number of Prepared_stmt_count is dramatically reduced from 20000 to 200. But why there is a different count between proxySQL and mysql?

Again refer to the ProxySQL’s doc. Once whenever a connection executed 20 statements, then only it’ll Close that prepared statement. In ProxySQL, its showing active statements. But MySQL is showing active + executed statements count.

Number of Backend connections:

After this change, there is a sudden drop in a number of backend connections in the ProxySQL. This proves that the statements which disable the multiplexing will create more backend connections.

MySQL’s Memory:

Now we can see the explanation for this blog title. See the below graph which is showing the high memory drop.

The two main parts where mysql used more memory:

1. mysql connections.
2. Prepared statements.

We all already knows that each mysql connection requires some amount of memory. And for prepared statements, I have explained about its memory consumption in my previous blog.

Conclusion:

ProxySQL has a lot of great features. Multiplexing is good. But after this incident only we realize that multiplexing will help to reduce the number of backend connections and MySQL’s memory utilization as well.

I hope if you are a DBA you will read this and implement it in your environment as well. If it helped for you then feel free to give your claps.