1

I have a c4.large EC2 instance which is running Windows Server 2012 and MySQL.

When I look at the CPU usage on the machine it seems that MySQL is capping out at 50% CPU usage when the system is under load, it also looks like this is relative to some kind of throttling but I am not sure why this is happening.

The c4.large instance comes with 2 vCPUs and dedicated ECU and I can see from the screenshot below that these are both being used but maybe the combined total of this is being capped?

I am not sure if this is a MySQL issue or relative to Amazon Web Services like a potential systems issue or if it's some kind of misconfiguration on my side.

Does anyone have any idea why this is happening?

Screenshot of system performance

2

This is probably neither throttling nor a misconfiguration, nor anything related to AWS. Consider these points:

  • A c4.large instance has 2 cores.

  • Any given query running in MySQL runs entirely within a single thread, and

  • a single thread within a process can only run on 1 core at any moment in time (though presumably the Windows process scheduler can move it between the cores at will), and

  • 100% utilization of 1 core by 1 thread would show up as 50% CPU utilization.

What you appear to have here, rather than the server really being "under load," is a single CPU-bound query running.

Any user with the PROCESS privilege can run the SHOW FULL PROCESSLIST; query in MySQL and see what all the session (connection) threads in MySQL Server are doing at the moment. Run this and you should find your CPU hog. If you run another instance of the same query, you should find that the CPU utilization hits 100% with two CPU expensive queries running concurrently. (Then you'll want to fix/optimize that query.) The TIME column tells you how long the connection has been in its current state.

  • Thanks Michael, that is spot on. Problem is a query that goes something LIKE "2017-03-10%" thousands of times but the real problem is that % is not something that can be indexed so it is in turn computing the result thousands of times with some funky select and concat queries (interesting to see how it breaks this down though). – Craig van Tonder Mar 10 '17 at 10:03
  • Great. One thing, though... LIKE "2017-03-10%" can use an index effectively, because it's anchored to the left of the column. It's expressions LIKE "%2017-03-10%" and LIKE "%2017-03-10" which can't, and even though they can't use an index properly, they may still benefit from an index, particularly a covering index, since the optimizer may choose an index scan -- read the entire index for matches, rather than read the entire table for matches. Also, you want to avoid functions like CONCAT() evaluating columns as arguments for comparison operators in WHERE when possible. – Michael - sqlbot Mar 10 '17 at 11:40
  • Thanks Michael, appreciate the input, it makes sense and is the case here too. You are a very smart guy :) – Craig van Tonder Mar 10 '17 at 16:29
  • @Michael-sqlbot - LIKE "2017-03-10%" cannot be done efficiently, unless it is a VARCHAR. To make it efficient, you really need x >= "2017-03-10" AND x < "2017-03-10" + INTERVAL 1 DAY. – Rick James Mar 13 '17 at 22:06
  • @RickJames that's correct, of course. It didn't occur to me that anyone would write a query like that if the column was a date/datetime/timestamp, so between that, the other examples with leading %, and the mention of concat... I assumed it was a varchar without so much as a fleeting thought otherwise. – Michael - sqlbot Mar 13 '17 at 23:57

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.