[MySQL FAQ]系列 — 5.6版本GTID复制异常处理一例

昨天处理了一个MySQL 5.6版本下开启GTID模式复制异常案例,MASTER上的任何操作都无法在SLAVE上应用,SLAVE的RELAY LOG里有记录,但SLAVE的BINLOG却找不到蛛丝马迹。由于开启了GTID,所以排查起来也简单,只需要在SLAVE上把RELAY LOG和BINLOG分别解析成文本文件,再直接搜索MASTER的UUID,就能找到SLAVE上是否应用了MASTER复制过来的事务。 排查过程中,曾经一度怀疑是因为设置了BINLOG-DO或者IGNORE规则,或者设置了REPLICATION-DO或IGNORE规则,甚至是GTID的严重BUG,但都没发现端倪。直到从 SHOW SLAVE STATUS 里发现下面这个信息:

[yejr@imysql.com]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
...
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2539
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 1996
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
# 两个线程工作正常
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
#没设置任何规则
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2539
# 无论binlog file 还是 pos,都和MASTER保持一致,也就是说BINLOG的接收和RELAYR LOG的APPLY都很正常,有条不紊工作着
              Relay_Log_Space: 2778
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
...
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
# 没设置忽略某些 server-id 上的BINLOG
             Master_Server_Id: 123315
                  Master_UUID: 35cc99c6-0297-11e4-9916-782bcb2c9453
             Master_Info_File: /data/db11_3316/master.info
                    SQL_Delay: 0
# 没有设置复制延迟策略          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 4294967295
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 35cc99c6-0297-11e4-9916-782bcb2c9453:1-451
            Executed_Gtid_Set: 35cc99c6-0297-11e4-9916-782bcb2c9453:1-2455:792490-4517929
                Auto_Position: 1

从上面的日志发现什么了没?尤其是这两行:

           Retrieved_Gtid_Set: 35cc99c6-0297-11e4-9916-782bcb2c9453:1-451
            Executed_Gtid_Set: 35cc99c6-0297-11e4-9916-782bcb2c9453:1-2455:792490-4517929
                Auto_Position: 1

这下有点明白了吧,意思是:

1、SLAVE从MASTER上复制了GTID范围是:1-451;
2、SLAVE上执行GTID的范围分为两段,一段是:1-2455,另一段是:792490-4517929;

尼玛,不应该是连续的嘛,怎么会这么奇葩⊙﹏⊙b,这可如何是好呀,好捉急~~~ 莫急,且容我们慢慢分析为啥GTID从MASTER到SLAVE之后发生了断点,产生了间隙。

正常滴,在MySQL 5.6启用GTID后,部署REPLICATION复制时,可以设定 MASTER_AUTO_POSITION = 1,让 SLAVE 根据 GTID 自动选择适当的事务点进行复制,DBA基本上无需关注和担心主从不一致的问题,还是很让DBA省心的。 在启用 MASTER_AUTO_POSITION = 1 的情况下,正常是不会发生 GTID 中间有个空隙,产生断点的问题发生。除非是下面这种情况:

1、人工暂停SLAVE进程;
2、MASTER上继续写入数据;
3、MASTER上刷新LOG;
4、MASTER上删除旧BINLOG,只保留最新的BINLOG;
5、SLAVE上启动MASTER,这时候会报错,像下面这样:
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

针对这种问题的处理方法可以这么做:

1、关闭MASTER_AUTO_POSITION,即设置 MASTER_AUTO_POSITION = 0;
2、手工CHANGE BINLOG FILE & POS;

这种情况下,不能再次设置 MASTER_AUTO_POSITION = 1,否则还会再次报错。 还有一种情况会发生 GTID 间隙断点问题,例如这样:

1、正常配置 REPLICATION 复制,但是设置 MASTER_AUTO_POSITION = 0,也就是人工指定 BINLOG FILE & POS的传统方法;
2、在复制过程中,暂时关闭 SLAVE 进程;
3、手工修改 BINLOG FILE & POS 信息,指向新的 BINLOG FILE & POS 点;
4、启动SLAVE,这时候就会发现 GTID 断点的现象重现了;

在主从高可用模式下,可能主从间会发生切换,然后再次切换回来,这时候也可能发生上述的断点问题。因此我们建议采用双主来部署高可用切换,基本上可以实现任意来回切换,无需手工指定新的 BINLOG FIEE & POS 信息。

还有最后一种情况,就是在 MASTER 上执行了 RESET MASTER,导致 MASTER 上的 BINLOG FILE & POS 全部重置,SLAVE 上读取到的信息自然也就不一致了。

好了,说了那么多,我们最后来说下如何应对处理 GTID 断点的问题。

方法一:手工修改 BINLOG FILE & POS

1、关闭SLAVE;
2、手工CHANGE BINLOG FILE & POS,指向MASTER上最新产生的BINLOG FILE & POS,并且设置 MASTER_AUTO_POSITION = 0;
3、启动SLAVE;

方法二:手工修改 GTID_PURGED 值

1、关闭 SLAVE;
2、在 SLAVE 上执行 RESET MASTER,重设 SLAVE 上的 BINLOG FILE & POS(如果这个节点用于复制中继,要注意所有binlog是否都被读取完毕,避免数据丢失);
3、在 SLAVE 上执行 SET @@GLOBAL.GTID_PURGED = '35cc99c6-0297-11e4-9916-782bcb2c9453:1-2455';
4、启动 SLAVE;

这种做法比较费解一点,意思是,我们告诉SLAVE要主动抛弃掉 MASTER 上传输过来的某些区间的事务。在这个例子中,我们抛弃了 1-2455 这个区间,也就是在 GTID 从 2466 开始,又会继续应用 RELAY LOG 了,相比我们最开始的那个信息:

           Retrieved_Gtid_Set: 35cc99c6-0297-11e4-9916-782bcb2c9453:1-451
            Executed_Gtid_Set: 35cc99c6-0297-11e4-9916-782bcb2c9453:1-2455:792490-4517929

我们强制 SLAVE 只忽略 1-2455 这个区间,从 2466 开始继续复制,消除了本来也会被忽略的区间: 792490-4517929,确保新产生的事务都会被继续应用。这个做法可以参考MySQL手册:Excluding transactions with gtid_purged

还有另外一种费力不讨好的做法,就是在 MASTER 上执行一些没用的空事务,使得 GTID 的序号一直在加大,直到超过 2555 为止,然后在 792490-4517929 这个区间依法炮制一番,但我们非常不推荐采用这种做法,既麻烦又容易误操作。 说了这么多,在 MySQL 5.6及以上版本中,我们强烈建议启用 MASTER_AUTO_POSITION = 1,让 MySQL 自己去做判断,减少一些不必要的问题,并且采用双主(其中一个主设为只读)的方式,方便两个主之间可以随意相互切换,而不必担心数据不一致。

上面过程我采用的MySQL版本:5.6.17-65.0-rel65.0-log Percona Server with XtraDB (GPL), Release rel65.0, Revision 587,实际案例发生的MySQL版本当时忘了记录了,但肯定也是5.6以上的啦,哈哈~~~

[MySQL优化案例]系列 — 分页优化

通常,我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询。例如下面这个SQL:

SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;

或者像下面这个不带任何条件的分页SQL:

SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10;

一般而言,分页SQL的耗时随着 start 值的增加而急剧增加,我们来看下面这2个不同起始值的分页SQL执行耗时:

yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10;
…

10 rows in set (0.05 sec)


yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=6 ORDER BY id DESC LIMIT 935500, 10;
…

10 rows in set (2.39 sec)

可以看到,随着分页数量的增加,SQL查询耗时也有数十倍增加,显然不科学。今天我们就来分析下,如何能优化这个分页方案。 一般滴,想要优化分页的终极方案就是:没有分页,哈哈哈~~~,不要说我讲废话,确实如此,可以把分页算法交给Sphinx、Lucence等第三方解决方案,没必要让MySQL来做它不擅长的事情。 当然了,有小伙伴说,用第三方太麻烦了,我们就想用MySQL来做这个分页,咋办呢?莫急,且待我们慢慢分析,先看下表DDL、数据量、查询SQL的执行计划等信息:

yejr@imysql.com> SHOW CREATE TABLE `t1`;
CREATE TABLE `t1` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
 `ftype` tinyint(3) unsigned NOT NULL,
...
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

yejr@imysql.com> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 994584 |
+----------+

yejr@imysql.com> EXPLAIN SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10\G
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: t1
 type: index
possible_keys: NULL
 key: PRIMARY
 key_len: 4
 ref: NULL
 rows: 510
 Extra: Using where

yejr@imysql.com> EXPLAIN SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500, 10\G
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: t1
 type: index
possible_keys: NULL
 key: PRIMARY
 key_len: 4
 ref: NULL
 rows: 935510
 Extra: Using where

可以看到,虽然通过主键索引进行扫描了,但第二个SQL需要扫描的记录数太大了,而且需要先扫描约935510条记录,然后再根据排序结果取10条记录,这肯定是非常慢了。 针对这种情况,我们的优化思路就比较清晰了,有两点:

1、尽可能从索引中直接获取数据,避免或减少直接扫描行数据的频率
2、尽可能减少扫描的记录数,也就是先确定起始的范围,再往后取N条记录即可

据此,我们有两种相应的改写方法:子查询、表连接,即下面这样的:

#采用子查询的方式优化,在子查询里先从索引获取到最大id,然后倒序排,再取10行结果集
#注意这里采用了2次倒序排,因此在取LIMIT的start值时,比原来的值加了10,即935510,否则结果将和原来的不一致
yejr@imysql.com> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC\G
*************************** 1. row ***************************
 id: 1
 select_type: PRIMARY
 table: <derived2>
 type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 10
 Extra: Using filesort
*************************** 2. row ***************************
 id: 2
 select_type: DERIVED
 table: t1
 type: ALL
possible_keys: PRIMARY
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 973192
 Extra: Using where
*************************** 3. row ***************************
 id: 3
 select_type: SUBQUERY
 table: t1
 type: index
possible_keys: NULL
 key: PRIMARY
 key_len: 4
 ref: NULL
 rows: 935511
 Extra: Using where

#采用INNER JOIN优化,JOIN子句里也优先从索引获取ID列表,然后直接关联查询获得最终结果,这里不需要加10
yejr@imysql.com> EXPLAIN SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2 USING (id)\G
*************************** 1. row ***************************
 id: 1
 select_type: PRIMARY
 table: <derived2>
 type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 935510
 Extra: NULL
*************************** 2. row ***************************
 id: 1
 select_type: PRIMARY
 table: t1
 type: eq_ref
possible_keys: PRIMARY
 key: PRIMARY
 key_len: 4
 ref: t2.id
 rows: 1
 Extra: NULL
*************************** 3. row ***************************
 id: 2
 select_type: DERIVED
 table: t1
 type: index
possible_keys: NULL
 key: PRIMARY
 key_len: 4
 ref: NULL
 rows: 973192
 Extra: Using where

然后我们来对比下这2个优化后的新SQL执行时间:

yejr@imysql.com> SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) T ORDER BY id DESC;
...
rows in set (1.86 sec)
#采用子查询优化,从profiling的结果来看,相比原来的那个SQL快了:28.2%

yejr@imysql.com> SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2 USING (id);
...
10 rows in set (1.83 sec)
#采用INNER JOIN优化,从profiling的结果来看,相比原来的那个SQL快了:30.8%

我们再来看一个不带过滤条件的分页SQL对比:

#原始SQL
yejr@imysql.com> EXPLAIN SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 935510
        Extra: NULL

yejr@imysql.com> SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10;
...
10 rows in set (2.22 sec)

#采用子查询优化
yejr@imysql.com> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 973192
        Extra: Using where
*************************** 3. row ***************************
           id: 3
  select_type: SUBQUERY
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 935511
        Extra: Using index

yejr@imysql.com> SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC;
…
10 rows in set (2.01 sec)
#采用子查询优化,从profiling的结果来看,相比原来的那个SQL快了:10.6%


#采用INNER JOIN优化
yejr@imysql.com> EXPLAIN SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: 
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 935510
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: t1.id
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 973192
        Extra: Using index

yejr@imysql.com> SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id);
…
10 rows in set (1.70 sec)
#采用INNER JOIN优化,从profiling的结果来看,相比原来的那个SQL快了:30.2%

至此,我们看到采用子查询或者INNER JOIN进行优化后,都有大幅度的提升,这个方法也同样适用于较小的分页,虽然LIMIT开始的 start 位置小了很多,SQL执行时间也快了很多,但采用这种方法后,带WHERE条件的分页分别能提高查询效率:24.9%、156.5%,不带WHERE条件的分页分别提高查询效率:554.5%、11.7%,各位可以自行进行测试验证。单从提升比例说,还是挺可观的,确保这些优化方法可以适用于各种分页模式,就可以从一开始就是用。 我们来看下各种场景相应的提升比例是多少:

大分页,带WHERE 大分页,不带WHERE 大分页平均提升比例 小分页,带WHERE 小分页,不带WHERE 总体平均提升比例
子查询优化 28.20% 10.60% 19.40% 24.90% 554.40% 154.53%
INNER JOIN优化 30.80% 30.20% 30.50% 156.50% 11.70% 57.30%

结论:这样看就和明显了,尤其是针对大分页的情况,因此我们优先推荐使用INNER JOIN方式优化分页算法。

上述每次测试都重启mysqld实例,并且加了SQL_NO_CACHE,以保证每次都是直接数据文件或索引文件中读取。如果数据经过预热后,查询效率会一定程度提升,但但上述相应的效率提升比例还是基本一致的。

2014/07/28后记更新:

其实如果是不带任何条件的分页,就没必要用这么麻烦的方法了,可以采用对主键采用范围检索的方法,例如参考这篇:Advance for MySQL Pagination

[MySQL FAQ]系列 — 大数据量时如何部署MySQL Replication从库

我们在部署MySQL Replication从库时,通常是一开始就做好一个从库,然后随着业务的变化,数据也逐渐复制到从服务器。

但是,如果我们想对一个已经上线较久,有这大数据量的数据库部署复制从库时,应该怎么处理比较合适呢?

本文以我近期所做Zabbix数据库部署MySQL Replication从库为例,向大家呈现一种新的复制部署方式。由于Zabbix历史数据非常多,在转TokuDB之前的InnoDB引擎时,已经接近700G,转成TokuDB后,还有300多G,而且主要集中在trends_uint、history_uint等几个大表上。做一次全量备份后再恢复耗时太久,怕对主库写入影响太大,因此才有了本文的分享。

我大概分为几个步骤来做Zabbix数据迁移的:

1、初始化一个空的Zabbix库

2、启动复制,但设置忽略几个常见错误(这几个错误代码对应具体含义请自行查询手册)
#忽略不重要的错误,极端情况下,甚至可以直接忽略全部错误,例如
#slave-skip-errors=all
slave-skip-errors=1032,1053,1062

3、将大多数小表正常备份导出,在SLAVE服务器上导入恢复。在这里,正常导出即可,无需特别指定 --master-data 选项

4、逐一导出备份剩下的几个大表。在备份大表时,还可以分批次并发导出,方便并发导入,使用mysqldump的"-w"参数,然后在SLAVE上导入恢复(可以打开后面的参考文章链接)

5、全部导入完成后,等待复制没有延迟了,关闭忽略错误选项,重启,正式对外提供服务

上述几个步骤完成后,可能还有个别不一致的数据,不过会在后期逐渐被覆盖掉,或者被当做过期历史数据删除掉。

本案例的步骤并不适用于全部场景,主要适用于:

不要求数据高一致性,且数据量相对较大,尤其是单表较大的情况,就像本次的Zabbix数据一样。

参考文章:

迁移Zabbix数据库到TokuDB

[MySQL FAQ]系列— mysqldump加-w参数备份

[MySQL优化案例]系列 — RAND()优化

众所周知,在MySQL中,如果直接 ORDER BY RAND() 的话,效率非常差,因为会多次执行。事实上,如果等值查询也是用 RAND() 的话也如此,我们先来看看下面这几个SQL的不同执行计划和执行耗时。
首先,看下建表DDL,这是一个没有显式自增主键的InnoDB表:

