关闭

mysql技术内幕-innodb存储引擎读书笔记(上)

标签: 引擎存储mysql读书bufferthread
679人阅读 评论(0) 收藏 举报
分类:


第一章、mysql体系结构和存储引擎

1.1、数据库和实例的区别
数据库:物理操作系统或其他形式文件类型的集合。在mysql下数据库文件可以是frm,myd,myi,ibd结尾的文件。
数据库实例:由数据库后台进程/线程以及一个共享内存区组成。
mysql数据库是但进程多线程的程序。

1.2、mysql的体系结构
mysql由连接池组件、管理服务和工具组件、sql接口组建、查询分析器组件、优化器组件、缓存组件、插件是存储引擎、物理文件。
示意图见书:P4.

1.3、mysql存储引擎
1.3.1、innodb存储引擎,特点支持外键、行锁、非锁定读(默认情况下读取不会产生锁)、mysql-4.1开始支持每个innodb引擎的表单独放到一个表空间里。innodb通过使用MVCC来获取高并发性,并且实现sql标准的4种隔离级别,同时使用一种被称成next-key locking的策略来避免换读(phantom)现象。除此之外innodb引擎还提供了插入缓存(insert buffer)、二次写(double write)、自适应哈西索引(adaptive hash index)、预读(read ahead)等高性能技术。
1.3.2、myisam存储引擎,myisam特点是不支持事物,适合olap应用,myisam表由MYD和MYI组成。mysql-5.0版本之前,myisam默认支持的表大小为4G,从mysql-5.0以后,myisam默认支持256T的表单数据。myisam只缓存索引数据,mysql-5.1.23版本之前无论32、64位操作系统环境下,缓存索引的缓冲区最大只能4G,在之后的版本中,64位系统可以支持大于4G的索引缓冲区。
1.3.3、NDB存储引擎,2003年mysql从索爱公司收购的NDB引擎,NDB的特点是数据放在内存中,mysql-5.1版本开始可以将非索引数据放到磁盘上。NDB之前的缺陷是join查询是mysql数据库层完成的,而不是存储引擎完成的,复杂的join查询需要巨大的网络开销,速度很慢。当前mysql cluster7.2版本中已经解决此问题,join查询效率提高了70倍。
1.3.4、memeory存储引擎,将数据放到内存中,默认使用hash索引,不支持text和blob类型,varchara是按照char的方式来存储的。mysql数据库使用memory存储引擎作为临时表还存储中间结果集(intermediate result),如果中间集结果大于memorg表的容量设置,又或者中间结果集包含text和blog列类型字段,则mysql会把他们转换到myisam存储引擎表而放到磁盘上,会对查询产生性能影响。
1.3.5、archive存储引擎,压缩能力较强,主要用于归档存储。
1.3.6、federated存储引擎,不存储数据,他指向一台远程mysql数据库上的表。
1.3.7、maria存储引擎,myisam的后续版本,支持缓存数据和索引,行锁设计,支持mvcc,支持事务和非事务安全的选项,以及更好的BLOG字符类型的处理性能。
1.3.8、其他存储引擎,sphinx用于全文索引,infobright用于数据仓库

1.4、各引擎之间的对比
可以通过 show engines查看mysql对存储引擎的支持情况。

1.5、连接mysql

第二章、innodb存储引擎

