MySQL insert/update records very slow

Question

I found out mysql update records very slow.

I have a logic whereby, if the records exist then, it will update, else it will insert. but the total record for source database having 14k rows, and it only successfully inserted each row in few sec, therefore, it is very slow, how can i make it 1 minutes to insert 14k rows? by the way, i'm using c#

foreach (row in rows)
    checkrecords(row); // if exist update, else create.

Regards, MH

innodb buffer pool set to 70% of my cpu memory, it still no effect. — Min Hong Tan, Feb 04 '14 at 02:54
I am not sure how to do in C#, but in php i usually combine insert statement and execute at one shot. Well it does takes time to check for row exists and decide update or insert row. Use innodb for tables, use profiling to see ehich part of your code takes most time. — Abhishek Salian, Feb 04 '14 at 02:57
Also write your code here, we cant read your mind to find out what you are trying to do. — Abhishek Salian, Feb 04 '14 at 02:59
I would assume you are committing your transactions within the `checkrecords()` method? I'm no expert in innodb, but try committing only after you've built all the queries, like in this example http://stackoverflow.com/questions/4818929/mysql-innodb-confused-about-transactions . This article may also prove useful (turn autocommit off) https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html — JW Lim, Feb 04 '14 at 03:22

score 0 · Answer 1 · answered Feb 04 '14 at 03:01

0

If exists may be the root of the problem. Make sure you are using a unique index on your check whether the record exists or not.

answered Feb 04 '14 at 03:01

Alain

109
2

hi thanks, i have a function to check the record is exist. but the problem is it 14k records only created 7xx records in few hours. – Min Hong Tan Feb 04 '14 at 03:03

score 0 · Answer 2 · edited May 23 '17 at 12:28

Try setting values:

innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT (for non-windows machine)
innodb_buffer_pool_size=25GB (currently it is close to 21GB)
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=0...1000 (try different values, beginning with 200)

References:

MySQL docs for description of different variables.

MySQL Server Setting Tuning

MySQL Performance Optimization basics

Hope it helps...

Please refer Why is MySQL InnoDB insert so slow?

Source

score 0 · Answer 3 · answered Feb 04 '14 at 03:55

Restructure the query and be sure the indexes are set up correctly.

Load the rows to be updated into a temporary table. Then do the insert/update as:

insert into t(. . .)
    select . . .
    from temptable tt
    on duplicate key update col1 = values(col1), . . .;

Make sure that the conditions that you are checking for the existence of the record are combined into a unique index. So, if you are looking for a combination of three columns, then make sure you have a unique index on t(col1, col2, col3).

MySQL insert/update records very slow

3 Answers3