Skip to content

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

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.

Configuration :

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.

Test with ProxySQL implementing redirection

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

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) :

nbths 2 4 8 16 32 64 128 256
tr/s 38 78 156 282 313 316 313 313
lat95 58 57 58 69 113 215 443 862
latm 67 78 99 113 373 254 479 986

Same metrics when going through ProxySQL implementing  redirection :

nbths 2 4 8 16 32 64 128 256
tr/s 1481 2760 4651 6161 6831 6995 6931 6166
lat95 1.79 1.82 2.22 4.1 8.58 17 40 81
latm 15 19 21 16 30 45 68 10251

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

Test with ProxySQL and no redirection

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).

ProxySQL with no redirection

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 :

2 4 8 16 32 64 128 256
tr/s 1063 1986 3642 5554 6679 6943 6857 6335
lat95 2.3 2.52 2.76 4.1 8.13 17 40 83
latm 21 23 24 16 24 266 67 8328

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.

Lesson learned with this second test

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.

other tests :

1/3
3/3

pointers :

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

Leave a Reply

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