High Performance MySQL [Electronic resources] نسخه متنی

A.2 SHOW INNODB STATUS

Chapter 1, the SHOW INNODB STATUS
command produces detailed statistics about what's
going on inside the InnoDB storage engine (far more detailed than
anything in MyISAM). A detailed understanding of all the statistics
InnoDB provides is beyond the scope of what most database
administrators will ever need. Much of the information InnoDB
presents is useful only in rare and very specific diagnostic
activities, so we'll keep the discussion fairly
basic here and focus on the more commonly used values.

Sample output from SHOW INNODB STATUS command is
included at the end of this section. The output is broken up into
several labeled groups. For most day to day use, the most informative
sections are Transactions, Buffer Pool and Memory, and Row
Operations.

Semaphores

This section details the various locks used inside InnoDB. Higher
values here generally indicate a busy server with frequent contention
inside InnoDB. They are cumulative statistics, however, so the longer
your server has been up, the higher you can expect them to be.

Transactions

Each of the active or pending transactions is listed in this section.
For each, InnoDB lists the MySQL thread ID as well as the IP address
and MySQL username responsible for initiating the transaction. You
may see indications of transactions waiting on locks here. If so,
there's a good chance your application is
encountering deadlocks.

File I/O

Here InnoDB lists the state of each file I/O thread and provides
counts of other I/O-related activity.

Insert Buffer and Adaptive Hash Index

When records are added to InnoDB, they are first put into the insert
buffer. From there InnoDB merges records into the tablespace. This
section provides a few metrics generated during those operations.

Log

The transaction log statistics are presented here, including the
current sequence number and the highest sequence numbers from the
most recent log flush and checkpoint operations. InnoDB also provides
average values for the number of log-related I/O operations per
second.

Buffer Pool and Memory

This section tells you how well InnoDB is using the memory
you've given it via the
innodb_buffer_pool setting. The
"buffer pool size" and
"free buffers" values give you an
idea of how much of that memory is in use. InnoDB also provides
read/create/write-per-second statistics that indicate how quickly the
database pages are changing.

Row Operations

Here you'll find some very useful high-level numbers
that track the frequency of INSERTs,
UPDATEs, DELETEs, and
SELECTs as well as counting the number of rows
affected by each.

Here's some sample output from a
SHOW INNODB
STATUS command:

mysql> SHOW INNODB STATUS \G
*************************** 1. row ***************************
Status:
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 
= = = = = = =
031218  8:29:53 INNODB MONITOR OUTPUT
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 
= = = = = = =
Per second averages calculated from the last 3 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5, signal count 5
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 6, OS waits 3; RW-excl spins 2, OS waits 2
------------
TRANSACTIONS
------------
Trx id counter 0 1039616
Purge done for trx's n:o < 0 454662 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 49162
MySQL thread id 16, query id 112 216.145.52.107 jzawodn
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
155 OS file reads, 4 OS file writes, 4 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 314, seg size 316,
0 inserts, 0 merged recs, 0 merges
Hash table size 138401, used cells 0, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 900654168
Log flushed up to   0 900654168
Last checkpoint at  0 900654168
0 pending log writes, 0 pending chkp writes
9 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 54384729; in additional pool allocated 1167488
Buffer pool size   2048
Free buffers       1983
Database pages     65
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 65, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 14344, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
1 row in set (0.09 sec)