innoDB缓冲池参数
来源:互联网 发布:centos 指定ip访问ssh 编辑:程序博客网 时间:2024/05/01 21:37
innodb_buffer_pool_size
如 果用Innodb,那么这是一个重要变量。相对于MyISAM来说,Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的key_buffer_size也还好,但Innodb在大数据量时用默认值就感觉在爬了。 Innodb的缓冲池会缓存数据和索引,所以不需要给系统的缓存留空间,如果只用Innodb,可以把这个值设为内存的70%-80%。和 key_buffer相同,如果数据量比较小也不怎么增加,那么不要把这个值设太高也可以提高内存的使用率。
innodb_additional_pool_size
这个的效果不是很明显,至少是当操作系统能合理分配内存时。但你可能仍需要设成20M或更多一点以看Innodb会分配多少内存做其他用途。
innodb_log_file_size
对于写很多尤其是大数据量时非常重要。要注意,大的文件提供更高的性能,但数据库恢复时会用更多的时间。我一般用64M-512M,具体取决于服务器的空间。
innodb_log_buffer_size
默认值对于多数中等写操作和事务短的运用都是可以的。如 果经常做更新或者使用了很多blob数据,应该增大这个值。但太大了也是浪费内存,因为1秒钟总会 flush(这个词的中文怎么说呢?)一次,所以不需要设到超过1秒的需求。8M-16M一般应该够了。小的运用可以设更小一点。
innodb_flush_log_at_trx_commit (这个很管用)
抱怨Innodb比MyISAM慢 100倍?那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘,这是很费时的。特别是使用电 池供电缓存(Battery backed up cache)时。设成2对于很多运用,特别是从MyISAM表转过来的是可以的,它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘,所以你一般不会丢失超过1-2秒的更新。设成0会更快一点,但安全方面比较差,即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。 innodb_flush_log_at_trx_commit
If the value of innodb_flush_log_at_trx_commit
is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1, the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.
The default value of this variable is 1 (prior to MySQL 4.0.13, the default is 0).
A value of 1 is required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any mysqldprocess crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB
's crash recovery is not affected and thus crash recovery does work regardless of the value.
For the greatest possible durability and consistency in a replication setup using InnoDB
with transactions, use innodb_flush_log_at_trx_commit=1
, sync_binlog=1
, and innodb-safe-binlog
in your master server my.cnf
file.
Many operating systems and some disk hardware fool the flush-to-disk operation. They may tellmysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt theInnoDB
database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix commandhdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.
- innoDB缓冲池参数
- innodb缓冲池
- innoDB源码分析--缓冲池
- innoDB源码阅读笔记--缓冲池
- InnoDB源码分析--缓冲池(二)
- InnoDB源码分析--缓冲池(三)
- InnoDB源码分析--缓冲池(三)
- Mysql的innodb缓冲池管理
- InnoDB缓冲池命中率(书摘备查)
- InnoDB双写缓冲
- innodb重做日志缓冲
- Mysql的Innodb存储引擎缓冲池个人理解
- MySQL Dumping and Reloading the InnoDB Buffer Pool(MySQLdump和重载InnoDB缓冲池)
- InnoDB缓冲池预加载在MySQL 5.7中的正确打开方式
- InnoDB存储引擎——插入缓冲
- InnoDB Insert Buffer(插入缓冲)
- Mysql 配置参数 Innodb
- [MySQL] Innodb参数优化
- redis中key的操作和数据库的选择
- From C++ to Objective-C: A quick guide for practical programmers
- 彻底解决_OBJC_CLASS_$_某文件名", referenced from:问题
- DSOJ Addition of Polynomial(多项式求和)
- 各开发者平台需要的android应用签名
- innoDB缓冲池参数
- iOS Programming 101: Record and Play Audio using AVFoundation Framework
- DSOJ Multiplication of large integer(大数乘法)
- shell字符串处理
- DSOJ Kth Number(第k大的数)
- 正则表达式
- 我的springmvc简单版
- udp
- 设计模式之模板方法模式