Databases Reference
In-Depth Information
The InnoDB Buffer Pool
If you use mostly InnoDB tables, the InnoDB buffer pool probably needs more memory
than anything else. 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.
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, typically with a process
such as that shown earlier in this chapter. You can use variables from
SHOW
commands
or tools such as
innotop
to monitor your InnoDB buffer pool's memory usage.
If you don't have much data, and you know that your data won't grow quickly, you
don't need to overallocate memory to the buffer pool. It's not really beneficial to make
it much larger than the size of the tables and indexes that it will hold. There's nothing
wrong with planning ahead for a rapidly growing database, of course, but sometimes
we see huge buffer pools with a tiny amount of data. This isn't necessary.
Large buffer pools come with some challenges, such as long shutdown and warmup
times. If there are a lot of dirty (modified) pages in the buffer pool InnoDB can take a
long time to shut down, because it writes the dirty pages to the data files upon shut-
down. 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 change the
innodb_
max_dirty_pages_pct
variable at runtime 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
.
Lowering the value of the
innodb_max_dirty_pages_pct
variable doesn't actually guar-
antee 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. InnoDB will
also go into “furious flushing” mode when there isn't enough space left in the trans-
action logs, which is one reason that large logs can improve performance.
When you have a large buffer pool, especially in combination with slow disks, the server
might take a long time (many hours or even days) to warm up after a restart. In such
cases, you might benefit from using Percona Server's feature to reload the pages after
restart. This can reduce warmup times to a few minutes. MySQL 5.6 will introduce a
similar feature. This is especially beneficial on replicas, which pay an extra warmup
penalty due to the single-threaded nature of replication.
