6

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.

Eric
  • 1,271
  • 4
  • 14
  • 21

5 Answers5

2

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.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • Indexes are also beneficial for UPDATE/DELETE performance. Anything with a JOIN, WHERE, or ORDER BY can use indexes. Using an index is crucial for getting row-level locking when you UPDATE or DELETE a small number of rows. Otherwise it has to lock the whole table. – Bill Karwin Mar 11 '14 at 20:53
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

Ezekiel Baniaga
  • 853
  • 1
  • 12
  • 26
2

  • Modify your config for MySQL server

    innodb_flush_log_at_trx_commit = 0

  • then Restart MySQL server

cursorrux
  • 1,382
  • 4
  • 9
  • 20
vega ho
  • 21
  • 1
0

please set the innodb_buffer_pool_size to 512M. It may increase the performance

SET GLOBAL innodb_buffer_pool_size=512M

user3058132
  • 1
  • Thank you for your answer. Quick question: how to find the buffer pool size – Eric Dec 02 '13 at 17:03
  • 1
    @Eric, `SELECT @@innodb_buffer_pool_size;` – Bill Karwin Dec 02 '13 at 17:05
  • 3
    @user3058132, you can't change innodb_buffer_pool_size dynamically with SET GLOBAL. You have to edit the my.cnf file and restart mysqld. – Bill Karwin Dec 02 '13 at 17:07
  • @BillKarwin Thank you, my buffer_pool_size is very small. I think it is the reason. – Eric Dec 02 '13 at 17:15
  • @BillKarwin Hi Bill, I changed the buffer pool size to 54MB, but insert speed is still very slow. It spend 2hr to insert 10MB data into MySQL DB. Here is my server info: CPU: Intel Xeon E5649 2.53GHZ 2.53GHZ RAM: 4GB, Last weekend, I did upgrade for my server. Based on my server info, what the buffer pool size is the best? Thank you – Eric Dec 10 '13 at 18:38
  • @Eric, I don't have enough information to answer that. The buffer pool size is not a magic value to make everything run fast. There are multiple tunable config parameters. The best tuning settings have to do with the kind of data you're inserting, and the rate at which you are inserting it, and what else is running on the server. Of course 2 hours seems excessive, so I think there is some other factor at play here. I can suggest trying http://tools.percona.com to help with an initial configuration, and you can also contact Percona Support for more specific help. – Bill Karwin Dec 11 '13 at 02:17
  • @BillKarwin Thank you for your answer. I am going to dig percona. – Eric Dec 11 '13 at 14:51
0

Recommendations could vary based on your implementation. Here are some notes copied directly from MySQL documentation:

Bulk Data Loading Tips

When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET autocommit and COMMIT statements.

Use the multiple-row INSERT syntax to reduce communication overhead between the client and the server if you need to insert many rows:

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

If you are doing a huge batch insert, try avoiding the "select from last_insert_id" that follows the insert as it seriously slows down the insertions (to the order of making a 6 minute insert into a 13 hour insert) if you need the number for another insertion (a subtable perhaps) assign your own numbers to the id's (this obviously only works if you are sure nobody else is doing inserts at the same time).

As mentioned already, you can increase the size of the InnoDB buffer pool (innodb_buffer_pool_size variable). This is generally a good idea because the default size is pretty small and most systems can accommodate lending more memory to the pool. This will increase the speed of most queries, especially SELECTs (as more records will be kept in the buffer between queries). The insert buffer is also a section of the buffer pool and will store recently inserted records, which will increase speed if you are basing future inserts on values from previous inserts. Hope this helps :)

Community
  • 1
  • 1
Joy Rê
  • 159
  • 4
  • 2
    Also, consider using LOAD DATA INFILE (http://dev.mysql.com/doc/refman/5.0/en/load-data.html), if it is possible by design. It is much much faster than regular insert – Andrew Dec 02 '13 at 16:30