MySQL教程:关于I/O内存方面的一些优化

这里使用的是MySQL  Ver 14.14 Distrib 5.6.19, for Linux (i686) using  EditLine wrapper

一、mysql目录文件

ibdata1:系统表空间 包含数据字典、回滚日志/undolog等

(insert buffer segment/double write segment/rollback segment/index segment/dictionary segment/undo segment)

ib_logfile0/ib_logfile1:事务日志/redolog

mysql-relay-bin:中继日志

binarylog:二进制日志

general_log.log:常规日志

mysql_error.log:错误日志

slow_query.log:慢日志

.ibd:用户表空间-数据文件(insert buffer bitmap page/leaf page segment/none leaf page segment)

Innodb buffer pool(内存):undo page /insert buffer page/adaptive hash index/index page/lock info/data dictionary

二、mysql线程

FILE IO

--------

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 (read thread)

I/O thread 4 state: waiting for i/o request (read thread)

I/O thread 5 state: waiting for i/o request (read thread)

I/O thread 6 state: waiting for i/o request (write thread)

I/O thread 7 state: waiting for i/o request (write thread)

I/O thread 8 state: waiting for i/o request (write thread)

I/O thread 9 state: waiting for i/o request (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

393 OS file reads, 5 OS file writes, 5 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

innodb后台所有线程

| thread/sql/main                        | BACKGROUND | YES          |

| thread/innodb/io_handler_thread        | BACKGROUND | YES          |

| thread/innodb/io_handler_thread        | BACKGROUND | YES          |

| thread/innodb/io_handler_thread        | BACKGROUND | YES          |

| thread/innodb/io_handler_thread        | BACKGROUND | YES          |

| thread/innodb/io_handler_thread        | BACKGROUND | YES          |

| thread/innodb/io_handler_thread        | BACKGROUND | YES          |

| thread/innodb/io_handler_thread        | BACKGROUND | YES          |

| thread/innodb/io_handler_thread        | BACKGROUND | YES          |

| thread/innodb/io_handler_thread        | BACKGROUND | YES          |

| thread/innodb/io_handler_thread        | BACKGROUND | YES          |

| thread/innodb/srv_master_thread        | BACKGROUND | YES          |

| thread/innodb/srv_purge_thread        | BACKGROUND | YES          |

| thread/innodb/srv_monitor_thread      | BACKGROUND | YES          |

| thread/innodb/srv_error_monitor_thread | BACKGROUND | YES          |

| thread/innodb/srv_lock_timeout_thread  | BACKGROUND | YES          |

| thread/innodb/page_cleaner_thread      | BACKGROUND | YES          |

| thread/sql/signal_handler              | BACKGROUND | YES          |

| thread/sql/slave_sql                  | BACKGROUND | YES          |

| thread/sql/slave_io                    | BACKGROUND | YES          | 


IO线程分别是insert buffer thread、log thread、read thread、write thread。

在MySQL 5.6.10之后,默认线程处理模型使用执行每个客户端连接一个线程语句。随着越来越多的客户端连接到服务器和执行语句,整体性能降低。线程池插件的提供旨在减少开销,提高性能的其他线程的处理模式。该插件实现了通过有效地管理语句执行线程的大量客户端连接的提高服务器性能的线程池。

InnoDB Plugin版本开始增加了默认IO thread的数量,默认的read thread和write thread分别增大到了4个,并且不再使用innodb_file_io_threads参数,而是分别使用innodb_read_io_threads和innodb_write_io_threads参数。

线程池解决每个连接模型解决单线程的几个问题

Too many thread stacks make CPU caches almost useless in highly parallel execution workloads. The thread pool promotes thread stack reuse to minimize the CPU cache footprint.

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/c38d361e0c8726b7784f50a26ae768d7.html