How to speed up MySQL INSERTs/UPDATEs?

Question

I have three large MySQL tables. They are approaching 2 million records. Two of the tables are InnoDB and are currently around 500 MB in size. The other table is MyISAM and is about 2.5 GB.

We run an import script from FileMaker to insert and update records in these tables but lately it has become very slow - only inserting a few hundred records per hour.

What can I do to increase performance to make inserts and updates happen faster?

Need more information about the 'import script from FileMaker to insert and update.' Is this a FileMaker script (slow)? Are there any unstored FileMaker calcuations involved (slow)? And how is FileMaker talking to MySQL? — Ted, Dec 10 '10 at 05:22
possible duplicate of [How to improve MySQL INSERT and UPDATE performance?](http://stackoverflow.com/questions/2367229/how-to-improve-mysql-insert-and-update-performance) — hakre, Aug 23 '13 at 15:16

score 2 · Answer 1 · answered Dec 07 '10 at 22:32

For INSERT it could have to do with the indexes you have defined on the tables (they have to be updated after each INSERT). Could you post more information about them? And are there triggers set on the tables?

For UPDATE it is a different story, it could be that not the record update is slow but finding the record is slow. Could you try to change the UPDATE into a SELECT and see if it is still slow? If yes, then you should investigate your indexes.

Riedsio · Answer 2 · 2010-12-08T14:02:52.583

2

For the Innodb table, if it's an acceptable risk, I'd consider changing the innodb_flush_log_at_trx_commit level. Some more details in this blog post, along with some more Innodb tuning pointers.

For both engines, batching INSERTs together can speed things up to a point. See doc.

What version of MySQL are you running? There have been many improvements with the new InnoDB "Plugin" engine and concurrency of operations on servers with multiple processors.

edited Dec 08 '10 at 14:02

answered Dec 07 '10 at 22:34

Riedsio

9,758
1
24
33

+1 for batching, if you're not already doing that it helps a lot. – littlegreen Dec 08 '10 at 08:28

score 2 · Answer 3 · answered Dec 19 '10 at 00:35

Is the query slow when executed on MySQL from the command line?

If you're using the Execute SQL Script step from FileMaker, that connects and disconnects after every call, causing major slowdowns when executing large numbers of queries. We've had clients switch to our JDBC plugin (self-promotion disclaimer here) to avoid this, resulting in major speedups.

score 0 · Accepted Answer · answered Feb 05 '11 at 01:27

0

It turns out the reason for the slowness was from the FileMaker side of things. Exporting the FileMaker records to a CSV and running INSERT/UPDATE commands resulted in very fast execution.

answered Feb 05 '11 at 01:27

Matt McCormick

13,041
22
75
83

How to speed up MySQL INSERTs/UPDATEs?

4 Answers4