Databases Reference
In-Depth Information
is waiting. You can find that in the
INFORMATION_SCHEMA
tables in MySQL 5.1 and newer,
if you're using the InnoDB plugin.
If there are many transactions, InnoDB might limit the number it prints to try to keep
the output from growing too large. You'll see “ ...truncated... ” if this happens.
FILE I/O
The
FILE I/O
section shows the state of the I/O helper threads, along with performance
counters:
1 --------
2 FILE I/O
3 --------
4 I/O thread 0 state: waiting for i/o request (insert buffer thread)
5 I/O thread 1 state: waiting for i/o request (log thread)
6 I/O thread 2 state: waiting for i/o request (read thread)
7 I/O thread 3 state: waiting for i/o request (write thread)
8 Pending normal aio reads: 0, aio writes: 0,
9 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
10 Pending flushes (fsync) log: 0; buffer pool: 0
11 17909940 OS file reads, 22088963 OS file writes, 1743764 OS fsyncs
12 0.20 reads/s, 16384 avg bytes/read, 5.00 writes/s, 0.80 fsyncs/s
Lines 4 through 7 show the I/O helper thread states. Lines 8 through 10 show the
number of pending operations for each helper thread, and the number of pending
fsync()
operations for the log and buffer pool threads. The abbreviation “aio” means
“asynchronous I/O.” Line 11 shows the number of reads, writes, and
fsync()
calls
performed. Absolute values will vary with your workload, so it's more important to
monitor how they change over time. Line 12 shows per-second averages over the time
interval shown in the header section.
The pending values on lines 8 and 9 are good ways to detect an I/O-bound application.
If most of these types of I/O have some pending operations, the workload is probably
I/O-bound.
On Windows, you can adjust the number of I/O helper threads with the
innodb
_file_io_threads
configuration variable, so you might see more than one read and
write thread. And in MySQL 5.1 and newer with the InnoDB plugin, or with Percona
Server, you can use
innodb_read_io_threads
and
innodb_write_io_threads
to configure
multiple threads for reading and writing. However, you'll always see at least these four
threads on all platforms:
Insert buffer thread
Responsible for insert buffer merges (i.e., records being merged from the insert
buffer into the tablespace)
Log thread
Responsible for asynchronous log flushes
