分类：
2012-03-04 18:42:57
这篇文章研究了在海量数据需求下，MySQL数据库的承载能力。如果读者想尽快的获得帮助，而不想纠于细节，可以只看第一章；如果读者想具体了解结论是怎 么得到的，是通过什么方式，可以继续看第二章；当然，所有的实验环境和部分SQL代码在第三章里指出，这一章读者可以快速浏览，以达到管中窥豹的效果。如 有任何疑问可以和我联系。原文来自http://write.blog.csdn.net/postedit/6947129。
第一章 概述 1.1 摘要
本文通过对MySQL数据库（Win32）进行各种方法下的插入操作，研究了MySQL数据库在各种情况下，对插入操作的吞吐量和性能瓶颈所在，并指出通 过“缓存”加“批处理”的方式可以使MySQL数据库的吞吐量达到每分钟46MB，可以满足目前大部分应用。
1.2 结论根据目前的实验，在现行实验环境下（见第三章），MySQL至少能支持每分钟46MB的插入数据量，所以每天能插入的数据量至少为64GB。
虽然实验结果是在Windows平台下得到的，但由于在Linux平台下和Windows平台下MySQL数据库的吞吐量并不会有数量级的差别，所以本文的结论也适用于Linux平台。
1.3 系统性能瓶颈结论1. 网络带宽并不是MySQL数据库插入操作的性能瓶颈。
2. 每次插入两条数据的存储过程比着每次插入一条数据的存储过程，吞吐量大致得到两倍的提升。说明应用和数据库之间的JDBC开销正是MySQL数据库插入操作的性能瓶颈。
3. 每次插入四十条数据的存储过程比每次插入一条数据的存储过程性能提高了40倍，说明此时JDBC仍然是系统的性能瓶颈，仍然可以通过提升JDBC效率（如增加一次通信插入的数据量）来提高系统的吞吐量，直到其不再是系统的性能瓶颈。
4. 并发操作并不能增加系统的吞吐量，所以系统的瓶颈不在并发。
1.4 MySQL自身对插入的优化1. 使用insert into tablename values(),(),()...语法。
2. 如果有索引，批处理插入前先把建索引disable了，插入完成后再从新建索引。
3. 插入前对表进行加锁，插入完后，再解锁。
4. 使用插入延迟，当客户使用插入延迟，服务器立刻返回，数据库自己对缓冲区做处理。
5.使用 LOAD DATA INFILE，把文件中的数据直接映射成表。这个技术我认为实际上是利用了缓存加批处理，另外加上2、3、4的方式，所以速度超过。但是使用时要注意文件的编码格式。
第二章 实验 的插入利用单机节点（应用和MySQL服务器运行于同一台计算机上）和存储过程insertToemployee（详见3.5）进行数据的插入，实验结果如表 2. 1所示。从中我们可以看出，插入的数据量基本与时间成线性关系，每分钟插入的数据量平均在1.3MB左右。
1单机节点和存储过程insertToemployee插入表
|
时间（分钟） |
记录数 |
数据量(MB） |
|
1 |
2645 |
1.29 |
|
2 |
5332 |
2.60 |
|
5 |
13657 |
6.67 |
|
10 |
28122 |
13.73 |
利用网络节点（应用和MySQL服务器运行于两台计算机上）和存储过程insertToemployee进行数据的插入，实验结果如表2. 2所示。从中我们可以看出，插入数据量基本与时间成线性关系，每分钟插入的数据量平均在1.3MB左右；同时，与2.1的实验相比，网络节点间的插入和单 机节点间的插入性能差不多（甚至利用网络更好一点，可能是单机节点情况下，要进行应用进程和MySQL服务器进程之间的调度所花费的开销），证明网络带宽 并不是性能的瓶颈。
2网络节点和存储过程insertToemployee插入表
|
时间（分钟） |
记录数（行） |
数据量(MB） |
|
1 |
2788 |
1.36 |
|
2 |
5446 |
2.66 |
|
5 |
14002 |
6.84 |
|
10 |
26461 |
12.92 |
利用单机节点和存储过程insertToemployee2（详见3.5）进行数据的插入，实验结果如表2. 3所示。
3单机节点和存储过程insertToemployee2插入表
|
时间（分钟） |
记录数 |
数据量(MB） |
|
1 |
5300 |
2.58 |
|
2 |
11156 |
5.44 |
|
5 |
27634 |
13.50 |
|
10 |
54100 |
26.42 |
利用网络节点和存储过程insertToemployee2进行数据的插入，实验结果如表2. 4所示。
4网络节点和存储过程insertToemployee2插入表
|
时间（分钟） |
记录数 |
数据量(MB） |
|
1 |
5516 |
2.70 |
|
2 |
11136 |
5.44 |
|
5 |
27158 |
13.26 |
|
10 |
54158 |
26.44 |
根据实验2.3和2.4，我们可以看出，应用一次向MYSQL数据库发送一条插入操作和两条插入操作，吞吐量基本上得到了两倍的提升，所以这说明应用和 MySQL数据库之间的连接开销（JDBC）是MySQL数据库插入操作的性能瓶颈。所以我们可以通过继续提升JDBC的效率来提高MySQL数据库的吞 吐量。
2.5 网络节点和insertToemployee20的插入由于JDBC是系统性能的瓶颈，所以现在研究一下，每次插入20条数据系统的吞吐量。从表2. 5中可以看出，MySQL能满足每分钟插入22MB数据的要求。吞吐量与每次插入的条数基本上成线性关系，说明每次插入20条数据时（数据量为 10KB），JDBC仍然是系统性能的瓶颈所在。
所以，仍然可以继续通过提升JDBC的效率（如增加每次插入记录的条数），来增加系统的性能，直到JDBC不再是系统的性能瓶颈。
5网络节点和存储过程insertToemployee20插入表
|
时间（分钟） |
记录数 |
数据量(MB） |
|
1 |
45520 |
22.23 |
|
2 |
101840 |
49.72 |
|
5 |
228260 |
111.45 |
|
10 |
475060 |
231.96 |
所以，继续增加一次通信插入的数据量，这次使每次插入40条记录，试验结果如表2. 6所示。
6网络节点和存储过程insertToemployee40插入表
|
时间（分钟） |
记录数 |
数据量(MB） |
|
1 |
96000 |
46.88 |
|
2 |
197920 |
96.64 |
|
5 |
368960 |
180.16 |
|
10 |
942360 |
460.14 |
从表2. 6中可以看出，每次插入40条记录，系统能满足每分钟插入46MB数据的要求。性能仍然得到了线性的增长，说明此时JDBC仍然是系统性能的瓶颈。所以， 仍然可以继续增加每次插入记录的条数，用以增加系统的性能，直到JDBC不再是系统的性能瓶颈，由于篇幅所限，继续提升JDBC效率的实验暂做到这里。
2.7 网络节点和insertToemployee的并发（多线程）插入利用网络节点和存储过程insertToemployee进行数据的并发插入，用两个线程分别插入数据，得到实验结果如表2. 7所示。
7网络节点和存储过程insertToemployee的多线程插入表
|
时间（分钟） |
线程号 |
记录数 |
数据量(MB） |
总数据量（MB） |
|
1 |
thread1 |
1344 |
0.66 |
1.31 |
|
thread2 |
1348 |
0.66 |
||
|
2 |
thread1 |
2693 |
1.31 |
2.63 |
|
thread2 |
2693 |
1.31 |
2.8 网络节点和insertToemployee的并发（多进程）插入
表2. 8网络节点和存储过程insertToemployee的多进程插入表
|
时间（分钟） |
线程号 |
记录数 |
数据量(MB） |
总数据量（MB） |
|
1 |
process1 |
1350 |
0.66 |
1.33 |
|
process2 |
1375 |
0.67 |
||
|
2 |
process1 |
2782 |
1.36 |
2.61 |
|
process2 |
2789 |
1.36 |
可以看出，多进程并不增加系统的吞吐量，所以多进程并不是系统性能瓶颈的所在。究其原因有可能是因为插入的是同一个表，数据库对其进行了加锁，所以多个连接只能顺序的插入，是否如此详见2.9实验。
利用网络节点和存储过程insertToemployee和存储过程insertTostudent（详见3.5）进行数据的并发（多进程）插 入，process1向student表插入数据，process2向employee表插入数据（student表和employee表除了表名不一样 外，其他都一样），得到的实验结果如表2. 7所示。
9不同的表同时插入数据实验
|
时间（分钟） |
表 |
进程号 |
记录数 |
数据量(MB） |
总数据量（MB） |
|
1 |
student |
process1 |
1312 |
0.64 |
1.28 |
|
employee |
process2 |
1319 |
0.64 |
||
|
2 |
student |
process1 |
2809 |
1.37 |
2.76 |
|
employee |
process2 |
2854 |
1.39 |
可以看出，系统的吞吐量并没有得到提升，所以数据库并没有对表进行加锁，是MySQL自身的吞吐量限制了吞吐量的提升。
2.10 各阶段开销我们首先研究下不同阶段所花费的时间为多少，进而为我们优化数据库应用铺平道路。一条数据从应用开始到最终被插入在数据库中，我们粗浅的把它分为三个阶段：数据准备时间、连接时间、插入时间。
10两分钟各阶段执行次数
|
存储过程 |
准备数据 |
准备数据+连接 |
准备数据+连接+执行插入操作 |
|
insertToemployee |
21131919 |
158064 |
5380 |
|
insertToemployee2 |
21429164 |
298310 |
8360 |
从表2. 10，我们可以得到下表。表2. 11表示每次所花费的时间。
11各阶段每次迭代执行时间（ms）
|
|
准备数据 |
准备数据+连接 |
准备数据+连接+执行插入操作 |
|
insertToemployee |
0.00567 |
0.75758 |
22.02643 |
|
insertToemployee2 |
0.01120 |
0.80453 |
28.70813 |
从表2. 11，我们可以得到每个阶段所花费的时间，如下表。
表2. 12各阶段每次执行时间（ms）
|
|
准备数据 |
连接 |
执行插入操作 |
|
insertToemployee |
0.00567 |
0.75191 |
21.26885 |
|
insertToemployee2 |
0.01120 |
0.79333 |
27.90360 |
去除准备数据花费的时间后，对连接和执行插入操作进行归一化处理。
表2. 12各阶段所占开销百分比
|
操作 |
时间百分比 |
|
|
insertToemployee |
insertToemployee2 |
|
|
连接 |
3.41% |
2.76% |
|
插入操作 |
96.59% |
97.24% |
可以看到，随着每次插入条数的增多，连接所占花费的百分比就下降了。因为虽然每次传输两条记录比每次传输一条记录 （0.79333>0.75191）所用的时间更长，但是与插入操作（27.90360>21.26885）相比，连接所花费的时间增长相对 较慢，这样就充分的利用了JDBC。
3.1 节点配置
|
|
应用服务器 |
MySQL服务器 |
|
|
硬件环境 |
CPU |
Intel Core 2 E7500 @ 2.93GHz 2.93GHz |
Intel Core 2 E7500 @ 2.93GHz 2.94GHz |
|
RAM |
1.87GB |
2.00GB |
|
|
软件环境 |
OS |
WinXP 32-bit |
Win7 32-bit |
|
类型 |
版本 |
|
MySQL |
mysql-5.5.17-win32 |
|
桥接器 |
mysql-connector-java-5.1.18 |
传输速率为100Mbps的以太网。根据局域网一般的效率而言，大概能维持在600MB/Min。
3.4 数据库表CREATE TABLE employee
(
id int(8),
id2 decimal(20,4),
name char(40),
name2 varchar(200),
name3 char(200)
) ;
employee表中每条记录大约为500字节的数据量。
CREATE TABLE student
(
id int(8),
id2 decimal(20,4),
name char(40),
name2 varchar(200),
name3 char(200)
) ;
student表中每条记录大约为500字节的数据量。
#insertToemployee存储过程每次插入一条数据
delimiter //
create procedure insertToemployee
(in id int(8),in id2 decimal(20,4),in name char(40),in name2 char(200),in name3 char(200))
begin
insert into employee values(id,id2,name,name2,name3);
end
//
#insertToemployee2存储过程每次插入两条数据
delimiter //
create procedure insertToemployee2
(
in id int(8),in id2 decimal(20,4),in name char(40),in name2 char(200),in name3 char(200),
in ids int(8),in id2s decimal(20,4),in names char(40),in name2s char(200),in name3s char(200)
)
begin
insert into employee values(id,id2,name,name2,name3),(ids,id2s,names,name2s,name3s);
end
//
存储过程insertToemployee20和存储过程insertToemployee40与insertToemployee2类似，这里为了减少篇幅，不再赘述。
版权所有，转载请注明出处http://blog.csdn.net/hongchangfirst
