January 2014

[MySQL 5.6] Percona Server 5.6.15(及之前版本)的优化点

Percona版本的MySQL 5.6目前已经GA了几个版本,本文大概理一下Percona版本相比Oracle版本的主要不同之处。 本文基于Percona Server 5.6.15,不排除后续有更多的新特性。 总的来说,Percona版本的5.6最吸引我的地方在于,强化了后台线程的优先级(例如Page cleaner),让后台线程多干活,同时弱化用户线程对全局资源(buffer pool)的管理介入;这在大负载写入的场景下,可以有助于性能稳定(例如通常我们在redo log推进到74%左右的时候,会发现急剧的性能下降,性能下降一半都是常有的事儿…) 1.社区PORT的特性   # 线程池 似乎最初port自mariadb,并做了持续的优化,具体的针对线程池的分析见我另外一篇博客(http://mysqllover.com/?p=826);值得一提的是,那篇博客中提到的blueprint也差不多在新版本中实现了,总的来说,更加接近Oracle版本线程池的实现方式; #超时SQL (statement timeout) 该特性port自 Twitter MySQL 允许设定一条SQL的最大执行时间,当到达设定的时间,如果SQL还没有执行完的话,就会自动中断SQL; 可以针对用户grant权限时设置超时时间,也可以通过变量设置; 该特性的好处时,可以避免大量烂SQL并发长时间运行造成实例负载飙升甚至不可用。 #Atomic write support for Fusion-io Port 自Mariadb。支持对FIO设备的原子写,需要硬件支持,据说该特性无需开启double write buffer,没有测试过 #优化Memory引擎 原生版本不支持创建列类型为TEXT/BLOB的memory表 貌似port自社区,线上不使用memory引擎,不关心,有兴趣的可以点开连接细看,文档有实现的细节 2.从Percona 5.5 port过来的特性 #多个AHI分区 降低AHI锁btr search latch竞争; #对buffer pool的mutex进行拆分 #对trx_sys和创建read view的优化) 另外一篇博客有讲这部分内容:http://mysqllover.com/?p=834 #可以调整redo log块的大小,默认为512字节,但我们可以把redo 调整到4086字节以适应SSD的块大小 #相比官方的innodb_flush_method,增加了新的值ALL_O_DIRECT 这也是我们在5.5时代的默认配置,表示使用O_DIRECT去打开数据文件和日志,但只对数据文件做fsync #优化了函数BUF_READ_AHEAD_AREA,对应的bug#54814 ,用于表示预读的page数 MySQL5.6中每次都会去计算该值,实际上这个值基本上是不会变动的;将计算后的值保存下来,每次直接用即可 注:在MySQL5.7.4,这个问题被fix掉了。 # 支持不加锁显示slave信息 语法:show slave status nolock;代价就是显示的位点信息可能不准确 […]

[MySQL5.6] Innodb独立的undo tablespace

在MySQL5.6中开始支持把undo log分离到独立的表空间,并放到单独的文件目录下;这给我们部署不同IO类型的文件位置带来便利,对于并发写入型负载,我们可以把undo文件部署到单独的高速存储设备上.  . 1.使用 有几个参数来控制该行为 . #innodb_undo_tablespaces 用于设定创建的undo表空间的个数,在Install db时初始化后,就再也不能被改动了; 默认值为0,表示不独立设置undo的tablespace,默认记录到ibdata中;否则,则在undo目录下创建这么多个undo文件,例如假定设置该值为16,那么就会创建命名为undo001~undo016的undo tablespace文件,每个文件的默认大小为10M . 修改该值可能会导致Innodb无法完成初始化;  . #innodb_undo_logs 用于表示回滚段的个数(早期版本的命名为innodb_rollback_segments ),该变量可以动态调整,但是物理上的回滚段不会减少,只是会控制用到的回滚段的个数; 默认为128个回滚段  . #innodb_undo_directory 当开启独立undo表空间时,指定undo文件存放的目录 如果我们想转移undo文件的位置,只需要修改下该配置,并将undo文件拷贝过去就可以了。  .  . 2.相关代码  . #在innodb启动时(innobase_start_or_create_for_mysql),会进行undo表空间初始化,细节见函数srv_undo_tablespaces_init . –>如果是新建实例,会去创建undo log文件,undo表空间的space id从1开始;默认初始化大小为10M,由宏SRV_UNDO_TABLESPACE_SIZE_IN_PAGES控制;  . –>读取当前实例的所有undo表空间的space id (trx_rseg_get_n_undo_tablespaces) 首先从ibdata中读取到事务系统的文件头,然后再从其中记录的回滚段信息,找到回滚段对应的space id和page no(trx_sysf_rseg_get_space,trx_sysf_rseg_get_page_no),并按照space id排序后返回;  . –>根据上一步读到的space id依次打开undo文件(srv_undo_tablespace_open),如果不存在,就标识启动失败 所以undo文件也是类似ibdata的重要文件,目前是不可以删除的。。。所以不要试图删除undo文件来释放空间- -! 可以容忍定义的table space个数比已有的undo文件个数要少(但所有的undo文件依然会打开),反之则报错初始化失败  . #undo回滚段初始化 (trx_sys_create_rsegs) 如果是正常shutdown重启,并且设置的回滚段个数大于目前已经使用的回滚段个数(trx_sysf_rseg_find_free),就会去新建回滚段(trx_rseg_create) 这里总是从第一个undolog tablespace开始初始化回滚段,看起来似乎有些问题,极端情况下,如果我每次重启递增innodb_undo_logs,是不是意味着所有的undo回滚段都会写入到第一个undo tablespace中? . 完成初始化后,将当前可用的undo 回滚段的个数复制给srv_available_undo_logs,可以通过show […]

[MySQL5.7] Innodb的索引锁优化

背景: . 这是一个臭名昭彰的问题,Innodb的btree发生合并/分裂等可能修改B-tree的操作时,都需要对其加排他的索引锁,这时候是无法对该索引进行读写操作的,极大的影响了性能;关于index lock,可以看看大神Domas的这篇博文:“Innodb locking makes me sad”  以及Vadim的这篇博客  . 总而言之,MySQL5.7.2的这个功能点的改进是万众期待的!  . 以下是阅读Rev6232的笔记,大概理了下关于索引锁优化的几个点。有些只是自己的理解,可能还需要仔细求证; 写的比较乱,同学们慎入- -!!  . 1.新的读写锁类型:SX锁  . 在之前的版本中,Innodb层有两种锁类型,一种是S共享锁,一种是X排他锁,在5.7.2增加了一种新的读写锁类型称为SX共享排他锁,这三类锁的关系为:         | S|SX| X|       –+–+–+–+        S| o| o| x|       –+–+–+–+       SX| o| x| x|       –+–+–+–+        X| x| x| x|       –+–+–+–+  . S锁和X锁与之前的逻辑相同,没有做变动 SX与SX和X互斥,与S共享,内部定义为RW_SX_LATCH,根据描述,在加上SX锁之后,不会影响读操作,但阻塞写操作 对应的加SX锁接口函数:rw_lock_sx_lock_func […]

MySQL5.7对PREPARE STATEMENT和UNION ALL的小优化

这两个问题都是之前曾经在生产环境遇到过的,在MySQL5.7里都被很好的解决掉了。简单的记录下; Q1: 对于prepare statement,总是需要生成完整的SQL,而生成的主要目的是为了复制 (即时你的复制模式为ROW模式!!!) 大概是2012年的下半年,接到一个线上的case,有业务用prepare statement导入数据非常缓慢,导入的方式类似于insert into tbname values (?)….(?)。。。大量的”?”。业务方发现这种批量插入的方式奇慢无比,完全无法满足业务的需要。 从perf 输出我们看到,bmove_upp 的占比非常高,进而根据其调用栈发现卡在Prepared_statement::setup_set_params 具体的不细说了,测试数据和当时我为了绕过这个问题写的一个小patch,可以阅读这个bug report:http://bugs.mysql.com/bug.php?id=67676 修复的方式也很简单,就是对于ROW模式,不再为prepared statement组建SQL. TIPS: 在5.7里bmove_upp函数已经被移除了,改而使用memmove (Rev:5943) 5.7.2的change log: Performance of prepared DML statements containing ? parameter substitution markers was improved under row-based logging format: Since the binary log in this case need not include the statement text, and since the statement will […]