mysql monitor_Mysql中的Monitor

Mysql调优调优，究竟如何调优？如果是我们自己开发的系统，肯定会在系统中打印一些信息结合jvm信息来调优。Mysql作为第三方的存储服务，调优当然也是通过其内部输出的信息。Mysql中InnoDB就是通过Monitor给使用者统计相关调优信息。为什么默认不开启

Mysql默认是不开启监控功能的。也就意味着不会统计相关信息并输出。

我们自己开发的系统平时也不会无端端将所有信息都打印出来，而是分级别的，因为输出信息意味着需要IO操作(文件IO，网络IO等)。IO操作会影响性能，同理Monitor功能基于性能考虑默认是不会开启，需要调优或者查找问题开启即可。

Monitor分类

Monotor有好几种，不同的Monitor输出的信息不一样，根据自己需要开启对应的Monitor。Standard InonoDB Monitor，InnoDB Lock Monitor等。

比如，如果你要查询是否发生了死锁，那么你应该开启哪种Monitor来辅助排查问题呢?

Monitor监控了哪些指标

这里就拿Stand Monitor来说说。

mysql> SHOW ENGINE INNODB STATUS\G

*************************** 1. row ***************************

Status:

=====================================

030709 13:00:59 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 18 seconds

----------

BACKGROUND THREAD

----------

srv_master_thread loops: 53 1_second, 44 sleeps, 5 10_second, 7 background,

7 flush

srv_master_thread log flush and writes: 48

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 413452, signal count 378357

--Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the

semaphore: X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135

a writer (thread id 32782) has reserved it in mode wait exclusive

number of readers 1, waiters flag 1

Last time read locked in file btr0sea.c line 731

Last time write locked in file btr0sea.c line 1347

Mutex spin waits 0, rounds 0, OS waits 0

RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits

375485

Spin rounds per wait: 0.00 mutex, 20.00 RW-shared, 0.00 RW-excl

------------------------

LATEST FOREIGN KEY ERROR

------------------------

030709 13:00:59 Transaction:

TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831

inserting

15 lock struct(s), heap size 2496, undo log entries 9

MySQL thread id 25, query id 4668733 localhost heikki update

insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')

Foreign key constraint fails for table test/ibtest11a:

,

CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`,

`D`) ON DELETE CASCADE ON UPDATE CASCADE

Trying to add in child table, in index PRIMARY tuple:

0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2:

len 4; hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4:

len 7; hex 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;

But in parent table test/ibtest11b, in index PRIMARY,

the closest match we can find is record:

RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex

80000005; asc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex

0000111ef3eb; asc ......;; 4: len 7; hex 800001001e0084; asc .......;; 5:

len 3; hex 6b6864; asc khd;;

------------------------

LATEST DETECTED DEADLOCK

------------------------

030709 12:59:58

*** (1) TRANSACTION:

TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733

inserting

LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146

MySQL thread id 21, query id 4553379 localhost heikki update

INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t',

'e187358f','g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d

%H:%i'),7

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index

symbole trx id 0 290252780 lock mode S waiting

Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138;

asc aa35818;; 1:

*** (2) TRANSACTION:

TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782

inserting

130 lock struct(s), heap size 11584, undo log entries 437

MySQL thread id 23, query id 4554396 localhost heikki update

REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','',

NULL,'h396', NULL, NULL, 7.31,7.31,7.31,200)

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index

symbole trx id 0 290251546 lock_mode X locks rec but not gap

Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138;

asc aa35818;; 1:

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index

symbole trx id 0 290251546 lock_mode X locks gap before rec insert intention

waiting

Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230;

asc aa35720;; 1:

*** WE ROLL BACK TRANSACTION (1)

------------

TRANSACTIONS

------------

Trx id counter 0 290328385

Purge done for trx's n:o < 0 290315608 undo n:o < 0 17

History list length 20

Total number of lock structs in row lock hash table 70

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002

MySQL thread id 32, query id 4668737 localhost heikki

show innodb status

---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id

38929 inserting

1 lock struct(s), heap size 320

MySQL thread id 29, query id 4668736 localhost heikki update

insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjg

jlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh

---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id

28684 committing

1 lock struct(s), heap size 320, undo log entries 1

MySQL thread id 19, query id 4668734 localhost heikki update

insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgj

gjlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf

---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id

36880 starting index read

LOCK WAIT 2 lock struct(s), heap size 320

MySQL thread id 27, query id 4668644 localhost heikki Searching rows for

update

update ibtest11a set B = 'kHdkkkk' where A = 89572

------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index

PRIMARY trx id 0 290328327 lock_mode X waiting

Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00;

asc supremum.;;

------------------

---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id

34831 rollback of SQL statement

ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9

MySQL thread id 25, query id 4668733 localhost heikki update

insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')

---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id

32782

58 lock struct(s), heap size 5504, undo log entries 159

MySQL thread id 23, query id 4668732 localhost heikki update

REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t',

'e200498f','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d

%H:%i'),

---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id

30733 inserting

4 lock struct(s), heap size 1024, undo log entries 165

MySQL thread id 21, query id 4668735 localhost heikki update

INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','',

NULL,'h321', NULL, NULL, 7.31,7.31,7.31,200)

--------

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

151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs

25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf for space 0: size 1, free list len 19, seg size 21,

85004 inserts, 85004 merged recs, 26669 merges

Hash table size 207619, used cells 14461, node heap has 16 buffer(s)

1877.67 hash searches/s, 5121.10 non-hash searches/s

---

LOG

---

Log sequence number 18 1212842764

Log flushed up to 18 1212665295

Last checkpoint at 18 1135877290

0 pending log writes, 0 pending chkp writes

4341 log i/o's done, 1.22 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 84966343; in additional pool allocated 1402624

Buffer pool size 3200

Free buffers 110

Database pages 3074

Modified db pages 2674

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages read 171380, created 51968, written 194688

28.72 reads/s, 20.72 creates/s, 47.55 writes/s

Buffer pool hit rate 999 / 1000

--------------

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue

Main thread process no. 3004, id 7176, state: purging

Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779

1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================开启Monitor的方式

(1)command方式，不同版本可能略有小区别。

SHOW ENGINE INNODB STATUS\G';

(2)建表方式，本质是发送一条命令給到sql parser.不同monitor对应不同的表，表结果随意。下面是standard monitor的开启和关闭。

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

DROP TABLE innodb_monitor;

从本文中:

1.理论结合实践论证会更有说服力。

2.系统的监控思想。很多大型系统都有相应的监控平台。

refer to:

http://download.nust.na/pub6/mysql/doc/refman/5.5/en/innodb-monitors.html