如何提高MySQL数据库每秒InnoDB写入速度 [英] How to improve the speed of InnoDB writes per second of MySQL DB

问题描述

在我的服务器上，将记录插入MySQL数据库非常慢.关于服务器状态，InnoDB每秒写入数约为20.

On my server, doing insert records into MySQL DB is very slow. Regarding the Server Status, InnoDB writes per second is around 20.

我不是专家，刚刚大学毕业.我没有很多经验. 如何提高InnoDB的写入速度?如果不升级服务器的硬件，有什么办法可以做到?

I am not an expert, just graduated from university. I don't have much experience on it. How could I improve the speed of InnoDB writes? If doesn't upgrade the hardware of my server, is there any way can do it?

我的服务器不好，所以我安装了Microsoft Windows Server 2003 R2.硬件信息如下:

My server is not good, so I installed Microsoft windows server 2003 R2. The hardware info is following:

• CPU:Intel Xeon E5649 2.53GHZ
• RAM:2GB

任何评论，谢谢.

推荐答案

一些提示:

• 最大程度地减少索引数量-减少索引维护.显然，这是在SELECT性能上的权衡.
• 最大程度地增加每笔事务的INSERT数量-持久性价格"将更少(即，当事务的其余部分仍在执行时，可以在后台对磁盘进行物理写入， if 交易时间足够长).一个大事务通常会比许多小事务要快，但这显然取决于您要实现的实际逻辑.
• 将表移至更快的存储空间，例如SSD.可以缓存读取，但是必须将持久事务物理写入磁盘，因此只需缓存还不够.

• Minimize the number of indexes - there will be less index maintenance. This is obvously a trade-off with SELECT performance.
• Maximize the number of INSERTs per transaction - the "durability price" will be less (i.e. physical writing to disk can be done in the background while the rest of the transaction is still executing, if the transaction is long enough). One large transaction will usually be faster than many small transaction, but this is obviously contingent on the actual logic you are trying to implement.
• Move the table to a faster storage, such as SSD. Reads can be cached, but a durable transaction must be physically written to disk, so just caching is not enough.

此外，如果您可以向我们展示您所使用的确切数据库结构和确切的INSERT语句，将会很有帮助.

Also, it would be helpful if you could show us your exact database structure and the exact INSERT statement you are using.

这篇关于如何提高MySQL数据库每秒InnoDB写入速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！