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) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article examines whether the past 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!A selection of interesting updatesCloudWatch 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)!
Below are some quotes from the official What's new release article and its contents.
MySQL 8.0.39 includes several security enhancements and bug fixes, improving database availability when dealing with large numbers of tables, and mitigating InnoDB issues related to redo logs and index handling. Aurora MySQL 3.08 also includes multiple 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.
A selection of interesting updates
Next, let's check out the release notes.
In addition to increased availability and improvements, it looks like a lot of metrics have been added.
CloudWatch Metrics
Three new Amazon CloudWatch metrics have been added to enable users to 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):
AuroraMillisecondsSpentInOomRecovery
AuroraNumOomRecoverySuccessful
AuroraNumOomRecoveryTriggered
Changed three CloudWatch metrics for avoiding 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 the Aurora MySQL 3 series that I have covered in a previous article.
A new parameter called temptable_max_ram was added in MySQL 8.0, and its default value is as follows:
- 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 the size exceeds the set value,Save the overflowed data to storage.。It writes to the 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 is about SELECT COUNT becoming slower in MySQL 8.0.
In this article,In some cases, SELECT COUNT was several dozen times slower in MySQL 8.0 than in MySQL 5.7, but this has been improved in MySQL 8.0.37.What he writes is:
I wonder if the new engine compatible with MySQL 8.0.39 has improved things. I'll check it out.
Reproduction verification
Creating tables and populating them 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 also applied0.068 secondsIt was confirmed that the situation had 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 will end in about a year.November 30, 2025It is up to now.
The LTS version 3.04 will be available until October 31, 2026, so this will be a shorter period than that.
Conclusion
With the release of 3.08.0, I feel that the issues I was concerned about have been significantly improved!
Thank you AWS!!
SRG is looking for people to work with us. If you are interested, please contact us here.