Rechercher dans le manuel MySQL

15.16.3 InnoDB Standard Monitor and Lock Monitor Output

Contents

The Lock Monitor is the same as the Standard Monitor except that it includes additional lock information. Enabling either monitor for periodic output turns on the same output stream, but the stream includes extra information if the Lock Monitor is enabled. For example, if you enable the Standard Monitor and Lock Monitor, that turns on a single output stream. The stream includes extra lock information until you disable the Lock Monitor.

Standard Monitor output is limited to 1MB when produced using the SHOW ENGINE INNODB STATUS statement. This limit does not apply to output written to server standard error output (stderr).

Example Standard Monitor output:

  1. mysql> SHOW ENGINE INNODB STATUS\G
  2. *************************** 1. row ***************************
  3.   Type: InnoDB
  4.   Name:
  5. Status:
  6. =====================================
  7. 2018-04-12 15:14:08 0x7f971c063700 INNODB MONITOR OUTPUT
  8. =====================================
  9. Per second averages calculated from the last 4 seconds
  10. -----------------
  11. BACKGROUND THREAD
  12. -----------------
  13. srv_master_thread loops: 15 srv_active, 0 srv_shutdown, 1122 srv_idle
  14. srv_master_thread log flush and writes: 0
  15. ----------
  16. SEMAPHORES
  17. ----------
  18. OS WAIT ARRAY INFO: reservation count 24
  19. OS WAIT ARRAY INFO: signal count 24
  20. RW-shared spins 4, rounds 8, OS waits 4
  21. RW-excl spins 2, rounds 60, OS waits 2
  22. RW-sx spins 0, rounds 0, OS waits 0
  23. Spin rounds per wait: 2.00 RW-shared, 30.00 RW-excl, 0.00 RW-sx
  24. ------------------------
  25. LATEST FOREIGN KEY ERROR
  26. ------------------------
  27. 2018-04-12 14:57:24 0x7f97a9c91700 Transaction:
  28. TRANSACTION 7717, ACTIVE 0 sec inserting
  29. mysql tables in use 1, locked 1
  30. 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
  31. MySQL thread id 8, OS thread handle 140289365317376, query id 14 localhost root update
  32. INSERT INTO child VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5), (NULL, 6)
  33. Foreign key constraint fails for table `test`.`child`:
  34. ,
  35.   CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE
  36.   CASCADE ON UPDATE CASCADE
  37. Trying to add in child table, in index par_ind tuple:
  38. DATA TUPLE: 2 fields;
  39.  0: len 4; hex 80000003; asc     ;;
  40.  1: len 4; hex 80000003; asc     ;;
  41.  
  42. But in parent table `test`.`parent`, in index PRIMARY,
  43. the closest match we can find is record:
  44. PHYSICAL RECORD: n_fields 3; compact format; info bits 0
  45.  0: len 4; hex 80000004; asc     ;;
  46.  1: len 6; hex 000000001e19; asc       ;;
  47.  2: len 7; hex 81000001110137; asc       7;;
  48.  
  49. ------------
  50. TRANSACTIONS
  51. ------------
  52. Trx id counter 7748
  53. Purge done for trx's n:o < 7747 undo n:o < 0 state: running but idle
  54. History list length 19
  55. LIST OF TRANSACTIONS FOR EACH SESSION:
  56. ---TRANSACTION 421764459790000, not started
  57. 0 lock struct(s), heap size 1136, 0 row lock(s)
  58. ---TRANSACTION 7747, ACTIVE 23 sec starting index read
  59. mysql tables in use 1, locked 1
  60. LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
  61. MySQL thread id 9, OS thread handle 140286987249408, query id 51 localhost root updating
  62. DELETE FROM t WHERE i = 1
  63. ------- TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED:
  64. RECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t`
  65. trx id 7747 lock_mode X waiting
  66. Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  67. 0: len 6; hex 000000000202; asc       ;;
  68. 1: len 6; hex 000000001e41; asc      A;;
  69. 2: len 7; hex 820000008b0110; asc        ;;
  70. 3: len 4; hex 80000001; asc     ;;
  71.  
  72. ------------------
  73. TABLE LOCK table `test`.`t` trx id 7747 lock mode IX
  74. RECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t`
  75. trx id 7747 lock_mode X waiting
  76. Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  77. 0: len 6; hex 000000000202; asc       ;;
  78. 1: len 6; hex 000000001e41; asc      A;;
  79. 2: len 7; hex 820000008b0110; asc        ;;
  80. 3: len 4; hex 80000001; asc     ;;
  81.  
  82. --------
  83. FILE I/O
  84. --------
  85. I/O thread 0 state: waiting for i/o request (insert buffer thread)
  86. I/O thread 1 state: waiting for i/o request (log thread)
  87. I/O thread 2 state: waiting for i/o request (read thread)
  88. I/O thread 3 state: waiting for i/o request (read thread)
  89. I/O thread 4 state: waiting for i/o request (read thread)
  90. I/O thread 5 state: waiting for i/o request (read thread)
  91. I/O thread 6 state: waiting for i/o request (write thread)
  92. I/O thread 7 state: waiting for i/o request (write thread)
  93. I/O thread 8 state: waiting for i/o request (write thread)
  94. I/O thread 9 state: waiting for i/o request (write thread)
  95. Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
  96. ibuf aio reads:, log i/o's:, sync i/o's:
  97. Pending flushes (fsync) log: 0; buffer pool: 0
  98. 833 OS file reads, 605 OS file writes, 208 OS fsyncs
  99. 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
  100. -------------------------------------
  101. INSERT BUFFER AND ADAPTIVE HASH INDEX
  102. -------------------------------------
  103. Ibuf: size 1, free list len 0, seg size 2, 0 merges
  104. merged operations:
  105. insert 0, delete mark 0, delete 0
  106. discarded operations:
  107. insert 0, delete mark 0, delete 0
  108. Hash table size 553253, node heap has 0 buffer(s)
  109. Hash table size 553253, node heap has 1 buffer(s)
  110. Hash table size 553253, node heap has 3 buffer(s)
  111. Hash table size 553253, node heap has 0 buffer(s)
  112. Hash table size 553253, node heap has 0 buffer(s)
  113. Hash table size 553253, node heap has 0 buffer(s)
  114. Hash table size 553253, node heap has 0 buffer(s)
  115. Hash table size 553253, node heap has 0 buffer(s)
  116. 0.00 hash searches/s, 0.00 non-hash searches/s
  117. ---
  118. LOG
  119. ---
  120. Log sequence number          19643450
  121. Log buffer assigned up to    19643450
  122. Log buffer completed up to   19643450
  123. Log written up to            19643450
  124. Log flushed up to            19643450
  125. Added dirty pages up to      19643450
  126. Pages flushed up to          19643450
  127. Last checkpoint at           19643450
  128. 129 log i/o's done, 0.00 log i/o's/second
  129. ----------------------
  130. BUFFER POOL AND MEMORY
  131. ----------------------
  132. Total large memory allocated 2198863872
  133. Dictionary memory allocated 409606
  134. Buffer pool size   131072
  135. Free buffers       130095
  136. Database pages     973
  137. Old database pages 0
  138. Modified db pages  0
  139. Pending reads      0
  140. Pending writes: LRU 0, flush list 0, single page 0
  141. Pages made young 0, not young 0
  142. 0.00 youngs/s, 0.00 non-youngs/s
  143. Pages read 810, created 163, written 404
  144. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  145. Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
  146. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  147. LRU len: 973, unzip_LRU len: 0
  148. I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  149. ----------------------
  150. INDIVIDUAL BUFFER POOL INFO
  151. ----------------------
  152. ---BUFFER POOL 0
  153. Buffer pool size   65536
  154. Free buffers       65043
  155. Database pages     491
  156. Old database pages 0
  157. Modified db pages  0
  158. Pending reads      0
  159. Pending writes: LRU 0, flush list 0, single page 0
  160. Pages made young 0, not young 0
  161. 0.00 youngs/s, 0.00 non-youngs/s
  162. Pages read 411, created 80, written 210
  163. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  164. Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
  165. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  166. LRU len: 491, unzip_LRU len: 0
  167. I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  168. ---BUFFER POOL 1
  169. Buffer pool size   65536
  170. Free buffers       65052
  171. Database pages     482
  172. Old database pages 0
  173. Modified db pages  0
  174. Pending reads      0
  175. Pending writes: LRU 0, flush list 0, single page 0
  176. Pages made young 0, not young 0
  177. 0.00 youngs/s, 0.00 non-youngs/s
  178. Pages read 399, created 83, written 194
  179. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  180. No buffer pool page gets since the last printout
  181. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  182. LRU len: 482, unzip_LRU len: 0
  183. I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  184. --------------
  185. ROW OPERATIONS
  186. --------------
  187. 0 queries inside InnoDB, 0 queries in queue
  188. 0 read views open inside InnoDB
  189. Process ID=5772, Main thread ID=140286437054208 , state=sleeping
  190. Number of rows inserted 57, updated 354, deleted 4, read 4421
  191. 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
  192. ----------------------------
  193. END OF INNODB MONITOR OUTPUT
  194. ============================

