/ 2019-06-06
10、InnoDB存储引擎核心特性-参数补充
show engines;
show variables like 'default_storage_engine';
select @@default_storage_engine;
(1) 通过参数设置默认引擎
(2) 建表的时候进行设置
(3) alter table t1 engine=innodb;
innodb_data_file_path
一般是在初始化数据之前就设置好
例子:
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
show variables like 'innodb_file_per_table';
select @@innodb_buffer_pool_size;
show engine innodb status\G
innodb_buffer_pool_size
一般建议最多是物理内存的 75-80%
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点,取值分别为0、1、2三个。
select @@innodb_flush_log_at_trx_commit;
1,每次事物的提交都会引起日志文件写入、flush磁盘的操作,确保了事务的ACID;flush 到操作系统的文件系统缓存 fsync到物理磁盘.
0,表示当事务提交时,不做日志写入操作,而是每秒钟将log buffer中的数据写入文件系统缓存并且秒fsync磁盘一次;
2,每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作。
--------
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
-------
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method
控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存
show variables like '%innodb_flush%';
10.5.3 参数值说明
O_DIRECT :数据缓冲区写磁盘,不走OS buffer
fsync :日志和数据缓冲区写磁盘,都走OS buffer
O_DSYNC :日志缓冲区写磁盘,不走 OS buffer
10.5.4 使用建议
最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT
最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync
innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_files_in_group = 3
更多内容,请关注微信公众号:oldboyedu
(0)