Advertisement
Guest User

mysql metadata

a guest
Mar 2nd, 2012
255
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.45 KB | None | 0 0
  1. *************************** 1. row ***************************
  2. Type: InnoDB
  3. Name:
  4. Status:
  5. =====================================
  6. 120302 11:38:00 INNODB MONITOR OUTPUT
  7. =====================================
  8. Per second averages calculated from the last 7 seconds
  9. -----------------
  10. BACKGROUND THREAD
  11. -----------------
  12. srv_master_thread loops: 78873 1_second, 78871 sleeps, 7829 10_second, 837 background, 837 flush
  13. srv_master_thread log flush and writes: 80380
  14. ----------
  15. SEMAPHORES
  16. ----------
  17. OS WAIT ARRAY INFO: reservation count 859619, signal count 21621021
  18. Mutex spin waits 21317919, rounds 27408797, OS waits 401483
  19. RW-shared spins 9292521, rounds 30419002, OS waits 216533
  20. RW-excl spins 7199604, rounds 40706180, OS waits 217765
  21. Spin rounds per wait: 1.29 mutex, 3.27 RW-shared, 5.65 RW-excl
  22. --------
  23. FILE I/O
  24. --------
  25. I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
  26. I/O thread 1 state: waiting for completed aio requests (log thread)
  27. I/O thread 2 state: waiting for completed aio requests (read thread)
  28. I/O thread 3 state: waiting for completed aio requests (read thread)
  29. I/O thread 4 state: waiting for completed aio requests (read thread)
  30. I/O thread 5 state: waiting for completed aio requests (read thread)
  31. I/O thread 6 state: waiting for completed aio requests (write thread)
  32. I/O thread 7 state: waiting for completed aio requests (write thread)
  33. I/O thread 8 state: waiting for completed aio requests (write thread)
  34. I/O thread 9 state: waiting for completed aio requests (write thread)
  35. Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
  36. ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
  37. Pending flushes (fsync) log: 0; buffer pool: 0
  38. 224237 OS file reads, 7063747 OS file writes, 5911116 OS fsyncs
  39. 0.00 reads/s, 0 avg bytes/read, 76.56 writes/s, 14.86 fsyncs/s
  40. -------------------------------------
  41. INSERT BUFFER AND ADAPTIVE HASH INDEX
  42. -------------------------------------
  43. Ibuf: size 1, free list len 5, seg size 7, 4450 merges
  44. merged operations:
  45. insert 4379, delete mark 42142, delete 291
  46. discarded operations:
  47. insert 0, delete mark 0, delete 0
  48. Hash table size 14874907, node heap has 8516 buffer(s)
  49. 26088.99 hash searches/s, 11814.60 non-hash searches/s
  50. ---
  51. LOG
  52. ---
  53. Log sequence number 550518259052
  54. Log flushed up to 550518259052
  55. Last checkpoint at 550001843022
  56. Max checkpoint age 1738042860
  57. Checkpoint age target 1683729021
  58. Modified age 516416030
  59. Checkpoint age 516416030
  60. 0 pending log writes, 0 pending chkp writes
  61. 5875604 log i/o's done, 12.86 log i/o's/second
  62. ----------------------
  63. BUFFER POOL AND MEMORY
  64. ----------------------
  65. Total memory allocated 7721713664; in additional pool allocated 0
  66. Internal hash tables (constant factor + variable factor)
  67. Adaptive hash index 258529616 (118999256 + 139530360)
  68. Page hash 7438216 (buffer pool 0 only)
  69. Dictionary cache 44879214 (29751248 + 15127966)
  70. File system 83552 (82672 + 880)
  71. Lock system 18597168 (18594536 + 2632)
  72. Recovery system 0 (0 + 0)
  73. Dictionary memory allocated 15127966
  74. Buffer pool size 458751
  75. Buffer pool size, bytes 7516176384
  76. Free buffers 204678
  77. Database pages 245557
  78. Old database pages 90627
  79. Modified db pages 12798
  80. Pending reads 0
  81. Pending writes: LRU 0, flush list 0, single page 0
  82. Pages made young 2720, not young 0
  83. 0.43 youngs/s, 0.00 non-youngs/s
  84. Pages read 224218, created 21339, written 1160838
  85. 0.00 reads/s, 0.43 creates/s, 62.13 writes/s
  86. Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
  87. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  88. LRU len: 245557, unzip_LRU len: 0
  89. I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  90. --------------
  91. ROW OPERATIONS
  92. --------------
  93. 0 queries inside InnoDB, 0 queries in queue
  94. 2 read views open inside InnoDB
  95. ---OLDEST VIEW---
  96. Normal read view
  97. Read view low limit trx n:o 1613267D
  98. Read view up limit trx id 1613267C
  99. Read view low limit trx id 1613267D
  100. Read view individually stored trx ids:
  101. Read view trx id 1613267C
  102. -----------------
  103. Main thread process no. 10869, id 140627909371648, state: sleeping
  104. Number of rows inserted 43405536, updated 108296273, deleted 22936918, read 6113756213
  105. 10.71 inserts/s, 43140.69 updates/s, 0.29 deletes/s, 248497.21 reads/s
  106. ------------------------
  107. LATEST DETECTED DEADLOCK
  108. ------------------------
  109. 120302 10:53:16
  110. *** (1) TRANSACTION:
  111. TRANSACTION 1606E3BB, ACTIVE 20 sec fetching rows
  112. mysql tables in use 3, locked 3
  113. LOCK WAIT 8386 lock struct(s), heap size 866744, 828664 row lock(s), undo log entries 7304
  114. MySQL thread id 226835, OS thread handle 0x7fe678a16700, query id 60604160 www.local 192.168.1.1 mydatabase preparing
  115. UPDATE sales_flat_quote SET trigger_recollect = 1
  116. WHERE entity_id IN (
  117. SELECT DISTINCT quote_id
  118. FROM sales_flat_quote_item
  119. WHERE product_id IN (SELECT DISTINCT product_id FROM catalogrule_product_price)
  120. )
  121. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  122. RECORD LOCKS space id 0 page no 107163 n bits 152 index `PRIMARY` of table `mydatabase`.`sales_flat_quote` trx id 1606E3BB lock_mode X waiting
  123. *** (2) TRANSACTION:
  124. TRANSACTION 1607B5C2, ACTIVE 0 sec inserting, thread declared inside InnoDB 500
  125. mysql tables in use 1, locked 1
  126. 10 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 5
  127. MySQL thread id 229412, OS thread handle 0x7fe678b3c700, query id 60670349 192.168.1.240 mydatabase update
  128. INSERT INTO `sales_flat_quote_item` (`quote_id`, `created_at`, `updated_at`, `product_id`, `store_id`, `is_virtual`, `sku`, `name`, `applied_rule_ids`, `free_shipping`, `is_qty_decimal`, `weight`, `qty`, `price`, `base_price`, `discount_percent`, `discount_amount`, `base_discount_amount`, `tax_percent`, `tax_amount`, `base_tax_amount`, `row_total`, `base_row_total`, `row_weight`, `product_type`, `weee_tax_applied`, `weee_tax_applied_amount`, `weee_tax_applied_row_amount`, `base_weee_tax_applied_amount`, `base_weee_tax_applied_row_amount`, `weee_tax_disposition`, `weee_tax_row_disposition`, `base_weee_tax_disposition`, `base_weee_tax_row_disposition`, `base_cost`, `price_incl_tax`, `base_price_incl_tax`, `row_total_incl_tax`, `base_row_total_incl_tax`) VALUES ('272058', '2012-03-02 08:33:03', '2012-03-02 08:33:03', '23247', '1', '', 'DVATRAVEL55USB', 'DVD Player portabil EBODA TRAVEL55USB, 18 cm, CD, USB', ''
  129. *** (2) HOLDS THE LOCK(S):
  130. RECORD LOCKS space id 0 page no 107163 n bits 152 index `PRIMARY` of table `mydatabase`.`sales_flat_quote` trx id 1607B5C2 lock_mode X locks rec but not gap
  131. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  132. RECORD LOCKS space id 0 page no 199228 n bits 1064 index `FK_SALES_QUOTE_ITEM_SALES_QUOTE` of table `mydatabase`.`sales_flat_quote_item` trx id 1607B5C2 lock_mode X insert intention waiting
  133. *** WE ROLL BACK TRANSACTION (2)
  134. ------------
  135. TRANSACTIONS
  136. ------------
  137. Trx id counter 1613C624
  138. Purge done for trx's n:o < 1613268B undo n:o < 0
  139. History list length 1604
  140. LIST OF TRANSACTIONS FOR EACH SESSION:
  141. ---TRANSACTION 1613C623, not started
  142. MySQL thread id 242505, OS thread handle 0x7fe679098700, query id 63636143 www3.local 192.168.1.241 mydatabase
  143. ---TRANSACTION 1613C622, not started
  144. MySQL thread id 242504, OS thread handle 0x7fe679005700, query id 63636142 192.168.1.240 mydatabase
  145. ---TRANSACTION 1613C581, not started
  146. MySQL thread id 242499, OS thread handle 0x7fe67888e700, query id 63635650 192.168.1.240 mydatabase
  147. ---TRANSACTION 0, not started
  148. MySQL thread id 242413, OS thread handle 0x7fe678332700, query id 63636144 localhost root
  149. SHOW ENGINE INNODB STATUS
  150. ---TRANSACTION 1613C191, not started
  151. MySQL thread id 242351, OS thread handle 0x7fe678458700, query id 63632734 www.local 192.168.1.1 mydatabase Waiting for table metadata lock
  152. TRUNCATE TABLE catalogsearch_result
  153. ---TRANSACTION 16139ABC, not started
  154. MySQL thread id 242141, OS thread handle 0x7fe678bcf700, query id 63596521 www.local 192.168.1.1 mydatabase Waiting for table metadata lock
  155. TRUNCATE `catalog_product_index_price_opt_agr_idx`
  156. ---TRANSACTION 1613267C, ACTIVE 290 sec
  157. mysql tables in use 1, locked 0
  158. MySQL thread id 241211, OS thread handle 0x7fe67826e700, query id 63586880 www3.local 192.168.1.241 mydatabase Sending data
  159. SELECT /*!40001 SQL_NO_CACHE */ * FROM `enterprise_logging_event_changes`
  160. Trx read view will not see trx with id >= 1613267D, sees < 1613267D
  161. ----------------------------
  162. END OF INNODB MONITOR OUTPUT
  163. ============================
  164.  
  165. 1 row in set (0.00 sec)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement