Databases Reference
In-Depth Information
which the data and log files were in a known state, and can be used for recovery). If the
last checkpoint falls too far behind the log sequence number, and the difference be-
comes close to the size of the log files, InnoDB will trigger “furious flushing,” which is
very bad for performance. Lines 7 and 8 show pending log operations and statistics,
which you can compare to values in the
FILE I/O
section to see how much of your I/O
is caused by your log subsystem relative to other causes of I/O.
BUFFER POOL AND MEMORY
This section shows statistics about InnoDB's buffer pool and how it uses memory:
1 ----------------------
2 BUFFER POOL AND MEMORY
3 ----------------------
4 Total memory allocated 4648979546; in additional pool allocated 16773888
5 Buffer pool size 262144
6 Free buffers 0
7 Database pages 258053
8 Modified db pages 37491
9 Pending reads 0
10 Pending writes: LRU 0, flush list 0, single page 0
11 Pages read 57973114, created 251137, written 10761167
12 9.79 reads/s, 0.31 creates/s, 6.00 writes/s
13 Buffer pool hit rate 999 / 1000
Line 4 shows the total memory allocated by InnoDB, and how much of that amount is
allocated in the additional memory pool. The additional memory pool is just a (typically
small) amount of memory it allocates when it wants to use its own internal memory
allocator. Modern versions of InnoDB typically use the operating system's memory
allocator, but older versions had their own allocator because some operating systems
didn't provide a very good implementation.
Lines 5 through 8 show buffer pool metrics, in units of pages. The metrics are the total
buffer pool size, the number of free pages, the number of pages allocated to store da-
tabase pages, and the number of “dirty” database pages. InnoDB uses some pages in
the buffer pool for lock indexes, the adaptive hash index, and other system structures,
so the number of database pages in the pool will never equal the total pool size.
Lines 9 and 10 show the number of pending reads and writes (i.e., the number of logical
reads and writes InnoDB needs to do for the buffer pool). These values will not match
values in the
FILE I/O
section, because InnoDB might merge many logical operations
into a single physical I/O operation. LRU stands for “least recently used”; it's a method
of freeing space for frequently used pages by flushing infrequently used ones from the
buffer pool. The flush list holds old pages that need to be flushed by the checkpoint
process, and single page writes are independent page writes that won't be merged.
Line 8 in this output shows that the buffer pool contains 37,491 dirty pages, which
need to be flushed to disk at some point (they have been modified in memory but not
on disk). However, line 10 shows that no flushes are scheduled at the moment. This is
