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

InnoDB system tablespace cannot be shrunk

    XMLWordPrintable

Details

    Description

      hello
      i used to have all tables on ibdata1 before, then i switched to one file per table, but the ibdata1 file keep its size.
      I recently switched to mariadb 10.1, and hoped that the defragmenting function would help to shrink the file, but it didn't worked.
      I optimised all tables, but the ibdata1 file did not changed at all, it is still 197gb

      MariaDB [(none)]> show status like '%defragment%';
      		 
      +----------------------------------------+--------+
      		 
      | Variable_name                          | Value  |
      		 
      +----------------------------------------+--------+
      		 
      | Innodb_defragment_compression_failures | 0      |
      		 
      | Innodb_defragment_failures             | 32872  |
      		 
      | Innodb_defragment_count                | 113744 |
      		 
      +----------------------------------------+--------+
      		 
      3 rows in set (0.01 sec)


      MariaDB [(none)]> show variables like '%defragment%';
      		 
      +--------------------------------------+----------+
      		 
      | Variable_name                        | Value    |
      		 
      +--------------------------------------+----------+
      		 
      | innodb_defragment                    | ON       |
      		 
      | innodb_defragment_fill_factor        | 0.900000 |
      		 
      | innodb_defragment_fill_factor_n_recs | 20       |
      		 
      | innodb_defragment_frequency          | 40       |
      		 
      | innodb_defragment_n_pages            | 7        |
      		 
      | innodb_defragment_stats_accuracy     | 0        |
      		 
      +--------------------------------------+----------+

      Attachments

        Issue Links

          blocks

          New Feature - A new feature of the product, which has yet to be developed. MDEV-28699 Shrink temporary tablespaces without restart

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32452 InnoDB system tablespace is not shrunk on slow shutdown

          • Major - Major loss of function.
          • Closed
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34216 Possible corruption when shrinking the system tablespace on innodb_fast_shutdown=0

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31373 innodb_undo_log_truncate=ON recovery results in a corrupted undo log

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is duplicated by

          Task - A task that needs to be done. MDEV-31462 ibdata1 shrinking

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-19229 Allow innodb_undo_tablespaces to be changed after database creation

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-28699 Shrink temporary tablespaces without restart

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-29983 Deprecate innodb_file_per_table

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29999 innodb_undo_log_truncate=ON is not crash safe

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33112 innodb_undo_log_truncate=ON is blocking page writes

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35398 Improve shrinking of system tablespace

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35689 InnoDB system tables cannot be optimized or defragmented

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36722 MariaDB 11.4.5: ibdata1 autoshrink feature does not reduce file size

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21952 ibdata1 file size growing in MariaDB

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-29986 Set innodb_undo_tablespaces=3 by default

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32622 [mariadb-galera] The undo log is very large, how can there be fewer undo log logs

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34529 Shrink the system tablespace when system tablespace contains MDEV-30671 leaked undo pages

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              martina342 martina342
              Votes:
              2 Vote for this issue
              Watchers:
              15 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: