[MySQL FAQ]系列 — MySQL无法启动例一

MySQL FAQ
插图来自网络并作简单加工,如果觉得不当还请及时告知 :)

【场景】某个打算用于slave新搭建的实例启动报错,启动过程中报告InnoDB数据页发生损坏。错误日志像下面这样:

150330 15:37:44 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mytest_3306
2015-03-30 15:37:45 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-30 15:37:45 5884 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2015-03-30 15:37:45 5884 [Note] Plugin ‘FEDERATED’ is disabled.
2015-03-30 15:37:45 5884 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-03-30 15:37:45 5884 [Note] InnoDB: The InnoDB memory heap is disabled
2015-03-30 15:37:45 5884 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-03-30 15:37:45 5884 [Note] InnoDB: Memory barrier is not used
2015-03-30 15:37:45 5884 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-03-30 15:37:45 5884 [Note] InnoDB: Using Linux native AIO
2015-03-30 15:37:45 5884 [Note] InnoDB: Using CPU crc32 instructions
2015-03-30 15:37:45 5884 [Note] InnoDB: Initializing buffer pool, size = 2.0G
2015-03-30 15:37:46 5884 [Note] InnoDB: Completed initialization of buffer pool
2015-03-30 15:37:47 5884 [Note] InnoDB: Highest supported file format is Barr.
2015-03-30 15:37:48 5884 [Warning] InnoDB: Resizing redo log from 3*32768 to 2*16384 pages, LSN=2740249189
2015-03-30 15:37:48 5884 [Warning] InnoDB: Starting to delete and rewrite log files.
2015-03-30 15:37:48 5884 [Note] InnoDB: Setting log file ./ib_logfile101 size to 256 MB
InnoDB: Progress in MB: 100 200
2015-03-30 15:37:49 5884 [Note] InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Progress in MB: 100 200
2015-03-30 15:37:50 5884 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2015-03-30 15:37:50 5884 [Warning] InnoDB: New log files created, LSN=2740249612
2015-03-30 15:37:50 5884 [Note] InnoDB: 128 rollback segment(s) are active.
2015-03-30 15:37:50 5884 [Note] InnoDB: Waiting for purge to start
2015-03-30 15:37:50 5884 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.21-rel69.0 started; log sequence number 2740249189
2015-03-30 15:37:50 5884 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ab27d3e4-d6af-11e4-8020-c81f66eeffa6.
150330 15:36:33 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mytest_3306
2015-03-30 15:37:50 5884 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ab27d3e4-d6af-11e4-8020-c81f66eeffa6.
2015-03-30 15:37:50 5884 [Note] RSA private key file not found: /data/mysql/mytest_3306//private_key.pem. Some authentication plugins will not work.
2015-03-30 15:37:50 5884 [Note] RSA public key file not found: /data/mysql/mytest_3306//public_key.pem. Some authentication plugins will not work.
2015-03-30 15:37:50 5884 [Note] Server hostname (bind-address): ’10.x.x.x'; port: 3306
2015-03-30 15:37:50 5884 [Note] – ’10.x.x.x’ resolves to ’10.x.x.x';
2015-03-30 15:37:50 5884 [Note] Server socket created on IP: ’10.x.x.x’.
2015-03-30 15:37:50 7f4ce4d68700 InnoDB: Error: page 32769 log sequence number 2740254202
InnoDB: is in the future! Current system log sequence number 2740249622.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
2015-03-30 15:37:50 5884 [Note] Event Scheduler: Loaded 0 events
2015-03-30 15:37:50 5884 [Note] /opt/Percona-Server-5.6.21-rel69.0-675.Linux.x86_64/bin/mysqld: ready for connections.
Version: ‘5.6.21-69.0-log’ socket: ‘/data/mysql/mytest_3306/mysql.sock’ port: 3306 Percona Server (GPL), Release 69.0, Revision 675
2015-03-30 15:37:58 7f4ce4d68700 InnoDB: Error: page 6327 log sequence number 2740254445
InnoDB: is in the future! Current system log sequence number 2740251356.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.

【分析】正常情况下,新部署的实例是不太可能出现InnoDB数据页损坏的。经了解,这个实例是采用xtrabackup工具从master备份过来做恢复的。

细心的同学,通过观察上面的日志,应该能从中发现一些蛛丝马迹。有几个地方需要引起注意:

1、版本是Percona Server 5.6.21;
2、刚启动就把InnoDB的redo log给resize了;
3、刚启动就发现InnoDB的page LSN和redo log中的不匹配;

之所以提醒大家注意上面的三点,并且把版本信息放在了第一条,是因为从5.6版本开始,InnoDB如如果发现当前的redo log文件大小和预设配置的redo log大小不一致的话,就会自动将其删除重建

写到这里,相信聪明的你应该已经想到什么了吧,没错,导致这个启动报错的原因是:从master上xtrabackup备份出来的innodb redo log大小和本地配置参数不一致,被删除重建,结果事务恢复失败,提示数据也损坏错误信息

