转: 千万级mysql db的性能实测

2006-11-21 11:23

在 JavaEye 上看到一篇对 MySQL FUD(Fear, uncertainty and doubt) 的文章 用MySQL InnoDB Benchmark 性能测试来说明 http://www.iteye.com/topic/34676 文中提到："InnoDB 的磁盘性能很令人担心，MySQL 缺乏良好的 tablespace 真是天大的缺陷！……网上有用户反映存在同样的插入性能问题，百万行记录插入之后，插入速度下降到了 1/30，从开始的 1600行/秒衰退到 50行/秒……" 50行/秒这么慢我从来没见过，见也没人反驳，所以自己再专门验证一下。 Tim's 环境： MySQL 5.0.26 x86_64 Linux x86_64 iddata 文件大小17G,未使用innodb_file_per_table分表选项 表1: 1000万行左右，表大小 2.2G，含主键有6个索引字段 表2: 3000万行左右，表大小 6.3G，含主键有6个索引字段 MySQL还有其他一些数据也在iddata中，但测试时候其他数据未访问。 MySQL Server Hardware 环境 双 XEON 3G 4G 内存， SCSI 硬盘 非专业非名牌，普通组装的服务器。 表结构就是一般的用户表，包含 int, varchar, datetime 字段类型，无 text,blob 类型字段。 单行长度 0.1K 左右 对表1测试2分钟插入操作，在1千万的表中每个调用插入1行，通过Java JDBC在另外一台测试机上调用。 服务器 CPU 30% JDBC Result: INSERT OK /ERR: 73824/0 Java HEAP:  7.12MB of 63.56 MB (11.2%) used TIME elapse(sec): 129 AVG平均/CUR当前/MAX最大 Inserts/SEC: 572/620/620 再测试一个3000万的表，通过Java JDBC在另外一台测试机上调用。 可能上一个测试2分钟会让人觉得没有说服力，3000万的表二就测试久一点吧。测试了10多分钟，又插入了100万条新的数据 一个调用只插入1行，8个线程。使用了连接池。 测试结果速度非常稳定。 服务器 CPU 30% OK /ERR: 1,006,907/0 HEAP:  6.57MB of 63.56 MB (10.3%) used TIME elapse(sec): 1683 AVG/CUR/MAX Inserts/Sec: 598/586/647 测试结果是：3000万行的表再插入100万行速度持续在将近 600行/秒 论坛文章中还提到"看着文件尺寸 100KB 100KB 的增长，是没法体会痛苦的"， 根据MySQL文档，这个增长的size可以配置的，如果数据库大量INSERT, 可以把innodb文件设成每次增长100M或更大。 测试后打算回复一下，可惜JavaEye上新注册了的账号没有论坛发文的权限，只好先放在自己家了。感兴趣的朋友可以去那边讨论。 第二天补充：今天觉得文章说服力不够，因为我的测试表没有TEXT/BLOB字段，所以再测试下大字段，加了个TEXT字段，每行TEXT插入10K，整体速度比上个肯定会低一点，  但可以证明没有线性下降的问题，也可以给大家提供更多参考。   表中已有1000万行记录，采用上面相同的程序。 服务器 CPU 持续在 30%左右，测试结果见下图 测试结果每行插入10k数据，速度维持在300行/秒左右。数据库写入速度大致为: 3M/S 下次有时间再试试单表1亿条记录的。我觉得速度应该还会保持。

类别：Mysql | 浏览( 738)

 

<script language="JavaScript" type="text/javascript"> allkey=allkey+"3c8bf3eddc140b4f78f05584_4df7e150a0df935c1138c202_"; </script>

网友评论：

<script type="text/javascript"> function writecmt(type,id,cmtname,cmturl,cmttime){ var html1=""; if(type==1){ if(cmturl==""){ html1=" "+cmtname+" - "+cmttime+" "; }else{ html1=" "+cmtname+" - "+cmttime+" "; } }else{ if(cmtname=="匿名网友"){ if(cmturl==""){ html1=" "+cmtname+" - "+cmttime+" "; }else{ html1=" "+cmtname+" - "+cmttime+""; } }else{ if(cmturl==""){ html1="

网友: "+cmtname+" - "+cmttime+"

"; }else{ html1="

网友: "+cmtname+" - "+cmttime+"

"; } } } document.write(html1); } </script>

1 <script language="javascript" type="text/javascript"> writecmt(2,"bc9f7e09854e9eaf2eddd48d","fog","","2006-11-21 17:18"); </script>

网友: fog - 2006-11-21 17:18

除了主键外，选一个字段做下索引看看。

 

2 <script type="text/javascript"> writecmt(1,"0c8402d11c47c0d3562c846b","iso1600","http://hi.baidu.com/jabber","2006-11-21 21:05"); </script> iso1600 - 2006-11-21 21:05

3000万记录的表中有6个索引字段，如果只对主键索引，速度可能还会快些。

 

3 <script language="javascript" type="text/javascript"> writecmt(2,"e05bdc070e50d4cd7a89477e","???","","2006-11-21 22:07"); </script>

网友: ??? - 2006-11-21 22:07

主键是自带唯一索引的吧。

 

4 <script language="javascript" type="text/javascript"> writecmt(2,"448a8a8bacb59ad3fc1f107f","fog","","2006-11-21 22:11"); </script>

网友: fog - 2006-11-21 22:11

不知道插入的数据是否随机(尽量的与实际环境相似)，从mysql的文档来看，如果key buffer不够大，将会严重的影响插入速度。

 

5 <script language="javascript" type="text/javascript"> writecmt(2,"f77a11d8ba92833532fa1c78","fog","","2006-11-21 22:17"); </script>

网友: fog - 2006-11-21 22:17

从文档看，因为除了primarykey的index外，其他index存在不同的文件块里。所以想让mysql速 度降下来的办法,尽量的让index变得随机，让key buffer足够小，让index尽量的存在不同文件块，这样无论查询或是插入就有足够多的磁盘访问来减慢速度。

 

6 <script language="javascript" type="text/javascript"> writecmt(2,"a628c4bf6f31b20a18d81f44","ncisoft","","2006-11-21 22:40"); </script>

网友: ncisoft - 2006-11-21 22:40

我是传说中的 MySQL FUD 作者 :-)

现在我的测试环境不足，原来的生产环境是双 XEON 2.4G，配置给 MySQL 的 InnoDB Buffer 是 512M，其他内存配置给了 java 使用，SCSI RAID5 磁盘，只是现在不能用了也没法测试，否则可以将曾经困扰我的原表数据 dump 出来供大家测试，烦请楼主做以下几个实验，并提供一些数据。

另外，我想办法将以前的数据 dump 出来，有点特别的是用了 Unique Index，供大家测试，导出来之后会另发帖子通知。

1. 依照你当前的测试方式，iddata 使用系统安装的缺省值，我记得是 10M，而不是当前的 17G，因为这样可能无法测试出文件增长带来的影响。

2. 测试 innodb_file_per_table 下的性能，并使用缺省的 innodb_autoextend_increment 参数（我在生产机上用的是缺省值）。

3. 将插入的数据 mysqldump 出来，然后用 mysql < xx.sql 导入，重复之前的测试。

4. innodb_buffer_pool_size 设置成 512M，重复之前的测试。

5. 用 MySQL 4.x 来测试，我的生产系统当时应该是 4.1.13，重复之前的测试。

6. 提供表结构和索引结构的 SQL 语句，提供插入之后，数据和索引的数据量大小（Mysql Administrator 工具可以帮助显示出来）

 

7 <script language="javascript" type="text/javascript"> writecmt(2,"084e6f09de8d42cf3ac76344","ncisoft","","2006-11-21 22:40"); </script>

网友: ncisoft - 2006-11-21 22:40

我是传说中的 MySQL FUD 作者 :-)

现在我的测试环境不足，原来的生产环境是双 XEON 2.4G，配置给 MySQL 的 InnoDB Buffer 是 512M，其他内存配置给了 java 使用，SCSI RAID5 磁盘，只是现在不能用了也没法测试，否则可以将曾经困扰我的原表数据 dump 出来供大家测试，烦请楼主做以下几个实验，并提供一些数据。

另外，我想办法将以前的数据 dump 出来，有点特别的是用了 Unique Index，供大家测试，导出来之后会另发帖子通知。

 

8 <script language="javascript" type="text/javascript"> writecmt(2,"ae43c09595f39f4bd0135e44","ncisoft","","2006-11-21 22:41"); </script>

网友: ncisoft - 2006-11-21 22:41

跟贴有 500 字限制，晕了～

我希望，经过以上的测试，只要能重现出性能瓶颈，就可以帮助我们检 查出来是什么因素导致影响了插入性能问题。基本上，我认为你的测试结果通过，可能跟四个因素相关：innodb_buffer_pool_size、 ibdata file size、innodb_autoextend_increment、MySQL 版本。

目的不是 FUD MySQL，我们谁跟 MYSQL 都没仇，能分析出原因，以后大家在使用中都可以借鉴。毕竟，MySQL 插入慢，不光是我一个人有反映，来自 MySQL 的 Senior Performance Engineer、Peter Zaitsev 同志也这么说的，他总不可能 FUD 自己公司的产品吧。

Very slow index creation (ALTER TABLE, LOAD DATA)
Loading data or bulk inserts are much slower than MyISAM

http://www.mysqlperformanceblog.com/files/presentations/UC2005-Advanced-Innodb-Optimization.pdf

 

9 <script type="text/javascript"> writecmt(1,"15a9fad3d79a23dfa8ec9a43","iso1600","http://hi.baidu.com/jabber","2006-11-21 23:33"); </script> iso1600 - 2006-11-21 23:33

谢谢 ncisoft 的回复。这个留言不太好用，可惜我的javaeye账号还不能发言，所以先补充一些信息这里，供大家参考

1. 我的 MySQL my.cnf 是 copy my-large.ini 作了少量调整。innodb_buffer_pool_size=2048M(50% of RAM)。
2. 插入的数据 text 字段是写死的，但索引字段肯定是变的，否则测试就不合理了。
3. ncisoft推荐使用 mysql < x.sql 方法并不能完整的测试性能，首先因为是单线程执行的，服务器在Disk IO时会阻塞。
服务器在阻塞时候几个CPU都在闲着，负荷没满，根据经验，把线程调成 CPU * 2 or CPU * 4 可以达到最佳性能。

 

10 <script type="text/javascript"> writecmt(1,"c58b4b16fc947d4a21a4e943","iso1600","http://hi.baidu.com/jabber","2006-11-21 23:34"); </script> iso1600 - 2006-11-21 23:34

4. mysqldump 出来SQL一个 insert 有多行的 insert into table (col) values(1), (2), (n)……对于MySQL服务器执行一个带多行的 insert (比如50行) 和执行一个单行的 insert 时间是差不多的。所以使用这样的方法统计行数也不准确。我的程序未使用一个insert插多行的技术。因为实际应用中这种情况比较少。
5. 我用的是 MySQL 5.0.x, MySQL 4很久没用，不便发表意见。
6. Load Data 因为我在实际中用得比较少，未作观察和相关测试。

 

11 <script type="text/javascript"> writecmt(1,"c70ec280a48e3ed79023d943","iso1600","http://hi.baidu.com/jabber","2006-11-21 23:36"); </script> iso1600 - 2006-11-21 23:36

7. 如果做 unique index, 速度可能比我这个测试慢一点，但根据我以前使用的情况如果一个表除了主键只有一个unique速度不会差太大。但unique字段应尽量短。
8. to fog: innodb 的 index 和数据是在一起的。没有单独的文件。

 

12 <script language="javascript" type="text/javascript"> writecmt(2,"adc442ed47cbb9d4b21cb14d","ncisoft","","2006-11-21 23:53"); </script>

网友: ncisoft - 2006-11-21 23:53

实际上 mysqldump 的做法等同于 alter index，而 alter index 是很难避免的操作，如果你用 innodb_file_per_table 方式，就可以观察到 MySQL 实际上是先创建临时表，把整个表都改写到临时表，然后在 rename 回来，如果这种操作速度很慢，是挺难接受的，而这种时候是否能利用到多 CPU，那就看 MySQL 怎么实现的了，使用者也无法去做 tuning。

刚才拿到了以前生产机的账号，他们现在不用了，正在倒数据，能否提供下载空间？我可以提供测试样本。

 

13 <script language="javascript" type="text/javascript"> writecmt(2,"ae89df2ac9e7172dd52af14b","fog","","2006-11-22 01:22"); </script>

网友: fog - 2006-11-22 01:22

to iso1600:我弄错了，应该称为索引块

 

14 <script language="javascript" type="text/javascript"> writecmt(2,"6ae5027b785048f40ad187ad","ncisoft","","2006-11-23 01:19"); </script>

网友: ncisoft - 2006-11-23 01:19

iso1600，我的测试和推理过程已全部完成，在 javaeye 的第 3 页，请看：

http://www.iteye.com/topic/34676