Managed db performance documentation

Just a bit of background that I have more information in this regard in relation to Azure. :)

The problem is with the difference btw Single Server and Flexible Server.

Single Server

Single Server is on a retirement path w/Azure and all future installs are asked to use Flexible Server for both MySQL and PostgreSQL.

This is where most of the performance issues arise due to the fact that Single Server is on a Shared pool of backend windows node behind a gateway. Performance can be slightly improved by using redirection which reduces network latency between client applications and MySQL servers by allowing applications to connect directly to backend server nodes. Please note no functionality like this was added for the PostgreSQL Single Server.

https://learn.microsoft.com/en-us/azure/mysql/single-server/how-to-redir...

Flexible Server

With Flexible Server you get pretty much a managed virtual machine of which you get complete control over the IOPS and it is actually running under Linux rather then Windows. With this setup I have found performance to be acceptable. I would say with all the proper configuration you can probably in most cases get similar to 75-80% of the performance you would get with local containerized db that would obviously have less latency then a remote db even if in the same availability zone.

https://github.com/canada-ca-terraform-modules/terraform-azurerm-flex-mysql

@sylus, thanks so much for the explanation, this is a huge leap forward for us!

I know at least one skilled devops resource who hit a wall on this for quite some time, this is key information that will bring redemption!

This information was not easy to find. It's good to know that Azure has fixed the problem with the Flexible server option.

I host my own server that absolutely blows away anything AWS and Azure has to offer.

I run two RAID arrays.

The first RAID array is for the the system drive is the fastest currently available nand devices with an M2.1 interface on the motherboard.

The second RAID array is mounted to /data and is a conventional array. I knock off one of the main system devices out of the first array, so that it's unmounted and make an image and store the image on the /data partition.

After making an image , re-add the device back to the array. This way easy to make a fresh clone to secondary warm spare live without down time.

From there, a weekly or daily rsync.

For high availability situations, two warm spares where the second warm spare is a couple weeks cold.

I have yet to see a proper high performance Azure server or AWS server.

Very disappointing performance from both Azure and Amazon AWS and extremely high cost.

@sylus,

We're continuing ongoing evaluation of Azure. The flexible server option is in use.

I'm going to paraphrase a colleague on this.

He says this:

"IOPS is not even the issue here, I still believe. The issue is whatever Azure did to secure connections etc. such as logging, auditing, intrution detection overhead. It is not a Drupal issue, it is an azure design issue.

Profiling with AWS, only 9 seconds vs 71 seconds with Azure"

time (for i in {1..1000}; do mysql -h hostname_for_mysql -u username --password='password_password' database_name -e "select 1 from dual;" 2>&1; done)>/dev/null 

real	0m7.653s
user	0m3.513s
sys	0m3.704s

7.653 seconds with my personal laptop running the wxt-docker-scaffold from the application docker container to the mariadb container.

However run the same test in an Azure environment and we're seeing between 32seconds and 47 seconds for the exact same test.

It's between 6 and 8 times slower than a 15 watt low power laptop running a full stack of 6 docker containers.

AWS at least is able to deliver decent performance but Azure is not.

An update on this, I ran another benchmark directly on a server grade db host

Using a socket connection (localhost)
we found a real result of 3.8 seconds

Compared to the loop back 127.0.0.1 came in at over 7.8 seconds (very surprising result)

Compare this to the real result of between 32 and 48 seconds for the azure db from the application side

So, it appears that there's up to 44 seconds of network lag with an azure managed db on 1000 simple select calls.

Surprising to see that a socket is over twice as fast as the loopback 127.0.0.1 when testing directly from the db server host.

ok, ran some more tests, @sylus, you're correct with saying the MariaDB is significantly better, for some reason MySQL has much more TCP lag than MariaDB.

a side note:
if using a socket connection (localhost instead of 127.0.0.1) for example, MySQL 8 compares to MariaDB in the above mentioned test metric. MariaDB 10.6.1 however appears to handle TCP much more efficiently than MySQL 8.

