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