I have multiple python scripts running statistical tests and saving the results in a MySQL database.
I have chosen to create a procedure, making these scripts do lot's of calls, and only commiting in the end. (So that no info got lost, sometimes internet goes down and script gets corrupted, and other bugs)
The thing is, with this I can get multiple scripts running at the same time with some locking mechanisms, but after 3 or 4 scripts running, my server gets all 4 cores at 99/100% ...
htop it shows lots of mysqld processes and they all consume lots of CPU.
Anyone has any idea on how to optimize the CPU usage from mysql?
[my thoughts right now are instead of making multiple calls, create a multi-line insert, reducing the number of calls to mysql, but i'm not sure this would reduce CPU usage...]
if( select exists(select id from frequency where `title` like _title and `subset` like _string) ) = 0 THEN insert into frequency(`title`, `subset`, `amount`) values(_title, _string, _amount); else update frequency set `amount` = amount+1 where `title` like _title and `subset` like _string; end if;