multiple calls to procedure optimization 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% ...

Using 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...]

The query

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;
High CPU usually implies SELECT with an inadequate index.

You mentioned inserts -- Yes, INSERTing 100 row in a single statement runs about 10 times as fast as 100 separate INSERT statements.  This is largely because of various overhead things that involve the CPU.  So, that should cut back on the CPU.

If you are doing any "normalization", consider doing it before entering the big transaction.  Ditto for anything else that might be "idempotent".