Setting up ProxySQL as a connection pool for Azure Database for MySQL – Flexible Server

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

ProxySQL is a high-performance MySQL proxy that enables developers to distribute queries across multiple MySQL servers to balance the load more efficiently. ProxySQL performs a read/write split that is transparent to the application and doesn’t require any application side changes. In addition to intelligent load balancing, ProxySQL also provides built-in connection pooling, which is useful for certain application frameworks and workloads that don’t handle connection management well.

 

This blog post provides the steps required to set up ProxySQL with Azure Database for MySQL - Flexible Server. You can use this functionality for connection pooling or splitting the read and write workloads across Azure Database for MySQL Flexible read replicas. 

 

Deployment options


There are multiple options available for deploying ProxySQL. You can install ProxySQL:

  • On an application server (Recommended) – You can install ProxySQL on the same server as application, which helps to minimize network latency, avoids having a single point of failure, and lowers cost. Before using this approach, ensure that your application server has sufficient CPU and memory to handle the extra workload incurred by running ProxySQL.
  • In a dedicated VM on Azure – You can deploy a dedicated server for ProxySQL on the same virtual network to manage all the short-lived sessions coming from application servers. To some degree, this option requires fewer backend connections and less configuration effort. In addition, it doesn’t add to the workload running on the application server. However, using this option does pose a potential risk in that it could lead to a situation in which the VM becomes a single point of failureleading to unavailability of the application if the ProxySQL VM is down. This approach also increases the network latency between the ProxySQL VM and the application server/database server. 
  • In a Kubernetes cluster – If there’s an existing Kubernetes cluster that hosts the application, consider deploying ProxySQL in the Kubernetes cluster when the system demands high availability and reliability. To set up the pod, put ProxySQL in a container coupled with an application container in same pod using a sidecar patternor deploy a separate pod for ProxySQL service for redundancy. For further information, see Deploy ProxySQL as a service on Kubernetes using Azure Database for MySQL. 

The traffic flow associated with this architecture is shown in the following diagram. 

ziling_0-1627354413591.png

The following table shows the network isolation and latency with each deployment option.

 

 

On app server

In extra VM

In K8S cluster

Public access (allowed IP addresses)

Not recommended

Private access (VNet

integration)

Round-trip time (RTT)*

2*t3

2*(t1+t2)

Internal communication
within k8s+2*t4

 *RTT explains the total amount of time that could be delayed in all of communication points. 

 

This blog focuses on installing ProxySQL on an Ubuntu VMwhich can either be the VM that hosts the application or a dedicated VM. The Azure Database for MySQL server is provisioned with the default public access networking option, but the steps below also apply to a server provisioned with private access (VNet integration).

 

Prerequisites

 

Before setting up ProxySQL, ensure that you have: 

 

Install ProxySQL on the Ubuntu VM

 

