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

In the 2 previous posts(1/3, 2/3) I gave you feedback about an experiment I made with ProxySQL and connection redirection. I used sysbench 1.1 with parameter ”reconnect=1" which force reconnect after every query. This was 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.

In this new test I will keep connections open during all the run without re-connection. To do so I will run sysbench 1.1 with parameter ”reconnect=0". This is the exact opposite of what we did before. We will have long running connection through which lots of queries will go. This is by the way is the default behavior of sysbench with no reconnect parameter. In that case at startup sysbench open a set of threads/connections and run all transactions through them without reconnecting and just committing when necessary.
Keeping the connections open will mimic the behavior you can for example with a java application server that implement connection pooling.
This test is not transactional and will just issue selects.

Like before we will test :

- normal connection through the shared gateway.
- connection through ProxySQL with redirection
- connection through standard Proxysql without redirection mechanism.

Configuration

I will use the same configuration for the Azure Database for MySQL Server : a General purpose 2 vCores 100G instance.
Sysbench and ProxySQL will run on an Azure Ubuntu VM General purpose 8 vCores / 32G RAM.
I used sysbench 1.1 with "reconnect=0” to keep the same connections open for the all duration of the injection.
I used the same script as previously ’oltp_point_select.lua' . This is a read only workload. Contrary to the two previous test there will be more stress on the database as less time will be used managing reconnection or waiting on the network.

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 a much better throughput than with the 2 previous test. As the database server does not have to handle reconnect more queries can be executed.
What is also noticeable is that there is no much difference between max queries/s achieved going through the gateway or through ProxySQL.

When we compare for this test the throughput and latency 95 percentile result going through proxySQL are slightly better when the database server is not saturated. This is normally the area you want to operate your database in production. You load your database to 100% mostly in benchmark. This means we execute more queries faster in normal condition with redirection.

We notice also that 16 threads are enough for sysbench to saturate the database server.

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
tr/s	2123	4437	8623	14968	15669	16332
lat95	1	1	1	1	2	4
latm	11	9	11	180	1545	5283

Same metrics when going through ProxySQL implementing redirection :

nbths	2	4	8	16	32	64
tr/s	3126	6881	13094	15319	15548	15495
lat95	1	1	1	1	3	4
latm	16	10	24	680	1961	12586

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

In this second test with standard ProxySQL in front of the gateway or directly through the shared gateway the performances are almost the same. Going through ProxySQL is even slightly slower in all case as it is just an extra hop before the gateway. ProxySQL gives no benefit in that case :

nbths	2	4	8	16	32	64
tr/s	1895	3928	8837	14631	15556	16335
lat95	1	1	1	1	2	4
latm	26	17	315	403	1141	4875

Lesson learned with this third test

With this test where we used fast network ( reduced latency, bigger bandwidth) and where we keep the connection opened we were able to generate a high level of transaction/s. We executed 2x more queries per sec. There were no significant differences between the 3 approaches. Going through ProxySQL with or without redirection had very limited effect as the connection pooling was basically not used.

The connection redirection implemented in ProxySQL brings a benefit in the initial low number of threads phase. This highlight the fact that if you run a single SQL batch you will get benefit to run it by bypassing the gateway. This benefit would be there with or without ProxySQL.

The lesson learned from these 3 test is that ProxySQL and Azure Database for MySQL/MariaDB are a great fit. The connection pooling implementation gives a great benefit when there are lots of connection/s. In that case it can greatly increase the throughput you get from your Azure Database for MySQL/MariaDB.
In that same connection intensive context having ProxySQL implementing redirection show improvement in database request execution time stability.

The benefit of ProxySQL with Azure database for MySQL/MariaDB is very relevant for 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 that case ProxySQL is almost 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 in that context brings stability to execution time and is something that is certainly worth exploring.