MySQL High CPU Usage (100% in peek times)

Question

I have a simple LAMP server on Amazon EC2. (find the specs towards the end of this message.) I used the following command in the terminal to see status of each query

watch --interval=2 "mysqladmin -u{username} -p{password} processlist"

I see roughly 4-5 queries running every now and then that result in 5-100 rows on an average (they're not very costly queries). In another console, I bring up the task manager using the "top" command. In it, I see mysqld using 150%+ CPU on an average time where the overall CPU usage is 80-90%. In the peak times, the overall CPU goes up to 100%. I've also realized the update takes a long timer sometimes. This server only uses MySQL.

The select queries that are running frequently are pulling on indexes. It's very strange to see the MySQL hogging up to more than 3/4th of the CPU to run just a few queries. I think I just don't have the correct configuration or need to fine tune MySQL configuration for a better performance. Anyone have any idea why this may be happening?

Server Specs:

• Ubuntu 12.04
• Intel Xeon E5-2670 v2 (Ivy Bridge) Processors
• 7.5 GB RAM
• SSD

MySQL Specs: (Ver 14.14 Distrib 5.5.37, for debian-linux-gnu (x86_64) using readline 6.2)

• max_allowed_packet 64M
• innodb_read_io_threads 4
• innodb_write_io_threads 4

After a few modification: I modified the queries to ensure the where clause is using all index and it's using the correct type. In the execution plan or EXPLAIN statement, I see the following

Select_type: simple type: index_merge possible_keys: idx_tickets_city_code,index3,index6 key: index5,idx_tickets_city_code key_len: 4,4 rows:1612 extra: Using intersect(index3,idx_tickets_city_code);

Total result count is 142 rows and the CPU usage is still ~60% (a little high) for few instances of this query running every few seconds.

Answer 1

It is not a strange - in most of cases it just indicate, some of queries do not proper use indexes.

Possible reasons:

• sort operations
• group operations
• type conversions (compare varchar with integer). In some cases MySQL could show You - it use index, but it will be high cpu usage (100%+ from single query)

Other possible reason - number of rows processed before send to client Query which return just 100 rows, could sort millions before calculate result.

If You already identify this 4-5 queries - just go thru each and check:

• Execution plan
• table structure (types) - check types for all columns used in WHERE and/or JOIN, types must be same
• indexes

Add for answer for comment:

• view inside view - definitely could be reason of high cpu usage

• not always present index used by SQL, You can check it by simple command

  EXPLAIN SELECT ... there Your query

it will show You, what real indexes used or not

You can add result in Your question and we could check, how it work

Answer 2

Consider using the Percona configuration tool for MySQL here https://tools.percona.com/

They also have a query advisor tool.

Both require registration in order to use them.