1. On the Ubuntu VM, download the latest version of ProxySQL (from https://github.com/sysown/proxysql/releasesinto the /tmp directory.

cd /tmp
curl -OL  https://github.com/sysown/proxysql/releases/download/v2.^C6/proxysql_2.0.6-ubuntu18_amd64.deb 

2. Install ProxySQL by using dpkgand then update the package repository to ensure that you have the latest version build.
 

sudo dpkg -i proxysql_*  
sudo apt-get update 

3. Install the mysql-client.

 

sudo apt install mysql-client-core-8.0

4. Restart the ProxySQL service.

 

sudo systemctl start proxysql 

5. Check the status of the service.

 

systemctl status proxysql  

If the service has started successfully, you should see output similar to the following:

 

apple@proxysqlnew:~$ systemctl status proxysql 
● proxysql.service - High Performance Advanced Proxy for MySQL
Loaded: loaded (/lib/systemd/system/proxysql.service; enabled; vendor pres>
Active: active (running) since Tue 2021-07-20 10:58:22 UTC; 1min 13s ago
Process: 743 ExecStart=/usr/bin/proxysql -c /etc/proxysql.cnf (code=exited,>
Main PID: 1137 (proxysql)
Tasks: 20 (limit: 38530)
Memory: 138.3M
CGroup: /system.slice/proxysql.service
├─1137 /usr/bin/proxysql -c /etc/proxysql.cnf
└─1139 /usr/bin/proxysql -c /etc/proxysql.cnf
Jul 20 10:58:18 proxysqlnew systemd[1]: Starting High Performance Advanced Prox>
Jul 20 10:58:19 proxysqlnew proxysql[743]: 2021-07-20 10:58:19 [INFO] Using con>
Jul 20 10:58:19 proxysqlnew proxysql[743]: 2021-07-20 10:58:19 [INFO] SSL keys/>
Jul 20 10:58:22 proxysqlnew systemd[1]: Started High Performance Advanced Proxy>
lines 1-15/15 (END)...skipping...
● proxysql.service - High Performance Advanced Proxy for MySQL
Loaded: loaded (/lib/systemd/system/proxysql.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2021-07-20 10:58:22 UTC; 1min 13s ago
Process: 743 ExecStart=/usr/bin/proxysql -c /etc/proxysql.cnf (code=exited, status=0/SUCCESS)
Main PID: 1137 (proxysql)
Tasks: 20 (limit: 38530)
Memory: 138.3M
CGroup: /system.slice/proxysql.service
├─1137 /usr/bin/proxysql -c /etc/proxysql.cnf
└─1139 /usr/bin/proxysql -c /etc/proxysql.cnf
Jul 20 10:58:18 proxysqlnew systemd[1]: Starting High Performance Advanced Proxy for MySQL...
Jul 20 10:58:19 proxysqlnew proxysql[743]: 2021-07-20 10:58:19 [INFO] Using config file /etc/proxysql.cnf
Jul 20 10:58:19 proxysqlnew proxysql[743]: 2021-07-20 10:58:19 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
Jul 20 10:58:22 proxysqlnew systemd[1]: Started High Performance Advanced Proxy for MySQL.

 

Set up ProxySQL

 

1. Connect to the ProxySQL administration interface using the default password ‘admin’.

 

mysql -h127.0.0.1 -uadmin -padmin -P6032 

2. Configure the host group to add Azure Database for MySQL as the backend server, and then enable read/write traffic to the server group defined.

 

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'pfsmysql57.mysql.database.azure.com',3306,1,'Azure Mysql'); 

3. Enable SSL support in the ProxySQL server pool.

 

UPDATE mysql_servers SET use_ssl=1 WHERE hostgroup_id=1;  

 

Create MySQL users on the Azure MySQL server

 

While you can use the default admin user in Azure Database for MySQL, you can also create another admin user for the ProxySQL connection. To create an admin user for the ProxySQL connection, perform the following steps:

 

1. Create a MySQL user. 

 

CREATE USER 'mydemouser'@'%' IDENTIFIED BY 'secretpassword';  

 2. Grant privileges to the new user and then refresh.

 

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'mydemouser'@'%' WITH GRANT OPTION; 
FLUSH PRIVILEGES; 

 

Create the ProxySQL user

 

  • Allow the ‘mydemouser’/default admin user to connect to ProxySQL server.

 

insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values ('mydemouser','secretpassword',1,1); 

 

Configure monitoring on ProxySQL

 

1. Create a monitor user on Azure Database for MySQL.

 

CREATE USER 'monitoruser'@'%' IDENTIFIED BY 'secretpassword';
GRANT SELECT ON *.* TO ' monitoruser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;  

2. On the server running ProxySQL, configure mysql-monitor to the username of the new account.

 

mysql -h127.0.0.1 -uadmin -padmin -P6032
set mysql-monitor_username='monitoruser';
set mysql-monitor_password='secretpassword';  

 

Configure the routing rules on ProxySQL

 

  • On the server running ProxySQL, configure a route rule to forward the read/write traffic to the backend host group.

 

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$,^SELECT',1,1);

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'SELECT',1,1); 

 Note: To split the read/write load to a different server group, change the "hostgroup" and "match_digest" parameters appropriately.

 

Key parameters to modify based on workload change

 

There are several important parameters to modify based on changes to the workload. 

 

Parameter 

Description 

mysql-max_connections 

Specifies the maximum number of client connections that the proxy can handle. If this threshold is reached, new connections are rejected with the #HY000 error, and the error message “Too many connections” displays. 

mysql-free_connections_pct 

Controls the percentage of open idle connections allowed out of the total maximum number of connections for a specific server in a host group.

 

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

mysql-wait_timeout 

Specifies the time that a proxy session (a conversation between a MySQL client and a ProxySQL) can be idle before the proxy ends the session.

mysql-connect_timeout_server_max 

Specifies the timeout for connecting to a backend server from the proxy. When this timeout is reached, an error with the code 9001 is returned to the client and the message “Max connect timeout reached while reaching hostgroup…” displays. 

Mysql-query_retries_on_failure 

Specifies the number of times failed queries can be restarted for applications tolerant to query failures. 

Mysql-max_transaction_time 

The default value of 4 hours is ideal for most applications and use-casesbut applications run in an HTAP mode. If any queries tend to take more than 4 hours (for example, weekly business reports running against an OLTP DB), then increase the value if applications should not tolerate stuck or very slow running queries against a database. 

Mysql-max_allowed_packet 

Specifies the maximum size of one packet that mysql can accept. The best practice is to mirror the max_allowed_packet  value from the backend mysql database. 

Mysql-long_query_time 

Specifies the threshold for counting queries passing through the proxy as ‘slow’ queries. 

 

1. To change the value of any parameter, run the following command: 

 

UPDATE global_variables SET variable_value=xxx WHERE variable_name=xxxx'; 

2. To view current settings for a specific parameter, run the following command: 

 

SELECT * FROM global_variables WHERE variable_name LIKE '%xxx%' 

Note: For more information about configuring parameters based on your workload, see the List of MySQL Variables.

 

Save the ProxySQL configuration changes to persist across restarts

 

1. To save the settings to runtime on the server hosting ProxySQL, run the following commands:

 

load mysql users to runtime;  
load mysql servers to runtime;  
load mysql query rules to runtime;  
load mysql variables to runtime;  
load admin variables to runtime; 


2. To save the settings to disk on the server running ProxySQL, run the following commands:

 

save mysql users to disk;  
save mysql servers to disk;  
save mysql query rules to disk;  
save mysql variables to disk;  
save admin variables to disk;  

 

Verify that ProxySQL is managing opening connections

 

After successfully completing the above steps, you’ve configured ProxySQL as a connection pool to manage all the short live sessions. To test the functionality by determining if IO requests are being forwarded properly, perform the following steps.

 

1. Sign in to the server running ProxySQL using the ProxySQL user you created.

 

mysql -h127.0.0.1 -u<AzureMySQLUsername> -p<password> -P6033  

2. Run the read and write queries.

 

SELECT * FROM mydemotable; 
UPDATE mydemotable SET mydemocolumn=value WHERE condition;  

3. To verify that ProxySQL has routed the above read and write correctly, connect to the ProxySQL administration interface with the default password ‘admin’.

 

mysql -h127.0.0.1 -uadmin -padmin -P6032 

4. To review the query details via ProxySQL, run the following query:

 

SELECT * FROM stats_mysql_query_digest 
SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt; 

5. To monitor connection usage in the pool, run the following query

 

SELECT * FROM stats.stats_mysql_connection_pool; 

6. Review the output for details about the pool usage and determine if there are any failed sessions by checking the items in bold text below.

 

mysql> SELECT * FROM stats.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         | pfsmysql57.mysql.database.azure.com | 3306     | ONLINE | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 892        | 

+-----------+-------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ 

1 row in set (0.01 sec) 

 

Conclusion

 

ProxySQL is a great lightweight load balancer proxy that can improve your application performance and scalability with MySQL. It helps with improved connection management, reduced latency, and transparent read/write load balancing across replicas running Azure Database for MySQL Flexible Server.

 

For more detailed configuration informationsee the ProxySQL official site: 

https://proxysql.com/documentation/getting-started/ 

 

If you have any feedback or questions about using the architecture described above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com.

One Reply to “Setting up ProxySQL as a connection pool for Azure Database for MySQL – Flexible Server”

  1. How the application connect with proxysql server? I have configurd proxysql server in a virtual machine and it is working fine with the backend server. But how it is connecting with front end application?

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.