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

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

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.