2.1、第一章已经大概介绍innodb的特点
2.2、innodb引擎架构
维护所有进程/线程需要访问的多个内部数据结构
缓存磁盘上的数据,方便快速的读取,并且在对磁盘文件的数据进行修改之前在这里缓存
重做日志缓存
……….
后台线程的主要作用是负责刷新内存池中的数据,保证缓冲池中的内存缓存是最近的数据,此外、将已经修改的数据文件刷新到磁盘文件,同时保证数据库发生异常情况下innodb能恢复到正常运行状态。
2.2.1、后台线程
innodb存储引擎后台有7个线程,—–4个IO线程,1个master thread,一个lock监控线程,一个错误监控线程。(当前5.5版本,默认IO线程是18个,8个读,8个写,一个insert buffer thread、一个log thread,加上master线程、lock监控线程、错误监控线程一共是21个)。可以通过 show variables like ‘innodb_%io_threads’\G和show engine innodb status\G来查看相关信息。
2.2.2、内存
innodb存储引擎内存由以下三个部分组成:缓冲池(buffer pool),重做日志缓存(redo log buffer),额外的内存池(additional memory pool)。分别使用innodb_buffer_pool_size和innodb_log_buffer_size、innodb_additional_mem_pool_size的大小决定。可以使用 show engine innodb status来查看innodb_buffer_pool的事情情况。在BUFFER POOL AND MEMORY里可以看到存储引擎缓存池的使用情况,buffer pool size表明一共有多少缓冲帧(buffer frame),每个buffer frame为16K.buffer pool size表明一共有多少缓冲帧、free buffers表示当前空闲的缓冲帧、database pages表示已经使用的缓存帧、modified db pages表示脏页的数量。
innodb_buffer_pool_size:具体看,缓冲池中的数据库类型有:索引页、数据库页、undo页、插入缓存页(insert buffer)、自适应hash(adaptive hash index)、innodb存储的锁信息(lock info)、数据字典信息(data dictionary)
示意图见书P24.
注意:在32位windows下innodb_buffer_pool_size可以通过开启AWE功能突破内存限制,但是会自动禁用自适应hash(adaptive hash index).
innodb_log_buffer_size:一般情况下innodb会每秒刷新log buffer到硬盘,因此保证每秒产生的事务梁在这个缓存大小之内就可以了。
innodb_additional_mem_pool_size:当你的innodb_buffer_pool_size很大的时候,这个值也需要扩大。
2.3、master thread
2.3.1、master thread源码分析

 void masteor_thread(){
 loop: //主循环,间隔10s
 for(int i=0;i thread_sleep(1) //sleep 1 s
 do log buffer flush to disk //每秒都要刷新日志缓存到硬盘
 if(last_one_second_iosinnodb_max_dirty_pages_pct){//如果缓存中的脏页比例大于配置中的innodb_max_dirty_pages_pct就刷新100个脏页到硬盘
 do buffer pool flush 100 dirty page
 }
 if(no user activity){ //如果当前没有活跃用户或者数据库关闭时,就跳入background loop
 goto backgroud loop
 }
 sleep 1 second if necessary
 }
 //每10秒执行的操作
 if(last_ten_second_ios70%){ //如果缓存中脏页比例大于70%,就刷新100个脏页到硬盘,否则只刷新10个
 do buffer pool flush 100 dirty page
 }else{
 buffer pool flush 10 dirty page
 }
 do fuzzy checkpoint //产生一个检查点
 goto loop:
 background loop: //backupgroud循环
 do full purge //总是删除bufferpool中无用的undo页
 do merge 20 insert buffer //总是合并20哥插入缓存
 if not idle://如果不空闲,就跳回主循环,如果空闲就跳入flush loop
 goto loop:
 else
 goto flush loop
 flush loop:
 do buffer pool flush 100 dirty page //总是刷新100个脏页到硬盘,直到缓存中的脏页比例小于innodb_max_dirty_pages_pct
 if(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct){
 goto flush loop
 }
 goto suspend loop //完成刷新脏页的任务后,跳入suspend loop
 suspend loop:
 suspend_thread() //将master线程挂起,等待事件激活
 waiting event
 goto loop:
 }

 

可以通过show engine innodb status\G命令查看BACKGROUND THREAD段关于mster thread的信息。这里主循环执行了2857554次,每秒的thread执行了2857350次,每十秒的tread执行了285310次,6239次background thread和6238次flush thread。这里的每秒和每十秒的比例差不多,证明服务器压力不是很大。

-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2857554 1_second, 2857350 sleeps, 285310 10_second, 6239 background, 6238 flush
srv_master_thread log flush and writes: 3040886

 

2.3.2、master thread的潜在问题
1、由于硬件的发展,现在的硬件性能已经提高了很多,如果innodb每秒最大刷新100个脏页,那么效率会很低,为了解决这个问题,innodb plugin提供了一个参数innodb_io_capacity,用来表示磁盘IO的吞吐量,默认值是200,规则如下:
在合并插入缓存时,合并插入缓存的数量为innodb_io_capacity的5%。
在从缓冲区刷新脏页时,啥新脏页的数量为innodb_io_capacity。
如果你使用的是ssd或者raid10,你可以调高这个参数,知道符合你的硬件。
2、关于innodb_max_dirty_pages_pct值的争议,如果值过大,内存也很大或者服务器压力很大,那么效率很降低,如果设置的值过小,那么硬盘的压力会增加,建议是在75-80.并且innodb plugin引进了innodb_adaptive_flushng(自适应的刷新),该值影响每秒刷新脏页的数量。他会通过innodb_flush_get_desired_flush_rate的函数判断需要刷新脏页最合适的量,这个函数是通过重做日志产生的速度来判断的。
通过上述改进,innodb master thread的为代码如下:

void masteor_thread(){
loop: //主循环,间隔10s
for(int i=0;i thread_sleep(1) //sleep 1 s
do log buffer flush to disk //每秒都要刷新日志缓存到硬盘
if(last_one_second_iosinnodb_max_dirty_pages_pct){//如果缓存中的脏页比例大于配置中的innodb_max_dirty_pages_pct就刷新innodb_io_capacity个脏页到硬盘
do buffer pool flush 100% innodb_io_capacity dirty page
}

if(no user activity){ //如果当前没有活跃用户或者数据库关闭时,就跳入background loop
goto backgroud loop
}

sleep 1 second if necessary
}
//每10秒执行的操作
if(last_ten_second_ios< innodb_io_capacity){ //如果最后10s内IO小于innodb_io_capacity次,那么就刷新innodb_io_capacity个脏页到硬盘 do buffer pool flush 100% * innodb_io_capacity dirty page } do merge at most 5 insert buffer //总是合并最多5个插入缓存 do log buffer flush to disk //总是将日志缓存刷新到磁盘 do full purge //总是删除buffer_pool中无用的undo页,一次最多20个 if(buf_get_modified_ratio_pct>70%){ //如果缓存中脏页比例大于70%,就刷新innodb_io_capacity个脏页到硬盘,否则只刷新10%*innodb_io_capacity个
do buffer pool flush 100% * innodb_io_capacity dirty page
}else{
buffer pool flush 10% * innodb_io_capacity dirty page
}
do fuzzy checkpoint //产生一个检查点
goto loop:
background loop: //backupgroud循环
do full purge //总是删除bufferpool中无用的undo页
do merge 100% * innodb_io_capacity insert buffer //总是合并innodb_io_capacity个插入缓存
if not idle://如果不空闲,就跳回主循环,如果空闲就跳入flush loop
goto loop:
else
goto flush loop

flush loop:
do buffer pool flush 100% * innodb_io_capacity dirty page //总是刷新innodb_io_capacity个脏页到硬盘,直到缓存中的脏页比例小于innodb_max_dirty_pages_pct
if(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct){
goto flush loop
}
goto suspend loop //完成刷新脏页的任务后,跳入suspend loop

suspend loop:
suspend_thread() //将master线程挂起,等待事件激活
waiting event
goto loop:
}

 

