<innoDB 的手记（一）>

最新推荐文章于 2024-04-30 16:16:39 发布

MyISDB

最新推荐文章于 2024-04-30 16:16:39 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： innodb innodb_buffer_pool_s innodb_buffer_pool_p

本文链接：https://blog.youkuaiyun.com/myisdb/article/details/16847689

MySQL 专栏收录该内容

10 篇文章

订阅专栏

本文深入探讨MySQL中InnoDB存储引擎的关键参数及其对性能的影响，包括innodb_buffer_pool_size及innodb_log_file_size的设置原则，并解析InnoDB如何管理脏页。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

蓝色标记为keyword，红色标记是笔者有问题的。

mysql下最常用的两个存储引擎一个是Myisam，另一个是innodb。对于主要是讲解一下innoDB的各个参数的作用和对性能的影响。其中，对性能最重要的影响的两个参数是：

innodb_buffer_pool_size和innodb_log_file_size

innodb_buffer_pool_size

这个参数可以说是对innodb的性能有着决定性的作用，缓冲区的大小决定了innodb性能的好坏。缓冲区不仅缓存innodb的索引，还存储了数据、自适应哈希索引（adaptive hash index）、插入缓存、锁、以及其他的一些内部结构。这个值的大小怎么设置呢，设置多大才合适呢，mysql的配置文件中给出的建议是设置为服务器内存的50%-80%，但是在32位的机器中，mysql的手册中有一个警告：

在32位GNU/Linux x86上,你必须要小心不要设置过高的内存用量。glibc可能允许进程堆积在线程堆栈上发展，它会造成你的服务器崩溃。如果下列表达式的值接近或者超过2GB，系统会面临危机：innodb_buffer_pool_size +

key_buffer_size（myisam使用的缓冲区）+

max_connections(sort_buffer_size+read_buffer_size+binlog_cache_size)+ max_connections2MB

我的操作系统是Mac OS 10.8.5，MySQL 是5.6.14.

mysql> select @@innodb_buffer_pool_size;默认的buffer cache

+---------------------------+

| @@innodb_buffer_pool_size |

+---------------------------+

| 134217728 |

+---------------------------+

1 row in set (0.00 sec)

默认是134M

mysql> select @@innodb_log_file_size; 默认的日志缓冲区

+------------------------+

| @@innodb_log_file_size |

+------------------------+

| 50331648 |

+------------------------+

1 row in set (0.00 sec)

默认50M

innodb使用这个缓冲区来延缓write，所以可以将多个write合并，然后顺序的写到disk中，这样可以将原来的随机写改进到顺序写，提高数据写的效率。

innodb对写操作的执行过程为先将写操作的改动写到buffer pool中，然后记录操作日志到log file（日志文件的配置参数为innodb_log_file_size）中，然后就返回结果到客户端，这样就提高了innodb的响应速度（不需要等写到磁盘后再返回，在内存中的操作会比在磁盘上的操作快），而在buffer pool改动过的记录就是“脏数据(脏页)dirty page”。而Oracle中，则是叫做dirty buffer（block），“脏块”。Oracle对这里的数据处理的思路也是类似的。也是先写日志再写数据。

那接下来问题就来了：

innodb是怎么把buffer pool中的“脏页”写到磁盘的呢？
innodb的缓冲区中能保存多少dirty page呢？

