-1

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 !!

user3017110
  • 155
  • 12
  • More execution means more work for the CPU, also most memory allocation is happening on a stack so the pushing and popping becomes quite time-consuming. Most people make sure that script1 is finished before script2 is finished. Just imagine opening 10 very memory consuming video-games, it's going to take a lot more time for them to do what they have to do with the memory and cpu at hand, rather than opening 1 game at a time. You can probably do some optimization, but it's probably not worth the time. Just have one script running on each machine at a time. – Jonast92 Jul 03 '14 at 10:04
  • 1
    This is either [tag:mysql] or [tag:sql-server] but not both. Once you've worked out which it is, you should probably ask on [dba.se] –  Jul 03 '14 at 10:05
  • Jonast92 : If this is coming from CPU/memory, do you think using nice to set priority on my scripts could help ? And yes it is usually what I am trying to do but this is not always possible. It is more a first script (not urgent) that takes 5-6h, and a second script (pretty urgent) that takes 30minutes... So I can't always wait... – user3017110 Jul 03 '14 at 10:09
  • I'd look into event scheduling. Prioritizing could change something but I'm not sure how much it would. – Jonast92 Jul 03 '14 at 10:12

2 Answers2

1

30 minutes for million INSERT is not fast. Do you have an index on date column? (or whatever column you are using to pivot on)

Regarding your original question.It's difficult to say much without knowing the details of both your scripts and the table structures, but one possible reason why the scripts are running reasonably quickly separately is because you are doing similar kinds of SELECT queries, which might be getting cached by MySQL and then reused for subsequent queries. But if you are running two queries in parallel, then the SELECT's for the corresponding query might not stay in the cache (because there are two concurrent processes which send new queries all the time).

You might want to explicitly disable cache for some queries which you are sure you only run once (using SQL_NO_CACHE modifier) and see if it changes anything. But I'd look into indexing and into your table structure first, because 30 minutes seems to be extremely slow :) E.g. you might also want to introduce partitioning by date for your tables, if you know that you always choose entries in a given period (say by month). The exact tricks depend on your data.

UPDATE: Another issue might be that both your queries work with the same table (table 1), and the default transaction isolation level in MySQL is REPEATABLE READS afair. So it might be that one query is waiting until the other is done with the table to satisfy the transaction isolation level. You might want to lower the transaction isolation level if you are sure that your table 1 is not changed when scripts are working on it.

Ashalynd
  • 12,363
  • 2
  • 34
  • 37
  • Thanks, actually all my INSERT/UPDATE are followed by many SELECT queries too. And I have an index on my date column ! And I can't really partionnate my tables. I am going to do some tests with SQL_NO_CACHE to see if this is better ! – user3017110 Jul 03 '14 at 10:13
  • Good luck, take into account that you should use this modifier only for the SELECT queries which are run once during your script. If there is a query that is run many times with the same parameters, then it makes sense to cache the results. There is one more possible issue, I'll add it to my answer. – Ashalynd Jul 03 '14 at 10:56
0

You can use an event scheduler so you can set mysql to launch this queries at different hours of the day, in another stackoverflow related question you have an exmaple of how to do it: MySQL Event Scheduler on a specific time everyday

Another thing to have in mind is to use the explain plan to see what could be the reason the query is that slow.

Community
  • 1
  • 1
Sandstorm
  • 144
  • 5
  • I don't really see how en event scheduler could help me.. I am already using crontab, but my first request is actually much longer (5h) but not very "important" (it doesn't need to be fast) and the second one is very important and only takes about 30minutes... – user3017110 Jul 03 '14 at 12:24