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

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä added a comment - 2020-08-05 13:48

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

Marko Mäkelä added a comment - 2020-08-05 13:48 I think that this could share a common root cause with MDEV-13013 .

Thirunarayanan Balathandayuthapani added a comment - 2020-10-15 07:57

Patch is in bb-10.3-MDEV-23072

Thirunarayanan Balathandayuthapani added a comment - 2020-10-15 07:57 Patch is in bb-10.3- MDEV-23072

Marko Mäkelä added a comment - 2020-10-19 09:11

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.

Marko Mäkelä added a comment - 2020-10-19 09:11 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.

MariaDB Server

Diskspace not reused for Blob in data file

Details

Description

MariaDB 10.1.45

Attachments

Issue Links

Activity

People

Dates

Git Integration