mysql分表|MySQL InnoDB 的性能问题讨论 postgresql|测试|insert|sap|数据库

原贴:http://ncisoft.javaeye.com/blog/34676?page=4#comments

MySQL InnoDB 的性能问题讨论关键字: Database

MySQL最为人垢病的缺点就是缺乏事务的支持，MyISAM 性能虽然出众，不是没有代价的，InnoDB 又如何呢？InnoDB 的磁盘性能很令人担心，MySQL 缺乏良好的 tablespace 真是天大的缺陷！
InnoDB的表空间分成三种，一种是裸设备，一种是若干个 ibdata 文件（缺省方式），再一种是 Per-Table 文件，第一种用得少，第二种显然比第三种效率更差，本文的讨论基于 Per-Table，也即 innodb_file_per_table 配置参数。
现象重现：导出一个几百万行数据、带若干索引、有过频繁更新的表出来再导入，如果能以真实环境下的表来做测试就更理想，到 data 目录下观察对应的数据文件的 size 增长情况，会发现前 1G 速度相当令人满意，可是越往后效率越低，到后面基本就是蜗牛般的速度了。
不是只有导入才会让你慢得受不了，alter column/index 都会这样。。。
InnoDB 跟磁盘相关的文件存储，可以分成两个部分，一个是日志文件，另一个是数据文件。当有频繁的 INSERT/UPDATE 操作的时候，InnoDB 需要分别写入这两个文件，日志文件是顺序操作，数据文件包括了表数据和索引数据两个部分（和 MyISAM 直接拆开成表文件和索引文件不同，InnoDB 的表和索引是在同一个文件当中的）。
InnoDB 的索引用的是 BTREE 格式，如果当前更新的记录影响到索引的变化，逻辑上就存在三个操作，从原来的 BTREE 找到并摘除原来这行的记录并做调整、插入行数据、根据新数据查找 BTREE 相应的位置并重新插入新索引信息，假设索引数为 N，相应的逻辑操作数就为 1 + 2*N，显然这些信息不能保证在同一个磁盘连续空间上，因此需要 1 + 2*N 次的磁头移动，行数越大、文件尺寸越大，磁头的移动幅度也就可能越大，带来的后果显然是极差的磁盘 IO 效率。
MySQL 对于 MyISAM 的的磁盘 IO 优化是如何建议的呢？使用符号链接将表文件和索引文件分别指向不同的不同的目录，分散到不同的磁盘上以增加系统的访问速度。这种优化方式，在 InnoDB 上完全没有可能性！
如果有 tablespace 支持，磁盘效率问题就好解决了，一如商业数据库的做法，将日志、表文件、索引文件分别分布到不同的表空间也就是物理磁盘上，可是 MySQL 一直到 5.1 都没有提供 tablespace 功能，仅在 NDB/NDBCLUSTER 中才提供，但是 -- "CREATE TABLESPACE was added in MySQL 5.1.6. In MySQL 5.1, it is useful only with Disk Data storage for MySQL Cluster."。
不知道 Yahoo 等大网站是怎么解决这个难题的。。。头痛。。。考虑切换到 PostgreSQL 中。。。

最后更新：2006-11-25 09:35
00:46|永久链接|浏览 (13097)|评论 (32)|收藏|进入论坛|

永久链接

http://ncisoft.javaeye.com/blog/34676

评论共 32 条

发表评论

robbin2006-11-17 08:34

日志文件还是比较好优化的，一则可以去掉默认写入的bin-log，不让他写update的log，二则在my.cnf里面开大log buffer，减少log flush次数：
innodb_log_buffer_size = 1M
innodb_flush_log_at_trx_commit = 0
其实Oracle数据库也是每个tablespace一个数据库表文件的，而且数据和索引也是写在一起的，不知道oracle是怎么存储的。
MySQL的表我尚未在生产环境中使用到百万级别，现在JavaEye也就10万级别的记录，速度很快，所以我还不知道到百万级别会碰到你说的问题。

together2006-11-17 08:58

