|
锁定老帖子 主题：MySQL InnoDB 的性能问题讨论
该帖已经被评为精华帖
|
|
|---|---|
| 作者 | 正文 |
|
发表时间：2006-11-21
jreros 写道 有位同志做了以下试验：
反驳"MySQL InnoDB (不行)的性能问题"，千万级别记录来测试说明 http://hi.baidu.com/jabber/blog/item/4df7e150a0df935c1138c202.html 具体情况不同，不好说啊 可能主要的差异就在主键和怎么个索引法了吧,此外无序主键(GUID)也会带来一些问题。. 貌似楼主的数据表是有主键之外的唯一索引的， 而jabber的表结构如何现在很难判断. |
| 返回顶楼 | |
|
发表时间：2006-11-21
我在 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 |
| 返回顶楼 | |
|
发表时间：2006-11-22
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。 刚才拿到了以前生产机的账号，他们现在不用了，正在倒数据，能否提供下载空间？我可以提供测试样本。 |
| 返回顶楼 | |
|
发表时间：2006-11-22
数据拿到了，以前的生产机也被允许用两天，我自己测试一下，测试过程和推导逻辑见下，数据情况：
引用 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 --- 动态察看文件生成大小的变化幅度，比如每次增长的时间间隔，可以有更直观的了解 |
| 返回顶楼 | |
|
发表时间：2006-11-23
来迟咯，飘过~
|
| 返回顶楼 | |
|
发表时间：2006-11-23
Arbow 写道
在楼上的楼上的楼上的楼上的楼上的帖子中已经提到了。 |
| 返回顶楼 | |
|
发表时间：2006-11-23
新注册的账号禁言几天后终于可以发言了。:)
我对楼主的测试方法有两个疑问，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等技术解决。 |
| 返回顶楼 | |
|
发表时间：2006-11-23
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/ |
| 返回顶楼 | |
|
发表时间：2006-11-24
引用 我是在 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 在满足以下条件下，千万级别的表 插入速度 性能稳定。 [list=] 不需要经常修改表结构 not always alter table, alter column or alter index 没有经常性的 bulk insert 需求, no always load data 需求 在没有 blob/text 字段的前提下 (有一两个速度也可以接受，见我测试文章) index 设置合理 (经常插入：减少 index, 经常查询：增加index) [/list] 在主流服务器上插入速度可以达到 500 ~ 1000 行 每秒。(每次插入1行，使用事务) 这个是我实践过3000～4000万行表插入100万行新记录后得到的经验，如果大家需求和我类似，那就可以大胆的用 MySQL InnoDB 如果大家对千万级别记录的表有经常的 alter index, alter table, load data, bulk insert 的需求而且不能避免，或者索引字段跟楼主的表相似而且确实有业务需要，那就请谨慎选择MySQL InnoDB，可以选择其他storage engine, 也可以考虑使用其他数据库。 |
| 返回顶楼 | |
|
发表时间：2006-11-25
如果使用上不需要 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？至少我是不会这么做。 如果你有兴趣，也有实际分库分表的项目经验，我倒是希望你可以另开一个新帖介绍你的分库分表具体设计和实现，我想 javaeye 很多人对这个技术都会很有兴趣。 |
| 返回顶楼 | |