【解决】修改slave本地配置文件,把下面几个InnoDB配置选项都修改成和在master上的一样,再次执行恢复启动即可。

innodb_data_file_path
innodb_log_file_size
innodb_log_files_in_group
innodb_file_per_table

[MySQL FAQ]系列 — pt-table-checksum工具使用报错一例

Percona Tollkit
图片来自Percona官网

今天同事在用 percona toolkit 工具中的 pt-table-checksum 对主从数据库进行校验,提交命令后,一直提示下面的信息:

Pausing because Threads_running=0

看字面意思是在提示当前活跃线程数为0,但为什么不继续执行呢。这个提示信息有点含糊其辞,该工具是用Perl写的,因此直接打开看脚本跟踪一下,大概就明白怎么回事了,原来是这个工具有负载保护机制,避免运行时对线上数据库产生影响。

和这个机制相关的参数名是: –max-load,其类型是:Array,用法是一个或多个 variables = value 组成的判断条件,然后根据这个规则判断某些条件是否超标。例如,设定 –max-load=”Threads_running=25″,意思是当前活跃线程数如果超过25,就暂停 checksum 工作,直到活跃线程数低于 25。

因此,在我们这个案例中,想要强制让 table-checksum 继续工作的话,可以设定 –max-load 的值,例如:

pt-table-checksum --max-load="Threads_running=25" ...其他选项...

或者

pt-table-checksum --max-load="Threads_connected=25" ...其他选项...

 

前面的选项意思是判断活跃线程数不要超过25个,后面的选项意思是当前打开的线程数不要超过25个。

下面是 pt-table-checksum 帮助手册里的一段话:

–max-load
type: Array; default: Threads_running=25; group: Throttle

Examine SHOW GLOBAL STATUS after every chunk, and pause if any status variables are higher than the threshold. The option accepts a comma-sep-
arated list of MySQL status variables to check for a threshold. An optional “=MAX_VALUE” (or “:MAX_VALUE”) can follow each variable. If not
given, the tool determines a threshold by examining the current value and increasing it by 20%.

For example, if you want the tool to pause when Threads_connected gets too high, you can specify “Threads_connected”, and the tool will check
the current value when it starts working and add 20% to that value. If the current value is 100, then the tool will pause when Threads_con-
nected exceeds 120, and resume working when it is below 120 again. If you want to specify an explicit threshold, such as 110, you can use
either “Threads_connected:110″ or “Threads_connected=110″.

The purpose of this option is to prevent the tool from adding too much load to the server. If the checksum queries are intrusive, or if they
cause lock waits, then other queries on the server will tend to block and queue. This will typically cause Threads_running to increase, and the
tool can detect that by running SHOW GLOBAL STATUS immediately after each checksum query finishes. If you specify a threshold for this vari-
able, then you can instruct the tool to wait until queries are running normally again. This will not prevent queueing, however; it will only
give the server a chance to recover from the queueing. If you notice queueing, it is best to decrease the chunk time.

[MySQL优化案例]系列 — slave延迟很大优化方法

mysql replication
备注:插图来自网络搜索,如果觉得不当还请及时告知 :)

一般而言,slave相对master延迟较大,其根本原因就是slave上的复制线程没办法真正做到并发。简单说,在master上是并发模式(以InnoDB引擎为主)完成事务提交的,而在slave上,复制线程只有一个sql thread用于binlog的apply,所以难怪slave在高并发时会远落后master。

ORACLE MySQL 5.6版本开始支持多线程复制,配置选项 slave_parallel_workers 即可实现在slave上多线程并发复制。不过,它只能支持一个实例下多个 database 间的并发复制,并不能真正做到多表并发复制。因此在较大并发负载时,slave还是没有办法及时追上master,需要想办法进行优化。

另一个重要原因是,传统的MySQL复制是异步(asynchronous)的,也就是说在master提交完后,才在slave上再应用一遍,并不是真正意义上的同步。哪怕是后来的Semi-sync Repication(半同步复制),也不是真同步,因为它只保证事务传送到slave,但没要求等到确认事务提交成功。既然是异步,那肯定多少会有延迟。因此,严格意义上讲,MySQL复制不能叫做MySQL同步(处女座的面试官有可能会在面试时把说成MySQL同步的一律刷掉哦)。

另外,不少人的观念里,slave相对没那么重要,因此就不会提供和master相同配置级别的服务器。有的甚至不但使用更差的服务器,而且还在上面跑多实例。

综合这两个主要原因,slave想要尽可能及时跟上master的进度,可以尝试采用以下几种方法:

  1. 采用MariaDB发行版,它实现了相对真正意义上的并行复制,其效果远比ORACLE MySQL好的很多。在我的场景中,采用MariaDB作为slave的实例,几乎总是能及时跟上master。如果不想用这个版本的话,那就老实等待官方5.7大版本发布吧;
    关于MariaDB的Parallel Replication具体请参考:Replication and Binary Log Server System Variables#slave_parallel_threads – MariaDB Knowledge Base
  2. 每个表都要显式指定主键,如果没有指定主键的话,会导致在row模式下,每次修改都要全表扫描,尤其是大表就非常可怕了,延迟会更严重,甚至导致整个slave库都被挂起,可参考案例:mysql主键的缺少导致备库hang
  3. 应用程序端多做些事,让MySQL端少做事,尤其是和IO相关的活动,例如:前端通过内存CACHE或者本地写队列等,合并多次读写为一次,甚至消除一些写请求;
  4. 进行合适的分库、分表策略,减小单库单表复制压力,避免由于单库单表的的压力导致整个实例的复制延迟;
  5. 其他提高IOPS性能的几种方法,根据效果优劣,我做了个简单排序:
    • 更换成SSD,或者PCIe SSD等IO设备,其IOPS能力的提升是普通15K SAS盘的数以百倍、万倍,甚至几十万倍计;
    • 加大物理内存,相应提高InnoDB Buffer Pool大小,让更多热数据放在内存中,降低发生物理IO的频率;
    • 调整文件系统为 XFS 或 ReiserFS,相比ext3可以极大程度提高IOPS能力。在高IOPS压力下,相比ext4有更稳健的IOPS表现(有人认为 XFS 在特别的场景下会有很大的问题,但我们除了剩余磁盘空间少于10%时引发丢数据外,其他的尚未遇到);
    • 调整RAID级别为raid 1+0,它相比raid1、raid5等更能提高IOPS性能。如果已经全部是SSD设备了,可以2块盘做成RAID 1,或者多快盘做成RAID 5(并且可以设置全局热备盘,提高阵列容错性),甚至有些土豪用户直接将多块SSD盘组成RAID 50;
    • 调整RAID的写cache策略为WB或FORCE WB,详情请参考:常用PC服务器阵列卡、硬盘健康监控 以及 PC服务器阵列卡管理简易手册
    • 调整内核的io scheduler,优先使用deadline,如果是SSD,则可以使用noop策略,相比默认的cfq,个别情况下对IOPS的性能提升至少是数倍的。

其他更多方法,欢迎大家帮忙补充 :)

[MySQL优化案例]系列 — discuz!热帖翻页优化

discuz-logo
备注:插图来自discuz!官方LOGO,如果觉得不当还请及时告知 :)

写在前面:discuz!作为首屈一指的社区系统,为广大站长提供了一站式网站解决方案,而且是开源的(虽然部分代码是加密的),它为这个垂直领域的行业发展作出了巨大贡献。尽管如此,discuz!系统源码中,还是或多或少有些坑。其中最著名的就是默认采用MyISAM引擎,以及基于MyISAM引擎的抢楼功能session表采用memory引擎等,可以参考后面几篇历史文章。本次我们要说说discuz!在应对热们帖子翻页逻辑功能中的另一个问题。

在我们的环境中,使用的是 MySQL-5.6.6 版本。

在查看帖子并翻页过程中,会产生类似下面这样的SQL:

mysql> desc SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline DESC LIMIT 15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: tid,displayorder,first
 key: displayorder
 key_len: 3
 ref: const
 rows: 593371
 Extra: Using index condition; Using where; Using filesort

这个SQL执行的代价是:

-- 根据索引访问行记录次数,总体而言算是比较好的状态
| Handler_read_key           | 16     |

-- 根据索引顺序访问下一行记录的次数,通常是因为根据索引的范围扫描,或者全索引扫描,总体而言也算是比较好的状态
| Handler_read_next          | 329881 |

-- 按照一定顺序读取行记录的总次数。如果需要对结果进行排序,该值通常会比较大。当发生全表扫描或者多表join无法使用索引时,该值也会比较大
| Handler_read_rnd           | 15     |

而当遇到热帖需要往后翻很多页时,例如:

mysql> desc SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline  LIMIT 129860, 15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: displayorder
 key: displayorder
 key_len: 3
 ref: const
 rows: 593371
 Extra: Using where; Using filesort

这个SQL执行的代价则变成了(可以看到Handler_read_key、Handler_read_rnd大了很多):

| Handler_read_key           | 129876 | -- 因为前面需要跳过很多行记录
| Handler_read_next          | 329881 | -- 同上
| Handler_read_rnd           | 129875 | -- 因为需要先对很大一个结果集进行排序

可见,遇到热帖时,这个SQL的代价会非常高。如果该热帖被大量的访问历史回复,或者被搜素引擎一直反复请求并且历史回复页时,很容易把数据库服务器直接压垮。

小结:这个SQL不能利用 `displayorder` 索引排序的原因是,索引的第二个列 `invisible` 采用范围查询(RANGE),导致没办法继续利用联合索引完成对 `dateline` 字段的排序需求(而如果是 WHERE tid =? AND invisible IN(?, ?) AND dateline =? 这种情况下是完全可以用到整个联合索引的,注意下二者的区别)。

知道了这个原因,相应的优化解决办法也就清晰了:
创建一个新的索引 idx_tid_dateline,它只包括 tid、dateline 两个列即可(根据其他索引的统计信息,item_type 和 item_id 的基数太低,所以没包含在联合索引中。当然了,也可以考虑一并加上)。

我们再来看下采用新的索引后的执行计划:

mysql> desc SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline  LIMIT 15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: tid,displayorder,first,idx_tid_dateline
 key: idx_tid_dateline
 key_len: 3
 ref: const
 rows: 703892
 Extra: Using where

可以看到,之前存在的 Using filesort 消失了,可以通过索引直接完成排序了。

不过,如果该热帖翻到较旧的历史回复时,相应的SQL还是不能使用新的索引:

mysql> desc SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: tid,displayorder,first,idx_tid_dateline
 key: displayorder
 key_len: 3
 ref: const
 rows: 593371
 Extra: Using where; Using filesort

对比下如果建议优化器使用新索引的话,其执行计划是怎样的:

mysql> desc SELECT * FROM pre_forum_post use index(idx_tid_dateline) WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline  LIMIT 129860,15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: idx_tid_dateline
 key: idx_tid_dateline
 key_len: 3
 ref: const
 rows: 703892
 Extra: Using where

可以看到,因为查询优化器认为后者需要扫描的行数远比前者多了11万多,因此认为前者效率更高。

事实上,在这个例子里,排序的代价更高,因此我们要优先消除排序,所以应该强制使用新的索引,也就是采用后面的执行计划,在相应的程序中指定索引。

最后,我们来看下热帖翻到很老的历史回复时,两个执行计划分别的profiling统计信息对比:

1、采用旧索引(displayorder):

mysql> SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15;

​#查看profiling结果
 | starting             | 0.020203 |
 | checking permissions | 0.000026 |
 | Opening tables       | 0.000036 |
 | init                 | 0.000099 |
 | System lock          | 0.000092 |
 | optimizing           | 0.000038 |
 | statistics           | 0.000123 |
 | preparing            | 0.000043 |
 | Sorting result       | 0.000025 |
 | executing            | 0.000023 |
 | Sending data         | 0.000045 |
 | Creating sort index  | 0.941434 |
 | end                  | 0.000077 |
 | query end            | 0.000044 |
 | closing tables       | 0.000038 |
 | freeing items        | 0.000056 |
 | cleaning up          | 0.000040 |

2、如果是采用新索引(idx_tid_dateline):

mysql> SELECT * FROM pre_forum_post use index(idx_tid_dateline) WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15;

​#对比查看profiling结果
 | starting             | 0.000151 |
 | checking permissions | 0.000033 |
 | Opening tables       | 0.000040 |
 | init                 | 0.000105 |
 | System lock          | 0.000044 |
 | optimizing           | 0.000038 |
 | statistics           | 0.000188 |
 | preparing            | 0.000044 |
 | Sorting result       | 0.000024 |
 | executing            | 0.000023 |
 | Sending data         | 0.917035 |
 | end                  | 0.000074 |
 | query end            | 0.000030 |
 | closing tables       | 0.000036 |
 | freeing items        | 0.000049 |
 | cleaning up          | 0.000032 |

可以看到,效率有了一定提高,不过不是很明显,因为确实需要扫描的数据量更大,所以 Sending data 阶段耗时更多。

这时候,我们可以再参考之前的一个优化方案:[MySQL优化案例]系列 — 分页优化

然后可以将这个SQL改写成下面这样:

mysql> EXPLAIN SELECT * FROM pre_forum_post t1 INNER JOIN (
 SELECT id FROM pre_forum_post use index(idx_tid_dateline) WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY
 dateline  LIMIT 129860,15) 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: 129875
 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: pre_forum_post
 type: ref
 possible_keys: idx_tid_dateline
 key: idx_tid_dateline
 key_len: 3
 ref: const
 rows: 703892
 Extra: Using where

再看下这个SQL的 profiling 统计信息:

| starting             | 0.000209 |
| checking permissions | 0.000026 |
| checking permissions | 0.000026 |
| Opening tables       | 0.000101 |
| init                 | 0.000062 |
| System lock          | 0.000049 |
| optimizing           | 0.000025 |
| optimizing           | 0.000037 |
| statistics           | 0.000106 |
| preparing            | 0.000059 |
| Sorting result       | 0.000039 |
| statistics           | 0.000048 |
| preparing            | 0.000032 |
| executing            | 0.000036 |
| Sending data         | 0.000045 |
| executing            | 0.000023 |
| Sending data         | 0.225356 |
| end                  | 0.000067 |
| query end            | 0.000028 |
| closing tables       | 0.000023 |
| removing tmp table   | 0.000029 |
| closing tables       | 0.000044 |
| freeing items        | 0.000048 |
| cleaning up          | 0.000037 |

可以看到,效率提升了1倍以上,还是挺不错的。

最后说明下,这个问题只会在热帖翻页时才会出现,一般只有1,2页回复的帖子如果还采用原来的执行计划,也没什么问题。

因此,建议discuz!官方修改或增加下新索引,并且在代码中判断是否热帖翻页,是的话,就强制使用新的索引,以避免性能问题。

扩展阅读:

1、MySQL优化 之 Discuz论坛优化
2、MySQL优化 之 Discuz论坛优化 — 续
3、MySQL优化 之 Discuz论坛MySQL通用优化

最后稍微吐槽一下:最近几天遇到了几起关于MySQL查询优化器的BUG,挺让人摸不着头脑的 :(

[MySQL FAQ]系列 — 为什么要关闭query cache,如何关闭

mysql-query-cache-seems-so-beautiful
备注:插图来自淘宝苏普的博客并保留水印,如果觉得不当还请及时告知 :)

写在前面:MySQL的query cache大部分情况下其实只是鸡肋而已,建议全面禁用。当然了,或许在你的场景下还是挺好的,还能发挥作用,那就继续使用吧,把本文当做参考就好。

不过,可能有的人人为只需要把 query_cache_size 大小调整为 0 就可以了,可以忽略 query_cache_type 参数的值,反正它也是可以在线调整的。

事实果真如此吗?让我们来实际模拟测试下就知道了。

我们模拟了以下几种场景:

1、初始化时,同时设置 query_cache_size 和 query_cache_type 的值为 0;

2、初始化时,设置 query_cache_size = 0,但设置 query_cache_type = 1;

3、初始化时,设置 query_cache_size = 0,query_cache_type = 1,但是启动后立刻 修改 query_cache_type = 0

4、初始化时,设置 query_cache_size = 0,query_cache_type = 0,但是启动后立刻 修改 query_cache_type = 1

5、初始化时,设置 query_cache_size = xMB,query_cache_type = 1,但是启动后立刻 修改 query_cache_type = 0

 

经过测试,可以得到下面几个重要结论(详细测试过程请见最后):

1、想要彻底关闭query cache,务必在一开始就设置 query_cache_type = 0,即便是启动后将 query_cache_type 从 1 改成 0,也不行;

2、即便query_cache_size = 0,但 query_cache_type 非 0 的话,在实际环境中,可能会频繁发生 Waiting for query cache lock;

3、一开始就设置 query_cache_type = 0 的话,没有办法在运行 过程中再次动态启用,反过来则可以。也就是说,一开始是启用 query cache 的, 在运行过程中将其关闭,但事实上仍然会发生  Waiting for query cache lock,并没有真正的关闭;

 

关于query cache的延伸阅读,请见:

1、我的前同事waterbin帅哥的悲惨经历:MySQL Troubleshoting:Waiting on query cache mutex
2、淘宝苏普的旧文:Query Cache,看上去很美

 

详细测试过程:

一、测试方法

采用sysbench模拟并发oltp请求:

sysbench --test=tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on --num-threads=64 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=1800 --max-requests=0 run

二、具体几种测试模式

1、一直关闭QC(query cache的简写,下同),即  query_cache_size = 0, query_cache_type = 0

测试过程中,一直都没有和query cache lock相关的状态出现,结果tps:2295.34

 

2、启用QC,但QC size 设置为 0,即:query_cache_size = 0,query_cache_type = 1

测试过程中,一直有 Waiting for query cache lock 状态出现,结果tps:2272.52

 

3、启用QC,但QC size为0,但启动时立刻关闭QC,即初始化时 query_cache_size = 0,query_cache_type = 1,启动后立刻修改 query_cache_type = 0

测试过程中,也一直有 Waiting for query cache lock 状态出现,结果tps:2311.54

 

4、关闭QC,但启动后立刻启用QC,即初始化时 query_cache_size = 0,query_cache_type = 0,启动后立刻修改 query_cache_type = 1

这时,会提示报错信息:

失败:ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

也就是说,如果一开始就关闭 QC 的话,是没办法在运行过程中动态再启用QC的。

 

5、启用QC,并设置QC size为256M,即 query_cache_size = 256M,query_cache_type = 1

这种情况下,在测试过程中一直有 Waiting for query cache lock 状态出现,并且结果tps也很差,只有 1395.39(几个案例中最差的一种)

 

6、启用QC,设置QC size为256M,但启动后立刻关闭QC,即 query_cache_size = 256M,query_cache_type = 1,启动后立刻修改 query_cache_type = 0

这种情况下,在测试过程中也一直有  Waiting for query cache lock 状态出现,结果tps:2295.79(在这个模式下,如果设置 query_cache_type = 2,效果也不佳)

 

第三种模式下,虽然看起来tps还不错,但毕竟上面只是简单模拟测试,实际情况下如果有频繁的query cache lock的话,tps肯定不会太好看。

因此,总的来说,想要获得较高tps的话,最好还是一开始就关闭QC,不要心存侥幸或者固守陈规。

[MySQL FAQ]系列 — 如何查看当前最新事务ID

InnoDB

写在前面:在个别时候可能需要查看当前最新的事务ID,以便做一些业务逻辑上的判断(例如利用事务ID变化以及前后时差,统计每次事务的响应时长等用途)。

通常地,我们有两种方法可以查看当前的事务ID:

1、执行SHOW ENGINE INNODB STATUS,查看事务相关信息

=====================================
150303 17:16:11 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
...
------------
TRANSACTIONS
Trx id counter 3359877657 -- 当前最大事务ID
Purge done for trx's n:o < 3359877468 undo n:o < 0 state: running
History list length 324
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started -- 该会话中执行SHOW ENGINE INNODB STATUS,不会产生事务,所以事务ID为0
MySQL thread id 4692367, OS thread handle 0x51103940, query id 677284426 xx.173ops.com 10.x.x.x yejr init
SHOW /*!50000 ENGINE*/ INNODB STATUS
---TRANSACTION 3359877640, not started --非活跃事务,还未开始
mysql tables in use 1, locked 0
MySQL thread id 4678384, OS thread handle 0x41a57940, query id 677284427 xx.173ops.com 10.x.x.x yejr System lock
select polinfo0_.Fid as Fid39_0_, ...

---TRANSACTION 3359877652, not started
MySQL thread id 4678383, OS thread handle 0x50866940, query id 677284420 xx.173ops.com 10.x.x.x yejr cleaning up

---TRANSACTION 3359877635, ACTIVE 1358 sec, thread declared inside InnoDB 5000 --活跃长事务,运行了1358秒还未结束,要引起注意,可能会导致大量锁等待发生
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
MySQL thread id 3120717, OS thread handle 0x529b4940, query id 677284351 xx.173ops.com 10.x.x.x yejr query end
insert into t_live_room ...

 

2、查看INFORMATION_SCHEMA.INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 三个表,通过这些信息能快速发现哪些事务在阻塞其他事务

#先查询 INNODB_TRX 表,看看都有哪些事务

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
*************************** 1. row ***************************
 trx_id: 17778 -- 当前事务ID
 trx_state: LOCK WAIT -- 处于锁等待状态,也就是等待其他会话释放锁资源
 trx_started: 2015-03-04 10:40:26
 trx_requested_lock_id: 17778:82:3:6 -- 欲请求的锁
 trx_wait_started: 2015-03-04 10:40:26
 trx_weight: 2 -- 大意是该锁影响了2行记录
 trx_mysql_thread_id: 657 -- processlist中的线程ID
 trx_query: update trx_fee set fee=rand()*1000 where id= 4
 trx_operation_state: starting index read
 trx_tables_in_use: 1
 trx_tables_locked: 1
 trx_lock_structs: 2
 trx_lock_memory_bytes: 360
 trx_rows_locked: 1
 trx_rows_modified: 0
 trx_concurrency_tickets: 0
 trx_isolation_level: REPEATABLE READ
 trx_unique_checks: 1
 trx_foreign_key_checks: 1
 trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
 trx_is_read_only: 0
 trx_autocommit_non_locking: 0
 *************************** 2. row ***************************
 trx_id: 17773
  trx_state: RUNNING
 trx_started: 2015-03-04 10:40:23
 trx_requested_lock_id: NULL
 trx_wait_started: NULL
 trx_weight: 10
 trx_mysql_thread_id: 656
 trx_query: NULL
 trx_operation_state: NULL
 trx_tables_in_use: 0
 trx_tables_locked: 0
 trx_lock_structs: 2
 trx_lock_memory_bytes: 360
 trx_rows_locked: 9
 trx_rows_modified: 8
 trx_concurrency_tickets: 0
 trx_isolation_level: REPEATABLE READ
 trx_unique_checks: 1
 trx_foreign_key_checks: 1
 trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
 trx_is_read_only: 0
 trx_autocommit_non_locking: 0

 

#再看 INNODB_LOCKS 表,看看都有什么锁

mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 17778:82:3:6 --当前锁ID
lock_trx_id: 17778 --该锁对应的事务ID
lock_mode: X -- 锁类型,排它锁X
lock_type: RECORD --锁范围,记录锁:record lock,其他锁范围:间隙锁:gap lock,或者next-key lock(记录锁+间隙锁)
lock_table: `test`.`trx_fee`
lock_index: PRIMARY --加载在哪个索引上的锁
lock_space: 82
lock_page: 3
lock_rec: 6
lock_data: 4
*************************** 2. row ***************************
lock_id: 17773:82:3:6
lock_trx_id: 17773
lock_mode: X
lock_type: RECORD
lock_table: `test`.`trx_fee`
lock_index: PRIMARY
lock_space: 82
lock_page: 3
lock_rec: 6
lock_data: 4

 

#最后看 INNODB_LOCK_WAITS 表,看看当前都有哪些锁等待

mysql> select * from information_schema.INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 17778 --请求锁的事务ID(等待方)
requested_lock_id: 17778:82:3:6 -- 请求锁ID
blocking_trx_id: 17773 -- 阻塞该锁的事务ID(当前持有方,待释放)
blocking_lock_id: 17773:82:3:6 -- 持有的锁ID

关于INFORMATION_SCHEMA中和InnoDB有关的表用途描述,可以查看手册:21.29 INFORMATION_SCHEMA Tables for InnoDB

 

3、利用percona分支的特性,查看当前最新事务ID,该特性从5.6.11-60.3版本开始引入,执行下面的2个命令即可查看

mysqladmin ext | grep Innodb_max_trx_id
或者
mysql> show global status like 'Innodb_max_trx_id';

最后,交代下问题的来源其实是这样的,有位朋友和我讨论问题,说在java连接池中,发现2个事务的事务ID是一样的,测试的SQL代码:

begin;update trx set un=rand() where id=round(rand()*10)+1;select * from information_schema.INNODB_TRX; commit;select sleep(0.01);begin;update trx set un=rand() where id=round(rand()*10)+1;select * from information_schema.INNODB_TRX;commit;

这串代码不能折行,中间的 sleep 停留 不能太大,也就是模拟足够快的情况下,检查2次事务的ID是否有变化。可以发现,时间足够短的话,2次查询到的事务ID是一样的,并没有发生变化。大家也可以在自己的环境下试试。

[MySQL FAQ]系列 — 使用mysqldump备份时为什么要加上 -q 参数

mysqldump

写在前面:我们在使用mysqldump备份数据时,请一定记住要加上 -q 参数,后果可能是很严重的,不要给自己挖坑哦。到底为什么呢,且听我慢慢道来!

先来看看 mysqldump –help 中,关于 -q 参数的解释:

-q, --quick         Don't buffer query, dump directly to stdout.

简言之,就是说加上 -q 后,不会把SELECT出来的结果放在buffer中,而是直接dump到标准输出中,顶多只是buffer当前行结果,正常情况下是不会超过 max_allowed_packet 限制的,它默认情况下是开启的。

如果关闭该参数,则会把SELECT出来的结果放在本地buffer中,然后再输出给客户端,会消耗更多内存。

在mysqldump.c中也能看到二者的对比(现在流行深入源码,虽然我不是专注开发的,找几行源码能力还尚存,用来装B的,大家知道就好,哈哈):

if (quick)
  res=mysql_use_result(sock);
else
  res=mysql_store_result(sock);

有理论,也要有实践不是,我们来看看在实际场景中,加不加 -q 的区别有多大。

部分备份(启用-q) 部分备份(禁用-q) 完整备份(启用-q) 完整备份(禁用-q)
备份总耗时 27.882秒 22.665秒 277.387秒 217.074秒
占用内存(含swap) 3056KB 2.5GB 3048KB 内存:12GBswap:305MB

可以看到,如果只是备份小量数据,足以放在空闲内存buffer中的话,禁用 -q 会快一些,但如果是大数据集,没办法完全hold在内存buffer中时,就会产生swap,效率反而更差,真是赔了夫人又折兵。

因此,如果使用mysqldump来备份数据时,建议总是加上 -q 参数,避免发生swap反而影响备份效率。

详细过程(有耐心的可以继续往下看)

1、全量备份:备份时不使用 -q 参数

mysqldump --quick=false -Smysql.sock -B yejr --tables t_yejr

#先看下一开始时的状态:
Mem:  32863040k total, 29338704k used,  3524336k free,   227632k buffers
Swap: 16777208k total,    23548k used, 16753660k free,  8200416k cached
PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
21986 root      20   0 6119m 5.9g 2192 S 20.6 18.9   0:21.69 mysqldump

#再看下备份结束后的状态,内存不够用,产生了swap
Mem:  32863040k total, 32521328k used,   341712k free,      440k buffers
Swap: 16777208k total,   336876k used, 16440332k free,   315192k cached
PID   USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+   COMMAND
21986 root      20   0 12.3g  12g  656 R 100.0 39.1   2:23.93 mysqldump

#最后看下备份总耗时
real    4m37.387s
user    2m2.731s
sys     0m24.608s

2、全量备份:备份时启用 -q 参数

mysqldump -Smysql.sock -B yejr --tables t_yejr

#先看下一开始时的状态:
Mem:  32863040k total, 20157476k used, 12705564k free,     4608k buffers
Swap: 16777208k total,        0k used, 16777208k free,   488296k cached

#再看下备份结束后,可以看到,没有使用到swap
Mem:  32863040k total, 32644496k used,   218544k free,      920k buffers
Swap: 16777208k total,        0k used, 16777208k free, 12618740k cached
PID   USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25234 root      20   0 50880 3048 2192 S 57.6  0.0   2:22.79 mysqldump

#最后看下总耗时统计:
real    3m37.074s
user    2m6.018s
sys     0m17.315s

3、部分备份:备份时不使用 -q 参数

mysqldump -w " id<100000 " -Smysql.sock --quick=false -Smysql.sock -B yejr --tables t_yejr

#看下总耗时
real 0m22.665s
user 0m20.458s
sys 0m2.156s

#再看下mysqldump进程消耗的内存,最高时大概使用了2.5G内存
20619 root      20   0 2571m 2.5g 2208 R 99.9  7.8   0:11.63 mysqldump

4、部分备份:备份时启用 -q 参数

mysqldump -w " id<100000 " -Smysql.sock -Smysql.sock -B yejr --tables t_yejr

#看下总耗时,并没有慢多少
real 0m27.882s
user 0m22.610s
sys 0m0.670s

#再看下mysqldump进程消耗的内存,只占用了极少量内存
19690 root      20   0 50880 3056 2200 S 73.4  0.0   0:06.01 mysqldump

MySQL出了门,Percona在左,MariaDB在右

MySQL vs Percona vs MariaDB

前言:作为没有自主研发能力的企业,MySQL、Percona、MariaDB到底该选择哪个?

今天看到SOHU-DBA公众号推送的文章:《MySQL分支的选择:Percona还是MariaDB》,原文出处:http://www.biaodianfu.com/mysql-percona-or-mariadb.html,这个文章比较长,有兴趣的可以找到原文看看,我也来简单说下我的看法。

就目前而言,我个人优先推荐使用Percona分支版本,它和官方版本相对是最近的,迁移切换的代价也很小。我从2008年开始就使用Percona分支版本到现在了,当初我们可是国内最大的Percona免费用户。最早只是在官方版本上打了一些补丁,现在已经发展形成了自己的XtraDB引擎,提供PXC高可用解决方案,并且附带了percona-toolkit等DBA管理工具箱,非常方便。

而MariaDB分支则做了较大改动,虽然也集成了XtraDB引擎的大多数优势,但从官方版本迁移过去,不确定因素等风险还是挺大的,比如GTID不兼容、查询优化器个别时候更糟糕等等,不过MariaDB还是挺值得期待的。

综上,建议先继续使用Percona分支,等MariaDB分支相对更成熟了再用不迟,对这两个分支都不感兴趣或不敢用的,再最后选择官方版本吧,把我上面的话当耳边风就行了,但我要告诉大家的是,在官方版本上能遇到奇葩的事情更多,尤其是查询优化器,快吐血了。

R620服务器idrac偶尔不可用问题解决方法

R620服务器中,比较经常出现idrac无法连接,或者连接用户数超限的问题,有几个方法可以尝试下:
1、 升级idrac卡的firmware
下载并升级最新的1.57.57版本的firmware:http://www.dell.com/support/home/us/en/04/Drivers/DriversDetails?driverId=XH6FX
该版本的bug fix中提到过一点:
– Fix for issues that cause iDRAC7 sluggish responsiveness after a prolonged period of time (approx. 45-100 days, depending on the usage). In some cases, if the iDRAC is not reset, the iDRAC may become unresponsive and requires a server AC Power on reset. This issue was introduced in firmware release 1.50.50 and fixed in 1.56.55.

该版本解决了idrac启动45-100天后可能出现无法响应的问题。

2、 杀掉超时连接会话
有2个方法:
a. 重启bmc卡
执行命令: ipmitool mc reset cold 即可,将bmc卡重启后,所有的session都会重置释放。

b. 杀掉超时会话
前提是,允许bmc卡通过网络远程连接
ipmitool lan set 1 access on

或者在下面这个地方启用
iDRAC 设置 => 服务 => VNC 服务器 => 超时

或者在idrac卡的下面这个地方设置:
iDRAC 设置 => 网络 => IPMI 设置 => 启用 LAN 上的 IPMI

同时,建议把web server及ssh服务的timeout值适当调低:
iDRAC 设置 => 服务 => Web Server => 超时
iDRAC 设置 => 服务 => SSH => 超时
iDRAC 设置 => 服务 => Telnet => 超时
iDRAC 设置 => 服务 => VNC 服务器 => 超时

利用 racadm 工具(racadm工具的安装自行搞定)关闭超时会话,首先可以查看当前的会话连接情况,例如:
[ 15:41:10-root@fzdm-10-59-xx-xx:~ ]#racadm -r 10.59.xx.xx -uroot -p”xx” getssninfo
Security Alert: Certificate is invalid – self signed certificate
Continuing execution. Use -S option for racadm to stop execution on certificate-related errors.
SSNID Type User IP Address Login Date/Time
—————————————————————————
20 SSH root 10.5.xx.xx 11/18/2014 15:38:43
25 GUI root 10.5.xx.xx 11/18/2014 15:40:27
28 RACADM root 10.59.xx.xx 11/18/2014 15:41:17
29 SSH root 10.59.xx.xx 11/18/2014 15:41:18

再执行下面的命令,关闭超时会话连接
[ 15:40:52-root@fzdm-10-59-xx-xx:~ ]#racadm -r 10.59.xx.xx -uroot -p”xx” closessn -i 25
Security Alert: Certificate is invalid – self signed certificate
Continuing execution. Use -S option for racadm to stop execution on certificate-related errors.
Session 19 closed successfully.

视频分享:MySQLDBA成长之路 – InnoDB事务隔离级别、行锁、死锁解读

录制了一个“MySQL DBA成长之路”系列视频,关于InnoDB事务隔离级别、锁的简要介绍,主要内容有:

1、四个不同事务隔离级别的区别;
2、InnoDB行锁案例演示;
3、InnoDB死锁案例演示;
4、在没有索引的列上锁定,会引发更大范围的锁。

百度云盘:http://t.cn/R73hP5i , 搜狐视频:http://t.cn/R73hP56 ,初学者们可以看看 :)