欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

关于INNODB存储引擎体系结构简析_MySQL

程序员文章站 2022-05-28 19:09:34
...
bitsCN.com
一,后台进程INNODB存储引擎 由4个I/O线程,1个master线程,1个锁监控线程,以1个错误监控线程。 下面说明innodb_file_io_threads参数值为8,系统默认值为4,实际表明在linux下修改innodb_file_io_threads参数值无效。在innodb plugin中,不在使用innodb_file_io_threads参数,而使用innodb_read_io_threads and innodb_write_io_threads 两个值代替。 //mysql5.1.50root@test 17:54>select version();+------------+| version() |+------------+| 5.1.50-log |+------------+root@test 17:54>show variables like 'innodb_file_io_threads';+------------------------+-------+| Variable_name | Value |+------------------------+-------+| innodb_file_io_threads | 8 |+------------------------+-------+ //查看引擎状态root@test 17:56>show engine innodb status/G;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 (write thread)Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 0323 OS file reads, 165433 OS file writes, 150609 OS fsyncs0.00 reads/s, 0 avg bytes/read, 6.20 writes/s, 3.40 fsyncs/s-------------------------------------//mysql5.5文件I/O如下mysql> select version();+------------+| version() |+------------+| 5.5.21-log |+------------+1 row in set (0.00 sec)mysql> show variables like 'innodb_version';+----------------+-------+| Variable_name | Value |+----------------+-------+| innodb_version | 1.1.8 |+----------------+-------+FILE显示的关于mysql5.5 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: 0478 OS file reads, 3 OS file writes, 3 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s-------------------------------------二,内存 INNODB内存由三部分组成:缓冲池(buffer pool),重做日志缓冲池(redo log buffer)和额外的内存池(additional memory pool) //缓冲池(buffer pool),2GBroot@test 18:13>show variables like 'innodb_buffer_pool_size';+-------------------------+------------+| Variable_name | Value |+-------------------------+------------+| innodb_buffer_pool_size | 2147483648 |+-------------------------+------------+1 row in set (0.00 sec) // 重做日志缓冲池(redo log buffer),16MBroot@test 18:13>show variables like 'innodb_log_buffer_size';+------------------------+----------+| Variable_name | Value |+------------------------+----------+| innodb_log_buffer_size | 16777216 |+------------------------+----------+1 row in set (0.00 sec) //额外的内存池(additional memory pool),32MB,在innodb中默认值为1MB,innodb plugin默认值为8MB.用于存储数据字典和内部数据结构。root@test 18:14>show variables like 'innodb_additional_mem_pool_size';+---------------------------------+----------+| Variable_name | Value |+---------------------------------+----------+| innodb_additional_mem_pool_size | 33554432 |+---------------------------------+----------+1 row in set (0.00 sec) 有上可见,数据缓冲池站内存块绝大部分。关于数据缓冲池(innodb_buffer_pool)包括:数据页(data page),索引页(index page),undo页(undo page),插入缓冲(insert buffer),自适应哈希索引(adaptive hash index),锁信息(lock info),数据字典(data dictionary)。 三、关于innodb的master thread线程 在mysql5.1中如果没有系统编译的innodb plugin插件引擎。系统默认的innodb引擎,主要的工作都有由一个master thread线程来完成。在innodb plugin引擎中,有线程池来完成,但是在mysql5.5社区版,是没有该功能,在mysql官方文档说线程池的使用在商业版可以用。 每秒都会操作的内容:
1,日志缓冲刷新到磁盘,即使这个事务还没有提交,这种设计导致很大的事务提交(commit)时也会很快。2,合并插入缓冲,在判断I/O次数少于5次时,可以执行插入缓冲操作。3,INNODB存储引擎最多每次只会刷新100个脏页到磁盘,每秒是否刷新取决于脏页的比例,如果超过innodb_max_dirty_pages_pct设置的值,就会将100个脏页刷入文件。root@(none) 22:46>show variables like 'innodb_max_dirty_pages_pct';+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| innodb_max_dirty_pages_pct | 60 |+----------------------------+-------+ innodb存储引擎的逻辑存储结构,默认情况下存放砸ibdata1空间中称之为表空间;当定义innodb_file_per_table时,存放在“表名”.idb中,包括数据,索引和插入缓冲;undo文件,系统事物信息和二次写缓冲任然保存在ibdata1中。 表空间由段(segment),区(extent),页(page)组成.segment由数据段,索引段,回滚段组成。extent由64个连续的页组成,每页大小为16KB,即大小为1MB. page(页)有数据页(b-tree page),undo页(undo page), 系统页(system page),事物数据页(transaction system page),插入缓冲位图页(insert buffer bitmap),插入缓冲空闲列表页(insert buffer free list),未压缩的二进制大对象页(uncompressed blob page),压缩的二进制大对象页(compressed blob page) 作者 alang85 bitsCN.com
相关标签: 存储 监控