Managed db performance documentation

@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 physical server that absolutely blows away anything I've seen from AWS and Azure.

I run two RAID arrays.

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

The second RAID array is a conventional SATA 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.

I am currently doing an upgrade, the first array will be a 2T highest possible performance
second array is Western Digital Gold conventional 8T drives.

Very disappointing performance from both Azure and Amazon AWS and extremely high cost. With that said, the Flexible server option sounds like it should be a huge improvement over the previous option we were stuck with.

@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, and I threw in my own personal laptop score results just for fun.

My personal laptop is using a consumer grade m2.1 device that delivers 3500 mb/s . On my servers I run 7000 mb/s devices, so I find anything cloud to be extremely slow compared to my physical servers that run raid1 arrays of 7000 mb/s m2.1 nand devices.

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 when using an IP address such as 127.0.0.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?

I'm asking because TCP latency is by far improved when using MariaDB 10.6.1 default configuration compared to MySQL 8 default configuration.

Yesterday I ran some additional tests:

installed MariaDB 10.6.15

ran tests

MARIADB 10.6.15 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.15 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.15 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.

MySQL taking 15.260 seconds to complete the TCP test
MariaDB taking 6.084 seconds to complete the TCP test. <-- MariaDB 10.6.15 wins by a huge margin.

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, re-configure settings.php / settings.local.php accordingly
2. load dump file for a real-world drupal wxt project
3. rebuild drupal caches with drush cr
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
• measured as follows: du -h /var/lib/mysql/schemaname or du -h /var/lib/mysql (can't recall which one I compared, but both same comparison between MySQL/MariaDB)
• MySQL data compressed: ~ 250mb
• MariaDB data 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. MariaDB is vastly more efficient.
** EDIT ** MariaDB has built-in ProxySQL service which vastly improves TCP throughput and drastically reduces TCP latency . MySQL does NOT have this and therefore suffers with very poor tcp performance. ** END EDIT **

Implications:

• This means 5x more IO operations for the same data on MySQL compared to MariaDB which has a profound effect on performance.
• **EDIT** Vastly improved tcp performance when using MariaDB in comparison to using MySQL. **END EDIT**

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.

#3478097: MySQL no longer recommended and remove Percona