2.4、关键特性,为innodb提高性能的技术
2.4.1、插入缓存
当一个表有非聚集索引时,对于非聚集索引的叶子节点的插入不是顺序的,这时候需要离散的访问非聚集索引页,性能就在这里降低了,这是由于b+树的原理导致的。插入缓存就是用来解决这个问题的。
对于非聚集索引的插入和更新操作,不是每一次都直接插入索引页,而是先判断插入的非聚集索引页是否在缓存中,如果在就直接插入,如果不在就放入到一个插入缓冲区中,好似欺骗数据库这个非聚集索引已经插入到叶子节点了。然后再以一定的频率插入缓存和非聚集索引页字节点的合并操作。
插入缓存的使用需要满足以下两个条件(也就是非唯一的辅助索引):
索引是辅助索引
索引不是唯一的
可以通过show engine innodb status\G查看INSERT BUFFER AND ADAPTIVE HASH INDEX段的信息来了解插入缓存的使用情况。

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 30, seg size 32, 8069 merges
merged operations:
insert 6063, delete mark 2347, delete 73
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 8850419, node heap has 1562 buffer(s)
34.71 hash searches/s, 127.48 non-hash searches/s
---
LOG
---
Log sequence number 94417057034
Log flushed up to 94417055332
Last checkpoint at 94415342503
Max checkpoint age 650641675
Checkpoint age target 630309123
Modified age 1714531
Checkpoint age 1714531
0 pending log writes, 0 pending chkp writes
3724702 log i/o's done, 1.39 log i/o's/second

 

seg size显示了当前插入缓冲的大小为32*16K,size代表使用了的插入缓冲,free list len代表空闲的插入缓冲。等待完善标记

2.4.2、两次写
两次写给innodb带来的是可靠性,主要用来解决部分写失败(partial page write)。doublewrite有两部分组成,一部分是内存中的doublewrite buffer,大小为2M,另外一部分就是物理磁盘上的共享表空间中联系的128个页,即两个区,大小同样为2M。当缓冲池的张也刷新时,并不直接写硬盘,而是回通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer,之后通过doublewrite buffer再分两次写,每次写入1M到共享表空间的物理磁盘上,然后马上调用fsync函数,同步磁盘。以下命令可以查看doublewrite的使用情况。

mysql> show global status like 'innodb_dblwr%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| Innodb_dblwr_pages_written | 60511554 |
| Innodb_dblwr_writes | 828734 |
+----------------------------+----------+

这台DB doublewrite一共写了60511554个页,但实际写入次数为828734,60511554/828734=73。说明这台DB的压力一般。slave上可以通过设置skip_innodb_doublewrite参数关闭两次写功能来提高性能,但是master上一定要开启此功能,保证数据安全。

2.4.3、自适应哈西索引
由于innodb不支持hash索引,但是在某些情况下hash索引的效率很高,于是出现了 adaptive hash index功能,innodb存储引擎会监控对表上索引的查找,如果观察到建立hash索引可以提高性能的时候,则自动建立hash索引。可以通过show engine innodb status\G来查看自适应哈西索引的使用情况。可以使用innodb_adaptive_hash_index来禁用和启用hash索引,默认开启。

2.5、启动、关闭、恢复
在关闭数据库时,innodb_fast_shutdown影响innodb存储引擎的行为。该参数有0、1、2三个参数。0代表mysql关闭时,innodb需要完成所有的full purge和merge insert buffer操作。1是默认值,表示不需要完成上述的full purge和merge insert buffer操作,但是缓冲池的一些数据脏页还是回刷新到磁盘。2表示不完成full purge和merge insert buffer操作,也不将缓冲池中的脏页回写磁盘,而是将日志都写入日志文件。
innodb_force_recovery影响整个innodb存储引擎的恢复状况,该值默认为0,表示当需要恢复时,需要执行所有的恢复操作,当不能进行有效恢复时,如数据页发生了corruption,mysql数据库可能宕机,并把错误写入错误日志中。
innodb_force_recovery还可以设置1-6这个几个值。大的数字包含小数字的影响。
1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到corrupt页。
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行,如主线程需要执行full purge操作,会导致crash。
3(SRV_FORCE_NO_TRX_UNDO)不知行事务回滚操作
4(SRV_FORCE_NO_IBUF_MERGE)不执行插入缓冲的合并操作
5(SRV_FORCE_NO_UNDO_LOG_SCAN)不查看撤销日志undo log。innodb存储引擎会将未提交的事务视为已提交
6(SRV_FORCE_NO_LOG_REDO)不执行前滚的操作