Still evaluating.

@sylus, you mentioned MariaDB in the last paragraph of comment #2, how was this provisioned in the context of azure managed db? Is MariaDB a special option , which option did you choose to provision the azure managed db which enabled you to get MariaDB?

Yesterday I ran some additional tests:

installed MariaDB 10.6.1

ran tests

MARIADB 10.6.1 TCP (127.0.0.1) test

time (for i in {1..1000}; do mysql -h 127.0.0.1 -u username --password='password_password' database_name -e "select 1 from dual;" 2>&1; done)>/dev/null
real 0m6.084s

user	0m3.403s
sys	0m2.546s

MARIADB 10.6.1 SOCKET test

time (for i in {1..1000}; do mysql -h localhost -u username --password='password_password' database_name -e "select 1 from dual;" 2>&1; done)>/dev/null
real 0m5.946s

user 0m3.224s
sys   0m2.608s

then on the same server -
purged MariaDB then:

MySQL 8 installed

MySQL v8 TCP (127.0.0.1) test

time (for i in {1..1000}; do mysql -h 127.0.0.1 -u username --password='password_password' database_name -e "select 1 from dual;" 2>&1; done)>/dev/null 

real 0m15.260s

user	0m9.498s
sys	0m4.024s

MySQL v8 SOCKET test

time (for i in {1..1000}; do mysql -h localhost -u username --password='password_password' database_name -e "select 1 from dual;" 2>&1; done)>/dev/null

real 0m6.649s

user 0m3.492s
sys   0m2.909s

Conclusion: MariaDB 10.6.1 handles TCP much more efficiently than MySQL v8 given a default configuration with only one option set
The only option set was:
max_allowed_packets=64M

Same option set for both installations and test runs.

Hi @sylus, I've been told that MariaDB is no longer available as a flexible server option.

With my own benchmarks I've noticed IP lag issues with MySQL compared to what MariaDB is delivering.

https://azure.microsoft.com/en-us/updates/azure-database-for-mariadb-wil...

Wondering if you had any idea how this will affect those being forced into the managed db and what might be our options to mitigate this?

It seems very strange that MySQL would have a marked IP lag issue compared to MariaDB, wondering if it is doing something or have a configuration option enabled by default that possibly could be disabled or ajusted to mitigate the performance issue?

ongoing performance work trying to get MySQL 8 to perform fast in Azure has not yet been fruitful. Have cranked up IOPS as high as they go, cranked up the cost of the instance to 16000$ per month or more , still slow, cranked up memory, cpu cores, filesystem options, still slow as molasses.

Yet when testing a db instance on a 200$ option with MariaDB in azure, it's nearly 3x faster than anything we've gotten out of Azure+MySQL 8.

Additional analysis comparing MariaDB to MySQL:

Measuring the /var/lib/mysql folders respectively from a fresh install and loading the same wxt based drupal dump file into both MySQL and MariaDB.

1. Install db server
2. load dump file
3. rebuild caches
4. turn off db service
5. measure size of /var/lib/mysql folder(s)

• MySQL folder contents size uncompressed approximately 5x more data compared to MariaDB uncompressed
• MySQL folder contents compressed: ~ 250mb
• MariaDB folder contents compressed: ~ 59 mb

Conclusion:

MySQL spreads it's filesystem out 5x larger than MariaDB with the exact same number of data rows and tables.
MariaDB performance is vastly superior in comparison.

Implications:

This means 5x more IO operations for the same data on MySQL compared to MariaDB which has a profound effect on performance.

Another note on the TCP performance.

MariaDB has ProxySQL built-in which vastly improves tcp performance as seen from the socket vs localhost test results comparing MariaDB and MySQL.

MySQL does not have ProxySQL - TCP performance is very poor in comparison to MariaDB.

Exploring a new option now, PostgreSQL with Azure.