How to improve the speed of InnoDB writes per second of MySQL DB

Question

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

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?

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

Any comments, Thank you.

Answer 1

Some hints:

• 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.

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

Answer 2

If using InnoDB engine+local disk, try to benchmark with innodb_flush_method = O_DSYNC. With O_DSYNC our bulk inserts (surrounded by TRANSACTION) was improved.

Adjust the flush method

In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and similar methods is surprisingly slow. If database write performance is an issue, conduct benchmarks with the innodb_flush_method parameter set to O_DSYNC.

https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html