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);
    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".

  • Yes, i had 2 selects, one to check if value existed or not, if it existed it would update one column, otherwise create the entire row... The other select was just pure stupid to increment a value: eg. ` select value into var...; set var = var+1; update ... value = var...; ` i removed the second , obviously to update / increment in a single statement, can i improve the exists(select ...) one? – Rui Costa Sep 12 '16 at 2:04
  • Use INSERT ... ON DUPLICATE KEY UPDATE ... for the first. It can also be done in batch (multiple rows at once). Show us the current queries if you would like help converting. – Rick James Sep 12 '16 at 2:56
  • Adding the index improved the CPU usage in almost 90%! Thanks :D – Rui Costa Sep 12 '16 at 3:54
  • Didn't see your comment @Rick James, let me put it in a pastebin ;) Basically I have a subset of words present in a title, and i want to count the amount of times that subset appears in a title. (just to give you the context) #link: pastebin.com/1Z56FPzv – Rui Costa Sep 12 '16 at 4:12
  • IODKU depends on there being a UNIQUE (or PRIMARY) key to know when to insert versus update. Your test seems to disallow such. Also, can't that UPDATE cause multiple rows to be matched? And, perhaps you meant + _amount instead of +1? – Rick James Sep 12 '16 at 19:40

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.