June 2013

[MySQL5.6] 一个简单的optimizer_trace示例

前面已经介绍了如何使用和配置MySQL5.6中optimizer_trace(点击博客),本篇我们以一个相对简单的例子来跟踪optimizer_trace的产生过程。 本文的目的不是深究查询优化器的实现,只是跟踪optimizer trace在优化器的那一部分输出,因此很多部分只是一带而过,对于需要深究的部分,暂时标注为红色,后续再扩展阅读;之前一直没看过这部分代码,理解起来还是比较困难的… 我们以一个简单的表为例过一下optimizer trace的产生过程: mysql> show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT ‘0’, `c` char(120) NOT NULL DEFAULT ”, `pad` char(60) NOT NULL DEFAULT ”, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY […]

[MySQL 5.6] 5.6新参数slave_rows_search_algorithms

我们知道,MySQL有一个老问题,当表上无主键时,那么对于在该表上做的DML,如果是以ROW模式复制,则每一个行记录前镜像在备库都可能产生一次全表扫描(或者二级索引扫描),大多数情况下,这种开销都是非常不可接受的,并且产生大量的延迟。 在MySQL5.6中提供了一个新的参数:slave_rows_search_algorithms, 可以部分解决无主键表导致的复制延迟问题,其基本思路是对于在一个ROWS EVENT中的所有前镜像收集起来,然后在一次扫描全表时,判断HASH中的每一条记录进行更新。 测试: 首先来看看性能怎么样,我们使用sbtest表,并将其上面的主键及二级索引全部删除,表中有200万行数据 主库执行随机更新操作:      update table sbtest2 set k = k +15 order by rand() limit  [$RECOR_NUM];  备库的最大延迟时间(基本等同执行时间)如下: 随机更新记录数 TABLE_SCAN,INDEX_SCAN TABLE_SCAN,INDEX_SCAN,HASH_SCAN 10 26s 6s 20 51s 9s 40 96s 15s 60 147s 25s 80 187s 30s 100 228s 37s 可以看出来,该特性对于无主键表的复制延迟问题,还是有很大的帮助的。 如何使用: slave_rows_search_algorithms的文档描述的非常清晰,该变量由三个值的组合组成:TABLE_SCAN,INDEX_SCAN, HASH_SCAN,使用组合包括: TABLE_SCAN,INDEX_SCAN  (默认配置,表示如果有索引就用索引,否则使用全表扫描) INDEX_SCAN,HASH_SCAN TABLE_SCAN,HASH_SCAN TABLE_SCAN,INDEX_SCAN,HASH_SCAN(等价于INDEX_SCAN, HASH_SCAN) 参数组合(摘自log_event.cc: […]

[MySQL 5.6] Innodb 新的监控表 INNODB_METRICS

除了Performance Schema外,在MySQL 5.6中还提供了一个新的information_schema表来监控Innodb的内部运行状态——INNODB_METRICS;该表维护了一组计数器,用户可以通过这些计数器,来监控Innodb内部运行是否健康。当前的MySQL5.6.12版本中,共有210个计数器: mysql> select count(*) from INNODB_METRICS; +———-+ | count(*) | +———-+ |      210 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from innodb_metrics where status = ‘disabled’; +———-+ | count(*) | +———-+ |      148 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from innodb_metrics where status = […]

[MySQL 5.6] 初识5.6的optimizer trace

在MySQL5.6中,支持将执行的SQL的查询计划树记录下来,目前来看,即使对于非常简单的查询,也会打印出冗长的查询计划,看起来似乎不是很可读,不过对于一个经验丰富,对查询计划的生成过程比较了解的DBA而言,这是一个优化SQL的宝藏,因为暴露了大量的内部产生查询计划的信息给用户,这意味着,我们可以对开销较大的部分进行优化。 新参数optimizer_trace可以控制是否为执行的SQL生成查询计划树,默认关闭,我们也建议关闭,因为它会产生额外的性能开销(dimitrik的评测:http://dimitrik.free.fr/blog/archives/2012/01/mysql-performance-overhead-of-optimizer-tracing-in-mysql-56.html)。 我在自己的机器上使用sysbench测试,64个并发,select.lua,纯内存操作,QPS从112,000下降到88,000。 这是session级别的参数,如果需要是,可以在session级别打开,线程只能看到当前会话的查询计划,无法看到其他会话的。 使用也很简单: 打开optimizer_trace mysql> set session optimizer_trace=’enabled=on’; Query OK, 0 rows affected (0.00 sec) <执行你的SQL>  (例如,这里执行select * from sbtest1 order by k limit 3;) 然后查询information_schema.optimizer_trace表,输出如下 | select * from sbtest1 order by k limit 3 | {   “steps”: [     {       “join_preparation”: {         “select#”: 1,         “steps”: [           […]

[MySQL 5.6] Innodb新特性之export/import 表文件

 在MySQL 5.6中,提供了新的接口来支持将单独表文件,从一个实例转移到另外一个实例。 对于MySQL运维同学来说,这是一个非常重要的特性,当MySQL实例挂掉了,或者因为其他什么原因重现异常了,就可以通过该特性,将所有的表迁移到其他实例。 以下分析及实验基于MySQL5.6.12 用法: 简单的实验了一把,使用sysbench创建的表 mysql>  FLUSH TABLES sbtest1 FOR EXPORT; Query OK, 0 rows affected (0.00 sec) 观察到sb库下产生的新文件: $ ls -lh sbtest1.* -rw-rw—- 1 mysql dba  563 Jun 10 10:29 sbtest1.cfg -rw-rw—- 1 mysql dba 8.5K Jun  7 11:29 sbtest1.frm -rw-rw—- 1 mysql dba 276M Jun  9 00:49 sbtest1.ibd .cfg包含了Innodb的数据词典信息,例如有多少列,数据类型及顺序是什么,以及包含哪些索引等等。 将ibd和cfg文件拷贝后,执行unlock tables,注意unlock操作会移除cfg文件 目标库上,建立对应的空表: […]

[MySQL 5.6] Innodb 新特性之 multi purge thread

在做5.6.12 vs 5.6.11的性能对比时,大量update产生了很长的purge history list。手贱把innodb_fast_shutdowns设置为0了,结果Purge线程一直干活了,差不多两个小时才结束…. 我们知道,在MySQL5.5版本中,就已经开始将purge 任务从master线程中独立出来,而到了5.6,已经支持多个purge线程同时进行,简单的理了下代码逻辑. //////////////////////////////////////////////////////// 在5.6中,提供了参数Innodb_purge_threads来控制做purge操作的后台线程数,最大允许设置为32. purge线程被分为两类,一类是coordinator thread,只有一个这样的线程,另外的Innodb_purge_threads-1个是worker线程. 线程在Innodb启动时创建 quoted code in innobase_start_or_create_for_mysql: 2584 if (!srv_read_only_mode 2585 && srv_force_recovery < SRV_FORCE_NO_BACKGROUND) { 2586 2587 os_thread_create( 2588 srv_purge_coordinator_thread, 2589 NULL, thread_ids + 5 + SRV_MAX_N_IO_THREADS); 2590 2591 ut_a(UT_ARR_SIZE(thread_ids) 2592 > 5 + srv_n_purge_threads + SRV_MAX_N_IO_THREADS); 2593 2594 /* We’ve already created the purge coordinator […]

MySQL 5.6.12的Innodb性能改进

简单的记录下,在MySQL5.6.12中innodb层的3点跟性能相关的改进 1.在文件操作部分,移除了许多sleep操作,而是改用condition wait 对应的bug http://bugs.mysql.com/bug.php?id=68588。 在Mark的测试中,有近一倍的性能提升 http://bazaar.launchpad.net/~mysql/mysql-server/5.6/revision/4981 主要修改都几种在函数fil_flush中: 每个文件结构体node都增加了一个event: fil_node_create: node->sync_event = os_event_create(); fil_node_free: os_event_free(node->sync_event) fil_flush: 当文件上已经有线程在做flush时: 5638 if (node->n_pending_flushes > 0) { 5639 /* We want to avoid calling os_file_flush() on 5640 the file twice at the same time, because we do 5641 not know what bugs OS’s may contain in file 5642 i/o */ […]

Innodb Adaptive hash index 相关函数流程

这两天正在把Percona5.5的Multi AHI补丁port到5.6.11,顺便过了下AHI相关的代码逻辑,因此写的比较散乱,慎入!! 以下的分析基于5.6.11原版,主要包括AHI的存储结构,以及在检索BTREE时如何使用AHI, AHI初始化 AHI的初始化发生在Innodb启动时,在为buffer pool分配完内存后(buf_pool_init),创建AHI相关结构体,配置其所需要的内存:       btr_search_sys_create(buf_pool_get_curr_size() / sizeof(void*) / 64); 内存占用默认不超过buffer pool大小的64分之一 AHI的初始化主要包括以下几个重要的对象: btr_search_latch_temp  读写锁对象,但外部使用的是btr_search_latch: #define btr_search_latch     (*btr_search_latch_temp) btr_search_sys,AHI的全局控制结构体,只有一个成员:       hash_table_t*     hash_index; 创建哈希表 btr_search_sys->hash_index = ha_create(hash_size, 0, MEM_HEAP_FOR_BTR_SEARCH, 0); 其他相关结构体: 每个索引对象为维护的index->search_info,类型为btr_search_t,其成员包括: ulint   ref_count 该index上被AHI索引的block数量,需要通过btr_search_latch保护 buf_block_t* root_guess 上次获取的root page ulint   hash_analysis 当该值超过BTR_SEARCH_HASH_ANALYSIS时,才会去更新AHI的entry. 这主要是为了避免太过频繁的更新AHI(默认超过17次查询后,才会去尝试更新AHI) 在为search info重设新的n_fields和n_bytes时,会重设该字段为0 ibool   last_hash_succ 如果上次的搜索成功了,并使用了AHI,则设置为TRUE ulint   n_hash_potential 使用AHI持续成功检索的次数. 在函数btr_search_guess_on_hash中,当成功使用了一次AHI后,如果当前n_hash_potential<BTR_SEARCH_BUILD_LIMIT+5=105,则将n_hash_potential++ […]