[yejr@imysql]> show create table t_innodb_random\G
*************************** 1. row ***************************
Table: t_innodb_random
Create Table: CREATE TABLE `t_innodb_random` (
`id` int(10) unsigned NOT NULL,
`user` varchar(64) NOT NULL DEFAULT '',
KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

往这个表里灌入一些测试数据,至少10万以上, id 字段也是乱序的。

[yejr@imysql]> select count(*) from t_innodb_random\G
*************************** 1. row ***************************
count(*): 393216

1、常量等值检索:

[yejr@imysql]> explain select id from t_innodb_random where id = 13412\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_innodb_random
type: ref
possible_keys: idx_id
key: idx_id
key_len: 4
ref: const
rows: 1
Extra: Using index

[yejr@imysql]> select id from t_innodb_random where id = 13412;
1 row in set (0.00 sec)

可以看到执行计划很不错,是常量等值查询,速度非常快。

2、使用RAND()函数乘以常量,求得随机数后检索:

[yejr@imysql]> explain select id from t_innodb_random where id = round(rand()*13241324)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_innodb_random
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 393345
Extra: Using where; Using index

[yejr@imysql]> select id from t_innodb_random where id = round(rand()*13241324)\G
Empty set (0.26 sec)

可以看到执行计划很糟糕,虽然是只扫描索引,但是做了全索引扫描,效率非常差。因为WHERE条件中包含了RAND(),使得MySQL把它当做变量来处理,无法用常量等值的方式查询,效率很低。

我们把常量改成取t_innodb_random表的最大id值,再乘以RAND()求得随机数后检索看看什么情况:

[yejr@imysql]> explain select id from t_innodb_random where id = round(rand()*(select max(id) from t_innodb_random))\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t_innodb_random
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 393345
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random where id = round(rand()*(select max(id) from t_innodb_random))\G
Empty set (0.27 sec)

可以看到,执行计划依然是全索引扫描,执行耗时也基本相当。

3、改造成普通子查询模式 ,这里有两次子查询

[yejr@imysql]> explain select id from t_innodb_random where id = (select round(rand()*(select max(id) from t_innodb_random)) as nid)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t_innodb_random
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 393345
Extra: Using where; Using index
*************************** 2. row ***************************
id: 3
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random where id = (select round(rand()*(select max(id) from t_innodb_random)) as nid)\G
Empty set (0.27 sec)

可以看到,执行计划也不好,执行耗时较慢。

4、改造成JOIN关联查询,不过最大值还是用常量表示

[yejr@imysql]> explain select id from t_innodb_random t1 join (select round(rand()*13241324) as id2) as t2 where t1.id = t2.id2\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: ref
possible_keys: idx_id
key: idx_id
key_len: 4
ref: const
rows: 1
Extra: Using where; Using index
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: No tables used

[yejr@imysql]> select id from t_innodb_random t1 join (select round(rand()*13241324) as id2) as t2 where t1.id = t2.id2\G
Empty set (0.00 sec)

这时候执行计划就非常完美了,和最开始的常量等值查询是一样的了,执行耗时也非常之快。
这种方法虽然很好,但是有可能查询不到记录,改造范围查找,但结果LIMIT 1就可以了:

[yejr@imysql]> explain select id from t_innodb_random where id > (select round(rand()*(select max(id) from t_innodb_random)) as nid) limit 1\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t_innodb_random
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 393345
Extra: Using where; Using index
*************************** 2. row ***************************
id: 3
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random where id > (select round(rand()*(select max(id) from t_innodb_random)) as nid) limit 1\G
*************************** 1. row ***************************
id: 1301
1 row in set (0.00 sec)

可以看到,虽然执行计划也是全索引扫描,但是因为有了LIMIT 1,只需要找到一条记录,即可终止扫描,所以效率还是很快的。

小结:
从数据库中随机取一条记录时,可以把RAND()生成随机数放在JOIN子查询中以提高效率。

5、再来看看用ORDRR BY RAND()方式一次取得多个随机值的方式:

[yejr@imysql]> explain select id from t_innodb_random order by rand() limit 1000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_innodb_random
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 393345
Extra: Using index; Using temporary; Using filesort

[yejr@imysql]> select id from t_innodb_random order by rand() limit 1000;
1000 rows in set (0.41 sec)

全索引扫描,生成排序临时表,太差太慢了。

6、把随机数放在子查询里看看:

[yejr@imysql]> explain select id from t_innodb_random where id > (select rand() * (select max(id) from t_innodb_random) as nid) limit 1000\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t_innodb_random
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 393345
Extra: Using where; Using index
*************************** 2. row ***************************
id: 3
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random where id > (select rand() * (select max(id) from t_innodb_random) as nid) limit 1000\G
1000 rows in set (0.04 sec)

嗯,提速了不少,这个看起来还不赖:)

7、仿照上面的方法,改成JOIN和随机数子查询关联

[yejr@imysql]> explain select id from t_innodb_random t1 join (select rand() * (select max(id) from t_innodb_random) as nid) t2 on t1.id > t2.nid limit 1000\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: range
possible_keys: idx_id
key: idx_id
key_len: 4
ref: NULL
rows: 196672
Extra: Using where; Using index
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: No tables used
*************************** 4. row ***************************
id: 3
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random t1 join (select rand() * (select max(id) from t_innodb_random) as nid) t2 on t1.id > t2.nid limit 1000\G
1000 rows in set (0.00 sec)

