Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-36722

MariaDB 11.4.5: ibdata1 autoshrink feature does not reduce file size

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.4.5
    • 11.4

    Description

      *Steps to Reproduce:*

      1. Configure MariaDB 11.4.5 on CloudLinux 8.10 (or similar RHEL-based OS) with the key settings listed in Environment, critically ensuring `innodb_data_file_path` includes the `autoshrink` parameter and `innodb_fast_shutdown = 0` are present in the loaded configuration.
      2. Start/Restart the MariaDB service (`systemctl restart mariadb`).
      3. Allow the server to run under a mixed workload. In this case, attempts were made to free space, including running `OPTIMIZE TABLE` on databases.
      4. Monitor the physical file size of the `ibdata1` file located in the MariaDB data directory (`/var/lib/mysql/ibdata1`) using filesystem tools like `ls -lh`. Monitor over an extended period (in this case, 2-3 weeks).

      *Expected Result:*

      The physical `ibdata1` file size on the filesystem should eventually decrease after space-freeing operations (like `OPTIMIZE TABLE` or large `DELETE`s) and server restarts (with `innodb_fast_shutdown=0`), as the `autoshrink` mechanism reclaims unused space and returns it to the operating system.

      *Actual Result:*

      The physical `ibdata1` file size on the filesystem remains unchanged (observed at 308G) over a 2-3 week monitoring period, despite the correct configuration options being set and attempts to optimize databases. This indicates the `autoshrink` mechanism is not freeing space back to the OS as expected.

      *Supporting Information:*

      1. *MariaDB Service Status (Redacted):*
      ```
      ● mariadb.service - MariaDB 11.4.5 database server
      [...]
      Active: active (running) [...]
      ```

      2. *Configuration Files (Redacted Excerpts):*

      • User confirms `innodb_data_file_path=...:autoshrink` and `innodb_fast_shutdown=0` exist in the configuration files.
      • Other relevant settings (from report):
        ```
        max_connections = 2500
        performance_schema = ON
        log_error = /var/log/mariadb/mariadb.err
        log_warnings = 2
        innodb_purge_threads = 12
        innodb_purge_batch_size = 1000
        innodb_io_capacity = 2000
        sql_mode = (Empty)
        innodb_flush_method = O_DIRECT
        ```

      3. *MariaDB Startup Log Snippets (`/var/log/mariadb/mariadb.err`):*

      • *Note:* While the user confirmed the settings exist in the config files, the provided startup log snippets *do not explicitly show MariaDB acknowledging the `autoshrink` parameter or logging the setting of `innodb_fast_shutdown=0`*. This might indicate the settings aren't being applied as expected, or the logging for these specific parameters is not verbose in this build.
      • Provided Log Snippets (showing clean startup/shutdown):
        ```log
        2025-05-01 13:52:55 0 [Note] InnoDB: End of log at LSN=126950431340130
        2025-05-01 13:52:55 0 [Note] InnoDB: Opened 8 undo tablespaces
        [...]
        2025-05-01 13:52:57 0 [Note] /usr/sbin/mariadbd: ready for connections.
        Version: '11.4.5-MariaDB-cll-lve-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
        [...]
        2025-05-01 13:55:00 0 [Note] /usr/sbin/mariadbd (initiated by: unknown): Normal shutdown
        [...]
        2025-05-01 13:55:01 0 [Note] /usr/sbin/mariadbd: Shutdown complete
        [...]
        2025-05-01 13:55:04 0 [Note] /usr/sbin/mariadbd: ready for connections.
        Version: '11.4.5-MariaDB-cll-lve-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
        2025-05-01 13:55:35 0 [Note] InnoDB: Buffer pool(s) load completed at 250501 13:55:35
        ```

      4. *`ibdata1` File Size Monitoring:*

      • File size checked periodically over 2-3 weeks.
      • Consistent size observed:
        ```bash
        ls -lh /var/lib/mysql/ibdata1
        rw-rw--- 1 [REDACTED_SYSTEM_USER] [REDACTED_SYSTEM_USER] 308G May 1 13:52 /var/lib/mysql/ibdata1
        ```
      • No decrease in size was observed during the monitoring period, despite attempts to optimize tables.

      5. *MariaDB Error Log Messages (During Observation):*

      • No specific errors related to InnoDB purge, file I/O, shutdown, or shrinking were observed in `/var/log/mariadb/mariadb.err` during the 2-3 week monitoring period in this isolated test environment.

      Attachments

        Issue Links

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              q9jason Quang Pham
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.