I have a MySQL server with many innodb tables. I have a background script that does A LOT a delete/insert with one request : it deletes many millions of rows from table 2, then insert many millions of rows to table 2 using data from table 1 :

INSERT INTO table 2 (date)
SELECT date from table 1 GROUP BY date

(The request is actually more complex but it is to shown what kind of request I am doing).

At the same time, I am going to run a second background script, that does about a million INSERT or UPDATE requests, but separately (I mean, I execute the first update query, then I execute an insert query, etc...) in table 3.

My issue is that when a script is running, it is fast, like let's say it takes 30minutes each, so 1h total. But when the two scripts are running at the same time, it is VERY slow, like it will take 5h, instead of 1h.

So first, I would like to know what can cause this ? Is it because of IO performance ? (like mysql is writing in two different tables so it is slow to switch between the two ?)

And how could I fix this ? If I could say that the big INSERT query is paused while my second background script is running, it would be great, for example... But I can't find a way to do something like this.

I am not an expert at MySQL administration.. If you need more information, please let me know !

Thank you !!

I have already asked my question on Stackoverflow and they only proposed to use an event scheduler. My issue is that, in production, my first script (the least important/urgent) can take 5h or even more, and the second one would only take 30min and is a little bit urgent. I can't wait for the first script to finish before running the second one.

closed as off-topic by JNK Jul 7 '14 at 12:09

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – JNK
If this question can be reworded to fit the rules in the help center, please edit the question.


It looks like 'page swapping' is the problem here.

When you deal with one table at a time, its indexes and parts of the table are loaded into the RAM. If you keep dealing with the same table, it will be fast as the table already exists in the ram.

On the other hand, if you keep changing the table that you are dealing with, then the data in RAM has to be written to the disk, and the second table's info will be loaded to RAM. This is one of the MOST EXPENSIVE operations.

Solutions!! Not straight forward though: You may distribute your DB, or run your scripts sequentially. Hardware upgrade may make the process faster.

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