Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3.12, 10.4.13, 10.5.4
Fix Version/s: 10.3.26, 10.4.16, 10.5.7
Component/s: Data Manipulation - Delete, Storage Engine - InnoDB
Labels:
None
Environment:
Windows 10
ubuntu 10.4

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

is caused by

MDEV-11369 Instant add column for InnoDB

Closed

Diskspace not reused for Blob in data file

Details

Description

MariaDB 10.1.45

Attachments

Issue Links

Activity

People

Dates

Git Integration