Aurora MySQL 3.08.0 (compatible with MySQL 8.0.39) released! Has the slow SELECT COUNT been improved?
This is Onkai Yuta (@fat47) from the Service Reliability Group (SRG) of the Media Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article examines whether the previous issue of slow SELECT COUNT has been resolved following the release of AWS Aurora MySQL 3.08.0 (compatible with MySQL 8.0.39).
I hope this helps in some way.
Aurora MySQL 3.08.0 (compatible with MySQL 8.0.39) released!Excerpts from the updates that caught my attentionCloudWatch MetricsGlobal Status VariablesWhat is the global status variable aurora_temptable_ram_allocation?Has the slow SELECT COUNT been improved?Reproduction verificationCOUNT resultPlease note that the support period is shorter than that of the LTS 3.04 series.Conclusion
Aurora MySQL 3.08.0 (compatible with MySQL 8.0.39) released!
Aurora MySQL 3.08.0 (compatible with MySQL 8.0.39) was released on November 18, 2024 (November 19, Japan time)!
Here are some excerpts from the official What's New release article and its contents:
MySQL 8.0.39 includes several security enhancements and bug fixes, as well as improvements to improve database availability when dealing with large numbers of tables and to mitigate InnoDB issues related to redo logs and index processing. Aurora MySQL 3.08 also includes several availability improvements to reduce database restarts, improved memory management telemetry with new CloudWatch metrics, optimizations for major version upgrades from Aurora MySQL 2 to 3, and general improvements to memory management and observability.
Excerpts from the updates that caught my attention
Next, let's check out the release notes.
In addition to increased availability and improvements, it appears that many metrics have been added.
CloudWatch Metrics
Three new Amazon CloudWatch metrics have been added to help users monitor the InnoDB purge process:
PurgeBoundary
PurgeFinishedPoint
TransactionFinishedPoint
Added new CloudWatch metrics to help users identify long-running transactions that may be interfering with purge boundaries
TransactionAgeMaximum
Added three new CloudWatch metrics to help you avoid out-of-memory (OOM) situations:
AuroraMillisecondsSpentInOomRecovery
AuroraNumOomRecoverySuccessful
AuroraNumOomRecoveryTriggered
Changed three CloudWatch metrics for preventing out-of-memory (OOM) from running totals to incremental counters.
AuroraMemoryNumDeclinedSqlTotal
AuroraMemoryNumKillConnTotal
AuroraMemoryNumKillQueryTotal
Global Status Variables
Added two global status variables that indicate the amount of memory used by internal temporary tables.
aurora_temptable_ram_allocation
aurora_temptable_max_ram_allocation
aurora_temptable_ram_allocation
There was an issue with Aurora MySQL 3 that we covered in a previous article.
A parameter called temptable_max_ram was added in MySQL 8.0, and the following value is set by default:
- Maximum common memory pool size for temporary tables
16MB
Here are some quotes from the article above.
Starting with Aurora MySQL Version 3 (MySQL 8.0), the default storage engine for temporary tables is the TempTable storage engine.TempTable creates a temporary table in memory, but if its size exceeds the set value,Save the overflow data to storage.。It writes to local storage first, and if that overflows, it writes to the shared cluster volume.but,Aurora reader instances cannot write to the shared cluster volume, so queries will fail when their local storage is full.It will be.
In other words, the two new global status variables allow you to check the following:
aurora_temptable_ram_allocation- The current amount of memory (in bytes) used by internal temporary tables.
aurora_temptable_max_ram_allocation- The maximum amount of memory (in bytes) used at any one time by internal temporary tables since the last restart.
This willYou can monitor whether the memory set for temptables is about to overflow.This will prevent query errors from occurring on the reader instance.
Has the slow SELECT COUNT been improved?
Another thing I wrote in a previous blog post was about SELECT COUNT being slow in MySQL 8.0.
In this article,
SELECT COUNT in MySQL 8.0 was sometimes several dozen times slower than in MySQL 5.7, but this was improved in MySQL 8.0.37.
That's what I'm writing.
I wonder if the new MySQL 8.0.39 compatible engine has improved things. I'll try to verify it.
Reproduction verification
Creating a table and populating it with data
COUNT result
Aurora MySQL 3.08.0 (MySQL8.0.39 compatible)
I'll check the previous engine version just to be sure.
Aurora MySQL 3.07.1 (MySQL 8.0.36 compatible)
With 300,000 records12 secondsSELECT COUNT, which was also0.068 secondsIt was confirmed that the performance has improved.
Please note that the support period is shorter than that of the LTS 3.04 series.
3.08.0 has many great improvements, but please note that the support period is short as it is not an LTS.
The standard support period for 3.08 is about one year from now.November 30, 2025It is up to now.
The LTS 3.04 will be available until October 31, 2026, so this will be a shorter period.
Conclusion
With this 3.08.0 release, I feel that the areas that I was concerned about have been significantly improved!
Thank you AWS!!
SRG is looking for people to work with us.
If you're interested, please contact us here.