[MDEV-23072] Diskspace not reused for Blob in data file Created: 2020-07-02  Updated: 2020-10-20  Resolved: 2020-10-20

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete, Storage Engine - InnoDB
Affects Version/s: 10.3.12, 10.4.13, 10.5.4
Fix Version/s: 10.3.26, 10.4.16, 10.5.7

Type: Bug Priority: Major
Reporter: Hamud Al Hammoud Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: None
Environment:

Windows 10
ubuntu 10.4

Issue Links:
Problem/Incident
is caused by MDEV-11369 Instant add column for InnoDB Closed

 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

 Comments   
Comment by Marko Mäkelä [ 2020-08-05 ]

I think that this could share a common root cause with MDEV-13013.

Comment by Thirunarayanan Balathandayuthapani [ 2020-10-15 ]

Patch is in bb-10.3-MDEV-23072

Comment by Marko Mäkelä [ 2020-10-19 ]

Thank you, it looks like this was indeed caused by my MDEV-11369.

I would suggest to simplify the code a bit more, to remove the local variable for no_compress_needed and to reorganize the code like this:

if (rec_offs_any_extern(offsets) || !btr_cur_can_delete_without_compress(
 
			cursor, rec_offs_size(offsets), mtr)) {
 
		/* prefetch siblings of the leaf for the pessimistic
 
		operation. */
 
		btr_cur_prefetch_siblings(block);
 
} else if (UNIV_UNLIKELY(block->page.id.page_no() == cursor->index->page
 
			  && page_get_n_recs(block->frame) == 1
 
			  + (cursor->index->is_instant()
 
			     && !rec_is_metadata(rec, cursor->index)))) {
 
…
 
		btr_page_empty(block, buf_block_get_page_zip(block),
 
			       index, 0, mtr);
 
…
 
} else {
 
…
 
}

That is, compute !no_compress_needed in the first if, and avoid the added code if it holds.

Generated at Thu Feb 08 09:19:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.