Mysql 进程结构
Mysql不像oracle那样是通过多进程来完毕其功能的。默认情况下,InnoDB存储引擎的后台线程有7个:
4个IO thread,
1个master thread,
1个锁(lock)监控线程。
1个错误监控线程。
在InnoDB Plugin版本号開始添加了默认IO thread的数量,默认的read thread和write thread分别增大到了4个。而且不再使用innodb_file_ io_threads參数,而是分别使用innodb_read_io_threads和innodb_write_io_threads參数。
查看mysql thread:
mysql> show variables like 'innodb_%version%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| innodb_version | 1.2.4 |
+----------------+-------+
1 row in set (0.00 sec)
mysql> show engine innodb status;
=====================================
150709 15:26:40 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from thelast 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0srv_shutdown, 804 srv_idle
srv_master_thread log flush and writes: 804
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2
OS WAIT ARRAY INFO: signal count 2
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 2, rounds 60, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 30.00RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 5377
Purge done for trx's n:o < 4872 undo n:o< 0
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle0x7f1656c75700, query id 5 localhost root init
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for completedaio requests (insert buffer thread)
I/O thread 1 state: waiting for completedaio requests (log thread)
I/O thread 2 state: waiting for completedaio requests (read thread)
I/O thread 3 state: waiting for completedaio requests (read thread)
I/O thread 4 state: waiting for completedaio requests (read thread)
I/O thread 5 state: waiting for completedaio requests (read thread)
I/O thread 6 state: waiting for completedaio requests (write thread)
I/O thread 7 state: waiting for completedaio requests (write thread)
I/O thread 8 state: waiting for completedaio requests (write thread)
I/O thread 9 state: waiting for completedaio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] ,aio writes: 0 [0, 0, 0, 0] ,
ibufaio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; bufferpool: 0
283 OS file reads, 5 OS file writes, 5 OSfsyncs
0.00 reads/s, 0 avg bytes/read, 0.00writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 0buffer(s)
0.00 hash searches/s, 0.00 non-hashsearches/s
---
LOG
---
Log sequence number 1611537
Log flushed up to 1611537
Pages flushed up to 1611537
Last checkpoint at 1611537
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; inadditional pool allocated 0
Dictionary memory allocated 39010
Buffer pool si