In SHOW INNODB STATUS many values are per second. If you're planning to use these values make sure they are sampled over decent period of time. In the very start of printout Innodb will print:

在 SHOW INNODB STATUS 中许多值都是按秒统计的,如果要使用这些值,就得隔适当的时间取样。SHOW INNODB STATUS 的开头应该是:

  1. =====================================
  2. 060717 3:07:56 INNODB MONITOR OUTPUT
  3. =====================================
  4. Per second averages calculated from the last 44 seconds

数据的取样应该至少是20-30秒。最好是写一个脚本来统计 SHOW INNODB STATUS 的结果。

下面是 Semaphores 信息:

  1. ----------
  2. SEMAPHORES
  3. ----------
  4. OS WAIT ARRAY INFO: reservation count 13569, signal count 11421
  5. --Thread 1152170336 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds the semaphore:
  6. Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
  7. waiters flag 0
  8. wait is ending
  9. --Thread 1147709792 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds the semaphore:
  10. Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
  11. waiters flag 0
  12. wait is ending
  13. Mutex spin waits 5672442, rounds 3899888, OS waits 4719
  14. RW-shared spins 5920, OS waits 2918; RW-excl spins 3463, OS waits 3163

该部分有2部分。一个是当前等待列表,如果并发数很大,Innodb就必须频繁等待 OS。那么该部分不包含由spinlock解决的等待。

该部分需要对源代码有一定了解,因为只能看到文件名和行数。但可以从文件名猜出其含义-buf0buf.ic是缓存池的内容。

“lock var”是当前互斥对象(locked=1/free=0)的值,"waiters flag"是当前等待线程数,还有一些状态信息:"wait is ending"意思是互斥锁已经释放,但OS还没有分配,这时这个对象可以执行。

第二部分是事件信息。"reservation count" 和 "signal count" 表明InnoDB使用内部同步数组的频繁程度。这些数值表示InnoDB需要等待OS的频率。"OS Waits" 表明由于互斥的OS的等待。OS Wait is not exactly the same as "reservation" - before falling back to complex wait using sync_array Innodb tries to "yield" to OS hoping when name thread is scheduled next time object will be free already. OS Waits 相对较慢,如果每秒钟该值有几万,就有问题。另外一种方式来看该值是通过OS状态的 context switch.

另一个重要信息是"spin waits"和"spin rounds"的数量。与OS wait相比,Spin locks 是低消耗的等待,但它是一种活跃的等待,会浪费CPU资源,因此spin waits和spin rounds 如果很大,CPU资源会被浪费。如果该值达到几十万,CPU危险了。

死锁部分:

  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 060717 4:16:48
  5. *** (1) TRANSACTION:
  6. TRANSACTION 0 42313619, ACTIVE 49 sec, process no 10099, OS thread id 3771312 starting index read
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 3 lock struct(s), heap size 320
  9. MySQL thread id 30898, query id 100626 localhost root Updating
  10. update iz set pad='a' where i=2
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 0 page no 16403 n bits 72 index `PRIMARY` of table `test/iz` trx id 0 42313619 lock_mode X locks rec but not gap waiting
  13. Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  14. 0: len 4; hex 80000002; asc ;; 1: len 6; hex 00000285a78f; asc ;; 2: len 7; hex 00000040150110; asc @ ;; 3: len 10; hex 61202020202020202020; asc a ;;

  15. *** (2) TRANSACTION:
  16. TRANSACTION 0 42313620, ACTIVE 24 sec, process no 10099, OS thread id 4078512 starting index read, thread declared inside InnoDB 500
  17. mysql tables in use 1, locked 1
  18. 3 lock struct(s), heap size 320
  19. MySQL thread id 30899, query id 100627 localhost root Updating
  20. update iz set pad='a' where i=1
  21. *** (2) HOLDS THE LOCK(S):
  22. RECORD LOCKS space id 0 page no 16403 n bits 72 index `PRIMARY` of table `test/iz` trx id 0 42313620 lock_mode X locks rec but not gap
  23. Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  24. 0: len 4; hex 80000002; asc ;; 1: len 6; hex 00000285a78f; asc ;; 2: len 7; hex 00000040150110; asc @ ;; 3: len 10; hex 61202020202020202020; asc a ;;

  25. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  26. RECORD LOCKS space id 0 page no 16403 n bits 72 index `PRIMARY` of table `test/iz` trx id 0 42313620 lock_mode X locks rec but not gap waiting
  27. Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  28. 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000285a78e; asc ;; 2: len 7; hex 000000003411d9; asc 4 ;; 3: len 10; hex 61202020202020202020; asc a ;;

  29. *** WE ROLL BACK TRANSACTION (2)

InnoDB只打印锁的几个信息,且只显示事务的最后一条语句,有可能被锁的行由前面一条语句引起。对复杂死锁的研究得看日志文件。

外键信息与死锁信息相似:

  1. ------------------------
  2. LATEST FOREIGN KEY ERROR
  3. ------------------------
  4. 060717 4:29:00 Transaction:
  5. TRANSACTION 0 336342767, ACTIVE 0 sec, process no 3946, OS thread id 1151088992 inserting, thread declared inside InnoDB 500
  6. mysql tables in use 1, locked 1
  7. 3 lock struct(s), heap size 368, undo log entries 1
  8. MySQL thread id 9697561, query id 188161264 localhost root update
  9. insert into child values(2,2)
  10. Foreign key constraint fails for table `test/child`:
  11. ,
  12. CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
  13. Trying to add in child table, in index `par_ind` tuple:
  14. DATA TUPLE: 2 fields;
  15. 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000401; asc ;;

  16. But in parent table `test/parent`, in index `PRIMARY`,
  17. the closest match we can find is record:
  18. PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
  19. 0: len 4; hex 80000001; asc ;; 1: len 6; hex 0000140c2d8f; asc - ;; 2: len 7; hex 80009c40050084; asc @ ;;

看不懂的16进制信息没关系-那是程序的调试信息。