SALogs - 专注于Linux技术、Web架构

RSS订阅

Todo list

munin、icinga、freebsd、cobbler、puppet、mongodb...

标签云

正在浏览 MySQL 里的文章

MySQL Query Cache 小结

2010/07/12 于 11:11 抢沙发

分类: MySQL |   发布: salogs.com  |   来源：简朝阳
最近经常有人问我 MySQL Query Cache 相关的问题，就整理一点 MySQL Query Cache 的内容，以供参考。

顾名思义，MySQL Query Cache 就是用来缓存和 Query 相关的数据的。具体来说，Query Cache 缓存了我们客户端提交给 MySQL 的 SELECT 语句以及该语句的结果集。大概来讲，就是将 SELECT 语句和语句的结果做了一个 HASH 映射关系然后保存在一定的内存区域中。

在大部分的 MySQL 分发版本中，Query Cache 功能默认都是打开的，我们可以通过调整 MySQL Server 的参数选项打开该功能。主要由以下5个参数构成：

选项 解释
query_cache_limit 允许 Cache 的单条 Query 结果集的最大容量，默认是1MB，超过此参数设置的 Query 结果集将不会被 Cache
query_cache_min_res_unit 设置 Query Cache 中每次分配内存的最小空间大小，也就是每个 Query 的 Cache 最小占用的内存空间大小
query_cache_size 设置 Query Cache 所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是整数倍，MySQL 会自动调整降低最小量以达到1024的倍数
query_cache_type 控制 Query Cache 功能的开关，可以设置为0(OFF),1(ON)和2(DEMAND)三种，意义分别如下：
0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache
1(ON)：开启 Query Cache 功能，但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后，将不使用Query Cache
2(DEMAND)：开启 Query Cache 功能，但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后，才使用 Query Cache
query_cache_wlock_invalidate 控制当有写锁定发生在表上的时刻是否先失效该表相关的 Query Cache，如果设置为 1(TRUE)，则在写锁定的同时将失效该表相关的所有 Query Cache，如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的 Query Cache


Query Cache 如何处理子查询的？
这是我遇到的最为常见的一个问题。其实 Query Cache 是以客户端请求提交的 Query 为对象来处理的，只要客户端请求的是一个 Query，无论这个 Query 是一个简单的单表查询还是多表 Join，亦或者是带有子查询的复杂 SQL，都被当作成一个 Query，不会被分拆成多个 Query 来进行 Cache。所以，存在子查询的复杂 Query 也只会产生一个Cache对象，子查询不会产生单独的Cache内容。UNION[ALL] 类型的语句也同样如此。

Query Cache 是以 block 的方式存储的数据块吗？
不是，Query Cache 中缓存的内容仅仅只包含该 Query 所需要的结果数据，是结果集。当然，并不仅仅只是结果数据，还包含与该结果相关的其他信息，比如产生该 Cache 的客户端连接的字符集，数据的字符集，客户端连接的 Default Database等。

Query Cache 为什么效率会非常高，即使所有数据都可以 Cache 进内存的情况下，有些时候也不如使用 Query Cache 的效率高？
Query Cache 的查找，是在 MySQL 接受到客户端请求后在对 Query 进行权限验证之后，SQL 解析之前。也就是说，当 MySQL 接受到客户端的SQL后，仅仅只需要对其进行相应的权限验证后就会通过 Query Cache 来查找结果，甚至都不需要经过 Optimizer 模块进行执行计划的分析优化，更不许要发生任何存储引擎的交互，减少了大量的磁盘 IO 和 CPU 运算，所以效率非常高。

客户端提交的 SQL 语句大小写对 Query Cache 有影响吗？
有，由于 Query Cache 在内存中是以 HASH 结构来进行映射，HASH 算法基础就是组成 SQL 语句的字符，所以必须要整个 SQL 语句在字符级别完全一致，才能在 Query Cache 中命中，即使多一个空格也不行。

一个 SQL 语句在 Query Cache 中的内容，在什么情况下会失效？
为了保证 Query Cache 中的内容与是实际数据绝对一致，当表中的数据有任何变化，包括新增，修改，删除等，都会使所有引用到该表的 SQL 的 Query Cache 失效。

