Something changed in MySQL 8.4 related to caching, and it is easy to miss, so it deserves a post. And a subject adjacent to this is the missing Release Candidate for MySQL 8.4 LTS, with my hope that the next LTS will have a Release Candidate, so I also cover this topic below.
(if you are not interested in Caching and Flushing, you can jump directly to the section about Release Candidate)
Flushing and Caching
I sometimes do tests with MySQL that involves quick IOs (example). An easy way to simulate this behavior is to have a small InnoDB Buffer Pool and an ibd / data file fitting in the Linux Page Cache. A few months ago, I was struggling to do this with MySQL 9.2.0, and after opening a bug about (Bug #117691), this was traced down to the new InnoDB Defaults of MySQL 8.4. These were introduced directly in 8.4.0 LTS, without going through an Innovation Release, which will bring us to the Missing Release Candidate section below. The involved parameter is innodb_flush_method, and contrarily to what its name suggests, it impacts more than flushing.
In the documentation for innodb_flush_method, the first sentence / paragraph is :
Defines the method used to flush data to InnoDB data files and log files, which can affect I/O throughput.
Its default in 8.0 is fsync and in MySQL 8.4, it becomes O_DIRECT if supported (which is the case for Linux). If we read further down in the documentation, we have (bold added for emphasis) :
O_DIRECT or 4: InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.
So innodb_flush_method does not only impacts flushing, it also impacts how data files are open / read. And not super clear in the above (you need to read the Linux open system-call documentation for figuring this out), the Linux Page Cache is bypassed when a file is opened with O_DIRECT. I think this deserves a clarification in the MySQL documentation, and for that, I opened Bug #119943 : Documentation for innodb_flush_method is too Vague about its Impact on Data File Reading / Caching.
O_DIRECT (since Linux 2.4.10)
Try to minimize cache effects of the I/O to and from this file.
Will this change in 8.4 impacts you ? As always, it depends...
If leveraging the Linux Page Cache with MySQL 8.0,
upgrading to 8.4 might cause a drop in performance !
Are there a lot of deployments benefitting from the Linux Page Cache ? It is hard to tell. If you have a MySQL instance deployed on a dedicated server, most of the RAM should be allocated to the InnoDB Buffer Pool, and data files should be cached there. If your Buffer Pool is not sized correctly (too small), there might be some hits in the Linux Page Cache when reading data files, and the new default will cause a performance regression. The solution here is not to change innodb_flush_method to its old value, it is to correctly size the Buffer Pool (innodb_buffer_pool_size).
Another situation with potential cache effect is a shared server. In such deployment, a MySQL instance is co-located with other applications needing RAM, and because of this, the Buffer Pool cannot be given most of the RAM of the server. Unclear if the new default is bad there, this needs more head scratching.
There is also the case of Containers and Kubernetes. The way RAM quotas are managed in such deployment is complicated and depends on how things are set up. An interesting reading on the subject is cgroup v2 and Page Cache (it is also interesting to read the whole series SRE deep dive into Linux Page Cache).
Overall, I think the new default for innodb_flush_method is not very bad, but it is not very good either. Personally, I might have left it untouched, letting people needing extra flushing performance modify it themselves (complete flushing tuning implies touching many parameters, so just changing this one is a marginal win IMHO).
However, one thing is clear to me :
It is bad to change defaults without announcement,
without discussion, and in a frozen / LTS Release !
Previous MySQL default changes were announced / discussed...
- Proposal to change Replication and InnoDB Settings in MySQL 5.7
- Proposal to change additional defaults in MySQL 5.7
...and I do not remember new defaults being introduced in a frozen / LTS Release. Forcing these new defaults in 8.4.0 LTS is IMHO a very bad choice from Oracle, and this brings us to the Release Candidate topic.
Missing Release Candidate in 8.4 LTS
Introducing unannounced and undiscussed InnoDB defaults is not the only problem in MySQL 8.4.0 LTS. There are others...
The command RESET MASTER is removed in 8.4.0, without being deprecated in 8.0 (it was deprecated in 8.2.0), and without having introduced its replacement in 8.0 (RESET BINARY LOGS AND GTIDS was introduced in 8.2, at the same time RESET MASTER was deprecated). This leads to a weird discontinuity in the documentation (see Bug #115187 and the RESET MASTER 8.0 documentation mentioning something only existing in 8.4). But worse, it makes it complicated to upgrade from 8.0 to 8.4 because a command working in 8.0 does not work in 8.4, and the replacement of this command in 8.4 not having been introduced in 8.0.
Another example is the removed SET_USER_ID grant in 8.4.0, also without being deprecated in 8.0 (it was also deprecated in 8.2.0). This leads to a potential replication breakage from 8.0 to 8.4 (I opened Bug #115310 for this, and there is a related Twitter thread on the subject).
And back at InnoDB Defaults : Change Buffering / innodb_change_buffering, enabled by default in 8.0, disabled in the new defaults of 8.4.0 (doc / release notes), is re-enabled in 9.5.0 (doc / release notes). A little weird, don't your think ? And this change of heart does not match the explanation given by LeFred in his post about the new defaults. But this might not be Oracle changing their mind... Searching the GitHub repository for WL #16967 (referenced in the 9.5.0 release notes) shed some light on this turnaround : I wished there would have been better transparency on this (this might be a little off-topic for this post, but relates to a dubious change in 8.4.0 which should have gone through an announcement / discussion / Release Candidate process).
For me, it looks like Oracle was pressed into releasing 8.4 LTS, that they included half-baked changes at the last minute, and users are suffering the consequences. And I am afraid the same will happen with 9.7 LTS, so I think...
Oracle should avoid making these mistakes again,
either introducing 9.7.0 LTS with minimal changes,
or either waiting for 9.8.0 or later to be LTS !
No comments:
Post a Comment