2

These are my RDS instance configs

  • Instance Class: db.m3.xlarge
  • Storage 40 GB
  • Engine MySQL 5.6.34
  • Multi AZ : No

I have a the node.js based backend hosted in EC2 instance and also have a cloud front with 10 minutes cache window which hosts one of our intense query with takes around 20-30 seconds (i know this is intense and i am optimizing this) but on the mean time with say minimal number of user say 2-3 the CPU spikes to around 85-100% and i am not able to figure out why. any suggestion and ideas are much appreciated.

2

What you describe is the expected behavior.

An m3.xlarge has 4 vCPUs (virtual CPUs -- and having 4 of these typically means two cores, each with two hyperthreads). 100% utilization means all 4 vCPUs are completely busy, while 75% means 3 are busy, 50% means 2, and 25% means 1. (Below 100%, this doesn't necessarily mean e.g. exactly 3 are working and one is idle, but refers to the total idle time of each vCPU, divided by the number of vCPUs, subtracted from 100%).

In MySQL Server, any single query can use only the equivalent of one vCPU (it's not physically locked to one specific vCPU by the OS, but it can't consume more than the equivalent of one) ... so with 2 or 3 long running queries, 50%-75% CPU utilization would be exactly normal, assuming the thing that causes the query to require a long amount of time in the first place is the amount of CPU work required to process it -- anything less than that means the CPU cycles aren't the primary factor that causes the query to be long-running. (The usual other cause of long-running queries is the amount of disk I/O that must be done to satisfy the query. Performance of queries is typically bounded by available disk or available CPU, with the nature of the query, and the size of the buffer pool relative to the working data set being significant factors).

If CPU cycles are indeed the primary cause for the query to require so much time, you can see why the numbers on the CPU are what they are -- it would not make sense to artificially throttle down the query to use even less, thus making it even slower, still... but this is what would have to happen.

As it stands, MySQL will consume the resources it needs, as quickly as the operating system will allow it to, in order to execute any individual query as quickly as possible. If you had more long running queries running at a given moment in time than there are vCPUs on the system, and the queries are CPU-bound, the OS thread scheduler is the entity responsible for allocating each of them a reasonable share of whatever is available --so on a 4 vCPU system, with 4 long-running queries already running, if a 5th query starts, it doesn't sit and wait for one of the others to finish -- it actually would slow each of them down by about 20% as it claimed its share of the total available CPU cycles -- all running queries get a share, based on how much work they are trying to do.

On a machine with only 1 vCPU, you'd find that only one single long-running query would spike the utilization to 100%, and this also would be completely normal, if the need for CPU cycles is the main factor influencing the query's overall execution time.

  • thanks for the information. so based on the answer 1.if there are long running query should consider increasing vCPU 2.optimize the query as much as possible so that it doesn't become a long running query and string the system. correct me if i am wrong. is there are other factors we need to consider ? – DragonDay Jan 12 '18 at 6:15

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.