[MySQL FAQ]系列 — 你所不知的table is full那些事

当我们要写入新数据而发生“The table is full”告警错误时,先不要着急,按照下面的思路来逐步分析即可:
1、查看操作系统以及MySQL的错误日志文件
确认操作系统的文件系统没有报错,并且MySQL的错误日志文件中是否有一些最直观的可见的错误提示。
有可能是数据库文件超过操作系统层的文件大小限制,比如fat/fat32以及低版本的Linux,文件最大不可以大于2G(最大扩展到4G),这就需要转换fat32为NTFS,或升级Linux版本。

2、确认磁盘空间没有满
执行 df -h 查看剩余磁盘空间,如果发现磁盘空间确实已经用完,则尽快删除不需要的文件。

如果通过 du 计算各个目录的总和却发现根本不会用完磁盘空间时,就需要注意了,可能是某个被删除的文件还没完全释放,导致 df 看起来已经用完,但 du 却又统计不到。
这时候可以执行 lsof | grep -i deleted 找到被删除的大文件,将其对应的进程杀掉,释放该文件描述符。

如果该进程不能被杀掉,例如是 mysqld 进程在占用的话,可以在 MySQL 里找到是哪个内部线程在用,停止该线程即可。

曾经发生过这样一个例子:
用vim打开MySQL的slow query log,退出时选择了 “wq” 指令,也就是保存退出,结果悲剧发生了。
因为在其打开的那段时间内,slow query log有新日志产生,会持续写入,但他退出时采用保存退出的方式,变成了一个“新”文件(或者说新文件句柄 file handler),这个“新”文件无法被mysqld进程识别,
mysqld进程依旧将slow query log写入到原来它打开的那个文件(或者说文件句柄)里,该日志文件在持续增长,但手工保存退出的文件却再也不增长了,直接查看文件看不出任何异常。
这时候只能用 lsof -p `pidof mysqld` 才能看到该文件。
解决方法很简单,将原来的文件备份一下,执行下面的指令:

FLUSH SLOW LOGS;

备注:MySQL 5.5开始才支持 BINARY/ENGINE/ERROR/GENERAL/RELAY/SLOW 等关键字,之前的版本只能刷新全部日志。

3、确认数据表状态

  • 如果是MyISAM引擎

默认配置下,MyISAM引擎最大可支持256TB(myisam_data_pointer_size = 6,256^6 = 256TB),除非操作系统层有限制。
在MySQL5.0中,MyISAM引擎行记录默认是动态长度,单表最大可达256TB,MyISAM行指针(myisam_data_pointer_size)长度为6字节。
在这之前,MyISAM行指针默认长度为4字节,只支持4GB的数据。改行指针最大值可设为8字节。
在行指针设置较小不够用的时候,为提高MyISAM表最大容量,可以修改表定义设定MAX_ROWS的值:

ALTER TABLE `xx` ENGINE=MyISAM MAX_ROWS=nn

备注:表定义中,AVG_ROW_LENGTH 属性定义的是 BLOB/TEXT 字段类型的最大长度。

  • 如果是InnoDB引擎

ibdata*共享表空间最后一个文件没有设置成自增长,或者超过32位系统的单文件大小限制。
解决方法:
1、ibdata*的最后一个文件(非最后一个文件无法设置为自动增长)设置成自动增长;
2、检查操作系统,迁移到64位操作系统下;
3、转成独立表空间;
4、删除历史数据,重整表空间;

  • 如果是MEMORY引擎

1、适当提高max_heap_table_size设置(注意该值是会话级别,不要设置过大,例如1GB,一般不建议超过256MB);
2、执行ALTER TABLE t_mem ENGINE=MEMORY; 重整表空间,否则无法写入新数据
3、删除部分历史数据或者直接清空,重整表空间;
4、设置 big_tables = 1,将所有临时表存储在磁盘,而非内存中,缺点是如果某个SQL执行时需要用到临时表,则性能会差很多;

顺便说下,如果数据表有一列自增INT做主键,但是该ID值达到了INT最大值的话,MyISAM、MEMORY、InnoDB三种引擎的告警信息是不一样的。
InnoDB引擎的告警信息类似这样:
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

而MyISAM和MEMORY引擎则都是这样:
ERROR 1062 (23000): Duplicate entry ’4294967295′ for key ‘PRIMARY’

参考

MySQL手册:B.5.2.12 The table is full

[MySQL FAQ]系列 — 如何安全地关闭MySQL实例

本文分析了mysqld进程关闭的过程,以及如何安全、缓和地关闭MySQL实例,对这个过程不甚清楚的同学可以参考下。

关闭过程:

  • 1、发起shutdown,发出  SIGTERM信号
  • 2、有必要的话,新建一个关闭线程(shutdown thread)

如果是客户端发起的关闭,则会新建一个专用的关闭线程

如果是直接收到 SIGTERM 信号进行关闭的话,专门负责信号处理的线程就会负责关闭工作,或者新建一个独立的线程负责这个事

当无法创建独立的关闭线程时(例如内存不足),MySQL Server会发出类似下面的告警信息:

Error: Can’t create thread to kill server

  • 3、MySQL Server不再响应新的连接请求

关闭TCP/IP网络监听,关闭Unix Socket等渠道

  • 4、逐渐关闭当前的连接、事务

空闲连接,将立刻被终止;

当前还有事务、SQL活动的连接,会将其标识为 killed,并定期检查其状态,以便下次检查时将其关闭;(参考 KILL 语法)

当前有活跃事务的,该事物会被回滚,如果该事务中还修改了非事务表,则已经修改的数据无法回滚,可能只会完成部分变更;

如果是Master/Slave复制场景里的Master,则对复制线程的处理过程和普通线程也是一样的;

如果是Master/Slave复制场景里的Slave,则会依次关闭IO、SQL线程,如果这2个线程当前是活跃的,则也会加上 killed 标识,然后再关闭;

Slave服务器上,SQL线程是允许直接停止当前的SQL操作的(为了避免复制问题),然后再关闭该线程;

在MySQl 5.0.80及以前的版本里,如果SQL线程当时正好执行一个事务到中间,该事务会回滚;从5.0.81开始,则会等待所有的操作结束,除非用户发起KILL操作。

当Slave的SQL线程对非事务表执行操作时被强制 KILL了,可能会导致Master、Slave数据不一致;

  • 5、MySQL Server进程关闭所有线程,关闭所有存储引擎;

刷新所有表cache,关闭所有打开的表;

每个存储引擎各自负责相关的关闭操作,例如MyISAM会刷新所有等待写入的操作;InnoDB会将buffer pool刷新到磁盘中(从MySQL 5.0.5开始,如果innodb_fast_shutdown不设置为 2 的话),把当前的LSN记录到表空间中,然后关闭所有的内部线程。

  • 6、MySQL Server进程退出

关于KILL指令

从5.0开始,KILL 支持指定  CONNECTION | QUERY两种可选项:

  • KILL CONNECTION和原来的一样,停止回滚事务,关闭该线程连接,释放相关资源;
  • KILL QUERY则只停止线程当前提交执行的操作,其他的保持不变;

提交KILL操作后,该线程上会设置一个特殊的 kill标记位。通常需要一段时间后才能真正关闭线程,因为kill标记位只在特定的情况下才检查:

  • 1、执行SELECT查询时,在ORDER BY或GROUP BY循环中,每次读完一些行记录块后会检查 kill标记位,如果发现存在,该语句会终止;
  • 2、执行ALTER TABLE时,在从原始表中每读取一些行记录块后会检查 kill 标记位,如果发现存在,该语句会终止,删除临时表;
  • 3、执行UPDATE和DELETE时,每读取一些行记录块并且更新或删除后会检查 kill 标记位,如果发现存在,该语句会终止,回滚事务,若是在非事务表上的操作,则已发生变更的数据不会回滚;
  • 4、GET_LOCK() 函数返回NULL;
  • 5、INSERT DELAY线程会迅速内存中的新增记录,然后终止;
  • 6、如果当前线程持有表级锁,则会释放,并终止;
  • 7、如果线程的写操作调用在等待释放磁盘空间,则会直接抛出“磁盘空间满”错误,然后终止;
  • 8、当MyISAM表在执行REPAIR TABLE 或 OPTIMIZE TABLE 时被 KILL的话,会导致该表损坏不可用,指导再次修复完成。

安全关闭MySQL几点建议

想要安全关闭 mysqld 服务进程,建议按照下面的步骤来进行:

  • 0、用具有SUPER、ALL等最高权限的账号连接MySQL,最好是用 unix socket 方式连接;
  • 1、在5.0及以上版本,设置innodb_fast_shutdown = 1,允许快速关闭InnoDB(不进行full purge、insert buffer merge),如果是为了升级或者降级MySQL版本,则不要设置;
  • 2、设置innodb_max_dirty_pages_pct = 0,让InnoDB把所有脏页都刷新到磁盘中去;
  • 3、设置max_connections和max_user_connections为1,也就最后除了自己当前的连接外,不允许再有新的连接创建;
  • 4、关闭所有不活跃的线程,也就是状态为Sleep  且 Time 大于 1 的线程ID;
  • 5、执行 SHOW PROCESSLIST  确认是否还有活跃的线程,尤其是会产生表锁的线程,例如有大数据集的SELECT,或者大范围的UPDATE,或者执行DDL,都是要特别谨慎的;
  • 6、执行 SHOW ENGINE INNODB STATUS 确认History list length的值较低(一般要低于500),也就是未PURGE的事务很少,并且确认Log sequence number、Log flushed up to、Last checkpoint at三个状态的值一样,也就是所有的LSN都已经做过检查点了;
  • 7、然后执行FLUSH LOCKAL TABLES 操作,刷新所有 table cache,关闭已打开的表(LOCAL的作用是该操作不记录BINLOG);
  • 8、如果是SLAVE服务器,最好是先关闭 IO_THREAD,等待所有RELAY LOG都应用完后,再关闭 SQL_THREAD,避免 SQL_THREAD 在执行大事务被终止,耐心待其全部应用完毕,如果非要强制关闭的话,最好也等待大事务结束后再关闭SQL_THREAD;
  • 9、最后再执行 mysqladmin shutdown。
  • 10、紧急情况下,可以设置innodb_fast_shutdown = 1,然后直接执行 mysqladmin shutdown 即可,甚至直接在操作系统层调用 kill 或者 kill -9 杀掉 mysqld 进程(在innodb_flush_log_at_trx_commit = 0 的时候可能会丢失部分事务),不过mysqld进程再次启动时,会进行CRASH RECOVERY工作,需要有所权衡。

啰嗦那么多,其实正常情况下执行 mysqladmin shutdown 就够了,如果发生阻塞,再参考上面的内容进行分析和解决吧,哈哈:)

MySQL 5.6 查询优化器新特性的“BUG”

最近碰到一个慢SQL问题,解决过程有点小曲折,和大家分享下。 SQL本身不复杂,表结构、索引也比较简单,不过个别字段存在于多个索引中。