可以看到,全索引检索,发现符合记录的条件后,直接取得1000行,这个方法是最快的。

综上,想从MySQL数据库中随机取一条或者N条记录时,最好把RAND()生成随机数放在JOIN子查询中以提高效率。
上面说了那么多的废话,最后简单说下,就是把下面这个SQL:

SELECT id FROM table ORDER BY RAND() LIMIT n;

改造成下面这个:

SELECT id FROM table t1 JOIN (SELECT RAND() * (SELECT MAX(id) FROM table) AS nid) t2 ON t1.id > t2.nid LIMIT n;

如果想要达到完全随机,还可以改成下面这种写法:

SELECT id FROM table t1 JOIN (SELECT round(RAND() * (SELECT MAX(id) FROM table)) AS nid FROM table LIMIT n) t2 ON t1.id = t2.nid;

就可以享受在SQL中直接取得随机数了,不用再在程序中构造一串随机数去检索了。

Percona Thread Pool性能基准测试

MySQL从5.5.16开始,在MySQL的商业化版本中将Thread Pool作为plugin提供官方功能支持。后来MariaDB也实现了这一功能,Percona也跟进实现了。从这几天对Percona 5.6.16版本做了下thread pool对比测试,试图找到较为合适的配置参数。

下面是几个测试模式对比:

模式 配置参数
Percona 5.6.16-nothp 未开启 thread pool 模式
CASE0-thp(128)-oversub(16)-max(2048) thread_handling = pool-of-threads
thread_pool_size = 128
thread_pool_oversubscribe = 16
thread_pool_max_threads = 2048
CASE1-thp(default) thread_handling = pool-of-threads
其他默认设置
CASE2-thp(default)-oversub(10) thread_handling = pool-of-threads
thread_pool_oversubscribe = 10
其他默认设置
CASE3-thp(default)-oversub(10)-max(10000) thread_handling = pool-of-threads
thread_pool_oversubscribe = 10
thread_pool_max_threads = 100000
其他默认设置
CASE4-thp(default)-oversub(16) thread_handling = pool-of-threads
thread_pool_oversubscribe = 16
其他默认设置
CASE5-thp(128)-oversub(16)-max(100000) thread_handling = pool-of-threads
thread_pool_size = 128
thread_pool_oversubscribe = 16
thread_pool_max_threads = 100000

仍然采用tpcc-mysql这个测试工具,基准值:

测试Warehouse数: 100
warmup time: 60s
run time: 1200s
并发线程数: 64 ~ 1920

测试环境信息:

测试机 DELL PE R710
CPU E5620  @ 2.40GHz(4 core, 8 threads, L3 Cache 12 MB) * 2
内存 32G(4G * 8)
RAID卡 PERC H700 Integrated, 512MB, BBU, 12.10.1-0001
系统 Red Hat Enterprise Linux Server release 6.4 (Santiago)
内核 2.6.32-358.el6.x86_64 #1 SMP
raid级别 raid 0
文件系统 xfs
硬盘 SSD: Intel 520系列SSD, 800G * 1

Percona版本号:5.6.16-64.2-rel64.2-log Percona Server with XtraDB (GPL), Release rel64.2, Revision 569,Percona相关的关键配置有:

innodb_buffer_pool_size = 26G
innodb_flush_log_at_trx_commit = 1

测试脚本可参考:MySQL压力测试经验

测试结果见下:

Percona-Thread-Pool测试-20140701.png

针对这个测试结果,我们可以得到一些结论:

 

1、通常地,只需要开启 pool-of-threads 模式就可以;
2、可以根据实际压力情况,适当调整 thread_pool_oversubscribe 选项以提升 TPS,这个选项值设置范围一般在 3~20;
3、thread-pool-size默认值是逻辑CPU个数,最大值是 128,不建议调整或显式设置,如果显式设定 thread-pool-size 的值,可能会带来反效果;
4、thread_pool_max_threads 默认值是 100000,强烈不建议修改。

综上,对于Thread Pool,我们一般建议设置下面2个选项就足够了:

thread_handling = pool-of-threads
thread_pool_oversubscribe = 10 #这个值建议在3~20间,不清楚的话,无需设置

备注:启用Thread Pool后,想要终止某个查询的话,要这么写KILL QUERY connection_id,而不是写成 KILL connection_id,否则就会导致整个连接被KILL。

如果还有什么问题,欢迎加入我的QQ群(272675472)讨论。