对于第一个问题，innodb使用一个后台的线程(在Oracle中，则是对应的进程是DBWN）来刷新（flush）数据到磁盘（将多个写操作合并执行，可以将随机写改为顺序写来提高效率）

对于第二个问题，innodb中使用参数innodb_max_dirty_pages_pct来控制dirty page在buffer pool中的最大比例，当达到这个值时，innodb就会使用flush thread来将数据写到磁盘（如何写将在后续中讨论），当没有达到这个值时，innodb的flush线程会在buffer pool中没有足够的空间来写新数据时执行flush的操作，这个行为称为“懒执行（lazy）”,就是innodb可以延迟flush操作。

mysql> select @@innodb_max_dirty_pages_pct;

+------------------------------+

| @@innodb_max_dirty_pages_pct |

+------------------------------+

| 75 |

+------------------------------+

1 row in set (0.00 sec)

默认是75个dirty page。

延迟执行不代表就不执行了，因为如果在服务器压力比较大的情况下（有大量的并发写），buffer pool中就会有很多的dirty page，那么就会很容易达到innodb_max_dirty_pages_pct，那么flush thread就会要尽量快将buffer pool中dirty page刷新到磁盘中，以降低脏页在缓冲区中的比例。

虽然buffer pool对innodb的性能有很大影响，但是也不是越大越好，越大的缓冲区对mysql服务的关闭和启动就要执行越长的时间。

笔者的建议是，越大越好。因为buffer cache越多，也就是可缓冲的cache比较多，对于那些DML来说，性能也能体现出来。对于数据库的关闭与开启，则是考验硬件的IO，硬件越好，把buffer cache中的数据flush到Disk上面，会越快！

比如缓冲区中有很多的“脏页”，innodb要花相对长的时间来将脏页刷新到磁盘中，只有等所有的脏页都刷新后，mysql才会关闭。当然也可以使用快速关闭，但是启动的时候，mysql要花相对长的时间来恢复，所以对于关闭和启动这整个流程来说，大的缓冲区还是对这整个流程的时间来说要相对长。

我们可以在mysql运行过程中动态的调整innodb_max_dirty_pages_pct值，动态的设置相对小一些，可以是flush线程尽快的将脏页刷新到磁盘，后再执行关闭的操作，这样可以从某一方面来说缩短关闭的时间。我们可以通过SHOW INNODB STATUS命令来查看当前脏页的数据innodb_buffer_pool_pages_dirty。有疑问，这个命令执行无效。我这个5.6.14，不知道是否和版本有关系。等以后学习了再更新回来。

这个通过show status like 'innodb_buffer_pool_pages_dirty';并不会有脏页的显示。这里可以看出，这个value值为0；

这个不好使，笔者通过执行，连续insert into a_1 select * from a_1；构建了一个有5w行的数据。变化还是为0.

但是通过这样的命令，

mysqladmin -uroot -pmysql ext -i 2|grep dirty

才能查看到有dirty page的变化。

相应的，我执行了show engine innodb status\g;

我发现比较引起我注意的几个字段。一个是对于dead lock的记录，以及对transaction的记录，第二个就是对于memory的统计。

| InnoDB | |
=====================================
2013-11-20 16:56:03 12cde3000 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 52 srv_active, 0 srv_shutdown, 88231 srv_idle
srv_master_thread log flush and writes: 88278
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 18
OS WAIT ARRAY INFO: signal count 18
Mutex spin waits 10, rounds 37, OS waits 0
RW-shared spins 17, rounds 510, OS waits 17
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 3.70 mutex, 30.00 RW-shared, 0.00 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2013-11-18 21:35:52 12ceac000
*** (1) TRANSACTION:
TRANSACTION 2852, ACTIVE 180 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 3 row lock(s)
MySQL thread id 6, OS thread handle 0x12cde3000, query id 139 localhost root updating
update a_1 set a=1 where a=2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`a_1` trx id 2852 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000200; asc ;;
1: len 6; hex 000000000b0a; asc ;;
2: len 7; hex 0a0000013b0110; asc ; ;;
3: len 4; hex 80000002; asc ;;

*** (2) TRANSACTION:
TRANSACTION 2853, ACTIVE 167 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s)
MySQL thread id 7, OS thread handle 0x12ceac000, query id 140 localhost root updating
update a_1 set a=1 where a=2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`a_1` trx id 2853 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000200; asc ;;
1: len 6; hex 000000000b0a; asc ;;
2: len 7; hex 0a0000013b0110; asc ; ;;
3: len 4; hex 80000002; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`a_1` trx id 2853 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000200; asc ;;
1: len 6; hex 000000000b0a; asc ;;
2: len 7; hex 0a0000013b0110; asc ; ;;
3: len 4; hex 80000002; asc ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 2900
Purge done for trx's n:o < 2877 undo n:o < 0 state: running but idle
History list length 15
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 2867, ACTIVE 109760 sec
MySQL thread id 6, OS thread handle 0x12cde3000, query id 182 localhost root init
show engine innodb status
Trx read view will not see trx with id >= 2868, sees < 2868
--------
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
169 OS file reads, 185 OS file writes, 131 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 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1672887
Log flushed up to 1672887
Pages flushed up to 1672887
Last checkpoint at 1672887
0 pending log writes, 0 pending chkp writes
63 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0 这里你发现这个是137363456 而在开始记录的有个innodb_buffer_pool_size，大小为134217728。这个区别在于，一个是OS给MySQL的大小，一个是buffer cache的大小。

Dictionary memory allocated 67800
Buffer pool size 8191
Free buffers 7985
Database pages 206
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 156, created 50, written 173
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: 206, 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 id 5024563200, state: sleeping
Number of rows inserted 6, updated 2, deleted 1, read 34
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT