减少 MySQL 中非关键数据的写入 I/O？
我有一个大约有 200 万行的 MySQL 表，以及一个每秒更新大约 100 行的脚本。我想减少正在进行的磁盘写入 I/O 量。对于这个特定的表，ACID 并不重要，就好像我在崩溃中丢失了一些行一样，脚本只会在正确的位置恢复。即使我失去了过去一小时的工作，也没什么大不了的。
该表使用的是 InnoDB，但我切换到 MyISAM，因为我认为如果它不记录每次写入，可能会将 I/O 减少一半。
但即使使用 MyISAM，也会发生大量写入 I/O。表+索引在磁盘上占用了大约1300 MB，但是MySQL每小时向磁盘写入大约1600 MB。我计算过，如果每一行都能完美高效地写入磁盘，那么每小时写入的数据量约为 160 MB。因此，它写入的数据量大约是所需数据的 10 倍。我意识到存在一些低效率，但我猜测大多数写入是因为它将整个页面写入磁盘。
有没有什么方法可以让它减少写入频率，以便它等到每个页面上的更多行被更新，以便更有效地写入（即使在崩溃时会丢失更多数据）？
I have a MySQL table with about 2 million rows, and a script that updates approximately 100 rows per second. I'd like to reduce the amount of disk write I/O that's going on. For this particular table, ACID isn't important as if I were to lose some rows in a crash the script would just resume at the proper place. Even if I lost the past hour's work, it wouldn't be that big of deal.
The table was using InnoDB, but I switched over to MyISAM because I figured if it wasn't logging every write that could cut the I/O by half.
But even with MyISAM there is a lot of write I/O going on. The table + index takes up about 1300 MB on disk, but MySQL is writing about 1600 MB to disk every hour. I've calculated that if each row could be written to disk perfectly efficiently, that would be about 160 MB written per hour. So its writing about 10x as much data as it needs to. I realize there are some inefficiencies, but I'm guessing that most of the writes are because its writing an entire page out to disk.
Is there any way to make it write less often, so it waits until more rows on each page have been updated so that it can be more efficient with the writes (even though there would be more data lost in event of a crash)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
例如，如果您使用 innodb，则可以将 innodb_flush_log_at_trx_commit 设置为 2。这极大地改善了系统更新期间的 I/O。
这里对设置进行一些说明：
http://www.mysqlperformanceblog.com/?s=innodb_flush_log_at_trx_commit
If you are using innodb you could set innodb_flush_log_at_trx_commit to 2 for example. This greatly improved the I/O during updates on our system.
Here some clarification on the setting:
http://www.mysqlperformanceblog.com/?s=innodb_flush_log_at_trx_commit