我有一个64 GB内存的MariaDB 10.5.18服务器,运行在企业级SSD上。
我正在使用一个相当通用的my.cnf来试图弄清楚为什么innodb_flush_method O_DIRECT对于SELECT (*)来说比fsync慢得多?
以下是my.cnf的内容:
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
open_files_limit = 50000
max_connections = 5000
max_connect_errors= 100000
key_buffer_size= 4096M
max_allowed_packet = 512M
bulk_insert_buffer_size= 2G
table_open_cache = 5000
connect_timeout= 30
skip-name-resolve = 1
query_cache_type= 0
tmp_table_size= 2G
max_heap_table_size= 2G
back_log= 10240
thread_cache_size= 400使用默认的innodb_flush_method (fsync),对包含6,400万行的表执行SELECT count(*) as count FROM ...需要62秒,+/- 1秒。这是非常一致的(我在测试期间重新启动服务器,并在大约10个其他表上进行类似的查询)。
然后,我将innodb_flush_method设置为O_DIRECT并重新启动服务器,并使用相同的方法测试相同的查询,需要228秒,+/- 3秒。
这是一个非常小的innodb_buffer_pool_size为128 MB,但结果基本上是相同的innodb_buffer_pool_size为16 GB。我们打算将其设置为更类似于40 GB,但我的理解是,由于操作系统中的双缓冲(因为所使用的内存数量将加倍,所以20 GB的innodb_buffer_pool_size将总共使用40 GB的RAM),我们不能使用fsync。
为什么fsync比O_DIRECT快4倍?
我们还需要对操作系统(CentOS 7.9.2009)做其他修改吗?
我的理解是,fsync将在操作系统中双缓冲并使用两倍的RAM,对吗?
文件系统是EXT4。它只是用“缺省值”挂载,所以可能会有一些优化。
CREATE TABLE `big_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`field1` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`field2` varchar(50) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`field3` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`field4` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `field1_field2` (`field1`,`field2`)
) ENGINE=InnoDB AUTO_INCREMENT=63799520 DEFAULT CHARSET=utf8 COLLATE=utf8_bin显示表状态
Name: big_table
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 54975738
Avg_row_length: 2710
Data_length: 149004746752
Max_data_length: 0
Index_length: 2314207232
Data_free: 5242880
Auto_increment: 63799520
Create_time: 2023-02-18 20:12:07
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N发布于 2023-02-21 23:41:42
(没有回答,但时间太长,无法发表评论。)
什么文件系统?ZFS,EXT等?里面有“日记”吗?
将innodb_buffer_pool_size设置为大约70%的内存(例如,40G)。128 m是一个可笑的小(和一个旧的默认)。
请提供SHOW TABLE STATUS ...和SHOW CREATE TABLE ...。(混淆的列名是可以的,但是数据类型和索引很重要。)
tmp_table_size和max_heap_table_size应在内存的1%以下。
其他查询是否执行某些写操作?SELECT COUNT(*)应该只执行读操作,除非缓存(buffer_pool)已满并需要写入。(我希望flush_method是不相干的。)
我很怀疑操作系统是否与buffer_pool缓存的内容一样多。
https://dba.stackexchange.com/questions/323877
复制相似问题