Databases Reference
In-Depth Information
Read thread
Performs read-ahead operations to try to prefetch data InnoDB predicts it will need
Write thread
Flushes dirty buffers
INSERT BUFFER AND ADAPTIVE HASH INDEX
This section shows the status of these two structures inside InnoDB:
1 -------------------------------------
2 INSERT BUFFER AND ADAPTIVE HASH INDEX
3 -------------------------------------
4 Ibuf for space 0: size 1, free list len 887, seg size 889, is not empty
5 Ibuf for space 0: size 1, free list len 887, seg size 889,
6 2431891 inserts, 2672643 merged recs, 1059730 merges
7 Hash table size 8850487, used cells 2381348, node heap has 4091 buffer(s)
8 2208.17 hash searches/s, 175.05 non-hash searches/s
Line 4 shows information about the insert buffer's size, the length of its “free list,” and
its segment size. The text “for space 0” seems to indicate the possibility of multiple
insert buffers—one per tablespace—but that was never implemented, and this text has
been removed in more recent MySQL versions. There's only one insert buffer, so line
5 is really redundant. Line 6 shows statistics about how many buffer operations
InnoDB has done. The ratio of merges to inserts gives a good idea of how efficient the
buffer is.
Line 7 shows the adaptive hash index's status. Line 8 shows how many hash index
operations InnoDB has done over the time frame mentioned in the header section. The
ratio of hash index lookups to non-hash index lookups is advisory information; you
can't configure the adaptive hash index.
LOG
This section shows statistics about InnoDB's transaction log (redo log) subsystem:
1 ---
2 LOG
3 ---
4 Log sequence number 84 3000620880
5 Log flushed up to 84 3000611265
6 Last checkpoint at 84 2939889199
7 0 pending log writes, 0 pending chkp writes
8 14073669 log i/o's done, 10.90 log i/o's/second
Line 4 shows the current log sequence number, and line 5 shows the point up to which
the logs have been flushed. The log sequence number is just the number of bytes written
to the log files, so you can use it to calculate how much data in the log buffer has not
yet been flushed to the log files. In this case, it is 9,615 bytes (13000620880-
13000611265). Line 6 shows the last checkpoint (a checkpoint identifies an instant at