为什么我的系统在开启了 Query Cache 之后整体性能反而下降了？
当开启了 Query Cache 之后，尤其是当我们的 query_cache_type 参数设置为 1 以后，MySQL 会对每个 SELECT 语句都进行 Query Cache 查找，查找操作虽然比较简单，但仍然也是要消耗一些 CPU 运算资源的。而由于 Query Cache 的失效机制的特性，可能由于表上的数据变化比较频繁，大量的 Query Cache 频繁的被失效，所以 Query Cache 的命中率就可能比较低下。所以有些场景下，Query Cache 不仅不能提高效率，反而可能造成负面影响。

如何确认一个系统的 Query Cache 的运行是否健康，命中率如何，设置量是否足够？
MySQL 提供了一系列的 Global Status 来记录 Query Cache 的当前状态，具体如下：

名称 解释
Qcache_free_blocks 目前还处于空闲状态的 Query Cache 中内存 Block 数目
Qcache_free_memory 目前还处于空闲状态的 Query Cache 内存总量
Qcache_hits Query Cache 命中次数
Qcache_inserts 向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数
Qcache_lowmem_prunes 当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数
Qcache_not_cached 没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL
Qcache_queries_in_cache 目前在 Query Cache 中的 SQL 数量
Qcache_total_blocks Query Cache 中总的 Block 数量

可以根据这几个状态计算出 Cache 命中率，计算出 Query Cache 大小设置是否足够，总的来说，我个人不建议将 Query Cache 的大小设置超过256MB，这也是业界比较常用的做法。

MySQL Cluster 是否可以使用 Query Cache？
其实在我们的生产环境中也没有使用 MySQL Cluster，所以我也没有在 MySQL Cluster 环境中使用 Query Cache 的实际经验，只是 MySQL 文档中说明确实可以在 MySQL Cluster 中使用 Query Cache。从 MySQL Cluster 的原理来分析，也觉得应该可以使用，毕竟 SQL 节点和数据节点比较独立，各司其职，只是 Cache 的失效机制会要稍微复杂一点。

MySQL ,

MySQL监控快速指南

2010/05/27 于 11:22 抢沙发

Mysql作为广泛应用的数据库系统，平时运维工作中对她的监控必不可少，现在把我对Mysql数据库的监控体会写成下文，欢迎拍砖。
无论是DBA或是SA，监控的目标都很明确，无外乎
1.快速的得到Mysql过去一段时间或者当前运行的状态
2.因硬件升级或者系统配置的改变而诊断对Mysql数据库性能影响
3.在Mysql数据库系统出现故障要能够及时收到告警
4.为日后编写运维报告提供各项数据指标供分析
5.……
想到了再做补充。而所有以上这些目的，通过各种方法和手段都可以做到。
自从Mysql数据库系统启动并且提供服务，Mysql内部自身的一套“计数器”就开始工作，可以通过
Show Global Status ， Show global variables ，Show full processlist
得到Mysql数据库系统当前各种系统变量和状态 ，并且后文介绍个各种监控工具几乎都是对这三个指令的输出
进行数据的计算和统计分析。


这里要提到的是mysqladmin这个Mysql自带的工具，除了用来做各种管理工作，还可以用来做监控
例如：每个10秒输出一次mysql的状态信息

#./mysqladmin -i 10 extended status

下面进入文章正题
一。GUI 类监控工具
1.MySQL administrator
Mysql公司发布管理工具，安装和使用都很方便
可以对Mysql 的状态，变量和进程监控，并且有简单图形绘制输出。
2.Cacti
用它来做系统监控的同仁相信很多，目前各种插件数量也很多，足以满足绝大多数公司监控要求。
这里要说的是Mysql监控插件teMySQL_cacti，能够对mysql做比较全面的监控，
但是我发现这个插件很长时间没有更新了。

3.Mysql-cacti-templates
Google cacti : http://code.google.com/p/mysql-cacti-templates/
这个应该说是teMySQL_cacti的升级和改进版本，项目发起人是主编《High Performance MySQL, Second Edition.》的作者
他的主页 http://www.xaprb.com/blog/
本人推荐使用，功能强大丰富。

4.Nagios
这个天天使用，没啥好说的，可以自己写监控脚本来监控，和cacti结合使用是不错的搭配！
5.Munin
Munin 是一款和cacti类似的系统监控工具，有兴趣的可以去研究研究
http://munin.projects.linpro.no/ ，这里不多说
6.Zenoss
测试过一次，用的不多，不做什么评价
7.MySQL Enterprise Monitor
Mysql 旗舰付费产品，功能丰富
mytop1.png
详细信息 http://www.mysql.com/products/enterprise/monitor.html
能够监控，报警，绘图，并提供性能优化和建议，集众多监控指标于一身，好是好，可以要花钱的哦
有兴趣的可以去download 30天的试用版本体验。

二。文字模式的监控工具
我了解并使用过的有
1.mytop
类似 linux下top命令的输入
mytop1.png
2.mtstat-mysql

http://pypi.python.org/pypi/mtstat-mysql/0.7.3.3


3.mysqlreport

http://hackmysql.com/mysqlreport

看我写的这篇文章
用mysqlreport监控并输出mysql状态值  http://www.askwan.com/read.php?44

4.innotop
这个针对innodb存储引擎数据库的监控，功能丰富
http://www.xaprb.com/blog/2006/07/02/innotop-mysql-innodb-monitor/

三。自己写脚本监控
参看我以前写过一篇文章
http://www.askwan.com/read.php?96
方法和原理一样，可以定制需求

以上各种工具和方法各有自己特色，如何选择，可以根据需求和自己的情况定,目的只有一个，效益最大化！
--------------------END--------------------

作者：askwan@『AskWan』
地址：http://www.askwan.com/post/140/

MySQL

[mysql]在线安装sphinx存储引擎

2010/05/21 于 18:15 抢沙发

由于业务需求，需要在现有mysql中安装sphinx的存储引擎，要保证现有mysql运行的情况下完成。mysql也的确支持存储引擎的在线热插拔，下面介绍安装步骤：

1、查看现有mysql的运行版本
# mysqladmin  -u user -ppwd version

...
Server version          5.1.47-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /data/mysql_db/mysql.sock
Uptime:                 15 days 2 hours 17 min 40 sec

2、下载mysql和sphinx
mysql：wget ftp://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-5.1/mysql-5.1.47.tar.gz
sphinx：wget http://sphinxsearch.com/downloads/sphinx-0.9.9.tar.gz

注意：mysql源码包的版本一定要与当前运行的mysql版本一致！

解压
# tar -xzvf mysql-5.1.47.tar.gz
# tar -xzvf sphinx-0.9.9.tar.gz

3、将sphinx-0.9.9下的mysqlse目录复制到mysql目录中
#cp -r sphinx-0.9.9/mysqlse/ mysql-5.1.47/storage/sphinx

build
# cd mysql-5.1.47
# sh BUILD/autorun.sh
#./configure
# make

注意：这里到make这步即可，不用install

4、将make好的文件复制到当前运行的mysql目录中
# cp storage/sphinx/.libs/ha_sphinx.* /usr/local/mysql/lib/mysql/plugin
更改所有者
# chown mysql.mysql /usr/local/mysql/lib/mysql/plugin/*

注：我当前运行的mysql目录在/usr/local/mysql

5、登陆mysql加载sphinx引擎模块
#mysql -u root -p -h localhost
# mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';
检查引擎模块是否正常加载
mysql> show engines;

+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| SPHINX     | YES     | Sphinx storage engine 0.9.9                                    | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

安装完成！如果要卸载存储模块使用
mysql> UNINSTALL PLUGIN sphinx;

原创文章，转载请注明:   转自 http://salogs.com

MySQL ,

编写批量转换MySQL表存储引擎的脚本

2010/05/10 于 18:09 抢沙发

mysql安装后提供了一个perl的脚本：mysql_convert_table_format 它默认情况下可以将某个表或某个数据库的所有表转换为MyISAM的存储引擎，通过修改该脚本可以将默认修改的存储引擎改为我们需要的存储引擎，如Innodb。该脚本使用了DBI和DBD的perl模块，如果系统没有安装的话可以使用yum intall perl-DBI perl-DBD-mysql 命令来安装。本文介绍的不是mysql_convert_table_format工具的使用，而是我自己写的一个shell脚本，也可以完成批量转换表存储引擎的目的，如下：

?Download convert_storage_engine.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

#!/bin/bash
 
# File          : convert_storage_engine.sh
# Info          : 批量转换表存储引擎,如果忽略-t参数
#                 则转换指定数据库中的所有表。
# Arg1          : -d dbname
# Arg2          : -t [tables]
# Arg3          : -e engine type (myisam | innodb)
# Author        : Cooper
# Site          : http://salogs.com 
# Version       : 2010.05.10 First Release
 
User=root
Pwd=dbpwd
TmpFile="/dev/shm/table.tmp"
MySQLBin="mysql -u$User -p$Pwd -e "
 
function Usage()
{
 echo "Useage:$0 -d dbname [-t table] -e engine_type"
}
 
# 检查用户输入的参数
if [ $# -eq 0 ] ;then
        Usage
        exit 1
fi
 
# 获得命令行参数值，并作相应处理
while getopts t:d:e:h OPTION
do
        case $OPTION in
        d)
           {    # 检查数据库是否存在
                DBName=$OPTARG
                DBExists=`$MySQLBin "show databases;" | grep $DBName`
                if [ "$DBExists" == "" ];then
                   echo "$DBName database not exists!"
                   exit 1
                fi
           }
           ;;
        t)
           {     # 检查表是否存在
                 TableName=$OPTARG
                 TableExists=`$MySQLBin "use $DBName;show tables" | grep $TableName`
                  if [ "$TableExists" == "" ];then
                      echo "$TableName table not exists!"
                      exit 1
                  fi
             }
             ;;
        e)
             EngineName=`echo $OPTARG | tr A-Z a-z `
             if [ $EngineName != "innodb" ] && [ $EngineName != "myisam" ];then
                Usage
                 exit 1
              fi
              ;;
        \?|h)
              Usage
               exit 0
               ;;
        esac
done
 
# 转换表
if [ "$TableName" != "" ];then
{
    CurrentEngine=`$MySQLBin "use $DBName;show table status like \"$TableName\"\G"\
| grep Engine | awk '{print $2}'|tr A-Z a-z`
    if [ $CurrentEngine == $EngineName ];then
        echo "Current Table $Table is already of type $EngineName;Ignored"
        exit 0
    fi
    $MySQLBin "use $DBName;alter table $TableName engine=$EngineName;"
}
else
{
    $MySQLBin "use $DBName;show tables;" | sed 1d >$TmpFile
    while read Table
    do
        CurrentEngine=`$MySQLBin "use $DBName;show table status like '$Table'\G"\
| grep Engine | awk '{print $2}'| tr A-Z a-z`
        if [ $CurrentEngine == $EngineName ];then
           echo "Current Table $Table is already of type $EngineName;Ignored"
           continue
        fi
        $MySQLBin "use $DBName;alter table $Table engine=$EngineName;"
    done<$TmpFile
}
fi
 
rm -rf $TmpFile

注意：
1、由于Innodb不支持FULLTEXT索引，因此在转换表时要格外注意。
2、不要将系统表mysql转换为Innodb，如果这样做了的话，mysql绝对无法启动，需要使用mysql_install_db 命令重建系统表。
3、修改现网数据库需格外注意，转换表时会对表进行锁定。

原创文章，转载请注明: 转自 http://salogs.com

bash, MySQL , ,

mysql 表整理命令

2010/04/21 于 13:58 抢沙发

MyISAM 整理表碎片

可以使用OPTIMIZE TABLE或myisamchk来对一个表整理碎片。

Innodb 整理表碎片

如果有随机插入到表的索引或从表的索引随机删除，索引可能变成碎片的。碎片意思是索引页在磁盘上的物理排序并不接近页上记录的索引排序，或者在分配给索引的64页块上有许多没有被使用的页。

碎片的一个“同义词”是一个表占据的空间超过它应该占据的空间的大小。确切是多少，这是很难去确定的。所有InnoDB数据和索引被存在B树中，并且它们的填充因子可能从50%到100%。碎片的另一个“同义词”是一个表扫描例如：

SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;

花了超过它应该花的时间。（在上面的查询中我们“欺骗”SQL优化器来扫描集束索引，而不是一个第二索引）。多数磁盘可以读10MB/s到50MB/s，这可以被用来评估一个表扫描可以多快地运行。

如果你周期地执行“null” ALTER TABLE操作，它就可以加速索引扫描：

ALTER TABLE tbl_name ENGINE=INNODB

这导致MySQL重建表。另一个执行碎片整理操作的办法是使用mysqldump来转储一个表到一个文本文件，移除表，并重新从转储文件重装载它。

如果到一个索引的插入总是升序的，并且记录仅从末尾被删除，InnoDB文件空间管理保证在索引中的碎片不会发生。

MySQL

mysql做主从同步指定binlog-do-db参数时遇到的问题

2010/04/19 于 15:54 抢沙发

由于公司业务需求，需要将先前mysql数据库服务器中添加第二个数据库的主从备份，打开my.cnf文件，发现配置文件中指定了binlog-do-db参数，后面填写了一个数据库名，按照常规，如果想再添加一个需要记录binlog的数据库名就可以了，因此我修改如下:
binlog-do-db = dbname1,dbname2
修改后重启mysql，发现binlog不再记录日志了，从库复制进程都正常，mysql也没有报任何错误。接下来开始排除问题：
（1）查看主从mysql的错误日志，看是否有错误输出
（2）在主库中使用show master status和在从库中使用show slave status\G命令查看从库复制情况。
（3）查看配置文件看配置选项是否有不妥之处
（4）修改主从配置文件，重启mysql再试

经过上面1-3步的分析，没有发现任何的错误，随即g.cn了一把，发现了MySQL Performance Blog的一篇文章，文章中建议不要使用binlog-do-db、binlog-ignore-db、replicate-do-db 和 replicate-ignore-db的参数，而是建议在从库中使用replicate-wild-ignore-table=dbname.%的参数对数据库表的过滤。详细的说明可以参考这篇文章：Why MySQL's binlog-do-db option is dangerous 同样的，要过滤多个数据库的表，中间用逗号隔开。但进过测试发现，如果主库中有dbname1数据库而从库没有这个数据库的话，从库复制会发生错误。最后我打算使用binlog-ignore-db参数再试一下，修改配置文件后，重启mysql，在从库中将主库没有的数据库在从库重新导入，主库中查看状态如下：

root@db001 [(none)] >show master STATUS;
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+------------------+----------+--------------+--------------------------+
| mysql-bin.007087 |   193424 |              | information_schema,mysql |
+------------------+----------+--------------+--------------------------+

从库状态如下：

root@db002 [(none)] >show slave STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting FOR master TO send event
Master_Host: 192.168.99.61
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.007087
Read_Master_Log_Pos: 165652
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 151428
Relay_Master_Log_File: mysql-bin.007087
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: dbname1.%,dbname2.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 165652
Relay_Log_Space: 151583
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2013
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row IN SET (0.00 sec)

那些忽略的数据库我是不关心的，经过测试，我想要同步的数据可以同步到从库了，现在来看，复制是正常的，等待继续观察……

从库中虽然使用了Replicate_Wild_Ignore_Table参数设置了需要过滤的表，但在实际测试中并没有骑到过滤的作用，这一部分需要进一步的测试……
原创文章，转载请注明: 转自 http://salogs.com

MySQL

InnoDB Double write

2010/04/14 于 10:18 抢板凳

记得刚开始看InnoDB文档的时候，Double Write一节（其实只有一小段）就让我很困惑。无奈当时内力太浅，纠缠了很久也没弄明白。时隔几个月，重新来整理一下。

涉及到的概念：Buffer Pool简称BP，Dirty Page，Log file，Flush，innodb tablespace。

1. 什么是Double Write

在InnoDB将BP中的Dirty Page刷（flush）到磁盘上时，首先会将Page刷到InnoDB tablespace的一个区域中，我们称该区域为Double write Buffer。在向Double write Buffer写入成功后，再择机将数据拷贝到正在的数据文件对应的位置。

咋一看，这个过程有些多余

2. 为什么需要Double Write

InnoDB中有记录（Row）被更新时，先将其在Buffer Pool（简称BP）中的page更新，并将这次更新记录到Log file中，这时候BP中的该page就是被标记为Dirty。在适当的时候（BP不够、系统闲置等），这些Dirty Page会被flush到磁盘上。

试想，在某个Dirty Page（一般是16K）flush的过程中，发生了系统断电（或者OS崩溃），16K的数据只有8K被写到磁盘上，这种现象被称为（partial page writes、torn pages、fractured writes）。一旦partial page writes发生，那么在InnoDB恢复时就很尴尬：在InnoDB的Log file中虽然知道这个数据页被修改了，但是却无法知道这个页被修改到什么程度，和这个页面相关的redo也就无法应用了。

举个例子：在InnoDB的log file中有如下Log：

Log sequence number 0 4285149977
Log sequence number 0 4287355447
Log sequence number 0 4289260680
Log sequence number 0 4291279900
Log sequence number 0 4293359020

其中第1、3个Log修改了该page，但是在断电时，BP中该page只被flush了一部分。那么InnoDB是无法决定上面的Log是否应该被应用的。这时，数据就出现了不一致。

所以，Log file的有效应用，前提是InnoDB的数据文件中的Page是一致的。

简而言之，Double write就是为了避免Partial page writes而设计的。

3. Double Write对性能的影响

系统需要将数据写两份，一般认为，Double Write是会降低系统性能的。peter猜测可能会有5-10%的性能损失，但是因为实现了数据的一致，是值得的。Mark Callaghan认为这应该是存储层面应该解决的问题，放在数据库层面无疑是牺牲了很多性能的。

事实上，Double Write对性能影响并没有你想象（写两遍性能应该降低了50%吧？）的那么大。在BP中一次性往往会有很多的Dirty Page同时被flush，Double Write则把这些写操作，由随机写转化为了顺序写。而在Double Write的第二个阶段，因为Double Write Buffer中积累了很多Dirty Page，所以向真正的数据文件中写数据的时候，可能有很多写操作可以合并，这样有可能会降低Fsync的调用次数。

基于上面的原因，Double Write并没有想象的那么糟。另外，Dimitri在测试后，发现打开和关闭Double Write对效率的影响并不大。

4. 相关参数与状态

是否打开了double write:

?View Code SQL
1
2
3
4
5
6

root@(none) 07:16:16&gt;show variables like &quot;%double%&quot;;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_doublewrite | ON    |
+--------------------+-------+

Double write的使用情况：

?View Code SQL
1
2
3
4
5
6
7

root@(none) 07:15:50&gt;SHOW STATUS LIKE &quot;%innodb_dblwr%&quot;;
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| Innodb_dblwr_pages_written | 145373349 |
| Innodb_dblwr_writes        | 2249336   |
+----------------------------+-----------+

上面可以看到，从BP共Flush了145373349个Pages到double write buffer中；一共调用了2249336次write写到真正的数据文件。可见，相当于每次write合并了 145373349 / 2249336 = 64.6次Flush。（这就是为什么double write buffer为什么并不会对效率有很大影响的原因）

5. 我的看法

在某些文件系统（ZFS等）层面能够保证不出现Partial page writes时，可以关闭Double Write。因为它对性能影响并不大，一般情况都建议打开，毕竟带来的数据安全性保障可能是我们更关心的。

参考文献：

[0]. Manual about Double Write

[1]. Innodb Double Write

[2]. Do you need the InnoDB doublewrite buffer

[3]. MySQL Performance: InnoDB Doublewrite Buffer Impact

MySQL

MySQL Timeout解析

2010/02/08 于 10:29 抢沙发

“And God said, Let there be network: and there was timeout”
在使用MySQL的过程中，你是否遇到了众多让人百思不得其解的Timeout？
那么这些Timeout之后，到底是代码问题，还是不为人知的匠心独具？

先看一下比较常见的Timeout参数和相关解释：
connect_timeout
The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.
interactive_timeout
The number of seconds the server waits for activity on an interactive connection before closing it.
wait_timeout
The number of seconds the server waits for activity on a noninteractive connection before closing it.
net_read_timeout
The number of seconds to wait for more data from a connection before aborting the read.
net_write_timeout
The number of seconds to wait for a block to be written to a connection before aborting the write.

从以上解释可以看出，connect_timeout在获取连接阶段（authenticate）起作用，interactive_timeout和wait_timeout在连接空闲阶段（sleep）起作用，而net_read_timeout和net_write_timeout则是在连接繁忙阶段（query）起作用。

获取MySQL连接是多次握手的结果，除了用户名和密码的匹配校验外，还有IP->HOST->DNS->IP验证，任何一步都可能因为网络问题导致线程阻塞。为了防止线程浪费在不必要的校验等待上，超过connect_timeout的连接请求将会被拒绝。

即使没有网络问题，也不能允许客户端一直占用连接。对于保持sleep状态超过了wait_timeout（或interactive_timeout，取决于CLIENT_INTERACTIVE标志）的客户端，MySQL会主动断开连接。

即使连接没有处于sleep状态，即客户端忙于计算或者存储数据，MySQL也选择了有条件的等待。在数据包的分发过程中，客户端可能来不及响应（发送、接收、或者处理数据包太慢）。为了保证连接不被浪费在无尽的等待中，MySQL也会选择有条件（net_read_timeout和net_write_timeout）地主动断开连接。

这么多Timeout足以证明MySQL是多么乐于断开连接。而乐于断开连接的背后，主要是为了防止服务端共享资源被某客户端（mysql、mysqldump、页面程序等）一直占用。

MySQL 没有标签

mysqldump意外终止的原因以及解决方法

2010/02/08 于 10:21 抢沙发

     mysqldump是非常重要的MySQL备份工具。然而在长年累月的使用过程中，TAOBAO多次出现了因mysqldump意外终止而导致备份失败的情况。
以下是我们经常遇到的问题：

1、Lost connection to MySQL server at ‘reading initial communication packet’：
这个主要是因为DNS不稳定导致的。如果做了网络隔离，MySQL处于一个相对安全的网络环境，那么开启skip-name-resolve选项将会最大程度避免这个问题。

2、Lost connection to MySQL server at ‘reading authorization packet’：
从MySQL获取一个可用的连接是多次握手的结果。在多次握手的过程中，网络波动会导致握手失败。增加connect_timeout可以解决这个问题；然而增加connect_timeout并不能防止网络故障的发生，反而会引起MySQL线程占用。最好的解决办法是让mysqldump重新发起连接请求。

3、Lost connection to MySQL server during query：
这个问题具备随机性，而淘宝MySQL的应用场景决定了我们无法多次备份数据以便重现问题。
然而我们注意到这个问题一般会在两种情况下会发生。一种是mysqldump **** | gzip ****；另外一种是mysqldump **** > /nfs-file
注意，不管是gzip还是nfs都有一种特点，那就是它们影响了mysqldump的速度。从这个角度思考，是不是mysqldump从MySQL接受数据包的速度不够快导致Lost connection to MySQL server during query错误呢？

