[MDEV-32268] GNU libc posix_fallocate() may be extremely slow - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3, 11.3.2, 11.4.1
Component/s: Storage Engine - InnoDB
Labels:
None

Description

After the "fix" for ~~MDEV-11687~~ we always use posix_fallocate() in the os_file_set_size (see https://github.com/MariaDB/server/blob/b0763f509a3f120e882cee2810495d169ff344a4/storage/innobase/os/os0file.cc#L4926):

...

# ifdef HAVE_POSIX_FALLOCATE

	int err;

	do {

		if (fstat(file, &statbuf)) {

			err = errno;

		} else {

			os_offset_t current_size = statbuf.st_size;

			if (current_size >= size) {

				return true;

			current_size &= ~4095ULL;

			err = posix_fallocate(file, current_size,

					      size - current_size);

	} while (err == EINTR

		 && srv_shutdown_state <= SRV_SHUTDOWN_INITIATED);

...

while in some cases (like ALTER TABLE ... ALGORITHM=COPY for COMPRESSED table and datadir on a NAS/NFS mount) using alternative allocation method provides performance benefits.

Consider the following test case on 10.1 that proves the point to some extent on a local NFS mount on current Ubuntu 22.04:

openxs@ao756:~/dbs/maria10.1/mysql-test$ ./mtr --mysqld="--innodb_file_format=Barracuda" --mysqld="--innodb_buffer_pool_size=32M" --mysqld="--innodb_use_fallocate=1" innodb.slow_alter

Logging: ./mtr  --mysqld=--innodb_file_format=Barracuda --mysqld=--innodb_buffer_pool_size=32M --mysqld=--innodb_use_fallocate=1 innodb.slow_alter

vardir: /home/openxs/dbs/maria10.1/mysql-test/var

Checking leftover processes...

Removing old var directory...

 - WARNING: Using the 'mysql-test/var' symlink

Creating var directory '/home/openxs/dbs/maria10.1/mysql-test/var'...

Checking supported features...

MariaDB Version 10.1.49-MariaDB

 - SSL connections supported

Sphinx 'indexer' binary not found, sphinx suite will be skipped

Collecting tests...

Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT

--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019

set default_storage_engine=innodb;

show global variables like '%fallocate'||

Variable_name	Value

innodb_use_fallocate	ON

create table tbig(id int auto_increment primary key, c1 char(100)) ROW_FORMAT=COMPRESSED;

insert into tbig(c1) values (repeat('a', 100));

select @now := now(6)||

@now := now(6)

2023-09-27 13:31:58.256905

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

select timestampdiff(microsecond, @now, now(6))||

timestampdiff(microsecond, @now, now(6))

86978103

show table status like 'tbig'||

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment

tbig	InnoDB	10	Compressed	1048731	60	63258624	0	0	3145728	1376221	2023-09-27 13:31:57	NULL	NULL	latin1_swedish_ci	NULL	row_format=COMPRESSED

set session profiling = ON||

select count(*) from tbig||

count(*)

alter table tbig add column c2 char(200) default 'b'||

set session profiling = OFF||

show profiles||

Query_ID	Duration	Query

1	1.69141316	select count(*) from tbig

2	385.22975249	alter table tbig add column c2 char(200) default 'b'

drop table tbig||

innodb.slow_alter 'xtradb'               [ pass ]  474460

--------------------------------------------------------------------------

The servers were restarted 0 times

Spent 474.460 of 562 seconds executing testcases

Completed: All 1 tests were successful.

openxs@ao756:~/dbs/maria10.1/mysql-test$ ./mtr --mysqld="--innodb_file_format=Barracuda" --mysqld="--innodb_buffer_pool_size=32M" --mysqld="--innodb_use_fallocate=0" innodb.slow_alter

Logging: ./mtr  --mysqld=--innodb_file_format=Barracuda --mysqld=--innodb_buffer_pool_size=32M --mysqld=--innodb_use_fallocate=0 innodb.slow_alter

vardir: /home/openxs/dbs/maria10.1/mysql-test/var

Checking leftover processes...

Removing old var directory...

 - WARNING: Using the 'mysql-test/var' symlink

Creating var directory '/home/openxs/dbs/maria10.1/mysql-test/var'...

Checking supported features...

MariaDB Version 10.1.49-MariaDB

 - SSL connections supported

Sphinx 'indexer' binary not found, sphinx suite will be skipped

Collecting tests...

Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT

--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019

set default_storage_engine=innodb;

show global variables like '%fallocate'||

Variable_name	Value

innodb_use_fallocate	OFF

create table tbig(id int auto_increment primary key, c1 char(100)) ROW_FORMAT=COMPRESSED;

insert into tbig(c1) values (repeat('a', 100));

select @now := now(6)||

@now := now(6)

2023-09-27 13:46:34.280800

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

insert into tbig(c1) select c1 from tbig||

select timestampdiff(microsecond, @now, now(6))||

timestampdiff(microsecond, @now, now(6))

86275891

show table status like 'tbig'||

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment

tbig	InnoDB	10	Compressed	1048681	59	62734336	0	0	3145728	1376221	2023-09-27 13:46:33	NULL	NULL	latin1_swedish_ci	NULL	row_format=COMPRESSED

set session profiling = ON||

select count(*) from tbig||

count(*)

alter table tbig add column c2 char(200) default 'b'||

set session profiling = OFF||

show profiles||

Query_ID	Duration	Query

1	1.91823956	select count(*) from tbig

2	377.63216720	alter table tbig add column c2 char(200) default 'b'

drop table tbig||

innodb.slow_alter 'xtradb'               [ pass ]  466519

--------------------------------------------------------------------------

The servers were restarted 0 times

Spent 466.519 of 555 seconds executing testcases

Completed: All 1 tests were successful.

openxs@ao756:~/dbs/maria10.1/mysql-test$ ls -l | grep var

drwxrwxr-x  8 openxs openxs   4096 вер 27 10:56 oldvar

lrwxrwxrwx  1 openxs openxs      4 вер 27 12:09 var -> /mnt

openxs@ao756:~/dbs/maria10.1/mysql-test$ mount | grep /mnt

127.0.0.1:/home/openxs/share on /mnt type nfs4 (rw,relatime,vers=4.2,rsize=524288,wsize=524288,namlen=255,hard,proto=tcp,timeo=600,retrans=2,sec=sys,clientaddr=127.0.0.1,local_lock=none,addr=127.0.0.1)

Here we have 378 seconds to ALTER the table that is twice as large as the buffer pool with innodb_use_fallocate = OFF vs 385 where it is ON. I expect bigger difference when table size increases, but wanted to keep the test fast enough to be pracical on my slow HDD.

Users reported much more serious impact (that prevents the use of 10.4+ for table terabytes in size) and upgrade from pre-10.2.4 versions.

Attachments

Issue Links

relates to

MDEV-11687 innodb_use_fallocate has no effect

Closed

Activity

Transition	Time In Source Status	Execution Times

Marko Mäkelä made transition - 2023-12-19 08:11

Open

Needs Feedback

82d 20h 3m

2

Valerii Kravchuk made transition - 2023-12-19 16:44

Needs Feedback

Open

8h 39m

2

Marko Mäkelä made transition - 2024-01-12 12:54

Open

In Progress

23d 20h 10m

1

Marko Mäkelä made transition - 2024-01-18 11:02

In Progress

Closed

5d 22h 7m

1

MariaDB Server

GNU libc posix_fallocate() may be extremely slow

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration