MySQL High CPU Usage (100% in peek times) 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.
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.