为了定位到问题，我搭建了一个测试环境：
test@192.168.0.1：3306
CREATE TABLE `test` (
`id` bigint(20) NOT NULL auto_increment,
`b` varchar(2000) default NULL,
`c` varchar(2000) default NULL,
`d` varchar(2000) default NULL,
`e` varchar(2000) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into test(b,c,d,e) values (lpad(’a’,1900,’b’), lpad(’a’,1900,’b’), lpad(’a’,1900,’b’), lpad(’a’,1900,’b’));
多次复制数据使测试环境达到一定数据量。

192.168.0.2：
编写一个c++程序
#include <stdio.h>
#include <mysql.h>

using namespace std;

int main()
{
MYSQL conn;
MYSQL_RES *result;
MYSQL_ROW row;
my_bool reconnect = 0;

mysql_init(&conn);
mysql_options(&conn, MYSQL_OPT_RECONNECT, &reconnect);

if(!mysql_real_connect(&conn, “192.168.0.1″, “test”, “test”, “test”, 3306, NULL, 0))
{
fprintf(stderr, “Failed to connect to database: %s\n”, mysql_error(&conn));
exit(0);
}
else
{
fprintf(stdout, “Success to connect\n”);
}

mysql_query(&conn, “show variables like ‘%timeout%’”);
result = mysql_use_result(&conn);
while(row=mysql_fetch_row(result))
{
fprintf(stdout, “%-10s: %s\n”, row[0], row[1]);
}
mysql_free_result(result);
fprintf(stderr, “\n”);

mysql_query(&conn, “select SQL_NO_CACHE * from test.test”);
result = mysql_use_result(&conn);
while((row=mysql_fetch_row(result))!=NULL)
{
fprintf(stderr, “Error %d: %s\n”, mysql_errno(&conn), mysql_error(&conn));
fprintf(stdout, “%s\n”, row[0]);
sleep(100);
}
fprintf(stderr, “Error %d: %s\n”, mysql_errno(&conn), mysql_error(&conn));
mysql_free_result(result);
mysql_close(&conn);
return 1;
}

在这段代码里，sleep函数用来模拟NFS的网络延迟和gzip的运算时间。执行一段时间之后，Lost connection to MySQL server during query出现了，程序意外终止。在数据处理足够快的情况下，又会是怎样的结果？

将sleep的时间改为1，重新编译后发现程序能够完整跑完。根据《MySQL Timeout解析》上对net_write_timeout的解释，我们可以发现，mysqldump处理数据过慢（NFS、gzip引起）会导致MySQL主动断开连接，此时mysqldump就会报Lost connection to MySQL server during query错误。经过多次测试，确定这个错误是由于net_write_timeout设置过短引起。

MySQL 没有标签

利用SSH隧道方式连接远程MySQL服务器

2009/11/03 于 16:23 抢沙发

1、简介：
    大多数人知道SSH是用来替代R命令集，用于加密的远程登录，文件传输，甚至加密的FTP(SSH2内置)，因此SSH成为使用极广的服务之一，不仅如此，SSH还有另一项非常有用的功能，就是它的端口转发隧道功能，利用此功能，让一些不安全的服务象TCP、POP3、SMTP、FTP，LDAP等等通过SSH的加密隧道传输，然后，既然这些服务本身是不安全的，密码和内容是明文传送的，通过使用SSH隧道传输的话再想在其中间监听也是徒劳无功的了。本文主要介绍通过SSH隧道连接远程Mysql服务器，SSH隧道更详细的文章参见：官方关于SSH隧道的介绍文章

2、使用Windows客户端连接

下面介绍使用windows系统下比较流行的MySQL终端工具SQLyog Enterprise，通过SSH隧道连接Mysql服务器。如下图：

SQLyog_Enterprise_new_connection.png
新建连接

SQLyog_Enterprise_Mysql_TAB.png
MySQL标签页

SQLyog_Enterprise_SSH_TAB.png
SSH标签页

3、linux命令行下使用ssh命令建立SSH隧道

mysql服务器地址为:192.168.99.52
本机地址为:192.168.99.91

前提
将本机的ssh public key复制到mysql服务器中，也就是将本机的id_rsa.pub内容添加到mysql服务器的~/.ssh/authorized_keys文件中。我这里用命令实现。

利用 ssh-keygen 命令 生成本机id_rsa.pub文件
# ssh-keygen 连续回车生成id_rsa.pub 文件

将id_rsa.pub文件复制到mysql服务器中
# ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.99.52

开始挖隧道
利用ssh命令在本机开个3388的端口，这个端口为隧道的入口端口，也就是说我一会儿通过在本机连接这个端口来达到连接mysql服务器3306端口的目的。使用命令如下：

ssh -NCPf root@192.168.99.52 -L 3388:192.168.99.52:3306

参数解释

-C    使用压缩功能，是可选的，加快速度。
-P    用一个非特权端口进行出去的连接。
-f    一旦SSH完成认证并建立port forwarding，则转入后台运行。
-N    不执行远程命令。该参数在只打开转发端口时很有用（V2版本SSH支持）

这里的root@192.168.99.52 是登陆mysql服务器的SSH用户名和IP地址-L 3388:192.168.99.52:3306 这个参数的意思是说在本机开放3388端口到192.168.99.52:3306端口的映射，也就是说隧道的入口为3388出口为mysql服务器的3306

执行完后查看本地连接情况
# netstat -tulnp | grep 3388
tcp    0    0 127.0.0.1:3388    0.0.0.0:*    LISTEN    14273/ssh
tcp    0    0 ::1:3388    :::*    LISTEN    14273/ssh

查看本机与mysql服务器的ssh连接情况
# netstat -an | grep 192.168.99.52
tcp    0    0    192.168.99.91:7612    192.168.99.52:9698    ESTABLISHED

通过这两条命令可知，执行完命令之后，本机与mysql服务器就建立起了ssh连接，且开放了3388端口。

通过隧道连接MySQL服务器

# mysql -u dbname -P 3388 -h 127.0.0.1 -pdbpwd
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52562
Server version: 5.1.31-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

dbname@192.168.99.52 [(none)] >show databases;
image
4 rows in set (0.00 sec)

连接成功！

MySQL, SSHD ,
Powered by WordPress Web Design by SRS Solutions © 2012 SALogs - 专注于Linux技术、Web架构 Design by SRS Solutions