本文详细介绍了InnoDB存储引擎的后台线程（如masterthread、iothread、purgethread、pagecleanerthread）如何协同工作，管理内存缓冲池，包括日志缓冲、重做日志缓冲、LRU列表、Free列表、Flush列表等，以及如何优化缓冲池大小、调整脏页比例、控制IO吞吐量等关键参数。
innodb体系架构：
后台线程：
master thread:主要负责将缓冲池中的数据异步刷新到磁盘。其内部由多个循环组成：
主循环 loop
4.2 后台循环 background loop
4.3 flush loop
4.4 suspend loop
io thread：使用了大量的aio来处理io请求，io thread主要负责这些io请求的回调
purge thread：事务提交后需要purge thread来回收已经使用并分配的undo页
page cleaner thread 赃页刷新工作
内存
缓冲池innodb_buffer_poll（存储的数据类型有：索引页，数据页，undo页，插入缓冲，自适应哈希索引，锁信息，数据字典）
缓冲池的管理方式为：LRU List,Free List,Flush List（脏页列表，脏页同时也存在LRU List中，Flush列表用来管理将页刷新回磁盘
重做日志缓冲redo log_buffer 额外的内存池innodb_additional_mem_pool_size查看innodb版本
show VARIABLES like 'innodb_version' -- version当前innodb引擎的文件格式
show variables like 'innodb_file_format';当前innodb引擎文件格式的支持度，该值默认为on
show variables like 'innodb_file_format_check'查看是否支持分区
show variables like '%partition%'查看io thread的线程数
show variables like 'innodb_%io_threads'
用来控制lru列表中可用页的数量，默认为1024
show variables like 'innodb_lru_scan_depth'
当缓冲池中脏页的数量占据这个比例时，将强制进行checkpoint
show variables like 'innodb_max_dirty_pages_pct'
查看缓冲池大小
show variables like 'innodb_buffer_pool_size' -- byte /1024/1024/1024=G
查看缓冲池个数
show variables like 'innodb_buffer_pool_instances'查看数据文件存放位置
show VARIABLES like 'datadir' （或者ps -ef | grep mysqld）
查看purge_threads线程数
show variables like 'innodb_purge_threads'
事务被提交后，其所使用的undo log可能不再需要，因此需要Purge Thread来回收已经使用并分配的undo页查看LRU列表midpoint的位置
show variables like 'innodb_old_blocks_pct'[可调优]
页读到mid位置后需要等待多久才会被加入到LRU的热端
show variables like 'innodb_old_blocks_time'[可调优]重做日志缓冲的大小
show variables like 'innodb_log_buffer_size'获取缓冲池中脏页的比例
show variables like 'buf_get_modified_ratio_pct'设置的脏页占缓冲没的比例，当大于这个比例，认为需要做磁盘同步操作
show variables like '%innodb_max_dirty_pages_pct%'show variables like 'innodb_io_capacity'[可调优]在合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity值的5%从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity设置磁盘io的吞吐量，默认为200
查看表空间设置
show variables like 'innodb_file_per_table' 每张表内的数据可以单独放在一个表空间内。默认情况下innodb只有一个共享的表空间idbata1
设置默认页的大小
show variables like 'innodb_page_size'查看表的状态，行格式等
show table status like 'erp_chat_detail_0'
检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
mysql> show status like 'innodb_row_lock%';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| InnoDB_row_lock_current_waits | 0 || InnoDB_row_lock_time | 0 || InnoDB_row_lock_time_avg | 0 || InnoDB_row_lock_time_max | 0 || InnoDB_row_lock_waits | 0 |查询表级锁争用情况
show status like 'table%';
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：mysql> show status like 'table%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Table_locks_immediate | 2979 || Table_locks_waited | 0 |+-----------------------+-------+设置和查看事务的隔离级别
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}
SELECT @@global.tx_isolation;
SELECT @@tx_isolation; SELECT @@session.tx_isolation;
查看控制重做日志刷新到磁盘的策略
show variables like 'innodb_flush_log_at_commit'[可调优] 重做日志先写入重做日志缓冲，再写入文件系统缓存，最后写入磁盘。为了确保重做日志写入磁盘，必须进行一次fsync操作。 该参数的默认值为1，表未事务提交时必须进行一次fsync操作。还可以设置该参数为0和2。0表示事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成，而在master thread 中每1秒进行一次重做日志的fsync操作。2表示事务提交时将重做日志写入重做日志文件，但仅写入系统的缓存中，不进行fsync操作。在这个设置下，当mysql系统发生宕机而操作系统不发生宕机时，并不会导致事务的丢失。而当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部分事务。数据库缓冲池的运行状态
select pool_id ,hit_rate,pages_made_young,pages_not_made_young from information_schema.innodb_buffer_pool_stats查看innodb状态
show engine innodb status 等同于:select pool_id ,pool_size,free_buffers,database_pages from innodb_buffer_pool_status; ===================================== 151215 21:51:24 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 64270 1_second, 64261 sleeps, 5608 10_second, 11435 background, 11458 flush srv_master_thread log flush and writes: 65665 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 44646, signal count 1751994 Mutex spin waits 14795653, rounds 10854651, OS waits 3028 RW-shared spins 614200, rounds 2174319, OS waits 34771 RW-excl spins 96838, rounds 1828427, OS waits 5670 Spin rounds per wait: 0.73 mutex, 3.54 RW-shared, 18.88 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 1747EBB Purge done for trx's n:o < 1747E82 undo n:o < 0 History list length 2852 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 28524, OS thread handle 0x2b8fffc9e940, query id 1487341 192.168.52.201 dev show engine innodb status ---TRANSACTION 1747EBA, not started MySQL thread id 28514, OS thread handle 0x2b8fffb47940, query id 1487338 192.168.52.108 dev -------- 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 1917112 OS file reads, 1020675 OS file writes, 78531 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 40746, seg size 40748, 20258 merges merged operations: insert 4656, delete mark 100770, delete 71643 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 2212673, node heap has 4612 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 54657899159 Log flushed up to 54657899159 Last checkpoint at 54657899159 0 pending log writes, 0 pending chkp writes 640264 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 1098907648; in additional pool allocated 0 Dictionary memory allocated 72032955 Buffer pool size 65535 （innodb存储引擎的缓冲池大小，单位为页：每页16k） Free buffers 1 (free列表页) Database pages 60922 （LRU列表中页的数量） Old database pages 22468 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 4125240, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 3031348, created 242918, written 825547 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 60922, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 4105, id 47897413163328, state: waiting for server activity Number of rows inserted 30962797, updated 1747410, deleted 137939, read 20759188611 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
