2

I know the question's title is bad, But I have an hard time figure it out my self... sometimes this query runs in 2-3 seconds and sometimes it's takes even 30 seconds

I have a table named ranking_temp_free: ranking_temp_free

I have one Index except the primary key:

Index

I have no triggers or partitions on this table..

I'm using this Query:

INSERT IGNORE INTO ranking_temp_free 
(countryID, categoryID, ranking, popID, app_id, check_time) 
VALUES (/*Here comes 50,000 rows of Data*/);

And as I wrote before.. The time that takes the query to finish is not fixed as I wish to be...

Logs:

THERE ARE MORE LOGS BEFORE..

07-02-2013 00:24:37 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:24:54 Inserted: 50000 rows
QUERY TIME : 17 SECONDS

07-02-2013 00:25:05 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:25:41 Inserted: 50000 rows
QUERY TIME : 36 SECONDS

07-02-2013 00:26:02 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:26:07 Inserted: 50000 rows
QUERY TIME : 5 SECONDS

07-02-2013 00:26:28 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:26:35 Inserted: 50000 rows
QUERY TIME : 7 SECONDS

07-02-2013 00:26:46 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:26:52 Inserted: 50000 rows
QUERY TIME : 6 SECONDS

07-02-2013 00:27:03 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:27:17 Inserted: 50000 rows
QUERY TIME : 14 SECONDS

07-02-2013 00:28:19 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:28:39 Inserted: 50000 rows
QUERY TIME : 20 SECONDS

AND SO ON.........

It's a simple INSERT so why the time is different?.

Just to be clear: THERE ARE NO ANY OTHER PROCESSES ON THE SQL SERVER! PROCESSES

EXAMPLE FOR THE TABLE DATA: EXAMPLE

3
  • Do you have wide variances in the number of rows which may not be inserted due to the IGNORE definition? – Mike Brant Feb 6 '13 at 23:01
  • I don't think so..since the table get truncate before every run (once per two hours). and I can see the difference in the time from the start... It's scan zip files with data and insert them to the DB, so the IGNORE is just to keep going even if there are any duplicate that shouldn't be.. – Ofear Feb 6 '13 at 23:06
  • We found that even so the SQL server have memory of 24GB , it's use only 4GB of it. All the other 20GB goes to other Apache processes.. I will post back to see if the problem still on. Thanks you all for the help! and I will remove the index before posting back! Thank! – Ofear Feb 7 '13 at 9:32
1

We found the solution to the slow random query execution time:

I added this line: SET TRANSACTION ISOLATION LEVEL READ COMMITTED right after the DB connect.

we are using PHP, So it's looks like this:

mysql_select_db($db, $link);
mysql_query("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

All our problem solved with this magic line.

Now our queries run in 40 minutes and not in 2.5 hours as it use too..

thanks all for your help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.