Connecting efficiently to Azure Database for MySQL with ProxySQL

Introduction

A main cause of slow application performance is inefficient interaction in the connection between the database and the application. Connection management, including connection pooling, retry logic, etc., can significantly improve your database performance.

ProxySQL, a high-performance MySQL proxy, provides out-of-box connection management for Azure Database for MySQL. Using ProxySQL offers several benefits, including:

• Intelligent load balancing across different databases
• Transparent Read/Write split, which can determine if a database instance is running so that read traffic can be redirected accordingly
• Out-of-box connection pooling
• In-built retry logic

Note:

ProxySQL is an open source community tool. It is supported by Microsoft on a best effort basis. In order to get production support with authoritative guidance, you can evaluate and reach out to ProxySQL Product support.

Important: Azure Database for MySQL currently supports ProxySQL version 2.0.6 and later.

This blog post shows how you can boost your Azure Database for MySQL server performance using ProxySQL by setting efficient connection management through:

• Connection pooling
• Retry logic
• DNS-like functionality

Connection pooling

Establishing new connections in MySQL is an expensive operation because of authentication and SSL negotiation, which causes significant overhead. The common recommendation for efficiently managing connections is to use connection pooling. A connection pool is a cache of connections that are reused to accommodate future requests. As a result, if a new request be made, the application server will already have a connection available and prioritize the allocation of existing idle database connections.

You can use ProxySQL’s built-in connection pooling with Azure Database for MySQL. Refer to the appropriate article below for detailed steps on setting up ProxySQL:

• ProxySQL on Ubuntu VM
• ProxySQL as a service in Kubernetes

Because every workload is different, you may need to configure the number of open idle connections in the pool to address the needs of your workload. In ProxySQL, you use the mysql-free_connections_pct variable to control the percentage of open idle connections in the pool out of the total number of connections for a specific server in a hostgroup.

Note: If your application frequently establishes new connections, then we recommend increasing the value of mysql-free_connections_pct from the default of 10% to a value of between 50-60% , which will increase the number of open idle connections in the connection pool and improve the performance significantly.

Important: wait_timeout is the number of seconds the server waits for activity on a noninteractive connection before closing it. The pooled connection will get terminated after the value of wait_timeout is reached. Please make sure to increase the value of wait_timeout according to your workload to maintain the idle connection in the connection pool. 

Retry logic

ProxySQL provides out-of-the-box retry logic, which is recommended for handling transient errors that can occur (e.g. as a result of hardware or network failure). Transient errors typically manifest as dropped connections to a database server or an inability to open new connections to a server. These types of errors ultimately resolve themselves, and most are automatically mitigated by the system in under 60 seconds.

In ProxySQL, configure the mysql-query_retries_on_failure parameter to specify the number of times a query will be retried.

Configuring DNS alias like functionality

While connecting to your database server, you may want to configure an alias for multiple reasons, including:

• During initial development, your alias can refer to a test MySQL database server. When the application goes live, you can modify the alias to refer to the production server. The transition from test to production requires no modification to the configuration of several clients that connect to the database server.

Note: If you are using ProxySQL for the above functionality, then you have to create a database user in your production server to allow ProxySQL to connect to it.

• During a regional outage you use geo-restore to recover your server in a different server and region. You can modify your existing alias to point to the new server so that the existing client application could re-connect to it without changing configurations on several clients.
• When you are using point-in-time restore to restore your Azure Database for MySQL, which will create a new server and do not want to update the connection properties on several clients.

You can use ProxySQL to achieve the DNS alias like functionality for Azure Database for MySQL. To better illustrate how you can achieve DNS alias like functionality, consider the example of point-in-time restore.

Assume that you have an Azure Database for MySQL server (mydemoserver.mysql.database.azure.com) and ProxySQL is set up to connect to this server. You can learn more about this set up in my blog post here. Now, you want to restore “mydemoserver” using point-in-time restore, which will create a new server (mydemoservernew.mysql.database.azure.com). With ProxySQL, you just have to log in to the

ProxySQL admin console and update the connection properties in the mysql_servers table with the new server name (mydemoservernew.mysql.database.azure.com).

If you didn’t have ProxySQL set up in the example scenario above, then you’d have to change the connection string in your application, which isn’t always feasible.

If you have any questions or trouble setting up ProxySQL using Azure Database for MySQL, please contact the Azure Database for MySQL team at AskAzureDBforMySQL@service.microsoft.com.

Thank you!

Amol Bhatnagar

Program Manager - Microsoft