关于INNODB存储引擎体系结构简析_MySQL
程序员文章站
2022-05-21 09:52:41
...
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
一，后台进程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