Bug #121775 InnoDB DDL buffers need 4KiB alignment for O_DIRECT on 4Kn storage

Submitted: 13 Nov 8:30
Reporter: Xingyu Yang (OCA) Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.4.13 OS:Any
Assigned to: CPU Architecture:Any

[13 Nov 8:30] Xingyu Yang
Description:
When innodb_disable_sort_file_cache=ON and innodb_tmpdir is on an ext4 filesystem
backed by a device with 4096-byte logical sectors, CREATE INDEX can fail with:

  ERROR 1878 (HY000): Temporary file write failure.

The error log reports MY-012639/MY-012640: a DDL temporary-file write at offset 0,
with requested length 4096, failed with errno 22 (EINVAL / Invalid argument).
Both the offset and length are already multiples of 4096.

The DDL temporary-file buffers are allocated with
ut::make_unique_aligned<byte[]>(..., UNIV_SECTOR_SIZE, ...).
UNIV_SECTOR_SIZE is 512. This guarantees only 512-byte memory-address alignment,
whereas the test filesystem requires 4096-byte alignment for direct I/O.
Consequently, failure depends on the address returned by the allocator; it need
not occur on every execution, even on the same filesystem.

An independent O_DIRECT pwrite probe on the same test mount isolated this
requirement, using a length of 4096 and offset of 0 in both cases:

  Buffer address modulo 4096   Bytes written   errno
  0                            4096            0
  512                          -1              22

The original SQL succeeds with innodb_disable_sort_file_cache=OFF on the same
4Kn mount. It also succeeds with the option ON on ordinary ext4 backed by
512-byte logical sectors. A 4096-byte filesystem block size alone does not
establish that the filesystem enforces 4096-byte direct-I/O buffer alignment;
the logical-sector check and the direct-I/O probe are important controls.

Expected behavior:
The index is created successfully and the indexed lookup returns (1, 12467),
including when direct I/O for DDL temporary files requires 4096-byte alignment.

Actual behavior:
CREATE INDEX fails with ERROR 1878, and the server reports EINVAL for a
4096-byte temporary-file write at offset 0.

How to repeat:
1. On Linux, create a disposable 128 MiB disk-image file and a loop device with
   4096-byte logical sectors. Format only this newly created image; do not use
   an existing disk or partition. The commands below require loop-device and
   mount privileges, util-linux, and e2fsprogs. Set MYSQL_OS_USER to the operating
   system account used by the dedicated test mysqld instance.

   MYSQL_OS_USER=mysql
   TEST_ROOT=$(mktemp -d /tmp/mysql-ddl-4kn.XXXXXX)
   chmod 755 "$TEST_ROOT"
   printf 'Test directory: %s\n' "$TEST_ROOT"
   truncate -s 128M "$TEST_ROOT/disk.img"
   mkfs.ext4 -q -F -b 4096 "$TEST_ROOT/disk.img"
   LOOP_DEVICE=$(sudo losetup --find --show --sector-size 4096 "$TEST_ROOT/disk.img")
   mkdir "$TEST_ROOT/mnt"
   sudo mount -o nodev,nosuid,noexec "$LOOP_DEVICE" "$TEST_ROOT/mnt"
   sudo install -d -o "$MYSQL_OS_USER" -m 700 "$TEST_ROOT/mnt/innodb-tmp"
   sudo blockdev --getss "$LOOP_DEVICE"
   stat -f -c '%S' "$TEST_ROOT/mnt"
   findmnt --target "$TEST_ROOT/mnt"

   Both numeric checks must report 4096. The attached direct-I/O probe should
   accept a 4096-aligned buffer and reject a buffer whose address is 512 modulo
   4096, with length=4096 and offset=0 in both cases.

2. Initialize and start a dedicated MySQL debug test instance. Its data directory
   may remain on the ordinary filesystem; only DDL temporary files need the
   strict-alignment mount. Add these options to its normal startup command,
   expanding TEST_ROOT to the actual directory printed/recorded in step 1:

   --innodb-tmpdir="$TEST_ROOT/mnt/innodb-tmp"
   --innodb-disable-sort-file-cache=ON

   Keep the shell variables from step 1 for cleanup. If using a configuration
   file, use the resolved absolute innodb_tmpdir path rather than a shell variable.

3. Connect to the dedicated instance and select a new, empty test database. Run
   the following SQL, also provided verbatim in repro.sql:

   SELECT VERSION();
   SELECT @@global.innodb_disable_sort_file_cache;
   CREATE TABLE sbtest1 (
     id INT NOT NULL AUTO_INCREMENT,
     k INT NOT NULL DEFAULT 0,
     c CHAR(120) NOT NULL DEFAULT '',
     pad CHAR(60) NOT NULL DEFAULT '',
     PRIMARY KEY (id)
   ) ENGINE=InnoDB;
   INSERT INTO sbtest1 SET id=1, k=12467,
     c='31451373586-15688153734-79729593694-96509299839-83724898275-86711833539-78981337422-35049690573-51724173961-87474696253',
     pad='98996621624-36689827414-04092488557-09587706818-65008859162';
   CREATE INDEX k_1 ON sbtest1(k);
   SELECT id, k FROM sbtest1 FORCE INDEX(k_1) WHERE k=12467;
   DROP TABLE sbtest1;

   The recorded run failed at CREATE INDEX with ERROR 1878. Collect the server
   error log. If an allocation happens to be 4096-aligned, that run can succeed;
   repeat in a clean test database or after restarting the dedicated instance.
   A successful single run does not establish that the allocator guarantees the
   required alignment. After a failure, sbtest1 may remain in the test database.

4. Repeat with innodb_disable_sort_file_cache=OFF on the same mount. The original
   SQL should succeed, as it did in the recorded control. A second control uses
   ON with ordinary ext4 backed by 512-byte logical sectors.

5. After stopping the dedicated test server, unmount and detach only the loop
   device created in step 1:

   sudo umount "$TEST_ROOT/mnt"
   sudo losetup --detach "$LOOP_DEVICE"

   The disposable image remains under TEST_ROOT and can be removed afterward.

Suggested fix:
Use IO_BLOCK_SIZE (4096), rather than UNIV_SECTOR_SIZE (512), as the requested
alignment for these four DDL temporary-file buffer allocations:

  storage/innobase/ddl/ddl0builder.cc     Builder::init
  storage/innobase/ddl/ddl0file-reader.cc File_reader::prepare
  storage/innobase/ddl/ddl0fts.cc         FTS::Parser::init
  storage/innobase/ddl/ddl0merge.cc       Merge_file_sort::sort

Add debug assertions at ddl::pread and ddl::pwrite to check that the buffer
address is aligned to IO_BLOCK_SIZE. This gives debug regression tests a way
to detect invalid buffer alignment even on filesystems which accept 512-byte
direct-I/O buffers.
Extend innodb.create_index_with_disable_sort_file_cache with coverage for the
one-row reproducer and the affected DDL paths, and validate on the strict 4Kn
mount as well as through ordinary MTR runs.
[13 Nov 8:32] Xingyu Yang
[PATCH] InnoDB: align DDL temporary I/O buffers to 4 KiB

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: mysql-ddl-4k-alignment.patch (application/octet-stream, text), 11.69 KiB.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.