Azure MySQL Database Flexible Server is Slow Out of the Box??

Question

I was notified by my database provider recently (ClearDB) that I would need to host my database somewhere else. I'd been using them for about 10 years and they were originally a partner with Microsoft, which is how I got hooked up with them in the first place. They didn't give any explanation as to why they were mysteriously going away. Regardless, they had a hard deadline of November 30 so I had to do something.

To me, the obvious choice was to migrate to Azure MySQL Flexible Server, since all of my web apps are hosted on Azure. It was initially challenging, because I wanted to create the server in East US and it continuously failed to deploy there. Upon someone's suggestion, I tried Central US and it deployed fine. So now, I have my web apps in East US and the database server in Central US.

My previous MySQL database was version 5.6, so it's probably a good thing that I upgraded to MySQL 8.0 at the same time. Sure, a handful of old queries had some syntax errors, but that was an easy fix. Everything is up and running now. But the queries are SO SLOW. I wouldn't think that I'd need to do anything to tune the database out of the box. And I did a SQL dump from my old database to create the new one, so all the indexes are still there. But the thing is just a lot slower:

1. My PHP pages which rely on the database load more slowly than they used to
2. My PHP batch jobs which call recursive INSERT or UPDATE statements went from sub 10 seconds in duration to over 100 seconds.

I want to be clear...I don't think I need to tune my queries to solve this problem. It isn't a micro problem, it is a macro problem. I read somewhere that someone else was having this issue and he solved it by turning off binary logging. He saw an immediate return to his MySQL 5.6 speed. Binary logging is ON by default in MySQL 8.0. And in the Azure console, it is a locked environment variable, so I cannot turn it off myself to see if that solves the issue.

Can anyone provide advice on how I can try to turn off binary logging? I'd like to try that before attempting things like revamping my batch jobs to do bulk inserts. I know there are a lot smarter people out there than me, but I really don't think the answer to this problem needs to be over-engineered. I think it is something very simple that just needs to be tweaked and I will be as good as new. Thank you for your help!

Answer 1

Hi Steve Pugh •,

Welcome to Microsoft Q&A forum.

As I understand, you are experiencing Slowness in execution of queries in Azure MySQL Flexible Server.

Thanks for the detailed explanation with setup in place and measures taken.

Could you please check resource allocation and Server Compute+ Storage if enough to handle workload.

Before analyzing individual queries, you need to define query benchmarks. With this information, you can implement logging functionality on the database server to trace queries that exceed a threshold you specify based on the needs of the application.

Azure Database for MySQL flexible server, it’s recommended to use the slow query log feature to identify queries that take longer than N seconds to run. After you've identified the queries from the slow query log, you can use MySQL diagnostics to troubleshoot these queries.

Before you can begin to trace long running queries, you need enable the slow_query_log parameter by using the Azure portal or Azure CLI. With this parameter enabled, you should also configure the value of the long_query_time parameter to specify the number of seconds that queries can run before being identified as “slow running” queries. The default value of the parameter is 10 seconds, but you can adjust the value to address the needs of your application’s SLA.

Monitoring InnoDB garbage collection

When InnoDB garbage collection is blocked or delayed, the database can develop a substantial purge lag that can negatively affect storage utilization and query performance.

The InnoDB rollback segment history list length (HLL) measures the number of change records stored in the undo log. A growing HLL value indicates that InnoDB’s garbage collection threads (purge threads) aren’t keeping up with write workload or that purging is blocked by a long running query or transaction.

Excessive delays in garbage collection can have severe, negative consequences:

• The InnoDB system tablespace will expand, thus accelerating the growth of the underlying storage volume. At times, the system tablespace can swell by several terabytes as a result of a blocked purge.
• Delete-marked records won’t be removed in a timely fashion. This can cause InnoDB tablespaces to grow and prevents the engine from reusing the storage occupied by these records.
• The performance of all queries might degrade, and CPU utilization might increase because of the growth of InnoDB storage structures.

Please refer to the below recommendation if useful in your case:

• Ensure that your database has enough resources allocated to run your queries. At times, you might need to scale up the instance size to get more CPU cores and additional memory to accommodate your workload.
• Avoid large or long-running transactions by breaking them into smaller transactions.
• Configure innodb_purge_threads as per your workload to improve efficiency for background purge operations. Note Test any changes to this server variable for each environment to gauge the change in engine behavior.
• Use alerts on “Host CPU Percent”, “Host Memory Percent” and “Total Connections” so that you get notifications if the system exceeds any of the specified thresholds.
• Use Query Performance Insights or Azure Workbooks to identify any problematic or slowly running queries, and then optimize them.
• For production database servers, collect diagnostics at regular intervals to ensure that everything is running smoothly. If not, troubleshoot and resolve any issues that you identify.

Also, if feasible you may try to move Server region by referring to this tutorial:

https://learn.microsoft.com/en-us/azure/mysql/flexible-server/how-to-move-regions

Reference official Microsoft Documenation:

https://learn.microsoft.com/en-us/azure/mysql/flexible-server/how-to-troubleshoot-query-performance-new

Let us know if this helped or you have more queries.

Thanks