且将新火试新茶-MySQLBenchmark（全文）

且将新火试新茶 - MySQL Benchmark（全文）收藏

且将新火试新茶 - MySQL Benchmark

公司内部最流行的数据库就是MySQL，而关于MySQL性能，我听过种种传说和流言。而对于数据库的性能优化和测试，我一直有强烈的兴趣，曾经见过一篇Oracle的性能优化文章，Linux Journal的Bert Scalzo所著的《Linux Maximus, Part 1: Gladiator-like Oracle Performance》，国人的翻译是《角斗士般的Oracle性能》[注]，那兄弟为了压榨Oracle的性能，从数据库的参数到操作系统的参数都进行了调整，最后得到的优化性能比开始提高了10多倍。为什么我用了压榨这个词，你可以想像将文件的最后修改时间调整为不可改变都可以提升数据库的性能。调整几个参数就可以使系统的能力如此提升，何乐而不为呢？

对于MySQL的性能，基本分成两种截然不同的声音：MySQL自己声称的性能数据是如此的优秀，超过Oracle等数据库若干倍等等。其实大多数公司进行的这样的对比测试都是不可信的，自己产品用专有接口，别人产品用出了名的慢接口ODBC，自己不用事务，别人用事务操作，这样的结果数据差距当然十万八千里。另外一种声音基本来之公司内部，主要质疑的是大数量下的性能，普遍的态度是认为在200000条记录以上，MySQL的性能将急剧下降。我个人是后一种观念的受害者，经常为了活动的开发还分表。最近有机会利用公司的设备资源，自己比较测试了若干种环境下的MySQL的表现，很多测试数据的结果颠覆了自己原来对MySQL的认识。

本文名为benchmark,但是实际是没有一个真正的基准，大部分测试是不同状况下的性能比较测试。如果非要找一个基准，那就是MySQL本身。

根据自己的疑惑，主要把测试的重点放在了MySQL不同版本间的性能比较，MySQL的查询缓存的性能，MySQL的MyISAM和InnoDB数据库引擎间性能比较，同时模拟了压力情况，真实环境测试性能，同时对于MySQL的新特性API接口STMT也进行了测试。在文档的最后，我给出了MySQL相关的优化参数说明以及MySQL的相关状态监控方法。

1 测试的相关环境

1.1 测试的机器

既然打着Benchmark的旗号，就必须介绍一下用于测试的机器。由于懒和想测试的用例是逐步的加入的，到测试结束，我耗费了将近6个月的时间。中间被我占用来测试的机器居然有4台，2种，HP380G3和HP380G4， 380G4的机器配置上要好一些（虽然我感觉上表现和G4的基本相同），所以可能影响测试结果数据。而且在后期整理数据阶段，发现了很多测试数据偏差过大。我不得不怀疑机器有人同时使用造成了影响。

所幸我最后基本上把所有的测试用例在一台绝对空闲380G3上全部重做了一次。如果没有特别指明之处，大家就把测试机器看作内核是2.6.8.1（这个内核好像有些不完善.）的380G3吧。

其中模拟多表访问的测试是在另有一台2.4.21的内核的380G3测试了。为了保证对比的公平性，相关的比较都是相同机器上的测试数据。

表1 HP380G4和HP380G3

CPU

MEM

DISK

HP380G4

CPU*2 （3000M or 3200M Hz）

4G ECC

SCSI RAID 5 36G *4

HP380G3

CPU*2 （2800M Hz）

2G ECC

SCSI RAID 5 73 G *4

两个的SCSI控制器好像都是Ultra 320 Smart Array 6i （64MB缓存）

1.2 测试的表

测试的表采用单键索引，主键为INT类型，记录类型覆盖了我们常用的记录类型，记录的长度为120多个字节，和我们平常的记录表类似。

CREATE TABLE IF NOT EXISTS benchmark.test1

(

F1 INT NOT NULL ,

F2 INT NOT NULL DEFAULT 0,

F3 INT NOT NULL DEFAULT 0,

F4 INT NOT NULL DEFAULT 0,

F5 INT NOT NULL DEFAULT 0,

F6 INT NOT NULL DEFAULT 0,

F7 INT NOT NULL DEFAULT 0,

F8 INT NOT NULL DEFAULT 0,

F9 INT NOT NULL DEFAULT 0,

F10 INT NOT NULL DEFAULT 0,

F11 FLOAT(10,4) NOT NULL DEFAULT 0,

F12 DOUBLE(10,4) NOT NULL DEFAULT 0,

F13 VARCHAR(64) NOT NULL DEFAULT “,

F14 DATETIME NOT NULL DEFAULT ‘0’,

PRIMARY KEY (F1)

);

2 MySQL不同版本间的性能比较

不知道大家初次见到MySQL是何种感觉，反正我见到MySQL3.23时很不以为然，没有视图，没有事务，没有触发器，没有子查询，没有存储过程，没有……，懒得提了，但是要说是今天MySQL5.0的能力已经得到了很大的提升，至少上面说明的这些问题，MySQL已经解决了大部分了。

2.1 比较的版本

选择比较的版本是3.23版本,4.0版本, 4.1版本，5.0 Beta版本。其中3.23版本为MySQL的最早的可用版本，XXX等产品也一直在使用，4.0为MySQL支持查询缓冲的版本，也有很多产品使用。MySQL 4.1和5.0版本为较新的版本，支持一些新的特性。

MySQL的安装可以使用源代码自己编译，也可以使用MySQL官方提供的编译版本，相对而言官方提供的静态编译版本比较容易安装(动态编译的版本要依赖glibc库版本)。

另外MySQL提供从4.1以后的提供了Intel C++ Compiler编译的版本，据称Intel C++ Compiler的版本比GCC编译的版本速度要快20%以上，所以也找来看看。

从下面这个表，我们也可以看出MySQL发展史和各个版本的基本特点，可以看出MySQL从一个过家家类型的数据库开始越来越像一个成熟的商用数据库了。

表2 测试的版本

说明

版本特点

MySQL3.23.51

公司内部分产品使用的版本，

为自己用gcc编译

MySQL3.23.55

官房网站下载的3.23的gcc编译static版本

3.23的一个成熟版本

MySQL 4.0 gcc

官房网站下载的gcc编译static版本下载4.0版本，公司相当产品使用

提供了查询Cache

提供了FULLTEXT的文本检索索引

提供了嵌入式的MySQL

InnoDB开始成为内建引擎,InnoDB支持事务，外键，操作行锁定等特性

可以动态调整MySQL的某些运行参数, ”SET”

部分功能性能提升，如批量插入，

功能增加，如TRUNCATE，UNION查询等

MySQL 4.1 gcc

官房网站下载的gcc编译static版本

提供的新特性

子查询的功能，SELECT的嵌套

使用MYSQL_STMT加快了C/S通讯速度，

增加了一些新函数

MySQL 4.1 icc

官房网站下载的intel c++ 编译4.1版本，要intel的动态库支持

Intel C++编译的版本号称速度要快20%以上

MySQL 5.0 gcc (beta)

最新的MySQL版本，官房网站下载版本

从这个版本开始MySQL开始真正像一个商用数据库了。

MySQL5.0的正式颁布已经发布

提供很多成熟商业数据库的特性，

视图，以及相关一些管理功能

存储过程，光标

触发器，已经支持时间和事件触发

VARCHAR长度的增加

InnoDB支持分布式事务

支持一些新的存储引擎(ARCHIVE,只支持插入和查询操作，FEDERATED, 访问远程数据)

2.2 测试的方法和结果

分别，插入，查询，修改，删除，1000000，5000000，10000000，条记录。尝试MySQL在不同的记录数量级别下的表现。表不进行压缩存储。

测试环境使用使用MyISAM数据库引擎，配置中比较关键的参数为，对于4.0后的查询版本使用查询Cache。同时为了模拟真实环境，记录二进制日志。

使用的关键参数为：

set-variable = key_buffer_size=384M

set-variable = query_cache_size=384M

set-variable = sort_buffer_size =1M

set-variable = read_buffer_size=1M

set-variable = table_cache=256

1. 测试采用脚本分组进行，每次测试前删除掉原来的数据，同时删除掉二进制日志（可能flush-log一下更好）。每次操作前sleep 20秒。

2. 插入记录为插入0-N条记录，每次单条插入。

3. 查询为每次查询一条的N次查询。遍历所有的记录。查询使用主键作为查询条件。查询操作完成转储结果集的操作。

4. 修改所有的数据，每个记录修改部分信息（4个字段），每次修改1条。

5. 删除所有的数据，为每次单独删除。删除使用主键作为查询条件。

表3 数据文件的大小

记录条数

数据文件大小

索引文件大小

1000000

111999988

8209408

5000000

559999988

41036800

10000000

1119999988

82071552

测试的性能数据为：

表4 不同版本的表现的耗时

数据库

3.23.51

耗时(s)

3.23.55

耗时(s)

4.0

耗时(s)

4.1gcc

耗时(s)

4.1icc

耗时(s)

5.0

耗时(s)

插入1000000条记录

175

170

169

176

183

185

查询1000000条记录

208

203

282

306

323

345

改写1000000条记录

173

167

165

174

185

178

删除1000000条记录

183

169

172

179

188

184

插入5000000条记录

899

876

898

947

949

查询5000000条记录

1029

1014

1472

1595

1671

1773

改写5000000条记录

871

841

816

859

908

911

删除5000000条记录

914

855

958

976

1005

1006

插入10000000条记录

1811

1745

1758

1817

1896

1954

查询10000000条记录

2080

2033

3001

3257

3386

3607

改写10000000条记录

1884

1793

1844

1904

1969

2019

删除10000000条记录

1976

1897

1908

1982

2045

2102

表5 不同版本处理性能比较

数据库

3.23.51处理速度(条/s)

3.23.55处理速度 (条/s)

4.0处理速度

(条/s)

4.1gcc处理速度 (条/s)

4.1icc

处理速度

(条/s)

5.0Beta

处理速度

(条/s)

插入1000000条记录

5714.29

5882.35

5917.16

5681.82

5464.48

5405.41

查询1000000条记录

4807.69

4926.11

3546.10

3267.97

3095.98

2898.55

改写1000000条记录

5780.35

5988.02

6060.61

5747.13

5405.41

5617.98

删除1000000条记录

5464.48

5917.16

5813.95

5586.59

5319.15

5434.78

插入5000000条记录

5561.74

5707.76

5567.93

5279.83

5268.70

查询5000000条记录

4859.09

4930.97

3396.74

3134.80

2992.22

2820.08

改写5000000条记录

5740.53

5945.30

6127.45

5820.72

5506.61

5488.47

删除5000000条记录

5470.46

5847.95

5219.21

5122.95

4975.12

4970.18

插入10000000条记录

5521.81

5730.66

5688.28

5503.58

5274.26

5117.71

查询10000000条记录

4807.69

4918.84

3332.22

3070.31

2953.34

2772.39

改写10000000条记录

5307.86

5577.24

5422.99

5252.10

5078.72

4952.95

删除10000000条记录

5060.73

5271.48

5241.09

5045.41

4889.98

4757.37

比较图表如下：

多个版本间的性能比较

从上面两张表可以看出，MySQL的简单查询，修改，插入，删除性能还是非常可圈可点的。在1000000的记录级别，效率也可以达到3000-6000条左右。

总体来看，除了查询，大家的性能其实都差不多。从4.0开始的版本，查询速度都有一个明显的下降，个人估计是由于4.0后MySQL支持查询Cache造成的，我的查询方式几乎不会利用到Cache数据，所以Cache不但没有帮助提高查询效率，反而降低了查询的效率。基于这个问题，我们后面单独对MySQL的Cache进行了单独的测试。

我在测试后惊讶的发现，最好的数据居然是mysql3.23.55的版本的性能。我只能猜测无数的功能堆加后，性能多少有下降，5.0Beta版本也的确排名最后。而对于现有使用的版本3.23.51和3.23.55版本的性能很接近。但是看来使用安装包版本比自己编译版本要好。

icc编译的版本没有想传言那样体现优势。我个人估计我的测试用例主要测试的是MySQL的I/O性能，而不是运算。icc的强项体现不出来。

即使到了10000000的数据量级别，查询和更新速度仍然可以接受。不想传说中那么恐怖。

插入速度的确快过查询速度。B树的重建索引速度大于查询的。？而且即使对于MySQL3.23的版本，查询的性能还是要落后于其他操作，个人估计是由于查询结果要返回结果集合造成的。

从这组数据我们可以发现，在数据量增加后，插入数据的性能变化很小，查询数据的性能成非常轻微的下降，这很好理解，这是由于查询的索引增大造成的。

个人觉得这些性能数据接近这些操作在MySQL在380G3上的极限性能。因为这些基本操作的瓶颈在都在I/O上，并发不可能提高这些性能[注]。倒是调整操作系统的参数和内核还可能有一些优化的余地。

当然改善硬件设备和关闭二进制日志等优化还是可以提高一些性能。

2.3 MySQL的版本

从上面的测试结果来，使用MyISAM引擎，MySQL的数据库的基本功能性能没有太多变化。MySQL后面的几个版本介绍说明有性能提高的地方也都不包括基本功能这块。

而对于4.0后提供的查询Cache这个功能，其实也是差强人意，（后面会着重讨论）。所以指望升级数据库大规模改善数据表的基础功能性能看来不现实。但考虑到4.0和4.1版本提供了一些新的功能，而且更加稳定。使用4.0以后的版本还是正确的选择[注]。

MySQL的升级版本其实比想像的容易，MyISAM,InnoDB的文件格式都是与位置无关的（当然操作系统的限制除外）。3.23的数据库升级4.0运行一个更改数据库权限表的脚步就可以万事大吉。而升级到4.1会有一些麻烦，4.1后的数据库的认证方式发生了变化，所以使用旧有的数据库客户端版本无法登陆。但可以通过修正新数据库的密码解决这个问题。详细见《Client does not support authentication protocol》

3 MySQL的查询Cache测试

我曾经对MySQL的Cache作了非常大的期望。因为提高数据库的性能的最好方法就是使用大规模的Cache，但是通过查询相关资料我了解到MySQL的查询Cache有很多的限制。

3.1 Without Cache测试

可以看到,MySQL从4.0后查询速度有所下降,所以我们也测试了一下不使用Cache的数据。我测试了4.0gcc编译版本和4.1gcc编译版本在在使用Cache和不使用Cache的情况下的速度。

关闭查询Cache的方法为调整参数 query_cache_size为0 或者设置 query_cache_type为0 [注]。

表6 是否使用cache的环境下的测试对比

数据库

4.0gcc with cache

耗时（s）

4.0gcc without cache

耗时（s）

4.1gcc with cache

耗时（s）

4.1gcc without cache

耗时（s）

插入1000000条记录

169

176

178

查询1000000条记录

282

200

306

219

改写1000000条记录

165

164

174

172

删除1000000条记录

172

171

179

181

插入5000000条记录

876

866

898

905

查询5000000条记录

1472

995

1595

1096

改写5000000条记录

816

832

859

875

删除5000000条记录

958

875

976

942

插入10000000条记录

1758

1743

1817

1878

查询10000000条记录

3001

2034

3257

2215

改写10000000条记录

1844

1744

1904

1862

删除10000000条记录

1908

1892

1982

2031

表7 是否使用cache的环境下的测试对比

数据库

4.0gcc with cache

处理速度(条/s)

4.0gcc without cache

处理速度(条/s)

4.1gcc with cache

处理速度(条/s)

4.1gcc without cache

处理速度(条/s)

插入1000000条记录

5917.16

5681.82

5617.98

查询1000000条记录

3546.10

5000.00

3267.97

4566.21

改写1000000条记录

6060.61

6097.56

5747.13

5813.95

删除1000000条记录

5813.95

5847.95

5586.59

5524.86

插入5000000条记录

5707.76

5773.67

5567.93

5524.86

查询5000000条记录

3396.74

5025.13

3134.80

4562.04

改写5000000条记录

6127.45

6009.62

5820.72

5714.29

删除5000000条记录

5219.21

5714.29

5122.95

5307.86

插入10000000条记录

5688.28

5737.23

5503.58

5324.81

查询10000000条记录

3332.22

4916.42

3070.31

4514.67

改写10000000条记录

5422.99

5733.94

5252.10

5370.57

删除10000000条记录

5241.09

5285.41

5045.41

4923.68

是否使用Cache的性能比较

这个测试可以证明，使用Cache时，同样是有成本的，特别是对查询语句，成本还比较高,单条查询SQL的使用Cache成本大约是比不是使用Cache的成本高40%，这个成本应该来自MySQL要消耗将结果保存到Cache和淘汰出Cache的时间。[注]

这儿说的是成本，不是性能比较。不过还是怀疑MySQL的Cache设计有问题。

MySQL有一个配置参数query_cache_type 表示查询的Cache类型。0表示 OFF，不进行缓冲，1 表示ON，进行缓冲，2表示 DEMAND，只对SELECT SQL_CACHE开头的查询进行缓冲。

3.2 查询时使用Cache

如何让查询时能使用上Cache，我考虑这样进行测试，查询1条记录后，再查询4次。同时不进行任何操作，保证后面的查询命中Cache。测试的数量为1000000条。

测试的结果如下：

表8 查询1000000条记录5次的消耗时间

数据库

查询1000000条记录5次耗时

平均

MySQL3.23

1082

4621.07

MySQL 4.0 gcc

892

5605.38

MySQL 4.1 gcc

855

5847.95

MySQL 4.1 gcc(不使用查询Cache)

1410

3546.10

MySQL 4.1 icc

858

5827.51

MySQL 4.1 icc(不使用查询Cache)

1530

3267.97

MySQL 5.0

942

5307.86

是否使用cache对查询效率的影响

可以看到MySQL的Cache在这个理想的测试环境下确实提高了。但是这些提高也是要付出代价的。

3.3 查询Cache的代价

正当我对上面的结果欢心鼓舞是，而查询的资料发现给自己浇了些冷水，上面的测试非常理想，MySQL的Cache使用有很多限制。

首先我们从Without Cache组测试数据可以看到，使用Cache后进行查询是有成本的。

在Cache中的MySQL查询结果是和查询SQL对应的，如果你想命中Cache，两次使用的查询语句必须完全一样。所以你最好使用同1个人编写的API查询数据库。

另外，MySQL的查询Cache淘汰策略近乎弱智，对于MyISAM引擎，对数据表的任何一次都会淘汰所有相关此表在Cache中的数据[注]，而InnoDB的引擎会在任何一次提交后淘汰所有的和此次交易相关表在Cache中的数据。所以对于一个频繁进行操作的数据库，MySQL的查询Cache的命中率肯定不会太高，而且查询同时要增加和Cache相关的成本，要消耗有入Cache和淘汰出Cache的时间。

所以你在使用MySQL Cache时最好仔细考虑一下。如果数据库的数据表基本是静态数据。可以使用这个Cache。如果是动态数据，而且改写操作频繁，是否值得用这个Cache很值得怀疑。[注]

更加理想的淘汰方式当然是和键相关。这正是c4a 和其他很多大型数据库Cache的设计思想。

从个人观察得数据来看，真实环境的查询Cache的命中率大约在3%左右，我观察得最好的情况某产品的DB命中率为30%，但是我怀疑其数据库中有一段时间不更改的静态表（混合静态数据和动态数据这不是一个良好的数据库设计风格）。即使有30%的命中率，考虑到前面所提到的查询成本，还是得不偿失，所以我个人建议在大规模更新处理的数据库上，关闭查询Cache。

4 InnoDB的性能

第一次认识InnoDB时，就是听说InnoDB能提供MySQL的支持事务和外键等支持，但是其性能大大不如MyISAM引擎。但是后来发现公司内部的游戏产品都在使用InnoDB数据库引擎，翻阅文档也发现InnoDB已经是MySQL自己从4.0开始内建的引擎。所以拿来比较一下。

4.1 MySQL数据库引擎

下面先介绍MySQL现有的数据库引擎，其中的InnoDB引擎已经不是一个过家家的数据库引擎，其为MySQL提供了许多标准数据库的基本支持。

MySQL支持的引擎种类之多有点吓人，但多少给人一些有点滥的感觉。

表9 MySQL的DB引擎

MySQL数据库引擎

特点

说明

isam

ISAM为一种的专为磁盘存取文件设计的文件组织方式，好像还是IBM开发的。mysql 3.23版本前使用的古董

即将被淘汰。

myisam

ISAM文件格式的改进版本

采用1张表定义对应1个文件，表数据对应一个文件，索引对应一个文件的方式，

有长度限制，

很多数据操作都是表锁定

恢复能力较弱，但有修复工具

3.23后默认的引擎

merge

可以将多个同一库相同结构MyISAM表组织为一个逻辑单元，依靠它可以突破MyISAM文件的大小限制，有点像一个自己建立视图（加索引）

限制很多，查询性能低下，打开一个MERGE表相当于打开所有的相关表。

算不上一个引擎，只能说是一个表格式

4.0以后支持一个查询关键字UION，没有太大使用的必要

HEAP

放在内存中使用的数据表，要求字段长度固定。

速度是快，但完全没有后备存储，只能存零时数据

索引采用散列，只对=,<=>操作有提高效率作用

BDB

大名鼎鼎的Berkeley DB引擎，

有事务性(为页面加锁)

Berkeley DB好像只支持key->data的模式，不知道具体结合情况。

国内几乎没有人在MySQL上用这个引擎

InnoDB

支持事务，外键，

采用数据表空间对应数据文件的管理方式，数据表没有大小限制

采用日志方式记录操作，在崩溃后大部分情况可以自动恢复，但是如果恢复后仍有问题修复较为繁琐

对于包含检索和修改的查询命令支持行锁定

4.0后的版本内嵌支持

有点像1个简化的Oracle

count(*),. truncate等操作慢，

MaxDB

MaxDB是SAP 授权给MySQL 的基于 SAP DB的数据库引擎。

国内用的人也很少

也不太了解授权协议内容

ARCHIVE, ,

只支持插入和查询操作，

5.0后支持

FEDERATED

访问远程数据

5.0后支持

4.2 InnoDB的性能

InnoDB引擎的性能可以用冰火两重天形容，网上对它的评价也可谓大相径庭。其实核心问题只有一个。你如何使用事务。其实坦白说所有的数据库，包括Oracle，DB2的频繁的提交事务都将大大影响数据库的性能。

重要的参数如下：

数据库为4.0.22的数据库,使InnoDB作为引擎，

数据文件每个2G，8个

日志文件3个，每个150M

Cache为打开状态

set-variable = key_buffer_size=384M

set-variable = query_cache_size=64M

set-variable = innodb_mirrored_log_groups=1

set-variable = innodb_log_buffer_size=8M

innodb_flush_log_at_trx_commit= 1 [这个参数很重要]

set-variable = innodb_buffer_pool_size=1G

set-variable = innodb_additional_mem_pool_size=20M

4.2.1 AUTOCOMMIT自动提交

MySQL对于事务的处理有两种方式：

自动提交方式，SET AUTOCOMMIT=1就是每条SQL MySQL都自动帮你提交。这样就相当于每条SQL都是一个事务。

手动提交方式，SET AUTOCOMMIT=0，必须自己写事物的开始（START TRANSACTION）,结束（COMMIT）语句或者回滚（ROLLBACK）语句。

自动提交的方式将使系统的事务成千倍的增加，所以性能自然会下降很多。而有趣的是MySQL的默认事务使用方式居然是自动提交方式，而且好像没有方法进行配置修改，SET AUTOCOMMIT是针对对每个SESSION处理。（后面会提到这个问题）

对于InnoDB的测试我们分为自动提交和手动提交两种，手动提交在连接数据库后，发送SET AUTOCOMMIT=0设置，然后通知开始交易START TRANSACTION，然后再进行后面的操作，在所有的操作完成后，在进行提交COMMIT，（注意只有一次交易事务）。而自动提交和前面的方式一样。

对比测试数据为100000条。(不是我偷懒不测试大数据量，自动提交太慢)

测试结果数据：

表10 AUTOCOMMIT测试100000条记录

参数配置

插入100000条记录耗时

平均

查询100000条记录耗时

平均

修改100000条记录耗时

平均

删除100000条记录耗时

平均

SET AUTOCOMMIT=1

2500s

40/s

27s

3704/s

2503s

2506s

40/s

SET AUTOCOMMIT=0

17s

5882/s

21s

4761

16s

6250

16s

6250

结果数据差别就是这么大，自动提交的性能真是惨不忍睹，所有的修改操作和非自动提交都有200多倍的性能差距。

从上面的结果我们看出，自动提交是一种不可接受的方案，但是如果使用非自动提交有两个限制，现有代码要更改，对于CGI这样的应用，每次就是一个语句进行修改，这样仍然是1个会话1个事务，对于大规模的这样使用效率仍然会造成低下。

正当我对此无比疑惑的时候，zengyu老大告诉我他们修改一个默认参数innodb_flush_log_at_trx_commit提高性能，查询了这个参数的解释如下：

表11 innodb_flush_log_at_trx_commit的取值说明

innodb_flush_log_at_trx_commit取值

说明

每秒写1次日志，将数据刷入磁盘，相当于每秒提交一次事务。

每次提交事务写日志，同时将刷新相应磁盘，默认参数。

每提交事务写一次日志，但每隔一秒刷新一次相应的磁盘文件[注]

如果你对安全没有苛刻要求，可以忍受极少量错误，而且你的业务是大量小规模的交易，你可以使用innodb_flush_log_at_trx_commit =0来加快处理效率。

上面取值说明解释完全抄于《MySQL权威指南》，其实我对这段话有点疑惑，到底最后刷新的是日志文件，还是日志文件和数据文件都刷新？从MySQL的参考手册的英文看是日志文件(commit the log is flushed to disk, and the modifications made by the transaction become permanent)。InnoDB引擎应该和大部分商用数据库相同，先改写日志，再改写数据文件，所以可以保证在故障后快速恢复。

既然知道还有窍门，我们将innodb_flush_log_at_trx_commit=0后，再进行一组测试。

表12 innodb_flush_log_at_trx_commit=0下的AUTOCOMMIT测试100000条记录

参数配置

插入100000条记录耗时

平均

查询100000条记录耗时

平均

修改100000条记录耗时

平均

删除100000条记录耗时

平均

innodb_flush_log_at_trx_commit=0

SET AUTOCOMMIT=1

18s

5556/s

27s

3704/s

17s

5882/s

17s

5882/s

innodb_flush_log_at_trx_commit=0

SET AUTOCOMMIT=0

17s

6667/s

20s

5000/s

15s

5882/s

14s

7142/s

我们可以看出这样，操作的效率可以大大提高，改写操作的速度都提高了很多。可以看作对于innodb_flush_log_at_trx_commit=0的设置下，InnoDB引擎自己控制提交的时机。

看来如果使用InnoDB，而且你的应用又是大数量级小事务操作（我们公司的业务基本上都是），还是使用innodb_flush_log_at_trx_commit=0比较好，对于一次有大规模的操作最好还是自己控制事务[注]。

即使使用innodb_flush_log_at_trx_commit=0，你只要在会话中使用SET AUTOCOMMIT=0标示不使用提交，你仍然可以使用START TRANSACTION 和COMMIT保证事务性。（事务性对于数据库和网络分布设计中的重要性是无需多言）

4.2.2 InnoDB和MyISAM的性能比较

本是同根生，相煎何太急。J

仍然采用大规模的数据的方式进行测试。测试环境为4.0,数据仍然保留Cache和二进制日志。

表13 MyISAM和InnoDB的性能比较

比较项目

MySQL4.0 MyISAM引擎

InnoDB引擎

innodb_flush_log_at_trx_commit=1

使用AUTOCOMMIT=0

InnoDB引擎

innodb_flush_log_at_trx_commit=0

(默认使用AUTOCOMMIT=1)

耗时(s)

处理速度（条/s）

耗时(s)

处理速度（条/s）

耗时(s)

处理速度（条/s）

插入1000000条记录

169

5917.16

162

6172.84

176

5681.82

查询1000000条记录

282

3546.10

195

5128.21

277

3610.11

改写1000000条记录

165

6060.61

156

6410.26

182

5494.51

删除1000000条记录

172

5813.95

138

7246.38

164

6097.56

插入5000000条记录

876

5707.76

869

5753.74

937

5336.18

查询5000000条记录

1472

3396.74

987

5065.86

1413

3538.57

改写5000000条记录

816

6127.45

778

6426.74

937

5336.18

删除5000000条记录

958

5219.21

710

7042.25

863

5793.74

插入10000000条记录

1758

5688.28

1805

5540.17

2107

4746.08

查询10000000条记录

3001

3332.22

1994

5015.05

2879

3473.43

改写10000000条记录

1844

5422.99

1588

6297.23

1858

5382.13

删除10000000条记录

1908

5241.09

1453

6882.31

1732

5773.67

从测试数据可以看出，MyISAM引擎和InnoDB在性能上基本没有太大的区别，半斤八两。AUTOCOMMIT=0的方式有较好的性能，但其对WEB的应用不是太实用（事务一多性能还是要急剧下降），我们重点比较InnoDB在innodb_flush_log_at_trx_commit=0下和MyISAM引擎的性能。

由于MyISAM的特性而且测试用例是顺序插入, MyISAM引擎占了些便宜。在MyISAM数据文件没有空洞时(删除记录会产生一个没有使用的记录区)，插入记录操作没有进行锁表操作（应该说是写锁），（因为所有的记录都是插入文件末尾）。由于我的测试正好属于这种情况，在测试中MyISAM引擎在INSERT语句上表现优秀，但是对于实际的环境，这个优势估计不会存在。

修改部分MyISAM引擎较好这不能反映真实情况，InnoDB的纪录锁的优势无法体现。[注]

InnoDB采用记录锁同步操作，MyISAM采用的是表锁。

MyIsam的的客户在访问数据前，必须得到相应的锁，而且同时写操作的优先级高于读取操作，可以这样理解，MyIsam引擎对于表的查询操作有两个锁队列，一个是读取锁队列，一个是写入锁队列，MyIsam引擎总是优先处理写入锁队列等待的请求。而一个写入锁将阻塞后面写入和读取操作，而读取锁操作只阻塞所有的写操作。

而InnoDB的引擎对于读取操作几乎不加锁，如果此记录正在被写，才会阻塞此记录相关的读取操作和写入操作。

一般认为MyISAM引擎利于检索，因为查询操作使用读取锁可以并发，InnoDB的引擎在大量更改的更改操作环境有更好的表现。

比较图表如下：

图5 InnoDB和MyISAM的性能比较

必须要指明的是，这个测试其实是比较利于MyISAM 引擎的，由于MyISAM的引擎的设计是1个表1个文件的，所以在真实环境，数据文件内部必然有记录碎片，（定期使用交互命令可以优化这个问题），而这些碎片会MyISAM的降低处理性能，而且MyISAM采用文件锁处理模式，不利于真实环境下的并发操作，后面的一些模拟测试也证明了这个问题。

4.3 总结

InnoDB和MyIASM引擎谁优谁劣其实是一个很难衡量的问题，我把我知道的两者的情况都拿出来让你自己对比一下。

表14 MyISAM引擎和InnoDB引擎的对比

MyISAM

InnoDB

性能

也不错，

在AUTO COMMIT=0的情况下好于MyISAM，

支持事务

不支持

支持

支持外键

不支持

支持

可移植

理论可以

表，索引大小是否有限制

有

无

锁类型

数据表的读写锁

记录锁定

是否存在死锁的可能

几乎无

存在

FULLTEXT索引

支持

不支持（路标版本有规划）

备份恢复

支持mysqldump备份为SQL语句

也支持mysqlhotcopy完全备份

支持mysqldump备份

完全热备份要依靠innodb的付费备份工具

维护

简单

较MyISAM复杂

故障率

在大数访问量下容易出现故障，但是提供了修复工具[注]

故障率低，一般的故障可以自动恢复

但如果恢复后仍然有故障修复比较麻烦，

文件碎片

在删除后存在碎片，但是可以通过命令进行

文件内部分片，也有碎片，但是影响较小。

已知缺陷

在很多数据的基本特性上没有提供支持

大压力环境下故障率较高

Select count(*) 慢

Truncate慢

不支持AUTO_INCREA初始化数值

innodb 可能存在一个限制， innodb引擎中使用的内存总和不能超过2G,否则会宕机。根据《MySQL Reference Manual》中的说明是由于glic引发的问题。对于这个问题我比较疑惑，实际测试的结果没有出现类似问题，而且感觉按照文档的描述问题原因,，mysqlisam也应该有类似的缺陷，但是从未见过说明？

个人感觉在大访问量的情况下，InnoDB引擎还是一个更好的选择。

MyISAM引擎的故障主要就是索引错误（数据文件和索引不一致），其实出现这个错误，主要是由于操作的不当造成的：

(1)在联机状态kill的mysqld。

(2)不锁表，在mysqld运行的时候拷贝，读写,备份数据文件，(很多备份数据文件采用直接的拷贝方式，这样不仅得不到可用的备份文件，还会破坏现有的数据文件)

(3)不锁表，在mysqld运行的时候，使用外部程序影响数据文件，比如用myisamchk在联机状态下修复。（mysqlcheck可以用于联机修复，一直错误认为mysqlcheck 也有问题，谢谢 owenzhuang指明。）

在联机状态下对数据表的外部操作必须按照LOCK TABLE，FLUSH TABLE，操作，UNLOCK TABLE的方式进行。

对于损坏的表，MySQL 提供了几种修复方法，一种是脱机使用myisamchk 进行修复，一种是联机使用交互命令CHECK TABLE 和 REPAIR TABLE 进行修复。另外，MyISAM引擎也有自我恢复功能。

InnoDB现在已经被Oracle收购，作为了Oracle的一个开源项目，一方面我期待有新的强大技术背景的公司大的支持下，InnoDB的性能能得到更大的提高的同时，另一方面，我倒要为它的未来捏把汗。（《Oracle收购InnoDB对MySQL的影响》一文对此有一些讨论）

5 模拟真实环境下的性能

再次强调的是，前面的测试数据都是性能测试，不能作为实际环境的性能数据，这些数据表现的应该是理想环境下的极限性能，其只能作为参考。

既然测试用例不完整，我们就考虑模拟一下真实的环境。

5.1 压力测试

先准备检测一下MySQL在压力情况下的性能变化情况，测试的版本为MysQL4.0 gcc静态版本，使用的配置参数和原来相同。

我先在数据库中插入10000000条记录，然后我在另外一台机器机器上启动1组压力进程对数据库进行查询或者修改操作，测试的进程组包括10，50，100个读的进程，10，50，100个读写进程（读写各一半），这些进程的操作间隔时间为10000微秒（0.01秒执行一次）或者1000微妙（0.001秒执行一次）。其中0.01秒执行一次间隔的压力相对较轻，0.001秒执行一次间隔压力就比较重了，一般用mysqladmin processlist检查，大部分进程的状态都是执行状态。

同时在这台机机器（本机上）测试查询1000000条记录（总记录数的1/10）所耗费的时间。同时为了比较，使用vmstat记录相关的CPU占用率的情况。分别记录压力进程运行后，测试进程运行前后的CPU空闲率。

MyISAM 引擎的测试结果如下：

表15 MyISAM引擎的压力测试

测试项目

压力进程的操作间隔为0.01秒

压力进程的操作间隔为0.001秒

运行前CPU空闲(%)

运行后CPU空闲(%)

耗时(s)

处理速度(条记录/s)

运行前CPU空闲(%)

耗时(s)

处理速度(条记录/s)

没有压力进程

100

258

3875.97

100

258

3875.97

10个读取压力进程

282

3546.10

318

4048.58

50个读取压力进程

316

3164.56

700

3333.33

100个读取压力进程

379

2638.52

2093

2283.11

10个读写压力进程(5读+5写)

247

3144.65

361

2770.08

50个读写压力进程(25读+25写)

300

1428.57

4206

237.76

100个读写压力进程(50读+50写)

438

477.78

大约需要200000

0.50

对于MyISAM引擎，最后一组测试我没能作完，实在是太慢了，在6个小时后我检查发现查询才进行到12477，我几乎崩溃，估计要让他跑完要5-6天，只好作罢。

比较的图表：

图6 MyISAM引擎的压力测试

通过比较，我们可以看出，在只有读取操作的压力下，MyISAM引擎表现还不错，在压力增加的情况下性能没有出现陡降。而对于读写的混合压力测试情况下，MyISAM引擎的表现急转直下，特别是最后的100个读写0.001s间隔压力进程测试情况下，速度已经不可接受。这是检查MySQL的状态计数变量Table_locks_waited数值非常大，而且远远大于Table_locks_immediate。这表示系统操作中，等待锁的操作非常多。

由于MyISAM引擎采用表锁，所以在读写压力测试下，由于写操作锁会阻塞所有的读取锁，而且优先基本大于读取锁，所以此时读取操作必须等待。而且由于最后的压力测试写操作频度很高，所以造成了几乎1秒1条的读取速度。而且值得注意的是此时CPU性能压力并不大，系统的能力并没有得以发挥。

为了横向比较MyISAM引擎和InnoDB引擎在不同压力的表现，我也测试了InnoDB在压力情况下的表现。

表16 InnoDB引擎的压力测试

测试项目

压力进程的操作间隔为0.01秒

压力进程的操作间隔为0.001秒

运行前CPU空闲(%)

运行后CPU空闲(%)

耗时(s)

处理速度(条记录/s)

运行前CPU空闲(%)

耗时(s)

处理速度(条记录/s)

没有压力进程

100

278

3597.12

100

278

3597.12

10个读取压力进程

281

3558.72

387

4504.50

50个读取压力进程

312

3205.13

634

3690.04

100个读取压力进程

326

2583.98

892

2832.86

10个读写压力进程(5读+5写)

222

3067.48

276

3623.19

50个读写压力进程(25读+25写)

271

1577.29

1243

804.51

100个读写压力进程(50读+50写)

353

1121.08

4011

249.31

比较的图表：

图7 InnoDB引擎的压力测试

可以看出InnoDB引擎在压力测试面前表现得不错，基本上是随着压力的增加，速度呈现线性的下降（注意第2步测试的进程是50个为第一步的5倍）。

为了方便比较，我也给出InnoDB和MySQL的两个比较图表，必须指出的是，这样的比较意义有一定的局限性。因为并不能保证每个连接对于两种引擎的压力都一致。

MyISAM和InnoDB的读取压力测试比较

图9 MyISAM和InnoDB的读写压力测试比较

从对比图表可以看出[注]，InnoDB引擎可以更好的承担查询压力。

MyISAM 为了避免在大压力的环境下性能下降，可能必须将表分割的更细。

如果你够心细应该可以发现在加入0.01秒读写压力测试后，得到的性能反而提升了。？而且MyISAM引擎和InnoDB引擎的数据都有提升？这个现象非常有趣。为了验证不是干扰，我重新测试了一次InnoDB在这个情况下的表现，发现测试结果没有错误。我猜测这还是和查询的Cache有关，InnoDB将查询结果放入Cache可能是在提交操作时，而在在提交时发现同时有更改语句的情况下，InnoDB引擎没有将查询语句的结果放入Cache。由于减少了Cache的成本，而且相对而言此时的更改操作压力很轻，所以这样反而加快了查询的速度。

5.2 多表访问环境模拟

前面测试的方式都是使用单张表进行测试，在多张表的情况下，MySQL的表现如何？我打算这样模拟，采用10张表，每张表存放5000000条记录，在另外一台机器上，每个进程都查询访问一个表，查询频率为0.005秒一次，查询记录为随机数选取，同时在本机上进行插入，查询，更改，删除1000000条记录的操作。

对于MyISAM引擎，同时由于增加了表的数量，调整了一些相关的参数，

set-variable = key_buffer_size=512M

set-variable = query_cache_size=128M

对于InnoDB引擎，仍然使用原来的配置，同时由于测试机器发生了更改[注]，为了对比的公正性，我重新测试了没有压力（没有程序同时访问）情况下的对比数据。

表17 多表访问的环境模拟

参数配置

插入100000条记录耗时

处理速度(条记录/s)

查询100000条记录耗时

处理速度(条记录/s)

修改100000条记录耗时

处理速度(条记录/s)

删除100000条记录耗时

处理速度(条记录/s)

MyISAM引擎,

197

5076.14

333

3003.00

173

5780.35

175

5714.29

MyISAM引擎,模拟多表环境

209

4784.69

3366

297.09

566

1766.78

507

1972.39

InnoDB引擎,

221

4524.89

425

2352.94

255

3921.57

234

4273.50

InnoDB引擎, 模拟多表环境

520

1923.08

1853

539.67

677

1477.10

629

1589.83

对比图表为

图10 模拟多表访问的测试

在非模拟情况下，MyISAM 引擎有少量优势，但是在模拟环境下查询记录情况,MyISAM引擎的表现远慢于InnoDB的表现，对于这个我感觉有点奇怪，因为此时不应该有锁排队的情况发生。？

在压力情况下，MyISAM引擎的插入语句性能看似不错，但是这是由于在我的测试用例中，MyISAM引擎不用加入写锁锁定表。（见前InnoDB和MyISAM引擎的对比测试章节）

由于所有的操作都是顺序操作，而且没有加入写操作，感觉测试比较倾向于MyISAM引擎[注]。

测试的机器也是为380G3，但是内核为2.4.21，初步看来2.6内核的性能更好一些。

对这个模拟感觉不是太理想，模拟正式环境和测试极限速度这两者可能就有矛盾。

5.3 网络访问速度

前面的测试基本都是基于本机操作访问数据库的操作，但是更多的应用是基于网络访问。所以也业考虑测试一下网络环境下MySQL访问速度。

基于网络的访问分为2种，一种是利用长链接，一种是短链接，就是每次访问都重新进行一次链接。

测试环境，数据库使用MyISAM引擎，使用Cache。网络带宽为100Mbps。

由于对于1个服务器，可以循环使用的SOCKET端口是有限，所以我只执行了20000次操作进行比较，由于测试的数据较少，对于这些数据比较差距就可以了。

比较项目

本地操作

网络操作

使用长链接

网络操作

使用短链接

网络操作(STMT)

使用长链接

耗时(s)

处理速度（条/s）

耗时(s)

处理速度（条/s）

耗时(s)

处理速度（条/s）

耗时(s)

处理速度（条/s）

插入20000条记录

5000.00

1538.46

740.74

2500.00

查询20000条记录

5000.00

952.38

588.24

1333.33

改写20000条记录

5000.00

1538.46

689.66

2500.00

删除20000条记录

6666.67

1538.46

555.56

2857.14

比较图表如下：

图11 网络环境性能下的测试

对于网络的访问，网络长链接的访问速度是本地执行速度的3-4倍（降低了60%），网络短链接的访问速度是本地执行速度的7-8倍（降低了85%）。[注]

注意这个速度只是单链接下的极限速度，所以这并不代表MySQL在网络环境下的处理速度（但个人认为极限速度不可能超过2倍），但是我们还是看出，MySQL在网络环境下速度有相当的下降[注]。而使用网络短链接要付出连接和断开连接的成本。

STMT的操作方式（参看STMT的章节）由于不用重新传递分析SQL，在网络环境也表现了出了优于其他方式的性能。

由于测试的数据较少，可能影响到测试的时间准确程度。

对于长链接的网络访问速度有如此的下降，我开始很怀疑，我查询了我原来对Oracle作过的一组入库测试数据，在同样的100M网络环境环境下速度只降低了30%左右。但我重新测试了1000000条的数据，但得到的结果还是如此。（当然也可能是IDC内部的网络过于繁忙）

基于MySQL在网络环境中的表现差强人意，如果硬件条件允许，在作DB服务器设计时，将DB服务器和MySQL放在同一个机器下可能是一个更好的选择。

5.4 小结

如何模拟真实的环境一直是我比较头大的问题，不管我如何模拟，好像感觉仍然只能专注到某个方面从而有相对性，而且模拟现实和测试极限性能本身可能就相互矛盾。这些测试数据更多的应该是一种参考，使你了解系统地性能极限和性能瓶颈[注]。如果你能有更好的方法模拟真实环境，请通知我。

通过前面的测试，个人感觉对于类似我测试环境的系统（机器+DB+表），个人感觉真实环境的最高本地处理速度可以接近2500条左右（包括读写），网络操作的最高速度可以接近1500条。

6 STMT的测试

MySQL的API为什么在网络环境下表现较差？一方面由于MySQL的API全部直接使用SQL语句执行，SQL的分析成本是比较大的，同时每次都要传递SQL也占用带宽，一方面由于MySQL的结果是采用字符串的方式传递的，也是一个比较耗费资源的事情。为了解决这些不足，MySQL推出了一组新的API STMT。(我到现在没有搞明白是什么的缩写？)

STMT是MySQL 4.1版本的一个重要的噱头，其提供一种和以往的API不同的形式访问数据库。其类似Oracle等提供的绑定变量的API方式。

这样的方式基本方法是使用一个绑定参数的SQL，这个SQL在使用前先要进行分析准备（mysql_stmt_prepare），在执行前还必须，为输入参数绑定变量（mysql_stmt_bind_param）比如查询参数，插入语句参数，为输出参数绑定结果（mysql_stmt_bind_result），比如查询语句。下次在进行插入和更新操作时就可以直接修改相关的参数，由于SQL语句已经经过分析，再次处理的速度可以得到大大的提高。

STMT的API基本是由自己独立的一套，如mysql_stmt_execute，mysql_stmt_errno

STMT的重要优势是避免了语法分析SQL的成本，同时由于STMT语句在次执行的时候无需传输大量的SQL语句数据，可以大大减少网络流量，在网络环境下也更有优势。

为了进行横向的比较，我将测试分成两种方法，一种是使用每次都使用Prepare语句的测试，一种是只在第一次使用Prepare语句，后面的操作只更新邦定变量。查询的操作都完成转储结果集。

表18 STMT的性能测试

MySQL4.1使用传统API的

MySQL4.1使用STMT API,每次都使用Prepare

MySQL4.1使用STMT API,只有第一次都使用Prepare

耗时(s)

处理速度（条/s）

耗时(s)

处理速度（条/s）

耗时(s)

处理速度（条/s）

插入1000000条记录

176

5681.82

334

2994.01

123

8130.08

查询1000000条记录

306

3267.97

406

2463.05

170

5882.35

改写1000000条记录

174

5747.13

279

3584.23

117

8547.01

删除1000000条记录

179

5586.59

257

3891.05

130

7692.31

插入5000000条记录

898

5567.93

1689

2960.33

653

7656.97

查询5000000条记录

1595

3134.80

2020

2475.25

863

5793.74

改写5000000条记录

859

5820.72

1430

3496.50

677

7385.52

删除5000000条记录

976

5122.95

1380

3623.19

798

6265.66

插入10000000条记录

1815

5509.64

3423

2921.41

1360

7352.94

查询10000000条记录

3257

3070.31

4065

2460.02

1742

5740.53

改写10000000条记录

1904

5252.10

2969

3368.14

1482

6747.64

删除10000000条记录

1982

5045.41

2794

3579.10

1693

5906.67

相关数据的比较图表如下。

STMT API性能测试

从上面看出，如果每个语句都使用Prepare，居然还慢了好多。看来这种STMT模式并不适合SQL语句要反复重新更换的环境。否则Prepare的成本反而更加突出。（只能执行一次CGI的环境就不要用这个东西了。）

而只在第一次执行前使用Prepare的测试结果令人惊喜，普遍的速度提高在30%左右，这个速度的提升是非常鼓舞人心的。所以看来STMT的API非常适合数据库服务器SERVER类的开发。

另外要补充说明的是STMT的语句不使用查询Cache。

7 MySQL的优化

熟悉Oracle优化的人都知道Oracle的重要优化对象就是SGA区,其实MySQL也是一样的.MySQL的参数配置在my.cnf文件中。[注]。

mysql的配置大部分在my.cnf中，参数和SHOW VARIABLES显示的变量一样名称，但是有些却不是，有些是在启动参数中设置，有些是必须用SET语句更改。而且多个版本中有些参数名称还不一样。有时候搞得人也挺头大的。

7.1 配置参数

MySQL有两种途径途径了解其的配置参数，一个是MySQL交互模式下的命令SHOW VARIABLES，一个使用mysqladmin variables 查询。

MySQL的配置参数分为2种，全局的和局部的。局部的配置变量可以在每次会话中自己更改。

从MySQL 4.0以后开始，在SHOW VARIABLES中显示的参数，大部分可以动态使用SET命令进行更改。

基本参数配置：

参数

说明

bind-address

绑定的IP地址

user

用户

port

端口号

datadir

数据文件目录

basedir

msyql应用程序的目录

socket

socket文件，默认在/tmp目录下，但是建议不要这样设置，/tmp目录是一个大家都愿意破坏的目录

default-table-type

默认表类型

查询的Cache的是从MySQL4.0版本开始提供的功能。相关的参数为：

参数

说明

query_cache_size

查询Cache的尺寸

query_cache_type

查询的Cache类型。

0 OFF，不进行缓冲

1 ON，进行缓冲

2 DEMAND，对SELECT SQL_CACHE开头的查询进行缓冲

query_cache_limit

查询的结果的限制长度，小于这个长度的数据才能Cache

MyISAM的索引参数：key_buffer_size为MyISAM引擎的最关键的优化参数之一。

参数

说明

key_buffer_size

(关键参数),索引块用的缓冲区大小，所有的连接程序线程共用

key_cache_block_size

每一个索引block的大小，默认1024字节,从4.1.1后才出现这个参数，原来都是直接采用1024字节作为Block的长度

InnoDB使用的参数：InnoDB的参数较少，笼统而不细致，内存的管理多由InnoDB引擎自己负责，主要的缓冲就是innodb_buffer_pool_size参数分配的缓冲。这样配置倒是简单了，但没有了细致优化乐趣。

参数

说明

innodb_buffer_pool_size

innodb的缓冲区大小，存放数据和索引,一般设置为机器内存的50%-80%

(关键参数)

innodb_log_buffer_size

InnoDB日志缓冲区大小

innodb_flush_method

刷新日志的方法

innodb_additional_mem_pool_size

innodb内存池的大小，存放着各种内部使用的数据结构

innodb_data_home_dir

InnoDB数据文件的目录

innodb_data_file_path

数据文件配置

innodb_log_files_in_group

Innodb日志的

innodb_log_file_size

Innodb日志文件的尺寸

innodb_lock_wait_timeout

等待数据锁的超时时间，避免死锁的一种措施

innodb_flush_log_at_trx_commit

日志提交方式 (关键参数)

0每秒写1次日志，将数据刷入磁盘，相当于每秒提交一次事务。

1每次提交事务写日志，同时将刷新相应磁盘，默认参数。

2每提交事务写一次日志，但每隔一秒刷新一次相应的磁盘文件[注]

innodb_force_recovery

在Innodb的自动恢复失败后，从崩溃中强制启动，有1-6个级别，数值越低恢复的方式也保守，默认为4。尽量使用较保守方式恢复。

恢复后要注释删除这一行。

Log的参数：MySQL的日志有6种，查询日志，慢查询日志，变更日志，二进制变更日志，告警日志，错误日志。my.cnf中可以配置日志的前缀和日志参数。日志是监控数据库系统的重要途径。

参数

说明

log

查询日志，记录所有的MySQL的命令操作，在跟踪数据库运行时非常有帮助，但在实际环境中就不要使用了

log-update

变更日志，用文本方式记录所有改变数据的变更操作，

log-bin

二进制变更日志，更加紧凑，使用mysqlbinlog读取，操作，转换

binlog_cache_size

临时存放某次事务的SQL语句缓冲长度

max_binlog_cache_szie

最大的二进制Cache日志缓冲区尺寸

max_binlog_size

最大的二进制日志尺寸

log-error

导致无法启动的错误日志

log-warnings

告警日志

long_query_time

慢查询时间限度，超过这个限度，mysqld认为是一个慢查询

log-queries-not-using-indexes

没有使用索引查询的日志,方便记录长时间访问的查询进行优化

log-slow-queries

慢速的查询日志，

打开文件参数：

参数

说明

table_cache

能够被同时打开的表最大个数，打开一个表使用2个文件描述符

(关键参数)

open_files_limit

mysqld保留的文件描述符号个数，和table_cache和max_connections设置相关，默认为0

设置为0, 系统设置max_connections*5或者max_connections + table_cache*2中的最大值

关于连接通信的参数：

参数

说明

max_connections

最大的连接数

max_connect_errors

同一个地址最大错误连接数，防止攻击用

net_buffer_length

服务器和客户之间通讯的使用的缓冲区长度

max_allowed_packet

服务器和客户之间最大的通信的缓冲区长度

net_read_timeout

网络读取超时

net_write_timeout

网络写入超时

interactive_timeout

交互模式下的没有操作后的超时时间

wait_ timeout

非交互模式的没有操作后的超时时间

每个会话使用的buffer设置，默认使用my.cnf的配置，也可以使用每个会话设置。不要设置的过大。

参数

说明

read_buffer_size

(record_buffer)

对数据表作顺序读取的缓冲大小

read_rnd_buffer_size

在排序后，读取结果数据的缓冲区大小，

sort_buffer_size

(sort_buffer)

用来完成排序操作的线程使用的缓冲区大小

join_buffer_size

全关联操作缓冲区（没有索引而进行关联操作）

write_buffer_size

myisamchk的特有选项

写入缓冲区大小

myisam_sort_buffer_szie

为索引的重新排序操作（比如CREATE INDEX）的分配的缓冲区的长度

对于磁盘缓式写入的一些选项，delay_key_write，flush，flush_time参数可能可以进一步提高MyISAM引擎的性能，但是在服务器Crash的时候，可能会丢失数据，造成表损坏。

MySQL对于插入语句支持一个选项INSERT DELAYED，如果有这个选项，MySQL将这些插入语句放入一个队列，并不马上读入磁盘。delay_insert_XXX的选项都是配置这个功能，

MySQL创建表的时候也有一个选项，DELAY_KEY_WRITE，有这个选项描述的表的键发生改动后，改动可以缓冲在key_buffer中，不立即回写磁盘。

参数

说明

delay_insert_limit

INSERT DELAYED语句选项。（插入语句的描述）

处理INSERT DELAYED语句，MYSQL插入delay_insert_limit条语句后检查是否有查询语句，如有有去查询，如果没有，则继续插入

delay_insert_timeout

在处理完INSERT DELAYED对列的插入数据后，MYSQL等待delay_insert_timeout秒后看看是否有INSERT DELAYED数据，如果有继续，如果没有结束这次操作。

delay_query_size

INSERT DELAYED插入数据对列的长度

max_delayed_threads

处理INSERT DELAYED语句的最大线程个数

delay_key_write

对于使用DELAY_KEY_WRITE选项的创建的表，可以延缓键读写

0N 不延缓所有的键写如操作

OFF延缓有DELAY_KEY_WRITE选项的标的键写入操作

ALL延缓所有的表

flush

是否要在每个操作后立即刷新数据表

flush_time

每隔多少秒，对数据表进行一次刷新。关闭后打开。

关闭某些选项：关闭某些选项可以加快MySQL的运行速度，这些选项在MySQL SHOW VARIABLES 中显示为have_XXX 的变量。

参数

说明

skip-openssl

关闭mysql服务器对SSL加密的支持

skip-isam

关闭mysql服务器对isam的引擎的支持

skip-bdb

关闭mysql服务器对bdb的引擎的支持

skip-external-locking

不使用外部锁,MySQL的外部锁用于防止其他程序修改正在数据文件，但其在部分系统上不可靠，一般都不使用。(4.03版本前叫skip-locking)

skip-innodb

关闭mysql服务器对innodb的引擎的支持

skip_networking

只能从本地访问数据库

其他参数：

参数

说明

slow_launch_time

用多于这个时间创建的线程视为一个慢创建线程

binlog_cache_size

临时存放构成每次事务的SQL的缓冲区长度，(全局变量，但是应该影响每一个会话)

max_binlog_cache_size

二进制日志缓冲区的最大长度，其实就是事物的最大长度，默认4G

max_heap_table_size

HEAP表的最大允许长度

max_tmp_tables

临时tables的最大个数

myisam_recover_options

myisam引擎的自动恢复模式

thread_cache_size

线程缓冲区的所能容纳的最大线程个数

tmp_table_size

临时tables的最大尺寸

7.2 运行状态监控

MySQL有两种途径途径了解其的运行状态，一个是MySQL交互模式下的命令SHOW STATUS，一个使用mysqladmin extended-status 。两种方法异曲同工，通过观察其运行状态可以了解我们的参数设置是否合理，是否有要优化的表和数据。

SHOW STATUS显示了MySQL从运行开始到现在为止状态，大部分为一些计数器，使用FLUSH STATUS可以重新对各种状态变量进行计数。

表19 MySQL的状态计数器

参数

说明

Aborted_clients

因客户没有正确关闭而丢弃的连接数量，没有正确关闭指没有调用mysql_close就退出，连接超时，数据传送中客户端退出

Aborted_connects

试图连接MySQL服务器但没有成功的次数

Connections

试图连接MySQL服务器的尝试次数，（包括成功的和没有成功）

Com_XXX

执行语句的计数器，比如Com_select变量记录了select语句的个数

Created_tmp_disk_tables

使用磁盘创建临时表的次数，如果要创建的临时表的尺寸大于tmp_table_size，那么临时表将创建在磁盘上，

Created_tmp_tables

创建临时表的次数

Delayed_XXX

INSERT DELAYED语句的执行性能参数

Opened_tables

曾经打开过的数据表总数

Open_tables

当前处于打开的表个数

Open_files

当前处于打开的文件个数

Bytes_received

从客户收到的字节总数

Bytes_send

发送给客户的字节总数

Handler_commit

Handler_rollback

事务提交或者回滚的次数

Handler_delete

对数据表删除一条记录的次数

Handler_update

对数据表修改一条记录的次数

Handler_write

对数据表插入一条记录的次数

Handler_read_first

读取索引中第一个索引项的个数

Handler_read_key

根据索引直接读取一行数据的次数，这个数值高表示数据库有较好的检索能力。

Handler_read_next

根据索引读取下个数据行的请求次数. 在一个索引的区间内进行查询( > < ,orderby 这类查询条件)会影响这个计数器。

Handler_read_prev

根据索引读取前个数据行的请求次数.用于一些反序查询。

Handler_read_rnd

通过一个固定位置(应该就是不通过索引)读取一个数据行的次数。这个数值很高表示你的很多查询操作的结果需要排序，可能这些查询操作不能适当使用索引而要检索整个表。

Handler_read_rnd_next

请求从数据文件中读取下一个记录的次数.如果有很多全表的检索这个值将很高. 通常这表示数据表没有合适的索引。

key_blocks_used

索引缓冲区块中已经被使用的区块大小。Block的尺寸默认是1024字节，4.1.1后可以通过key_cache_block_size参数设置。可以根据key_buffer_size/(1024 or key_cache_block_size) 得到Block总数，然后知道key_buffer的利用率

Key_read_requests

从缓冲读取1个Block的次数

Key_read

从磁盘读取的次数

Key_write_requests

写入索引缓冲区写入一个Block的次数

Key_write

写回磁盘的次数

Qcache_free_blocks

Qcache没有使用的内存块个数

Qcache_free_memory

Qcache没有使用的内存尺寸

Qcache_hits

查询在Qcache中的命中次数，和Com_select比较，就可以知道Qache的大约命中率是多少。

Qcache_inserts

加入Cache中的查询个数

Qcache_lowmem_prunes

由于Qcache不够用，造成替换出Qcache的查询个数

Qcache_not_cached

没有能Cache的查询个数

Slow_queries

慢查询的次数，如果一个查询的所用的时间大于long_query_time设置的时间，则计数加1

Select_XXXX

关联查询的一些状态计数

Innodb_XXXX

InnoDB的状态技术器，不过只有MySQL 5.02的版本才支持这些计数器。这儿略过

Table_locks_waited

必须等待后才能完成表锁定的请求个数，如果这个数值和下面数值的比率过大，表示数据库的性能较低

Table_locks_immediate

无需等待，立即完成表锁定的请求个数。

Thread_connected

现在处在连接打开状态的线程个数

Thread_cached

现在在现场缓冲区的线程个数

Thread_created

到目前为止,创建的线程个数

Thead_running

现在运行的线程个数，不是所有打开的线程都在运行，有些会处于SLEEP状态

InnoDB的状态监控的要在交互模式下使用show innodb status命令。相对的可以利用InnoDB状态参数也过少。

7.3 参数调整

了解了参数的含义，剩下的事情就是如何设置一个合理的MySQL参数了。下面我们结合几个关键参数讲解以下如何通过根据状态了解参数是否设置合理。

比如MySQL的重要参数table_cache,如果设置过小，SHOW STATUS显示的变量Opened_tables 会迅速增加，而正常状态下应该是保持稳定或者缓慢增加。

又比如想要了解查询Cache的状态，可以查询SHOW STATUS显示的Qcache_XXX 变量。变量Com_select表示查询语句的数量（实际放入Cache的语句数量是Qcache_inserts），Qcache_hits表示查询在Cache中命中的数量，除一下就可以得到你的查询Cache的命中率。是否值得使用查询Cache，就很明确了。

Com_XXX相关的状态计数是反映数据库处理的SQL语句的。通过这写计数器你可以了解你的数据库什么操作更多，从而更好的优化。

key_buffer_size对于MyISAM引擎是最重要的参数之一，其的使用情况可以通过key_XXX参数了解，key_blocks_used 状态计数器可以让你了解Key_buffer的使用情况，Block的尺寸默认是1024字节，MySQL4.1.1版本后可以通过key_cache_block_size参数设置。可以根据key_buffer_size/(1024 or key_cache_block_size) 得到Block总数，然后知道key_buffer的利用率。

最大连接数max_connections 是否设置合理可以查询SHOW STATUS的变量Connections，Connections变量为尝试连接的数量，监控其的增长速率就也可以知道客户对连接数的需求程度。

检查Table_locks_waited的数量以及和Table_locks_immediate的比率，可以了解查询操作是否有过多的锁等待，如果等待数量很大，建议你考虑更换引擎或者继续分割你的表。

Thread_XXX相关的状态参数可以告诉你MySQL的线程处理情况，Thead_running和Thread_connected的比率可以让你知道大部分连接的状态如何，是在SLEEP还是RUNNIG。

如果状态中的Created_tmp_disk_tables和Created_tmp_tables的比率过大，表示大量的临时表是在磁盘上创建的。这样效率当然是很低的，所以建议调整tmp_table_size的大小。

Slow_queries也是一个关键的计数器，MySQL将查询时间超过long_query_time的查询时为一个慢查询，出现一个慢查询就会将Slow_queries+1 。而long_query_time的默认设置是10秒，这个时间是比较长的，你可以调整的更加短一些，同时MySQL提供了日志可以记录慢查询的查询语句。my.cnf中可以配置log-queries-not-using-indexes参数记录没有使用索引查询的日志,log-slow-queries参数记录慢速的查询日志，在数据库设计初期阶段，通过这两个日志可以了解数据表的设计是否合理。

7.4 飞得更高

当然，调整MySQL的配置参数只是优化的一种方式。飞得更高的方法还很多。

kenix问过一个奇怪的问题，如果想最小的成本进行优化,如何进行？对于这个问题我和sunbirdcui的答案一致，把硬盘改为RAID10（0+1）的。对于提高性能，升级硬件可能是最简单最省力的方式，而且对于我们大部分的DB，在磁盘空间不够前，机器的性能可能已经远远跟不上服务增长而要分布了。所以建议DB服务器不要采用RAID 5而采用RAID 10，这样可以轻松的提高20-30%的性能。网上能找到的相关比较的文章是《常见RAID模式性能比拼》，不过可惜是其比较的不是SCSI硬盘。

而对于内存的使用方面，MySQL好像没有过高要求，主要的除了每个连接使用的read_buffer_size 和sort_buffer_size尺寸的内存外，全局有一个Qcache使用query_cache_size尺寸的cache。MyISAM引擎主要使用的是key_buffer_size尺寸的内存,InnoDB主要使用内存就是 innodb_buffer_pool_size 尺寸内存。而且InnoDB可能还有一个2G内存使用限制（是否真没有这个问题有待验证）。所以依靠大规模增加内存提高MySQL性能未必有效。MySQL自己在内存管理可以提高的地方还很多。

其他的方面也还有很多，比如Linux的内核优化，2.6的内核的更加倾向于I/O的应用。大家也许记得我前面有台测试机器使用的是380G3 2.4.21的内核，一台是380G3 2.6.8.1的内核我们把它们相同测试的数据列出来。但请大家注意的是，由于不是同一台机器（型号倒是一致），参考意义有待进一步证实。

表20 不同内核下的性能比较

参数配置

插入100000条记录耗时

处理速度(条记录/s)

查询100000条记录耗时

处理速度(条记录/s)

修改100000条记录耗时

处理速度(条记录/s)

删除100000条记录耗时

处理速度(条记录/s)

2.4.21内核下MyISAM引擎,

197

5076.14

333

3003.00

173

5780.35

175

5714.29

2.6.8.1内核下MyISAM引擎

169

5917.16

282

3546.10

165

6060.61

172

5813.95

2.4.21内核下InnoDB引擎,

221

4524.89

425

2352.94

255

3921.57

234

4273.50

2.6.8.1内核下InnoDB引擎

176

5681.82

277

3610.11

182

5494.51

164

6097.56

另外的一个非常重要的决定性能的因素就是你对数据表的设计了。这部内容分包括数据表结构的设计，范式和索引，以及MySQL本身的查询优化.但是它们不在本文的讨论范畴之内了。

8 后记

终于，终于在2005年把手上的测试数据整理为文档了。其实手上最早的测试记录已经是今年6月的了。毕业后工作之外几乎就没有写过5页以上的文档，这个文档几乎又是半途而废。激励自己完成这个稿子的事情很偶然，一次培训，liddlechen介绍说MySQL Insert的语句快于Select，我当即反驳，但是回来翻看自己的测试记录，却的确如此。一方面惭愧，一方面感觉到测试的数据如果不文档化几乎没有任何意义。

8.1 如何看待测试数据

如何看待这些测试数据？正如我一直强调的任何Benchmark测试所产生的数据都只是参考，对于真实环境都是一种模拟，比如对于查询，如果是多个表的真实环境，数据表的查询效率可能要低很多，因为相关的索引必须在内存中换进换出，效率肯定成倍降低。

本文的作用是给出比较，对比参考，让你了解什么什么状态下可能具有更好的性能，比如MySQL的本地环境下的性能是网络环境下的性能的3倍多。STMT比原有的API可以提高处理30%的性能，这才是Benchmark参考的意义。

8.2 鸣谢

感谢 zengyu提供InnoDB 测试的指导，感谢wang,chris对文档提供意见。同时也向对提供机器给我测试的echoqin,penghao表示谢意。

记得和wang聊天的一句话，“搞技术的就是在自己领域YY的人”。把自己的YY作品献给5年（9年）的技术生活。

9 附录：

9.1 测试程序

下面是我的测试程序，IP和密码部分已经注释了，请参考时注意，另外，所有的测试程序基于我自己的一套程序库zenlib，如果有需要，可以请向我索要，您也可以直接使用MySQL API替换之。而且对于Benchmark，我建议你自己写自己的测试用例，这样才能有最好的效果。

9.2 参考书目

参考书目：

参考书目

作者

说明

《MySQL® Administrator’s Guide》

MySQL AB

《MySQL® Database Design and Tuning》

Robert D. Schneider

《MySQL:The definitive guide to using,programming,and administering MySQL 4 (second Edition)》

中文译本《MySQL权威指南》

Paul DuBois

中文译本是国内最好的中文MySQL书籍

唯一的瑕疵是没有介绍MySQL4.0后版本的新特性

《MySQL Reference Manual》

MySQL自己随颁布发布的参考手册，还是最好，最全的MySQL的参考

而且早期版本参考手册有中文翻译文档。

9.3 作者说明

曾星（Sail）工作于腾讯科技（深圳）有限公司互动娱乐事业部

Tencent Technology (Shenzhen) Company Limited IED

EMail：sailzeng@tencent.com

本着自由的精神，阅读者可以无须授权就可以自由的转载这个文档，我只保留作者的署名权利，也就是说，你转载只需保留这段说明和文档的完整性。

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。