Mysql中show engine innodb status怎么操作
发布时间：2021-12-23 11:41:57 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍了Mysql中show engine innodb status怎么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 mysql show engine innodb statusG; *************************** 1. row
|
这篇文章主要介绍了Mysql中show engine innodb status怎么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 mysql> show engine innodb statusG; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2016-11-17 10:20:57 7f5fd92a8700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 5 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 263748 srv_active, 0 srv_shutdown, 959366 srv_idle srv_master_thread log flush and writes: 1223114 InnoDB 存储引擎的核心操作大部分都集中在 Mater Thread 后台线程中，该状态显示了后台线程状态信息，Master Thread 的主要工作： 主循环(loop)主要以每一秒和每十秒的频率执行刷新日志缓存，合并插入缓存，刷新脏页缓存，删除无用 undo 页等操作 如果当前没有用户活动，则进入后台循环流程(backgroud loop)，主要执行删除无用的 undo 页，合并插入缓存 如果没有什么事情可以做了，便进入了暂停循环(suspend loop)，等待事件循环唤起 ----------------- BACKGROUND THREAD ----------------- # srv_active 为每秒的循环次数，srv_idle 为每 10 秒的的循环次数，srv_shutdown 为停止的循环，通常为 0 # 如果每秒循环次数少，每 10 秒次数多，证明当前负载很低；如果每秒循环次数多，每 10 秒次数少，远大于10：1，证明当前负载很高 srv_master_thread loops: 2818842 srv_active, 0 srv_shutdown, 411 srv_idle # 日志缓冲刷盘次数 srv_master_thread log flush and writes: 2819194 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 335693 OS WAIT ARRAY INFO: signal count 7995932 Mutex spin waits 2260302, rounds 4566188, OS waits 73333 RW-shared spins 4335920, rounds 14935679, OS waits 171554 RW-excl spins 209573, rounds 11472909, OS waits 84315 Spin rounds per wait: 2.02 mutex, 3.44 RW-shared, 54.74 RW-excl ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2016-11-16 14:54:08 7f5fdcd77700 *** (1) TRANSACTION: TRANSACTION 200992143, ACTIVE 2 sec fetching rows mysql tables in use 4, locked 4 LOCK WAIT 1760 lock struct(s), heap size 210472, 339660 row lock(s), undo log entries 1 MySQL thread id 591553, OS thread handle 0x7f5fdcdb8700, query id 36452144 192.168.10.42 VIPUSER Sending data UPDATE pre_order LEFT JOIN `order` ON `order`.pre_order_id = pre_order.id LEFT JOIN loan_demand ON loan_demand.id = pre_order.demand_id LEFT JOIN store_customer ON store_customer.mobile = pre_order.lender_phone SET pre_order.saleman_id = 224, `order`.saleman_id = 224, loan_demand.saleman_charge = 287, store_customer.saleman_id=224, store_customer.update_time = 1479279001 WHERE `store_customer`.mobile = 18662175906 and pre_order.lender_phone=18662175906 and pre_order.status in( 4) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1558 page no 3636 n bits 248 index `PRIMARY` of table `vip`.`store_customer` trx id 200992143 lock_mode X waiting Record lock, heap no 141 PHYSICAL RECORD: n_fields 18; compact format; info bits 0 0: len 4; hex 00053499; asc 4 ;; 1: len 6; hex 00000bfae59a; asc ;; 2: len 7; hex a80000015c012a; asc *;; 3: len 4; hex 0000000b; asc ;; 4: len 4; hex 0000013a; asc :;; 5: len 4; hex 00062e65; asc .e;; 6: len 11; hex 3135323334313337313233; asc 15234137123;; 7: len 1; hex 81; asc ;; 8: len 1; hex 02; asc ;; 9: len 4; hex 582c028e; asc X, ;; 10: len 4; hex 00000000; asc ;; 11: len 1; hex 02; asc ;; 12: len 1; hex 00; asc ;; 13: len 4; hex 00000000; asc ;; 14: len 4; hex 00000000; asc ;; 15: len 1; hex 00; asc ;; 16: len 1; hex 00; asc ;; 17: len 1; hex 02; asc ;; *** (2) TRANSACTION: TRANSACTION 200992154, ACTIVE 2 sec starting index read mysql tables in use 1, locked 1 13 lock struct(s), heap size 2936, 92 row lock(s), undo log entries 232 MySQL thread id 592115, OS thread handle 0x7f5fdcd77700, query id 36453180 192.168.10.42 VIPUSER updating UPDATE `store_customer` SET `mobile`='13903404842',`store_id`=11,`saleman_id`=314,`pre_order_id`=405131,`is_repeat`=1,`update_time`=1479279248 WHERE ( `id` = 303168 ) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1558 page no 3636 n bits 248 index `PRIMARY` of table `vip`.`store_customer` trx id 200992154 lock_mode X locks rec but not gap Record lock, heap no 141 PHYSICAL RECORD: n_fields 18; compact format; info bits 0 0: len 4; hex 00053499; asc 4 ;; 1: len 6; hex 00000bfae59a; asc ;; 2: len 7; hex a80000015c012a; asc *;; 3: len 4; hex 0000000b; asc ;; 4: len 4; hex 0000013a; asc :;; 5: len 4; hex 00062e65; asc .e;; 6: len 11; hex 3135323334313337313233; asc 15234137123;; 7: len 1; hex 81; asc ;; 8: len 1; hex 02; asc ;; 9: len 4; hex 582c028e; asc X, ;; 10: len 4; hex 00000000; asc ;; 11: len 1; hex 02; asc ;; 12: len 1; hex 00; asc ;; 13: len 4; hex 00000000; asc ;; 14: len 4; hex 00000000; asc ;; 15: len 1; hex 00; asc ;; 16: len 1; hex 00; asc ;; 17: len 1; hex 02; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1558 page no 3206 n bits 288 index `PRIMARY` of table `vip`.`store_customer` trx id 200992154 lock_mode X locks rec but not gap waiting Record lock, heap no 33 PHYSICAL RECORD: n_fields 18; compact format; info bits 0 0: len 4; hex 0004a040; asc @;; 1: len 6; hex 00000a77948a; asc w ;; 2: len 7; hex 48000001611c78; asc H a x;; 3: len 4; hex 0000000b; asc ;; 4: len 4; hex 000000ad; asc ;; 5: len 4; hex 0005888d; asc ;; 6: len 11; hex 3133393033343034383432; asc 13903404842;; 7: len 1; hex 83; asc ;; 8: len 1; hex 01; asc ;; 9: len 4; hex 581bf514; asc X ;; 10: len 4; hex 581bf621; asc X !;; 11: len 1; hex 02; asc ;; 12: len 1; hex 00; asc ;; 13: len 4; hex 00000000; asc ;; 14: len 4; hex 00000000; asc ;; 15: len 1; hex 00; asc ;; 16: len 1; hex 00; asc ;; 17: len 1; hex 01; asc ;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 202747662 Purge done for trx's n:o < 202747617 undo n:o < 0 state: running but idle History list length 2516 ---还有unpurge 2516 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 626494, OS thread handle 0x7f5fd92a8700, query id 38472637 127.0.0.1 root init show engine innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 1 117165 OS file reads, 4860463 OS file writes, 3532584 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 9.00 writes/s, 9.20 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 75, seg size 77, 839 merges ---insert buffer合并插入 merged operations: insert 373, delete mark 904, delete 130 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 4425293, node heap has 7459 buffer(s) 37262.35 hash searches/s, 269.75 non-hash searches/s --- LOG --- Log sequence number 9856475404 Log flushed up to 9856475404 Pages flushed up to 9856471068 Last checkpoint at 9856470666 0 pending log writes, 0 pending chkp writes 1186853 log i/o's done, 2.40 log i/o ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 10989076480; in additional pool allocated 0 Dictionary memory allocated 932159 Buffer pool size 655352 -----innodb buffer size大小 655353页*16k Free buffers 8505 ------free 列表中页的数量 Database pages 624627 -----lRu列表中页的数量 Old database pages 230411 ------lru列表中old list（非热快数据：5/8-列表最后）的页的数量 Modified db pages 26 ------脏页，存在于flush list中也存在lru list中，各司其职 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 8034747, not young 844963 23.47 youngs/s, 0.00 non-youngs/s Pages read 78033, created 1790917, written 492746631 0.00 reads/s, 0.47 creates/s, 42.94 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 ------内存命中率这个值应该大于95%，不然sql需要优化啊 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 624627, unzip_LRU len: 0 ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 16384 Free buffers 1025 Database pages 14428 Old database pages 5305 Modified db pages 1 Pending reads 0 Pending writes: LRU 0, flush list 1, single page 0 Pages made young 111884, not young 729335 0.00 youngs/s, 0.00 non-youngs/s Pages read 14143, created 18647, written 441843 0.00 reads/s, 0.00 creates/s, 0.40 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 14428, unzip_LRU len: 0 I/O sum[247]:cur[2], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 16384 Free buffers 1026 Database pages 14430 Old database pages 5306 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 106938, not young 556784 0.00 youngs/s, 0.00 non-youngs/s Pages read 13932, created 18419, written 259908 0.00 reads/s, 0.00 creates/s, 0.20 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 14430, unzip_LRU len: 0 I/O sum[247]:cur[2], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 16384 Free buffers 1025 Database pages 14414 Old database pages 5300 Modified db pages 2 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 111161, not young 408726 0.00 youngs/s, 0.00 non-youngs/s Pages read 14114, created 18430, written 406694 0.00 reads/s, 0.00 creates/s, 0.60 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 14414, unzip_LRU len: 0 I/O sum[247]:cur[2], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 16384 Free buffers 1025 Database pages 14420 Old database pages 5303 Modified db pages 4 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 113167, not young 708499 0.00 youngs/s, 0.00 non-youngs/s Pages read 14242, created 18731, written 409053 0.00 reads/s, 0.00 creates/s, 0.40 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 14420, unzip_LRU len: 0 I/O sum[247]:cur[2], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 16384 Free buffers 1027 Database pages 14440 Old database pages 5310 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 110331, not young 461397 0.00 youngs/s, 0.00 non-youngs/s Pages read 14224, created 18645, written 278405 0.00 reads/s, 0.00 creates/s, 0.20 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 14440, unzip_LRU len: 0 I/O sum[247]:cur[2], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 16384 Free buffers 1024 Database pages 14425 Old database pages 5304 Modified db pages 2 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 108069, not young 370734 0.00 youngs/s, 0.00 non-youngs/s Pages read 13755, created 18494, written 233833 0.00 reads/s, 0.00 creates/s, 0.40 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 14425, unzip_LRU len: 0 I/O sum[247]:cur[2], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 16384 Free buffers 1027 Database pages 14426 Old database pages 5305 Modified db pages 4 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 106842, not young 789185 0.00 youngs/s, 0.00 non-youngs/s Pages read 13862, created 18461, written 351585 0.00 reads/s, 0.00 creates/s, 0.60 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 14426, unzip_LRU len: 0 I/O sum[247]:cur[2], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 16384 Free buffers 1025 Database pages 14426 Old database pages 5305 Modified db pages 5 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 109852, not young 628401 0.00 youngs/s, 0.00 non-youngs/s Pages read 14021, created 18798, written 336451 0.00 reads/s, 0.00 creates/s, 0.60 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 14426, unzip_LRU len: 0 I/O sum[247]:cur[2], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 13133, id 140049721313024, state: sleeping Number of rows inserted 2464227, updated 569725, deleted 1790, read 56368048059 1.40 inserts/s, 1.80 updates/s, 0.00 deletes/s, 206769.85 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec) ERROR: No query specified 点击(此处)折叠或打开 ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 10989076480; in additional pool allocated 0 Dictionary memory allocated 932159 Buffer pool size 655352 -----innodb buffer size大小 655353页*16k Free buffers 8505 ------free 列表中页的数量 Database pages 624627 -----lru列表中页的数量 Old database pages 230411 ------lru列表中old list（非热快数据：5/8-列表最后）的页的数量 Modified db pages 26 ------脏页，存在于flush list中也存在lru list中，各司其职 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 8034747, not young 844963 23.47 youngs/s, 0.00 non-youngs/s Pages read 78033, created 1790917, written 492746631 0.00 reads/s, 0.47 creates/s, 42.94 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 ------内存命中率这个值应该大于95%，不然sql需要优化啊 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 624627, unzip_LRU len: 0 这部分显示了缓冲池和内存的利用率相关信息。可以看到Innodb分配的所有内存(有些时候可能比你设置的还要多点)，以及额外的内存池分配情况 (可以检查它的大小是否正好)，缓冲池总共有多少个内存页，有多少空闲内存页，数据库分配了多少个内存页以及有多少个脏内存页。从这些信息中，就可以判断 内存缓冲池是否设定合理，如果总是有大量空闲内存页，则不需要设置那么多内存，可以适当减小一点。如果空闲内存页为 0，这种情况下数据库内存页就不一定会和缓冲池的总数一致，因为缓冲池还需要保存锁信息，自适应哈希索引以及其他系统结构等信息。 等待中的读写是指内存缓冲池级别的请求。Innodb可能会把多个文件级别的请求合并到一个上，因此各不相同。我们还可以看到Innodb提交的各 种不同类型的IO，LRU内存页中需要刷新的页 - 脏内存页，它们不会被长时间存取；刷新列表 - 检查点进程处理完之后需要刷新的旧内存页；独立内存页 - 独立的写内存页。 我们还可以看到内存页总共读写了多少次。已经创建的内存页是当前一个内存页中的内容没有读取到内存缓冲池中时，专门为新数据创建的空内存页。 最后我们可以看到缓冲池的命中率，它预示着缓冲池的效率。1000/1000 相当于 100% 的命中率。不过这样也很难说明缓冲池的命中率就足够高了，这要需要根据不同的负载环境而定。通常情况下，950/1000 就够了，有些时候在IO负载较高的环境下，命中率可能为 995/1000。 （编辑：昌吉站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