oracle的索引，也是可以单独指定表空间的。
既然mysql暂时没有tablespace，那就优化一下磁盘性能好了。换更高转速更大缓存的硬盘设备，效果能好一些？
我们公司的一个mysql数据库是用的isam，现在已经有十个左右百万级的大表，性能还可以。不过商业应用采用mysql真是天生的失败，没有databaselink，需要和其它数据库交互的时候非常的麻烦。

ncisoft2006-11-17 09:00

参考 PostgreSQL 的语法：
CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]
在创建表、索引的时候，分别创建到不同的表空间，并将表空间放在不同的磁盘上，Oracle 也可以是同样的处理。
InnoDB 的日志文件是好优化，除了 robbin 说的，还可以指定日志文件的路径，和数据文件分布在不同的磁盘上，可是数据文件无法优化磁盘性能。

nihongye2006-11-17 10:20

http://forum.mysqlperformanceblog.com/s/t/17/
http://software.newsforge.com/article.pl?sid=04/12/27/1243207
看看，有些建议。
http://dev.mysql.com/doc/refman/5.0/en/estimating-performance.html
按照这篇说的一个500,000的表，在没有cache的情况下，命中一条记录需要磁盘操作数:
log(500,000)/log(1024/3×2/(3+4)) + 1 = 4 seeks.
写入需要:4seeks查找，两次seeks进行更新索引和写入行。
所以对于大表:1.有足够多的内存做index的cache。2.避免全表检索。

lgn21st2006-11-17 13:16

题外话！有一个地方不太明白，MySQL提供的MaxDB从介绍上看来，性能，稳定性等各方面都有无可比拟的优势
但是在好几个论坛上数据库板块很少有人讨论，不知为何maxdb的人气这么差

Lucas Lee2006-11-17 13:59

我觉得磁盘IO问题可以有很多方法解决吧，不一定局限于tablespace。比如透明的使用RAID，不就提高了磁盘性能了么？

Lucas Lee2006-11-17 14:01

而且，PostgreSql就性能来说，在网上一般的比较资料中，都是比MySQL低的。所以不能单纯比较一项功能是否支持，就得出性能是否好的结论吧。

charon2006-11-17 16:49

postgresql如果做了适当的索引和优化，一般情况下性能也够用了。但是postgresql的功能点和成熟度上比mysql强太多了，基本上和oracle有一拼.
关键的一点，postgresql是BSD类许可证的，商业上应用没有心理负担。

bigpanda2006-11-17 17:19

lgn21st 写道题外话！有一个地方不太明白，MySQL提供的MaxDB从介绍上看来，性能，稳定性等各方面都有无可比拟的优势
但是在好几个论坛上数据库板块很少有人讨论，不知为何maxdb的人气这么差
资料太少，去网上翻一圈，资料少的可怜。我九月底在amazon订了一本书，MaxDB for enterprise，几次延期，现在还没拿到。
抓下来代码看一看，光搭个build environment就要用到perl，python。这两门语言我都不会，就没有深究下去。现在别的事忙得很，以后再有时间研究吧。
我对MaxDB的兴趣是很大的，SAP做的东西，用来跑SAP系统的，并发事务处理的效率肯定不低。
现在InnoDB给Oracle买去了，MySQL应该会在MaxDB上下更大功夫的。
兄弟也对MaxDB感兴趣？

ncisoft2006-11-17 17:41

光说不练没用的，有条件的朋友可以自己去试验，我在这里置疑的不是 MyISAM，连交易都不支持，就不讨论了，也不是查询，是 InnoDB 下的 Insert/Update/Remove 性能，Cache 只能解决小数据量的问题，大数据量是不够的，RAID 0+1 能解决问题吗，看有没有机会做个试验吧，我比较怀疑，没经历过导入几百万条 InnoDB 数据到最后看着文件尺寸 100KB 100KB 的增长，是没法体会痛苦的。
btw，MySQL 我前前后后断断续续用了 7 年。。。

评论共 32 条

发表评论

nihongye2006-11-17 20:51

ncisoft 写道光说不练没用的，有条件的朋友可以自己去试验，我在这里置疑的不是 MyISAM，连交易都不支持，就不讨论了，也不是查询，是 InnoDB 下的 Insert/Update/Remove 性能，Cache 只能解决小数据量的问题，大数据量是不够的，RAID 0+1 能解决问题吗，看有没有机会做个试验吧，我比较怀疑，没经历过导入几百万条 InnoDB 数据到最后看着文件尺寸 100KB 100KB 的增长，是没法体会痛苦的。
btw，MySQL 我前前后后断断续续用了 7 年。。。

说的是

qinyf2006-11-17 22:07

这么说对于百万级的频繁写入的情况，innodb在I/O上会有一些不爽了吗？只接触过百万级频繁读少量写的，没有做过准确调研到底比MyISAM慢多少。

ncisoft2006-11-18 00:34

http://www.mysqlperformanceblog.com/files/presentations/UC2005-Advanced-Innodb-Optimization.pdf
Peter Zaitsev, MySQL Inc.
– Senior Performance Engineer －－权威的牛人～
– MySQL Performance Group Manager
– MySQL Performance consulting and partner relationships
Very slow index creation (ALTER TABLE, LOAD DATA)
– Indexes are currently built row by row
BLOBs stored outside of the main row, in many pages
– Slower BLOB retrieval and much slower updates
Loading data or bulk inserts are much slower than MyISAM
UNIQUE keys are more expensive than non unique －－正好用到了
– Insert buffering does not work
Manual partitioning still make sense －－咣当～
– ie users01, users02... users99
– Table locks is not the problem but ALTER TABLE is

ncisoft2006-11-19 15:07

网上有用户反映存在同样的插入性能问题，百万行记录插入之后，插入速度下降到了 1/30，从开始的 1600行/秒衰退到 50行/秒，同样的测试环境下，MyISAM 没有这样的问题。InnoDB 的 Roadmap 对此问题的时间表是“Long Term”。FeedLounge.com 也因为这个原因迁移到 PostgreSQL。
http://www.mail-archive.com/mysql@lists.mysql.com/msg99746.html
http://feedlounge.com/blog/2005/11/20/switched-to-postgresql/
InnoDB 的风险因素：数据量是否会超过百万行的规模，是否需要 alter column/alter index/backup recovery。

iceboundrock2006-11-19 20:37

有一个基于PostgreSQL 8.1.3专门为BI做了优化的数据库：bizgres
据chinaunix上一个兄弟的试用，性能比PostgreSQL 8.1有比较大幅度的提升。

无明2006-11-20 22:41

bigpanda 写道 lgn21st 写道题外话！有一个地方不太明白，MySQL提供的MaxDB从介绍上看来，性能，稳定性等各方面都有无可比拟的优势
但是在好几个论坛上数据库板块很少有人讨论，不知为何maxdb的人气这么差
资料太少，去网上翻一圈，资料少的可怜。我九月底在amazon订了一本书，MaxDB for enterprise，几次延期，现在还没拿到。
抓下来代码看一看，光搭个build environment就要用到perl，python。这两门语言我都不会，就没有深究下去。现在别的事忙得很，以后再有时间研究吧。
我对MaxDB的兴趣是很大的，SAP做的东西，用来跑SAP系统的，并发事务处理的效率肯定不低。
现在InnoDB给Oracle买去了，MySQL应该会在MaxDB上下更大功夫的。
兄弟也对MaxDB感兴趣？

SAP的效率可不敢恭维。去年一朋友公司准备上SAP，他用一台配置很好的PC来进行测试。只是基本系统，还没多少数据就慢的不行。后来总的测算下来，上SAP的成本非常大——不是指硬件，而是软件的改造成本太高，最后作罢。
MySql好像把MaxDB搁置了，不会在上面投入太多精力。Innodb估计也会淡出，只是目前还没有更好的替代引擎。
楼主设计的测试强调写操作性能，这对Innodb还是挺不利的。对于强调事务的应用，更重要的是重负下的交易完整性，以及数据可靠性。
我这里没有这么大的mysql数据库，也测试不了，不过，我们现在跑的oracle有近20G的数据了，导出再导入的速度也很慢。
要作这样的测试，得先对磁盘做个I/O测试，看看同等级的数据量下系统的极限I/O性能是多少，然后以这个基准跟数据库的测试的结果作对比。不然硬件的差异会影响测试结果。
题外话，据称裸设备下，性能是最好的，但是出了问题的时候，修复的难度也是最大的，所以也没去试过。

noble2006-11-20 23:48

Craigslist 的数据库架构
这个，还有类似的Mysql的文章，至少说明在一定范围内，mysql还是够用的。

JavaVision2006-11-21 13:31

无明写道 bigpanda 写道 lgn21st 写道题外话！有一个地方不太明白，MySQL提供的MaxDB从介绍上看来，性能，稳定性等各方面都有无可比拟的优势
但是在好几个论坛上数据库板块很少有人讨论，不知为何maxdb的人气这么差
资料太少，去网上翻一圈，资料少的可怜。我九月底在amazon订了一本书，MaxDB for enterprise，几次延期，现在还没拿到。
抓下来代码看一看，光搭个build environment就要用到perl，python。这两门语言我都不会，就没有深究下去。现在别的事忙得很，以后再有时间研究吧。
我对MaxDB的兴趣是很大的，SAP做的东西，用来跑SAP系统的，并发事务处理的效率肯定不低。
现在InnoDB给Oracle买去了，MySQL应该会在MaxDB上下更大功夫的。
兄弟也对MaxDB感兴趣？

SAP的效率可不敢恭维。去年一朋友公司准备上SAP，他用一台配置很好的PC来进行测试。只是基本系统，还没多少数据就慢的不行。后来总的测算下来，上SAP的成本非常大——不是指硬件，而是软件的改造成本太高，最后作罢。
MySql好像把MaxDB搁置了，不会在上面投入太多精力。Innodb估计也会淡出，只是目前还没有更好的替代引擎。
楼主设计的测试强调写操作性能，这对Innodb还是挺不利的。对于强调事务的应用，更重要的是重负下的交易完整性，以及数据可靠性。
我这里没有这么大的mysql数据库，也测试不了，不过，我们现在跑的oracle有近20G的数据了，导出再导入的速度也很慢。
要作这样的测试，得先对磁盘做个I/O测试，看看同等级的数据量下系统的极限I/O性能是多少，然后以这个基准跟数据库的测试的结果作对比。不然硬件的差异会影响测试结果。
题外话，据称裸设备下，性能是最好的，但是出了问题的时候，修复的难度也是最大的，所以也没去试过。

配置很好的pc？多好，说出来
sap的软件，你以为像装个浏览器那么容易阿，你会配吗？
sap的软件贵，也不是一天两天的事。它都是卖个大企业的。
中国的大部分都只能算small and middle business,本来就用不起。

JavaVision2006-11-21 13:35

eastviking2006-11-21 13:45

如果认为自己的数据库会控制在100G以下的话，MAXDB做SAP数据库是问题不大的，但最重要的是进行性能调优。
但这个帖子：
http://xsb.itpub.net/post/419/106223
中提到：MAXDB被SAP送给MySQL后，会被MySQL消化并消灭，可能未来只有一个MySQL

无明写道
SAP的效率可不敢恭维。去年一朋友公司准备上SAP，他用一台配置很好的PC来进行测试。只是基本系统，还没多少数据就慢的不行。后来总的测算下来，上SAP的成本非常大——不是指硬件，而是软件的改造成本太高，最后作罢。

配置一台高性能的SAP系统需要专业的SAP BC做技术支持。
这样的技术人员需要对硬件(CPU、内存、磁盘阵列)、操作系统调优、内存管理、任务管理、数据性能调整有非常高的造诣。
空的SAP系统也有20G-30G的DB，如果是做DEMO用的IDES，新系统的DB就有100-150G。
另外，你“配置非常好的PC”是什么概念？SAP服务器的入门配置也要有2颗1.5G以上的CPU，2G以上内存
磁盘性能、数据库性能、SAP系统参数调整过了吗？

评论共 32 条

发表评论

jreros2006-11-21 15:28

有位同志做了以下试验：
反驳"MySQL InnoDB (不行)的性能问题"，千万级别记录来测试说明
http://hi.baidu.com/jabber/blog/item/4df7e150a0df935c1138c202.html

JavaVision2006-11-21 16:51

jreros 写道有位同志做了以下试验：
反驳"MySQL InnoDB (不行)的性能问题"，千万级别记录来测试说明
http://hi.baidu.com/jabber/blog/item/4df7e150a0df935c1138c202.html

牛的

charon2006-11-21 22:04

jreros 写道有位同志做了以下试验：
反驳"MySQL InnoDB (不行)的性能问题"，千万级别记录来测试说明
http://hi.baidu.com/jabber/blog/item/4df7e150a0df935c1138c202.html

具体情况不同，不好说啊
可能主要的差异就在主键和怎么个索引法了吧,此外无序主键(GUID)也会带来一些问题。. 貌似楼主的数据表是有主键之外的唯一索引的，
而jabber的表结构如何现在很难判断.

ncisoft2006-11-21 22:48

我在 http://hi.baidu.com/jabber/blog/item/4df7e150a0df935c1138c202.html 上做了回应，另外，我在导入的时候 I/O 很高，磁盘速率将近 10MB/s，CPU 利用率倒是不高，不超过 70%，操作系统是 FreeBSD，如下：

引用
我是传说中的 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 工具可以帮助显示出来）
我希望，经过以上的测试，只要能重现出性能瓶颈，就可以帮助我们检查出来是什么因素导致影响了插入性能问题。基本上，我认为你的测试结果通过，可能跟四个因素相关：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

ncisoft2006-11-22 01:10

ISO1600 说

引用
谢谢 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 可以达到最佳性能。
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 因为我在实际中用得比较少，未作观察和相关测试。
7. 如果做 unique index, 速度可能比我这个测试慢一点，但根据我以前使用的情况如果一个表除了主键只有一个unique速度不会差太大。但unique字段应尽量短。
8. to fog: innodb 的 index 和数据是在一起的。没有单独的文件。

我的回应

引用
实际上 mysqldump 的做法等同于 alter index，而 alter index 是很难避免的操作，如果你用 innodb_file_per_table 方式，就可以观察到 MySQL 实际上是先创建临时表，把整个表都改写到临时表，然后在 rename 回来，如果这种操作速度很慢，是挺难接受的，而这种时候是否能利用到多 CPU，那就看 MySQL 怎么实现的了，使用者也无法去做 tuning。
刚才拿到了以前生产机的账号，他们现在不用了，正在倒数据，能否提供下载空间？我可以提供测试样本。

ncisoft2006-11-22 01:14

数据拿到了，以前的生产机也被允许用两天，我自己测试一下，测试过程和推导逻辑见下，数据情况：

引用
rows number: 10M －－怎么这么大了
dump length: 1.75G －－ zipped: 413M
idb length: 5.8G －－以前的 IDB 文件没超过 4GB 的，就先拿这个来测试吧，比的也就是相对值
data length: 1.5G
index length: 3.8G
FreeBSD 5.3, MySQL 4.1.10a, Dual XEON 2.4, 2G RAM, RAID5 SCSI
CREATE TABLE `tl_test_log` (
`ID` int(10) unsigned NOT NULL auto_increment,
`X_ID` varchar(32) NOT NULL default '',
`X_NAME` varchar(32) NOT NULL default '',
`Y_ID` int(11) default NULL,
`B_TIME` datetime NOT NULL default '0000-00-00 00:00:00',
`A_TIME` datetime NOT NULL default '0000-00-00 00:00:00',
`B_DATE` date NOT NULL default '2005-07-31',
`A_DATE` date NOT NULL default '2005-07-31',
`T_DATE` date NOT NULL default '2005-07-31',
`S_DAYS` int(10) default NULL,
`X_DAYS` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`),
UNIQUE KEY `X_ID` (`X_ID`,`X_NAME`),
KEY `COPY_01` (`A_TIME`),
KEY `SEARCH_01` (`B_DATE`,`X_NAME`,`Y_ID`,`X_ID`),
KEY `SEARCH_02` (`A_DATE`,`X_NAME`,`Y_ID`,`T_DATE`,`X_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

测试数据（全部完成）：

引用
1. time=4706s, avs=2124行/s
--- innodb_buffer_pool_size = 512M，thread_concurrency = 2
--- 观测 idb 文件的生成情况，越往后长得越慢，前一个G和最后一个G的增长速度相差 8 倍以上
2. time=3317s, avs=3014行/s
--- innodb_buffer_pool_size = 3*512M，thread_concurrency = 2
--- innodb_buffer_pool_size 大小对速度有相当的影响
3. time=3101s, avs=3224行/s
--- innodb_buffer_pool_size = 3*512M，thread_concurrency = 2, unique key -> normal key
--- unique key 对速度有一定的影响，小于 10%
4. time=954s, avs=10482行/s
--- 从测试 3 得出的表，改变表类型 alter table tl_test_log ENGINE=MyISAM
--- key_buffer_size = 192M
--- InnoDB 的 alter table 效率，本次测试中三倍落后于 MyISAM
5. time=554s, avs=5392行/s, count(id)=2,933,380
--- 测试条件同 2，行数将近原 10M 的 1/3
--- 保证索引数据能完全存放在内存中：index length: 3.8G/3=1.3G < innodb_buffer_pool_size = 3*512M
--- 前 3M 行记录的插入速度，相对于测试 2 有 78% 的效率提升，显然是之后的插入速度降低拖累了测试 2 的总体成绩
6. time=238s, avs=12325行/s, count(id)=2,933,380
--- 测试条件同 4，行数将近原 10M 的 1/3
--- 前 3M 行记录的插入速度，相对于测试 4 有 17% 的效率提升，显然是之后的插入速度降低拖累了测试 4 的总体成绩
--- 对比测试 5，可知之后的插入速度降低幅度，InnoDB >> MyISAM
--- 动态察看文件生成大小的变化幅度，比如每次增长的时间间隔，可以有更直观的了解

Arbow2006-11-23 11:48

来迟咯，飘过~

charon2006-11-23 12:15

Arbow 写道请看我一朋友Tim的实验结果：
http://hi.baidu.com/jabber/blog/item/4df7e150a0df935c1138c202.html

在楼上的楼上的楼上的楼上的楼上的帖子中已经提到了。

iso16002006-11-23 19:46

新注册的账号禁言几天后终于可以发言了。:)
我对楼主的测试方法有两个疑问，blog提过了，再重复一下。
1. 如前所言，如果测试方式是 mysql < my.sql 这样的方法我不认同
a. 单线程，不符合实际应用程序的情况
b. 因为导入的 sql 使用了 bulk insert 方法，什么叫 bulk insert 呢，就是一个 insert 包含多行，
into t values (1),(2),(3)...(10) 插10行的速度和 insert into t values(1) 插一行的速度是一样的，所以你的结果的行数能达到几千。但实际的应用程序一次都是插入一行的。你把 insert 的语句一行的行数再增大点，你的测试结果会变化很大的。所以我不认同用这种方法来统计行数。
而且 MyISAM 对 bulk insert 做了优化

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to non-empty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

我试了，把我千万记录的innodb程序改成一个bulk insert，100 行/insert，速度立即从600升到 8000 行/秒，这个表还有6个索引字段呢：）不过一般的程序都用不上bulk insert，所以即使拿到8000的速度也对解决实际问题没什么帮助。根据我的经验，使用普通insert每秒上千很困难的，不可能上2000。

这种测试实际在测试哪个 storage engine 实现的 bulk insert 好，但在实际应用中我认为能够使用bulk insert这种情况的比较少，大部分都是一次一行的。也是一行一个事务。
2. index 不合理。
因为从你这些字段名看不出业务意义，所以也提不出什么改进建议。
如果你的表主要是面对 select 的，这样的索引无可厚非，但是如果新增修改量比较大还是把索引改改，即使某些query慢点可以用cache等技术解决。

ncisoft2006-11-23 20:39

iso1600 所说的“单线程，不符合实际应用程序的情况”，之前给你的回复相信你应该看到了，写得很清楚

引用实际上 mysqldump 的做法等同于 alter index/column，而 alter index/column 是很难避免的操作，如果你用 innodb_file_per_table 方式，就可以观察到 MySQL 实际上是先创建临时表，把整个表都改写到临时表，然后在 rename 回来，如果这种操作速度很慢，是挺难接受的，而这种时候是否能利用到多 CPU，那就看 MySQL 怎么实现的了，使用者也无法去做 tuning。
我是在 alter index 的时候，发现速度让人无法忍受的，难道生产系统上线之后不 alter index 了吗？我相信这是会经常发生的事情。
“2. index 不合理。因为从你这些字段名看不出业务意义，所以也提不出什么改进建议”，显然我将字段名都改过了，没可能将真实的表结构给贴出来的，这样是给公司找我麻烦的机会，这个表要处理频繁的读写查询，每天几百万笔的写交易，system/io 的占用颇高，至于表的设计是否合理，在这就不用探讨了吧？呵呵。
另外，你似乎没有仔细看我之前贴的资料，MySQL 自己公司的资深性能工程师也承认 InnoDB 的写操作性能是很差的，我再贴一次给你：

引用 http://www.mysqlperformanceblog.com/files/presentations/UC2005-Advanced-Innodb-Optimization.pdf
Peter Zaitsev, MySQL Inc. －－来自 MySQL 公司
– Senior Performance Engineer －－权威的牛人～
– MySQL Performance Group Manager
– MySQL Performance consulting and partner relationships
Very slow index creation (ALTER TABLE, LOAD DATA)
– Indexes are currently built row by row
BLOBs stored outside of the main row, in many pages
– Slower BLOB retrieval and much slower updates
Loading data or bulk inserts are much slower than MyISAM
UNIQUE keys are more expensive than non unique －－正好用到了
– Insert buffering does not work
Manual partitioning still make sense －－咣当～
– ie users01, users02... users99
– Table locks is not the problem but ALTER TABLE is

甚至 InnoDB 自己的开发人员，也将此问题的解决放在了 TODO 上，网上有个 PPT 可以看到，只是 InnoDB 的 Roadmap 对此问题的时间表是 Long Term，以下两个链接提供了找到该文档的线索。
http://www.mail-archive.com/mysql@lists.mysql.com/msg99746.html
http://feedlounge.com/blog/2005/11/20/switched-to-postgresql/

分页 :上一页 [ 1][ 2]3 [ 4]下一页

评论共 32 条

发表评论

iso16002006-11-24 21:18

引用
我是在 alter index 的时候，发现速度让人无法忍受的，难道生产系统上线之后不 alter index 了吗？我相信这是会经常发生的事情。

经常alter index的系统应该不多，可能我视野不够开阔，反正我做的系统在上线之前 index 会想了又想，但是上线后肯定不会动它，除非产品要升级了。我相信很多人不会用Alter index来衡量性能吧。

引用
Very slow index creation (ALTER TABLE, LOAD DATA)
– Indexes are currently built row by row

他括号里面说 alter table, load data create index 很慢，可以理解，但是一个上百万记录的表应当避免这样的操作。我想也是一个系统架构师的责任如何去合理的利用好一个数据库。

引用
BLOBs stored outside of the main row, in many pages
– Slower BLOB retrieval and much slower updates

我的建议是上千万记录的表尽量避免 blob字段，而且在我 blog 文章中的测试上千万记录插入text字段速度也可以接受。

引用
Loading data or bulk inserts are much slower than MyISAM

我的意见bulk insert多用在系统维护，备份和恢复数据等方面，真正的应用程序用不上bulk insert/load data。

引用
UNIQUE keys are more expensive than non unique －－正好用到了
– Insert buffering does not work

可以理解

引用
Manual partitioning still make sense －－咣当～
– ie users01, users02... users99
– Table locks is not the problem but ALTER TABLE is

这个当然是业界认可的，在次之前我就写过这方面设计的文章。
http://hi.baidu.com/jabber/blog/item/adc442ed647adad4b31cb11e.html
跟我下面的结论不矛盾。
如果楼主对我的说法分歧很大，那我重申下我的看法。
MySQL InnoDB 在满足以下条件下，千万级别的表插入速度性能稳定。

不需要经常修改表结构 not always alter table, alter column or alter index
没有经常性的 bulk insert 需求, no always load data 需求
在没有 blob/text 字段的前提下 (有一两个速度也可以接受，见我测试文章)
index 设置合理 (经常插入：减少 index, 经常查询：增加index)

在主流服务器上插入速度可以达到 500 ~ 1000 行每秒。(每次插入1行，使用事务)
这个是我实践过3000～4000万行表插入100万行新记录后得到的经验，如果大家需求和我类似，那就可以大胆的用 MySQL InnoDB
如果大家对千万级别记录的表有经常的 alter index, alter table, load data, bulk insert 的需求而且不能避免，或者索引字段跟楼主的表相似而且确实有业务需要，那就请谨慎选择MySQL InnoDB，可以选择其他storage engine, 也可以考虑使用其他数据库。

ncisoft2006-11-25 01:58

如果使用上不需要 alter index，那么可以同意 iso1600 的意见，alter index 在性能上的负面影响可以不考虑。
可能我 DB 水平不够吧，index 在上线之后是经常会调整的，因为功能总会有变动，这时候增加/删除/修改 index 就我的经验而言，往往是必须的。iso1600 是否是做产品的？项目或者网站上线之后的功能修改，我觉得是少不了的，项目还好一点，网站可能会动得相当的频繁，在一个 7x24 的大数据量的网站上，停下一天来做 alter index，比较不可思议吧。再说了，如果 index 都不用调整，dba 还用来干嘛呢。。。
另一方面，“在主流服务器上插入速度可以达到 500 ~ 1000 行每秒。(每次插入1行，使用事务)”，如果就这能满足的话，我倒是觉得有点好笑，如果可以有更好的性能选择，为什么就到此为止就满足了呢？难道性能的进一步提升有人会不欢迎吗？不知道 iso1600 在什么样的公司工作，可能硬件条件很充裕，我当时用的服务器，同时包括了 proxy server, java web server, mysql 的服务，而只是一台 Dual XEON 2.4G，2G RAM 的机器而已，没有预算来增加设备了，CPU 利用率正常时候跑在 80% 左右，稍微有点波动网站的访问速度就碰到天花板了。
建议 iso1600 按照我前面的测试思路、用你的插入方式，测试和比较 InnoDB vs MyISAM 的性能差异，在我做的测试中，有超过三倍以上的差距的，有时间我也许会做测试 InnoDB vs PostgreSQL 的插入性能。等你测试完了，再说你是否愿意接受这个性能上的差距。而对于我来说，是不能接受的，因为我的系统性能瓶颈就在 InnoDB 上，如果性能可以改善一点，我的服务器一段时间内支撑就不成问题。
分库分表，确实是解决大数据量的不二法门，比如在电信行业是比较普遍的做法，在其它行业尤其是网站上至少国内而言用得还相当的少。但是，机器物理性能限制造成的分库分表，和数据库本身的实现性能差劲而不得不分库分表（和别的数据库实现相对比），还是有着本质的区别的，否则我们都不用关心 InnoDB 的性能问题，性能再烂十倍，我们不也可以用分库分表来实现不是吗，Oracle 出那么贵的 RAC 也不会有人去买了，不行了就分库分表去好了。照顾大多数开发人员的能力、实现的复杂性、时间进度因素，我以为能不用分库分表，尽量直接在数据库层面解决主要的性能问题方为上策。
我相信，在实际项目中设计并实现了分库分表操作的开发人员，姑且不论是否优美，已经步入高手的行列，至少在网站方面，性能和扩展性的魔术你已经初窥门径。
按照你贴的网址，个人感觉对分库分表的理解是想当然的成分多了一些，有在实际项目中做过分库分表吗？如果你看过 mixi.jp、Live Journal 是怎么在 MySQL 上使用分库分表的，应该就可以明白我说的是什么意思。鉴于分库分表跟本贴无直接关系，这里就不展开讨论了。
我在实际项目中倒是用过分库分表，技术上不是一般的麻烦，要改造的东西很多，设计不当的话代码会非常的乱，绝对不是一个简单的 jdbc driver 的封装就能完成的。java 开发人员常用的 ORM 工具，Hibernate、iBatis、JDO、Spring Template，如何配合你的 pattern 使用，都要设计并封装得合理。直接使用 jdbc？至少我是不会这么做。
【mysql分表|MySQL InnoDB 的性能问题讨论】如果你有兴趣，也有实际分库分表的项目经验，我倒是希望你可以另开一个新帖介绍你的分库分表具体设计和实现，我想 javaeye 很多人对这个技术都会很有兴趣。

分页 :上一页 [ 1][ 2][ 3]4