The InnoDB Buffer Pool

The InnoDB Buffer Pool

2009年10月26日 13:18

If you use mostly InnoDB tables, the InnoDB buffer pool probably needs more memory than anything else. Unlike the MyISAM key cache, the InnoDB buffer pool doesn’t just cache indexes: it also holds row data, the adaptive hash index ,the insert buffer, locks, and other internal structures.---buffer pool缓存了数据和索引,自适应的hash索引,插入缓冲缓存,锁和其他一些内部结构. InnoDB also uses the buffer pool to help it delay writes, so it can merge many writes together and perform them sequentially. In short, InnoDB relies heavily on the buffer pool, and you should be sure to allocate enough memory to it. The MySQL manual suggests using up to 80% of the machine’s physical memory for the buffer pool on a dedicated server; in reality, you can use more than that if the machine has a lot of memory. As with the MyISAM key buffers, you can use variables from SHOW commands or tools such as innotop to monitor your InnoDB buffer pool’s memory usage and performance.

There’s no equivalent of LOAD INDEX INTO CACHE for InnoDB tables. However, if you’re trying to warm up a server and get it ready to handle a heavy load, you can issue queries that perform full table scans or full index scans.---通过表或者索引扫描来预热innodb的buffer pool. In most cases, you should make the InnoDB buffer pool as large as your available memory allows. However, in rare circumstances, very large buffer pools (say, 50 GB) can cause long stalls. For example, a large buffer pool may become slow during checkpoints or insert buffer merge operations, and concurrency can drop as a result of locking. If you experience these problems, you may have to reduce the buffer pool size.
  

You can change the innodb_max_dirty_pages_pct variable to instruct InnoDB to keep more or fewer dirty (modified) pages in the buffer pool. If you allow a lot of dirty pages, InnoDB can take a long time to shut down, because it writes the dirty pages to the data files upon shutdown. You can force it to shut down quickly, but then it just has to do more recovery when it restarts, so you can’t actually speed up the shutdown and restart cycle time. If you know in advance when you need to shut down, you can set the variable to a lower value, wait for the flush thread to clean up the buffer pool, and then shut down once the number of dirty pages becomes small. You can monitor the number of dirty pages by watching the Innodb_buffer_pool_pages_dirty server status variable or using innotop to monitor SHOW INNODB STATUS. ----在关机前,innodb会刷新脏数据到磁盘.为了使停机的时间最短,可以给innodb_max_dirty_pages_pct一个较小的值.

Lowering the value of the innodb_max_dirty_pages_pct variable doesn’t actually guarantee that InnoDB will keep fewer dirty pages in the buffer pool. Instead, it controls the threshold at which InnoDB stops being “lazy.” InnoDB’s default behavior is to flush dirty pages with a background thread, merging writes together and performing them sequentially for efficiency. This behavior is called “lazy” because it lets InnoDB delay flushing dirty pages in the buffer pool, unless it needs to use the space for some other data. When the percentage of dirty pages exceeds the threshold, InnoDB will flush pages as quickly as it can to try to keep the dirty page count lower. The variable’s default value is 90, so by default InnoDB will flush lazily until the buffer pool is 90% full of dirty pages.You can tweak the threshold for your workload if you wish to spread out the writes a bit more. ----大多数情况下,都是lazy的.除非脏数据达到阈值,flush进程才会尽快的刷新

For example, lowering it to 50 will generally cause InnoDB to do more write operations, because it will flush pages sooner and therefore be unable to batch the writes as well. However, if your workload has a lot of write spikes, using a lower value may help InnoDB absorb the spikes better: it will have more “spare” memory to hold dirty pages, so it won’t have to wait for other dirty pages to be flushed to disk. ----如果写高峰较多,可以把pct设置的小一些,随时留出空间容纳新的脏数据,否则需要等待flush后,腾出空间.