In my previous post I gave you feedback about an experiment I made with ProxySQL implementing connection redirection for Azure Database for MySQL.

ProxySQL connection redirection experiment with Azure database for MySQL/MariaDB. 1/2

My initial test was run by injecting from my Windows10 laptop WSL linux subsystem running sysbench and ProxySQL. It went through the internet to reach the Azure Database for MySQL. This is not production practice but it was deliberate to exacerbate the network impact.
But now I will redo the same test with injection coming from a VM in azure. This is definitely more realistic. The network latency will be much smaller. The VM will host  sysbench and proxySQL. The VM will be in the same Azure region as the Azure Database for MySQL.

We will test the same 3 different scenario we tested :
- normal connection through the shared gateway.
- connection through ProxySQL with redirection mechanism implemented
- connection through ProxSQL without redirection mechanism.

Sysbench and ProxySQL will run on an Azure Ubuntu VM General purpose 8 vCores / 32G RAM. It is always great to have an injecting machine with enough resources not to be the limiting factor in the benchmark 😉

The Azure Database for MySQL Server  run on  General purpose 2 vCores /10G RAM /100G storage

sysbench will run with "reconnect=1" to force reconnect after every query. This is to represent a PHP application for which you get connection at each page.
I used the script 'oltp_point_select.lua' not to have too much stress on the database.

Let us look at the sysbench result with traffic going either through ProxySQL implementing redirection (green line) or through the shared gateway (red line).

When looking at these results we notice important differences :
We get a much better throughput when going through ProxySQL.
We get a much lower latency 95 percentile which means that most queries execute faster (as seen from sysbench, including network transit).
When going through ProxySQL we get a very stable execution time with small outliers.  On the opposite with access through the gateway we get big outliers even under small workload.

If we look in more details. Here the metrics when going through the gateway (lat 95 and lat max are in ms) :

Same metrics when going through ProxySQL implementing  redirection :

Roughly when going through the proxy we get :

• throughput is roughly 20X better
• late 95 percentile is around 10X smaller
• max latency is around 6 time smaller

Let us also now look at the sysbench result with traffic going either through standard ProxySQL with no  redirection (green line) or through the shared gateway (red line).

This second test is to check the benefit of ProxySQL with  redirection vs standard ProxySQL. Let us look at sysbench result for the same workload on ProxySQL with no redirection :

The throughput are almost the same. The only significant difference is the stability of the response time. ProxySQL with redirection gives more stable results with a smaller standard deviation. Most of the benefit comes from ProxySQL connection pooling efficiency.

With a faster network ( reduced latency, bigger bandwidth) we get a bigger throughput. We also get with ProxySQL the same comparative benefit we saw in the previous post going through internet from a laptop to Azure Database for MySQL.
The lesson learned is that ProxySQL and Azure Database for MySQL/MariaDB are a great fit. The connection pooling implementation gives a great benefit. It can greatly increase the throughput you get from your Azure Database for MySQL/MariaDB.
The test with ProxySQL implementing redirection show improvement in database request execution time stability. These stability improvement in execution time with ProxySQL implementing connection redirection are worth exploring..

The benefit of ProxySQL with Azure database for MySQL/MariaDB covers a wide range of languages/frameworks and applications that do not offer a connection pooling mechanism and that generate a lot of connections ( PHP, WordPress, Drupal, Magento ...). In fact in many cases ProxySQL is a mandatory requirement to get great performance and fully use the power of your Azure Database for MySQL/MariaDB.

Implementing connection redirection at the ProxySQL level is something that is certainly worth exploring for execution time stability.

1/3
3/3

Some valuable links by Amol Bhatnagar ( @ambhatnaif ) if you want to implement ProxySQL with Azure Database for MySQL/MariaDB.

Load balance read replicas using ProxySQL in Azure Database for MySQL

Deploy ProxySQL as a service on Kubernetes using Azure Database for MySQL

Scaling an Azure Database for MySQL workload running on Kubernetes with read replicas and ProxySQL

Connecting efficiently to Azure Database for MySQL with ProxySQL

Set up ProxySQL clustering to connect efficiently to Azure Database for MySQL

Ingest ProxySQL Metrics into the Azure Monitor Log Analytics Workspace