I hope this message finds you well. I am currently working on a project involving the migration of a MySQL database to another MySQL database using ProxySQL. My setup is illustrated in the attached diagram.
I have been following the method suggested in this article: https://planet.mysql.com/entry/?id=5992282, which involves setting the current database to OFFLINE_SOFT, waiting for some time, and then adding the new database to ProxySQL.
However, I encountered an issue where setting a server to OFFLINE_SOFT does not delay requests from already connected clients. My expectation was that ProxySQL would allow ongoing transactions to complete but would hold any subsequent queries. Specifically, if an application uses a single connection for two transactions and the server's status is set to OFFLINE_SOFT during the first transaction, I expected ProxySQL to hold the second transaction. However, it seems ProxySQL proceeds with the second transaction without delay.
This behavior could lead to issues since application programs typically use a connection pool library (e.g., Hikari CP for Spring applications). The application’s connection pool maintains the connection to ProxySQL (as shown by the blue arrow in the diagram) for extended periods. Thus, after setting the server to OFFLINE_SOFT, the Spring application continues to query the OFFLINE_SOFT database instead of holding the query for the switchover time, resulting in unavoidable connection errors.
I have the following questions:
I appreciate your assistance and look forward to your response.
Since my last email, I have discovered that my initial understanding was incorrect. ProxySQL can indeed delay queries from already connected clients when a server is set to OFFLINE_SOFT. I am writing this email to document my findings.
To test the OFFLINE_SOFT behavior, I used Sysbench, as described in the blog post https://planet.mysql.com/entry/?id=5992282, with the following command:
sysbench --report-interval=1 \
--threads=4 --max-requests=0 --time=20 \
--mysql-user=serveruser --mysql-password=serveruser \
--mysql-host=127.0.0.1 --mysql-port=6033 --mysql-db=test \
oltp_read_write run
However, unlike the results mentioned in the blog post, when I set the server to OFFLINE_SOFT and loaded it into runtime, the queries per second (QPS) did not drop.
To test if the issue was related to reconnection, I used the --reconnect=1 option in Sysbench. With this option, QPS actually dropped to 0 when the server was set to OFFLINE_SOFT. This initially led me to believe that ProxySQL does not handle already connected clients in OFFLINE_SOFT.
Upon further examination of the extended_info from stats_mysql_processlist, I found that multiplexing for the Sysbench connection was disabled. The reason was due to the mysql-auto_increment_delay_multiplex option. The auto_increment_delay_token was 4 for the connection, which caused multiplexing to be disabled.
When I set mysql-auto_increment_delay_multiplex to 0 and executed the same Sysbench command, QPS dropped to 0 when the server was set to OFFLINE_SOFT, confirming that ProxySQL can delay queries from connected clients as expected.
I wanted to share these findings to clarify the situation and correct my previous understanding.