MySQL社区

标题: innodb_read_io_threads|innodb_write_io_threads介绍（MySQL5.1|MySQL5.5） [打印本页]

作者: kider 时间: 2014-3-18 17:05
标题: innodb_read_io_threads|innodb_write_io_threads介绍（MySQL5.1|MySQL5.5）
mysqlpub.com网摘整理

重点对比说明下在MySQL5.1和MySQL5.5中这两个参数的情况：

参数说明
### 5.1
# innodb_file_io_threads
# The number of file I/O threads in InnoDB.
# Normally, this should be left at the default value of 4, but disk I/O on Windows may benefit from a larger number.
# On Unix, increasing the number has no effect; InnoDB always uses the default value.
### 5.5
innodb_read_io_threads=4 # (default)
innodb_write_io_threads=4 # (default)

参数查看（5.1）：
# show engine innodb status on V5.1
--------
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
24 OS file reads, 138506 OS file writes, 6249 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

参数查看（5.5）：
#show variables like 'innodb_%io%threads';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_read_io_threads | 4 |
| innodb_write_io_threads | 4 |
+-------------------------+-------+
2 rows in set (0.00 sec)
#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: 0
153 OS file reads, 3 OS file writes, 3 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

我们发现：
#在5.1，这FILE_IO这一块MYSQL只用了4个线程来处理IO，包括:
insert buffer thread
log thread
read thread
write thread

#而在5.5, 随着对多线程的更强支持；INNODB也加强了对IO读写线程的支持；更多发挥多线程的优势，总共有10个FILE_IO线程：
(insert buffer thread) * 1
(log thread) * 1
(read thread) * 4
(write thread) * 4
有了更多读写线程的支持，也相应地增加了INNODB更大的并发能力；
IO线程默认增加到了10个；（配太多也没用，因为IO能力有限，需要配合特殊机型）
期待5.5版本的更快成熟；

作者: yoon 时间: 2014-3-18 17:37
明白了，谢谢。

欢迎光临 MySQL社区 (http://www.mysqlpub.com/)