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

Diskspace not reused for Blob in data file

    XMLWordPrintable

    Details

      Description

      We migrated our application from Mariadb 10.1 to 10.3. After a while, the disk was full. We noticed that the disk usage for a table is extremly high considering there are very few if any rows in it. This Table contains rows with large blobs which are deleted after a short time.

      This can be reproduced with a simple Test. I tested it with Version 10.3.12, 10.4.13 and 10.5.4.
      Below is the output from 10.1.45 where the Problem doesn‘t exists.

      CREATE DATABASE blob_test;
      USE blob_test;
      CREATE TABLE `my_sample_table` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(128) NOT NULL, `some_data` longblob, `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
       
      DELIMITER //
       
      FOR i IN 1..1000
      DO
            INSERT INTO my_sample_table (id, name, some_data) VALUES (1,'data1', REPEAT('A',100000));
            COMMIT;
            DELETE FROM my_sample_table WHERE ID = 1;
            COMMIT;
      END FOR;
      //
      
      

      Even if you wait and do more tests with the same table, it will continue to grow.

      File Size

      C:\Program Files\MariaDB 10.5\data\blob_test>dir
      Datenträger in Laufwerk C: ist System
      Volumeseriennummer: 8219-F31C

      Verzeichnis von C:\Program Files\MariaDB 10.5\data\blob_test

      02.07.2020 11:52 <DIR> .
      02.07.2020 11:52 <DIR> ..
      02.07.2020 11:52 61 db.opt
      02.07.2020 11:52 1.402 my_sample_table.frm
      02.07.2020 12:27 343.932.928 my_sample_table.ibd
      3 Datei(en), 343.934.391 Bytes

      This ist the output from the innodb checksum

      innodb_check_sum

      C:\Program Files\MariaDB 10.5\data\blob_test>..\..\bin\innochecksum.exe -v -S my_sample_table.ibd

      Variables (--variable-name=value)
      and boolean options

      Unknown macro: {FALSE|TRUE}

      Value (after reading options)
      --------------------------------- ----------------------------------------
      verbose TRUE
      count FALSE
      start-page 0
      end-page 0
      page 0
      strict-check crc32
      no-check FALSE
      allow-mismatches 0
      write crc32
      page-type-summary TRUE
      page-type-dump (No default value)
      per-page-details FALSE
      log (No default value)
      leaf FALSE
      merge 0

      File::my_sample_table.ibd
      ================PAGE TYPE SUMMARY==============
      #PAGE_COUNT PAGE_TYPE
      ===============================================
      1 Index page
      0 Undo log page
      1 Inode page
      0 Insert buffer free list page
      139 Freshly allocated page
      1 Insert buffer bitmap
      0 System page
      0 Transaction system page
      1 File Space Header
      0 Extent descriptor page
      13937 BLOB page
      0 Compressed BLOB page
      0 Page compressed page
      0 Page compressed encrypted page
      0 Other type of page

      ===============================================
      Additional information:
      Undo page type: 0 insert, 0 update, 0 other
      Undo page state: 0 active, 0 cached, 0 to_free, 0 to_purge, 0 prepared, 0 other
      index_id #pages #leaf_pages #recs_per_page #bytes_per_page
      43 1 1 0 0

      index_id page_data_bytes_histgram(empty,...,oversized)
      43 1 0 0 0 0 0 0 0 0 0 0 0

      my.ini

      [mysqld]
      datadir=C:/Program Files/MariaDB 10.5/data
      port=3306
      innodb_buffer_pool_size=128M
      character-set-server=utf8
      [client]
      port=3306
      plugin-dir=C:/Program Files/MariaDB 10.5/lib/plugin

      MariaDB 10.1.45

      The Testcase with Mariadb 10.1 is the same but with an unrolled For loop.
      The File is never big:

      File size

      C:\Program Files\MariaDB 10.1\data\blob_test>dir
      Datenträger in Laufwerk C: ist System
      Volumeseriennummer: 8219-F31C

      Verzeichnis von C:\Program Files\MariaDB 10.1\data\blob_test

      02.07.2020 12:25 <DIR> .
      02.07.2020 12:25 <DIR> ..
      02.07.2020 12:25 61 db.opt
      02.07.2020 12:25 1.891 my_sample_table.frm
      02.07.2020 12:51 229.376 my_sample_table.ibd
      3 Datei(en), 231.328 Bytes

      my.ini

      C:\Program Files\MariaDB 10.1\data\blob_test>..\..\bin\innochecksum.exe -v -S my_sample_table.ibd

      Variables (--variable-name=value)
      and boolean options

      Unknown macro: {FALSE|TRUE}

      Value (after reading options)
      --------------------------------- ----------------------------------------
      verbose TRUE
      count FALSE
      start-page 0
      end-page 0
      page 0
      strict-check crc32
      no-check FALSE
      allow-mismatches 0
      write crc32
      page-type-summary TRUE
      page-type-dump (No default value)
      per-page-details FALSE
      log (No default value)
      leaf FALSE
      merge 0

      File::my_sample_table.ibd
      ================PAGE TYPE SUMMARY==============
      #PAGE_COUNT PAGE_TYPE
      ===============================================
      2 Index page
      0 Undo log page
      1 Inode page
      0 Insert buffer free list page
      2 Freshly allocated page
      1 Insert buffer bitmap
      0 System page
      0 Transaction system page
      1 File Space Header
      0 Extent descriptor page
      7 BLOB page
      0 Compressed BLOB page
      0 Page compressed page
      0 Page compressed encrypted page
      0 Other type of page

      ===============================================
      Additional information:
      Undo page type: 0 insert, 0 update, 0 other
      Undo page state: 0 active, 0 cached, 0 to_free, 0 to_purge, 0 prepared, 0 other
      index_id #pages #leaf_pages #recs_per_page #bytes_per_page
      20 1 1 0 0
      21 1 1 0 0

      index_id page_data_bytes_histgram(empty,...,oversized)
      20 1 0 0 0 0 0 0 0 0 0 0 0
      21 1 0 0 0 0 0 0 0 0 0 0 0

      my.ini

      [mysqld]
      datadir=C:/Program Files/MariaDB 10.1/data
      port=3307
      sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
      default_storage_engine=innodb
      innodb_buffer_pool_size=128M
      innodb_log_file_size=50M
      character-set-server=utf8
      [client]
      port=3307
      plugin-dir=C:/Program Files/MariaDB 10.1/lib/plugin

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              thiru Thirunarayanan Balathandayuthapani
              Reporter:
              alhammoud Hamud Al Hammoud
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: