Has Azure database for MySQL changed its IO percentage counting?

Harto Rannisto 5

We have Azure Database for MySQL flexible server service running as the database for a WordPress site. We have some periodic sync tasks that fetch data from our backend server using WP All Import and it saves the data to this MySQL database. We have also Storage IO Percentage metric alert in this MySQL database. This has been working fine for some time and we have not done any changes to it recently.

Suddenly last week we started to get alerts from the database, that the IO percentage has went over the limit. When investigating this issue we found out, that there has been "Routine Maintenance"operation on August 21, so assuming it has run this August maintenance update:
https://learn.microsoft.com/en-us/azure/mysql/flexible-server/release-notes/august-2024

Right after that maintenance the Storage IO Percentage metrics have started to behave differently even though our database usage has stayed the same and the Storage IO Count metrics are showing exactly same kind of numbers as earlier. So when checking the Storage IO Count metric nothing has changed when this maintenance was run, there are same kind of peaks in the IO usage every time those periodic sync tasks are started, otherwise the IO usage is quite flat and moderate. But the Storage IO Percentage metric is now reporting totally different kind of numbers. Before this maintenance the IO Percentage was peaking at 10-15% when the sync tasks were running and other times it was around 1%. Right after this change the metric never goes under 5% level and hits the 100% every time those sync tasks are running. The day's three first task runs seem to be using more IO Count and in that case the IO Percentage also stays at the top longer, for about five minutes, where as with other task runs it just peaks at 100% and then drops immediately.

So what in the august database update has caused this change in the IO Percentage metrics change and has it been intentional? Are we now suddenly really hitting the limits of our database plan IO rates?

Harto Rannisto 5 Reputation points

2024-08-27T11:57:57.04+00:00

Thank you for the quick response. We already have the "Auto scale IOPS" option selected in our database, sorry for not mentioning it already in the original post. For the background info we have General Purpose database with Compute size property: "Standard_D4ds_v4 (4 vCores, 16 GiB memory, 6400 max iops)". So to my understanding we should be allowed to use 6400 IO operations per second.

I now dived deeper to see how much IO we are really using. When selecting short enough custom time scale in Azure Metrics for "Storage IO Count", it drops the granularity to one minute (couldn't get the metrics per second nor anything under one minute, is that somehow possible, either in Azure Metrics UI or some other way?). When zooming in to our usage peaks the Azure Metrics shows that we are using quite constantly 30k to 35k IO operations per minute. With the day's first sync runs there is always one peak to about 45k IO operations that lasts only for one minute and then settles for this 30k to 35k operations per minute for about five minutes. Later in the day it peaks in the 30k to 35k level only for one minute per sync (which are run once in every half hour).

If counting average IOPS based on the above metrics, even the 45k operations per minute translates in average to about 750 IOPS which is not even close to our limit of 6400 IOPS. In theory it is possible that in some seconds the count reaches the maximum of 6400 operations, but even in theory this could last at maximum only seven seconds and then it should idle for the rest of the minute. Which is quite unlikely when taking into account the per minute ratio stays quite stable for about five minutes.

Then we can take into account what I said already in the original post: "Before this maintenance the IO Percentage was peaking at 10-15% when the sync tasks were running". These 30k to 45k operations per minute translate to 500 to 750 IOPS which is about 8-11% of the 6400 IOPS. So in the ball park of 10-15%.

So in the light of these calculations I'm quite sure that Storage IO Percentages shown before this August maintenance operations were the correct ones and these current metric values are way too high. So could you take a look what happened to the Storage IO Percentage metrics in the lates maintenance operation?
Harto Rannisto 5 Reputation points

2024-08-27T12:56:20.6+00:00

Thank you for the quick response. We already have that "Auto scale IOPS" option selected for our database, sorry for not mentioning it already in the original post. For further background we have General Purpose tier database with "Compute size" property: "Standard_D4ds_v4 (4 vCores, 16 GiB memory, 6400 max iops)". So to my understanding we should be allowed to use 6400 IO operations per second.

Also to clarify your comment: "As I understand, you are experiencing change in Storage IOPS count for Azure Database for MySQL." No, our IOPS counts have not changed at all, they are exactly the same as earlier. Only the "Storage IO Percentage" metrics have changed and show now 100% IO percentages for our peak usage times.

I now dived deeper into the Azure Metrics "Storage IO Count" metrics to see how much IO operations we are really using. When selecting small enough custom time window the Metrics UI drops the granularity to one minute (couldn't get per second values nor anything less that one minute, are there any way to do that either with the Azure Metrics or some other way?). When zooming into the peak IO counts (that happens when our periodic sync tasks run) I can see that during the peak times the IO Count stays quite stable at 30k to 35k operations per minute. Only exceptions are the day's first three syncs where it peaks to about 45k operations per minute for the first minute and then settles to this 30k to 35k operations per minute for the next about five (5) minutes. With the later syncs in the day it only peaks in this 30k to 35k range and for only one (1) minute.

When translating these per minute values to per second values (IOPS) we can see that 30k per minute translates to 500 IOPS and even the highest 45k operations per minute to 750 IOPS. So these are not even close to our plan limit of 6400 IOPS. They are about 8-11% of the plan limit. This percentage is actually in the same ball park with this comment in my original post: "Before this maintenance the IO Percentage was peaking at 10-15% when the sync tasks were running".

In theory it could be possible that our service is using 6400 IOPS for some seconds, but even in theory with the highest peak value of 45k operations per minute this could last only for 7 seconds and then it should idle for the rest of the minute. Which is highly unlikely when taking into account that the peak per minute rate stays always quite stable for five minutes in a row.

So when taking these calculations into account I would say that "Storage IO Percentage" metrics were correct before the August maintenance operation, but now after the operation the percentages shown do not correspond to the plan IOPS limits. So I wonder how are these percentages calculated now and could they be fixed so that would be again percentages of the plan IOPS limit? Because we currently use the IO Percentage metrics with alerts so that we get alert when the percentage goes over a specific limit. Now we get alerts daily even when we are not really hitting the limits.
Harto Rannisto 5 Reputation points

2024-08-28T06:33:53.3166667+00:00

Oh, now it shows the both answers. When I posted the first comment it didn't say anything whether saving it succeed or not, it just vanished. When after half an hour the comment still wasn't visible, I wrote it again. At that time it actually showed error message saying "This comment has been deleted due to violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken." Maybe they got first banned by some AI heuristic, but then made visible via manual investigation.

Anyway, now the same answer is there twice with slightly different wording. Hopefully it makes understanding my issue easier. :)
ShaktiSingh-MSFT 15,421 Reputation points

2024-08-28T06:34:56.61+00:00

Hi Harto •,

Thanks for providing the details.

For deeper investigation and troubleshooting, this case has to be taken care by support.

We request you to raise support case so that dedicated engineer can look into your server and help in analysis.

Let us know if you need any help in raising the same.

Thanks
Harto Rannisto 5 Reputation points

2024-08-28T06:56:19.88+00:00

And now when starting my work day by checking the metrics, it seems that the percentage metric has reverted back to the correct behavior. This day's first sync times now show the Storage IO Percentage as ~13%. Actually the change in the percentage metric behavior happened already yesterday around 7:35 UTC time.

So looks like somebody else noticed this also and the issue is now fixed. Thank you for your attention anyway!
ShaktiSingh-MSFT 15,421 Reputation points

2024-08-30T06:02:13.3666667+00:00

Hi Harto •,

Thanks for your reply here that you are able to see correct numbers now.

Have a good day!

1 answer

ShaktiSingh-MSFT 15,421 Reputation points

2024-08-27T08:38:42.58+00:00

Hi Harto •,

Welcome to Microsoft Q&A forum.

As I understand, you are experiencing change in Storage IOPS count for Azure Database for MySQL.

From the details it appears to be Autoscale IOPS configuration, it offers the flexibility to scale IOPS on demand, eliminating the need to pre-provision a specific amount of IO per second. By enabling Autoscale IOPS, your server will automatically adjust IOPS based on workload requirements. With the Autoscale IOPS featured enable, you can now enjoy worry free IO management in Azure Database for MySQL flexible server because the server scales IOPs up or down automatically depending on workload needs. For detailed information on the ‘Max Supported IOPS’ for each service tier and compute size, refer to the service tiers documentation. AutoScale IOPS will scale up to these limits to optimize your workload performance.

For example, E-commerce workloads:

If you own an e-commerce website that experiences fluctuations in traffic throughout the year. During normal periods, the workload is moderate, but during holiday seasons or special promotions, the traffic surges exponentially.

With Autoscale IOPS, your database can dynamically adjust its IOPS to handle the increased workload during peak periods. When traffic spikes, such as during Black Friday sales, the auto scale feature allows your database to seamlessly scale up the IOPS to meet the demand. This ensures smooth and uninterrupted performance, preventing slowdowns or service disruptions. After the peak period, when the traffic subsides, the IOPS scale back down, allowing for cost savings as you only pay for the resources utilized during the surge.

You may move back to pre-provisioned IOPS by opting for pre-provisioned IOPS under Compute + Storage section under Settings blade.

Refer: https://learn.microsoft.com/en-us/azure/mysql/flexible-server/concepts-storage-iops

for more details.

Hope this helps.

Let us know if you have a different ask.

Thanks
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Has Azure database for MySQL changed its IO percentage counting?

1 answer

Your answer