On Wed, 11 Feb 2026 at 07:12, Marko Mäkelä <[email protected]> wrote: > > Hi Gordan, > > On Wed, Feb 11, 2026 at 1:56 AM Gordan Bobic via discuss > <[email protected]> wrote: > > > > Ah - apologies - I just noticed that it was reintroduced again in a > > later version. I needed a bigger version jump. Was the auto-detection > > also removed, or is just outright not working with ZFS? > > When I designed the variable-size-block format that was introduced by > MDEV-14425 in 10.8, I chose the log file header size to be 12288 > bytes, or three 4096-byte blocks. The motivation for this was to allow > the two checkpoint headers to be safely written in an alternating > fashion, also when using O_DIRECT. According to my research back then, > the largest physical block size that is available is 4096 bytes.
I can assure you SSDs, especially large enterprise grade ones, were coming with erase block sizes greater than that even back then. Not to mention that virtualisation brings a whole new layer of complexity that can result in large underlying block sizes. > In https://archive.fosdem.org/2022/schedule/event/mariadb_innodb/ I > explained how the log format changes were motivated by performance. > > > Limiting max size of it to 4K is still sub-optimal, when underlying > > physical sector, especially on virtual storage, can be larger (e.g. > > ZFS volblocksize of 4K for block device volumes is expensive on > > metadata overheads and inefficient). > > I now realize that limiting the write size to 4096 bytes could be > suboptimal also in a RAID setting, where one might want to write one > stripe at a time. If you write one block at a time, I can imagine that > a write of 1 block could lead to N-2 blocks being read and 2 blocks > being written: the actual data block, and a parity block. Writing all > N blocks at a time would result in a write of the optimal N+1 blocks. Or more. If you are using n+3 RAID (e.g. ZFS RAIDZ3) with sector size of 4KB, then each 4KB write will result in 3 more parity blocks being written, resulting in 4x write amplification. > On ZFS, do you see any benefit for enabling compression on the InnoDB > WAL? That can make write amplification even worse because the underlying write becomes smaller. > If yes, what would be the compression block size, and is it > aligned in some way? Say, each 1 MiB block from the start of a file > would be compressed separately? If yes, then maybe the maximum write > block size should be 1 MiB, or whatever the input block size of the > built-in compression is. I don't think compression should factor into this from trying to guess it from the WAL side. That should be left to the user, because the compression could be happening at the exposed block device level, or it could be happening underneath the exposed block device level. For example, if I am exposing 16KB logical sectors and using compression underneath that, then I would not use compression at the top level. > The parameter innodb_log_write_ahead_size was reintroduced in > MDEV-33894. Before that, we simply tried to determine the physical > block size of the underlying storage (typically, 4096 or 512 bytes), > and falling back to 512 bytes if the size cannot be determined. > > Given the way InnoDB writes the log (it can overwrite the tail of the > log with new data, until the last block is completely filled up) and > the presence of the 12288-byte log file header, implementing a larger > write block size than 4096 bytes is somewhat tricky. Near the start of > the file, we would either have to rewrite the file header (and > allocate 12288 bytes of extra space in both log_sys.buf and > log_sys.flush_buf for that), or do some magic to subtract the 12288 > bytes from the write request. Rewriting the header should be the > simplest on any copy-on-write file system, and possibly on any SSD as > well. With an update-in-place, O_DIRECT file system on HDD, a power > cut in the middle of writing the file header could lead to > catastrophic data loss. Or pad the header out with a reservation up to innodb_page_size. Let's face it, nobody is going to care about wasting potentially up to 52KB of header space on a multi MB/GB file. -- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net Follow us: LinkedIn: https://www.linkedin.com/company/shatteredsilicon X: https://x.com/ssiliconbg _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