CREATE TABLE `pre_forum_post` (
  `pid` int(10) unsigned NOT NULL,
  `fid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `tid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `first` tinyint(1) NOT NULL DEFAULT '0',
  `author` varchar(40) NOT NULL DEFAULT '',
  `authorid` int(10) unsigned NOT NULL DEFAULT '0',
  `subject` varchar(80) NOT NULL DEFAULT '',
  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
  `message` mediumtext NOT NULL,
  `useip` varchar(15) NOT NULL DEFAULT '',
  `invisible` tinyint(1) NOT NULL DEFAULT '0',
  `anonymous` tinyint(1) NOT NULL DEFAULT '0',
  `usesig` tinyint(1) NOT NULL DEFAULT '0',
  `htmlon` tinyint(1) NOT NULL DEFAULT '0',
  `bbcodeoff` tinyint(1) NOT NULL DEFAULT '0',
  `smileyoff` tinyint(1) NOT NULL DEFAULT '0',
  `parseurloff` tinyint(1) NOT NULL DEFAULT '0',
  `attachment` tinyint(1) NOT NULL DEFAULT '0',
  `rate` smallint(6) NOT NULL DEFAULT '0',
  `ratetimes` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `status` int(10) NOT NULL DEFAULT '0',
  `tags` varchar(255) NOT NULL DEFAULT '0',
  `comment` tinyint(1) NOT NULL DEFAULT '0',
  `replycredit` int(10) NOT NULL DEFAULT '0',
  `position` int(8) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`tid`,`position`),
  UNIQUE KEY `pid` (`pid`),
  KEY `fid` (`fid`),
  KEY `displayorder` (`tid`,`invisible`,`dateline`),
  KEY `first` (`tid`,`first`),
  KEY `new_auth` (`authorid`,`invisible`,`tid`),
  KEY `idx_dt` (`dateline`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


"root@localhost Fri Aug  1 11:59:56 2014 11:59:56 [test]>show table status like 'pre_forum_post'\G
*************************** 1. row ***************************
           Name: pre_forum_post
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 23483977
 Avg_row_length: 203
    Data_length: 4782024708
Max_data_length: 281474976710655
   Index_length: 2466093056
      Data_free: 0
 Auto_increment: 1
    Create_time: 2014-08-01 11:00:56
    Update_time: 2014-08-01 11:08:49
     Check_time: 2014-08-01 11:12:23
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 


mysql> show index from pre_forum_post;
+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| pre_forum_post |          0 | PRIMARY      |            1 | tid         | A         |      838713 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          0 | PRIMARY      |            2 | position    | A         |    23483977 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          0 | pid          |            1 | pid         | A         |    23483977 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | fid          |            1 | fid         | A         |        1470 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | displayorder |            1 | tid         | A         |      838713 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | displayorder |            2 | invisible   | A         |      869776 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | displayorder |            3 | dateline    | A         |    23483977 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | first        |            1 | tid         | A         |      838713 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | first        |            2 | first       | A         |     1174198 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | new_auth     |            1 | authorid    | A         |     1806459 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | new_auth     |            2 | invisible   | A         |     1956998 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | new_auth     |            3 | tid         | A         |    11741988 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | idx_dt       |            1 | dateline    | A         |    23483977 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

我们来看下这个SQL的执行计划:

mysql> explain select * from pre_forum_post where tid=7932612 and `invisible` in('0','-2') order by dateline  limit 15\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pre_forum_post
         type: index
possible_keys: PRIMARY,displayorder,first
          key: idx_dt
      key_len: 4
          ref: NULL
         rows: 14042
        Extra: Using where

可以看到执行计划比较奇怪,从几个可选的索引中,最终选择了 idx_dt,结果悲剧了,这个SQL执行耗时很长:

mysql> select * from pre_forum_post where tid=7932612 and `invisible` in('0','-2') order by dateline  limit 15;
15 rows in set (26.78 sec)

看下MySQL的会话状态值:Handler_read_next

| Handler_read_next          | 17274153 |

1700多万数据中选取15条记录,结果可想而知,非常慢。 我们强制指定比较靠谱的索引再看下:

mysql> explain select * from pre_forum_post force index(displayorder) where tid=7932612 and `invisible` in('0','-2') order by dateline  limit 15\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pre_forum_post
         type: range
possible_keys: displayorder
          key: displayorder
      key_len: 4
          ref: NULL
         rows: 46131
        Extra: Using index condition; Using filesort

看下实际执行的耗时:

mysql> select * from pre_forum_post force index(displayorder) where tid=7932612 and `invisible` in('0','-2') order by dateline  limit 15;
15 rows in set (0.08 sec)

尼玛,怎么可以这么快,查询优化器未免太坑爹了吧。 再看下MySQL的会话状态值:Handler_read_next

| Handler_read_next          | 31188 |

和不强制索引的情况相比,差了553倍! 所幸,5.6以上除了EXPLAIN外,还支持OPTIMIZER_TRACE,我们来观察下两种执行计划的区别,发现不强制指定索引时的执行计划有诈,会在最后判断到 ORDER BY 子句时,修改执行计划:

          {\
            "reconsidering_access_paths_for_index_ordering": {\
              "clause": "ORDER BY",\
              "index_order_summary": {\
                "table": "`pre_forum_post`",\
                "index_provides_order": true,\
                "order_direction": "asc",\
                "index": "idx_dt",\
                "plan_changed": true,\
                "access_type": "index_scan"\
              } /* index_order_summary */\
            } /* reconsidering_access_paths_for_index_ordering */\

而在前面analyzing_range_alternativesconsidered_execution_plans阶段,都认为其他几个索引也是可选择的,直到这里才给强X了,你Y 看起来像是MySQL 5.6查询优化器的bug了,GOOGLE了一下,还真发有人已经反馈过类似的问题: MySQL bug 70245: incorrect costing for range scan causes optimizer to choose incorrect index

看完才发现,其实不是神马BUG,而是原来从5.6开始,增加了一个选项叫eq_range_index_dive_limit 的高级货,这货大概的用途是: 在较多等值查询(例如多值的IN查询)情景中,预估可能会扫描的记录数,从而选择相对更合适的索引,避免所谓的index dive问题。

当面临下面两种选择时:

1、索引代价较高,但结果较为精确;
2、索引代价较低,但结果可能不够精确;

简单说,选项 eq_range_index_dive_limit 的值设定了 IN列表中的条件个数上线,超过设定值时,会将执行计划分支从 1 变成 2

该值默认为10,但社区众多人反馈较低了,因此在5.7版本后,将默认值调整为200了。

不过,今天我们这里的案例却是想反的,因为优化器选择了看似代价低但精确的索引,实际却选择了更低效的索引。 因此,我们需要将其阈值调低,尝试设置 eq_range_index_dive_limit = 2 后(上面的例子中,IN条件里有2个值),再看下新的查询计划:

mysql> set eq_range_index_dive_limit = 2;

mysql> explain select * from pre_forum_post where tid=7932612 and `invisible` in('0','-2') order by dateline  limit 15\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pre_forum_post
         type: range
possible_keys: PRIMARY,displayorder,first
          key: displayorder
      key_len: 4
          ref: NULL
         rows: 54
        Extra: Using index condition; Using filesort

卧槽,预估扫描记录数又降了557倍,相比最开始降了接近32万倍! 在这个案例中,虽然通过修改选项 eq_range_index_dive_limit 的阈值可以达到优化效果,但事实上更靠谱的做法是:直接删除 idx_dt 索引。 是的,没错,删除这个垃圾重复索引,因为实际上这个索引的用处不大,够坑爹吧~~

参考资料:
http://blog.163.com/li_hx/blog/static/18399141320147521735442/
http://mysqlserverteam.com/you-asked-for-it-new-default-for-eq_range_index_dive_limit/ https://www.facebook.com/note.php?note_id=10151533648715933
http://bugs.mysql.com/bug.php?id=70586
http://bugs.mysql.com/bug.php?id=67980
http://bugs.mysql.com/bug.php?id=70331

个人PPT分享

个人最近几年内整理过的PPT,都放在百度文库上了,大家可以看看 :)

M​y​S​Q​L​ ​t​p​c​h​测​试​工​具​简​要​手​册

高​效​L​i​n​u​x​ ​S​A​

P​C​服​务​器​阵​列​卡​管​理​简​易​手​册​

服​务​器​基​准​测​试

M​y​S​Q​L​数​据​库​设​计​、​优​化 

M​y​S​Q​L​之​设​计​、​优​化​、​运​维

 

[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;
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;

就可以享受在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)讨论。

迁移Zabbix数据库到TokuDB

背景介绍

线上的Zabbix数据库有几个大表数据量疯狂增长,单表已经超过500G,而且在早期也没做成分区表,后期维护非常麻烦。比如,想删除过期的历史数据,在原先的模式下,history、history_uint等几个大表是用 (itemid, clock) 两个字段做的联合主键,只用 clock 字段检索效率非常差。

TokuDB 是一个高性能、支持事务处理的 MySQL 和 MariaDB 的存储引擎。TokuDB 的主要特点是高压缩比,高 INSERT 性能,支持大多数在线修改索引、添加字段,特别适合像 Zabbix 这种高 INSERT,少 UPDATE 的应用场景。

迁移准备

欲使用 TokuDB 引擎,服务层可以选择和 MariaDB ,也可以选择 Percona ,鉴于我以往使用 Percona 的较多,因此本次也选择使用 Percona 版本集成 TokuDB 引擎。

当前最新版下载地址:http://www.percona.com/redir/downloads/Percona-Server-5.6/LATEST/binary/tarball/Percona-Server-5.6.17-rel66.0-608.TokuDB.Linux.x86_64.tar.gz

按照正常方式安装即可,配置文件中增加3行:

malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so
plugin-dir = /usr/local/mysql/lib/mysql/plugin/
plugin-load=ha_tokudb.so

如果不加载jemalloc,启动时就会有类似下面的报错:

[ERROR] TokuDB not initialized because jemalloc is not loaded
[ERROR] Plugin 'TokuDB' init function returned error.
[ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed.

并且,修改内核配置,禁用transparent_hugepage,不关闭的话可能会导致TokuDB内存泄露(建议写到 /etc/rc.local 中,重启后仍可生效):

echo never > /sys/kernel/mm/redhat_transparent_hugepage/defrag
echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

如果不修改内核设置,启动时就会有类似下面的报错:

Transparent huge pages are enabled, according to /sys/kernel/mm/redhat_transparent_hugepage/enabled
Transparent huge pages are enabled, according to /sys/kernel/mm/transparent_hugepage/enabled
[ERROR] TokuDB will not run with transparent huge pages enabled.
[ERROR] Please disable them to continue.
[ERROR] (echo never > /sys/kernel/mm/transparent_hugepage/enabled)
[ERROR]
[ERROR] ************************************************************
[ERROR] Plugin 'TokuDB' init function returned error.
[ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed.

然后,初始化数据库,启动即可。

我的服务器配置:E5-2620 * 2,64G内存,1T可用磁盘空间(建议datadir所在分区设置为xfs文件系统),下面是我使用的相关选项,仅供参考:

#
#my.cnf
# 
# Percona-5.6.17, TokuDB-7.1.6,用于Zabbix数据库参考配置
# 我的服务器配置:E5-2620 * 2,64G内存,1T可用磁盘空间(建议datadir所在分区设置为xfs文件系统)
# TokuDB版本:Percona-5.6.17, TokuDB-7.1.6(插件加载模式)
# 
# created by yejr(http://imysql.com), 2014/06/24
# 
[client]
port            = 3306
socket          = mysql.sock
#default-character-set=utf8
 
[mysql]
prompt="\\u@\\h \\D \\R:\\m:\\s [\\d]>
#pager="less -i -n -S"
tee=/home/mysql/query.log
no-auto-rehash
 
[mysqld]
open_files_limit = 8192
max_connect_errors = 100000
 
#buffer & cache
table_open_cache = 2048
table_definition_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
 
#innodb
#只有部分小表保留InnoDB引擎,因此InnoDB Buffer Pool设置为1G基本上够了
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_file_per_table = 1
innodb_status_file = 1
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

#tokudb
malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so
plugin-dir = /usr/local/mysql/lib/mysql/plugin/
plugin-load=ha_tokudb.so
 
#把TokuDB datadir以及logdir和MySQL的datadir分开,美观点,也可以不分开,注释掉本行以及下面2行即可
tokudb-data-dir = /data/mysql/zabbix_3306/tokudbData
tokudb-log-dir = /data/mysql/zabbix_3306/tokudbLog
 
#TokuDB的行模式,建议用 FAST 就足够了,如果磁盘空间很紧张,建议用 SMALL
#tokudb_row_format = tokudb_small
tokudb_row_format = tokudb_fast
tokudb_cache_size = 44G
 
#其他大部分配置其实可以不用修改的,只需要几个关键配置即可
tokudb_commit_sync = 0
tokudb_directio = 1
tokudb_read_block_size = 128K
tokudb_read_buf_size = 128K

迁移过程

建议在一台全新的服务器上启动Percona(TokuDB)实例进程,初始化新的Zabbix数据库,直接将大表转成TokuDB引擎,并且开启分区模式。这样相比直接在线ALTER TABLE或者INSERT…SELECT导入数据都要来的快一些(我简单测试了下,差不多能快2-3倍,甚至更高)。

在做数据迁移时,建议在目标服务器上做库表结构初始化,在源服务器上采用分段方式导出,一个表导出多个备份文件,方便在恢复时可以并发导入。在导入时,并且记得临时关闭 binlog,最起码设置 sync_binlog = 0 以及 tokudb_commit_sync = 0,以提高导入速度。采用 mysqldump 增加 -w 参数即可实现根据条件分段导出,具体可参考上一次的文章:[MySQL FAQ]系列— mysqldump加-w参数备份,或者是用MySQLDumper

需要用到外键的表继续保留InnoDB引擎,其他表都可以转成TokuDB,history_str、trends、trends_uint、history、history_uint等几个大表是一定要转成TokuDB的,events由于需要用到外键,所以继续保留InnoDB引擎。

我将表结构初始化SQL脚本提供下载了,一份是没有采用分区表的,一份是采用分区表的,大家可自行选择。一般如果记录数超过1亿,就建议使用分区表,根据时间字段(clock)分区,方便后期维护,例如删除过期历史数据什么的。

收尾

剩下的基本没啥可做的了,就是观察下运行状态,是否还有个别慢查询堵塞。在我的环境中,一开始把items表也转成TokuDB了,结果有个画图的SQL执行计划不准确,非常慢。后来发现items表也需要用到外键,于是又转回InnoDB表,这个SQL也恢复正常了。

数据库初始化脚本我整理后提供下载了,大家可以直接使用。

附件1:不使用分区表附件2:使用分区表

适用版本:

Zabbix版本:Zabbix 2.2.0
TokuDB版本:Percona-5.6.17, TokuDB-7.1.6(插件加载模式)

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