2.6、innodb plugin = 新版本的innodb存储引擎
mysql-5.1使用了插件式的架构。在mysql-5.1.38前,安装innodb plugin必须下载plugin的文件,再进行一系列的安装。

第三章、文件

3.1参数文件
my.cnf文件。
3.1.1、什么是参数
键=值
3.1.2、参数类型
分为global和session

3.2、日志文件
3.2.1、错误日志
show global variables like ‘log_error’;
3.2.2、慢查询日志
show global variables like ‘%long%’;
show global variables like ‘log_slow_queries’; //mysql-5.1.2开始支持微妙级慢查询。
show global variables like ‘log_queryies_not_using_indexs’; //未使用索引的查询
mysql-5.1开始可以将慢查询放入log_output表中。
3.2.3、查询日志
mysql-5.1开始可以将查需日志放入general_log表中
3.2.4、二进制日志
主要功能
恢复 基于时间点的恢复
复制
以下参数影响二进制日志
max_binlog_size
binlog_cache_size
sync_binlog
binlog-do-db
binlog-ignore-db
log-slave-update
binlog_format
将binlog_format设置成row,可以支持事务隔离级别为READ COMMITTED,以获得更好的并发性。
在使用MIXED格式下,mysql采用STATEMENT格式进行二进制日志文件的记录,但是有一些情况下会使用ROW格式,可能的情况如下:
1、表的存储引擎为NDB,这个时候DML操作都会以ROW格式记录
2、使用了uuid()、user(),current_user(),found_rows(),row_count(),等不确定函数。
3、使用了insert delay语句
4、使用了用户定于的函数(UDF)
5、使用了临时表(temporary table)

注意:针对系统库mysql里面的表发生变化的处理规则如下:
1、如果采用insert,update,delete直接操作表,则日志根据binlog_format设定的格式记录。
2、如果使用grant,revoke,set password等DCL语句,那么无论如何都会使用SBR模式记录。
3、blockhole引擎不支持row格式,ndb引擎不支持statement格式。

3.3、套件字文件
show variables likea ‘socket’;
3.4、pid文件
show variables like ‘pid_file’;
3.5、表结构定义文件
*.frm
3.6、innodb引擎文件
重做日志文件,表空间文件
3.6.1、表空间文件
默认会有一个大小10M的tablespace file,名字叫ibdata1.可以通过innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend 来配置。这里使用了两个文件来组成的表空间,如果他们不在一个磁盘上的话,对IO性能会有所优化。
还有一个innodb_file_per_table,这个参数会让每个innodb引擎的表都单独产生一个表空间。
注意:单独表空间只保存该表的数据,索引和插入缓冲等信息,其余信息还是保存在共享表空间的(undo页,系统事务信息,二次写)。
3.6.2、重做日志文件(redo log)
redo log是在实例或者介质失败的时候,用来保证数据完整性。
每个innodb存储引擎至少有一个重做日志组,每个重做日志文件组下至少又2个重做日志文件,如默认的ib_logfile0、ib_logfile1.为了得到更高的可靠性,你可以设置多个重做镜像日志组(mirrored log groups)。
innodb_log_file_size //指定重做日志文件的大小
innodb_log_files_in_group //指定重做日志组中文件的数量,默认为2
innodb_mirrored_log_groups //指定日志镜像文件组的数量,默认为1
innodb_log_group_home_dir //指定了日志文件组所在的路径
还有一个很重要的参数与redo log相关,那就是innodb_flush_log_at_trx_commit的值,对innodb的性能影响很大。他有0,1,2三个值,0代表提交事务时,并不同步写redo log,而是等master threas每秒写。1代表commit的时候就将redo log缓存写入磁盘,2代表commit的时候将redo log缓存异步的写入磁盘。


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:239330次
    • 积分:2599
    • 等级:
    • 排名:第14098名
    • 原创:9篇
    • 转载:159篇
    • 译文:2篇
    • 评论:8条
    最新评论