MySQL 索引创建速度很慢（在 EC2 上）
我有一个相当简单的表
requestparams (
requestid varchar(64) NOT NULL,
requestString text,
) ENGINE=MyISAM;
在用“LOAD DATA”填充表后，我正在更改架构并使“requestid”成为主键。
该表有1100万行，数据大小小于2GB（MYD文件的大小）。处理结束时索引文件大小约为600M。
在我的笔记本电脑上创建索引只需不到 20 分钟。但是，当我在 Amazon 的 EC2（中型实例）上运行该过程时，该过程花费了超过 12 个小时。在整个过程中，磁盘处于超级繁忙状态，IO 等待（如 top 所示）在 40-100% 之间。 CPU 大部分时间都处于空闲状态。我不认为 EC2 上的磁盘那么慢。
在 MySQL 邮件列表上，一些人建议更改服务器变量 myisam_sort_buffer_size 和 myisam_sort_buffer_size 。 myisam_max_sort_file_size。我将它们设置为 512MB &分别为4GB。但索引创建同样缓慢。事实上，MySQL的内存使用量很少超过40M。
我该如何解决这个问题？
解决方案：增加“key_buffer_size”有帮助。我将此值设置为 1GB，该过程在 4 分钟内完成。确保使用 mysqladmin 变量命令验证新设置。
I have a fairly simple table
requestparams (
requestid varchar(64) NOT NULL,
requestString text,
) ENGINE=MyISAM;
After populating the table with "LOAD DATA", I am changing the schema and making "requestid" the primary key.
There are 11 million rows in this table and the data size is less than 2GB (size of the MYD file.) The index file size is around 600M at the end of the process.
Creating index takes less than 20 minutes on my laptop. But, when I ran the process on Amazon's EC2 (medium instance), the process took more than 12 hours. During entire process, the disk was superbusy with IO wait (as see by top) between 40-100%. The CPU was mostly idle. I don't think, the disk on EC2 are that slow.
On MySQL mailing list, some had suggested to change server variables myisam_sort_buffer_size & myisam_max_sort_file_size. I set them to 512MB & 4GB respectively. But index creation was equally slow. In fact, the memory usage by MySQL rarely went beyond 40M.
How do I fix this?
Solution: Increasing "key_buffer_size" helped. I set this value to 1GB and the process completed in 4 minutes. Make sure you verify the new settings with mysqladmin variables command.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您收到的建议是正确的。我怀疑您所做的更改实际上并未生效。尝试在 my.cnf 中进行更改并重新启动服务器。
The suggestions you received are correct. I suspect the changes you made didn't actually take effect. Try making the changes in my.cnf and restart the server.