Standard Monitor Output Sections

For a description of each metric reported by the Standard Monitor, refer to the Metrics chapter in the Oracle Enterprise Manager for MySQL Database User's Guide.

  • Status

    This section shows the timestamp, the monitor name, and the number of seconds that per-second averages are based on. The number of seconds is the elapsed time between the current time and the last time InnoDB Monitor output was printed.

  • BACKGROUND THREAD

    The srv_master_thread lines shows work done by the main background thread.

  • SEMAPHORES

    This section reports threads waiting for a semaphore and statistics on how many times threads have needed a spin or a wait on a mutex or a rw-lock semaphore. A large number of threads waiting for semaphores may be a result of disk I/O, or contention problems inside InnoDB. Contention can be due to heavy parallelism of queries or problems in operating system thread scheduling. Setting the innodb_thread_concurrency system variable smaller than the default value might help in such situations. The Spin rounds per wait line shows the number of spinlock rounds per OS wait for a mutex.

    Mutex metrics are reported by SHOW ENGINE INNODB MUTEX.

  • LATEST FOREIGN KEY ERROR

    This section provides information about the most recent foreign key constraint error. It is not present if no such error has occurred. The contents include the statement that failed as well as information about the constraint that failed and the referenced and referencing tables.

  • LATEST DETECTED DEADLOCK

    This section provides information about the most recent deadlock. It is not present if no deadlock has occurred. The contents show which transactions are involved, the statement each was attempting to execute, the locks they have and need, and which transaction InnoDB decided to roll back to break the deadlock. The lock modes reported in this section are explained in Section 15.7.1, “InnoDB Locking”.

  • TRANSACTIONS

    If this section reports lock waits, your applications might have lock contention. The output can also help to trace the reasons for transaction deadlocks.

  • FILE I/O

    This section provides information about threads that InnoDB uses to perform various types of I/O. The first few of these are dedicated to general InnoDB processing. The contents also display information for pending I/O operations and statistics for I/O performance.

    The number of these threads are controlled by the innodb_read_io_threads and innodb_write_io_threads parameters. See Section 15.13, “InnoDB Startup Options and System Variables”.

  • INSERT BUFFER AND ADAPTIVE HASH INDEX

    This section shows the status of the InnoDB insert buffer (also referred to as the change buffer) and the adaptive hash index.

    For related information, see Section 15.5.2, “Change Buffer”, and Section 15.5.3, “Adaptive Hash Index”.

  • LOG

    This section displays information about the InnoDB log. The contents include the current log sequence number, how far the log has been flushed to disk, and the position at which InnoDB last took a checkpoint. (See Section 15.11.3, “InnoDB Checkpoints”.) The section also displays information about pending writes and write performance statistics.

  • BUFFER POOL AND MEMORY

    This section gives you statistics on pages read and written. You can calculate from these numbers how many data file I/O operations your queries currently are doing.

    For buffer pool statistics descriptions, see Monitoring the Buffer Pool Using the InnoDB Standard Monitor. For additional information about the operation of the buffer pool, see Section 15.5.1, “Buffer Pool”.

  • ROW OPERATIONS

    This section shows what the main thread is doing, including the number and performance rate for each type of row operation.


Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-innodb-standard-monitor.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut