How do we troubleshoot an issue with Amazon wherein my database queries (PHP/MySQL) are eating 100% CPU most of the day?

Traffic=200 connections average

 

mySQL version = 5.5.40

The following specific query seems to be the culprit with each query being sorted. (Most of the time, the status is Sorting Result).

SELECT reports . * , domains.name AS dom_name, publisher.pub_name
FROM reports, domains, publisher
WHERE reports.domain_id = domains.dom_id
AND publisher.pub_id = reports.pub_id
AND reports.rep_type =  'N'
AND reports.pub_id
IN ( 3 ) 
AND reports.rep_status =  'Y'
ORDER BY reports.rep_pub_date DESC 
LIMIT 1200 , 100

The database contains roughly 100 tables, and the biggest table is reports with roughly 3 GB size and 2,45,00 rows.

Based on the developers demands, we downgraded from being on MySQL 5.6 on RDS to mySQL 5.5 on EC2, situation remains the same. (4 CPU/15 GB RAM)

How do we troubleshoot the issue? Is it related to queries or is it inherently slow on EC2 and thereby we need to upgrade the hardware?

How do we troubleshoot an issue with Amazon wherein my database queries (PHP/MySQL) are eating 100% CPU most of the day?

Traffic=200 connections average

mySQL version = 5.5.40

The following specific query seems to be the culprit with each query being sorted. (Most of the time, the status is Sorting Result).

SELECT reports . * , domains.name AS dom_name, publisher.pub_name
FROM reports, domains, publisher
WHERE reports.domain_id = domains.dom_id
AND publisher.pub_id = reports.pub_id
AND reports.rep_type =  'N'
AND reports.pub_id
IN ( 3 ) 
AND reports.rep_status =  'Y'
ORDER BY reports.rep_pub_date DESC 
LIMIT 1200 , 100

The database contains roughly 100 tables, and the biggest table is reports with roughly 3 GB size and 2,45,00 rows.

Based on the developers demands, we downgraded from being on MySQL 5.6 on RDS to mySQL 5.5 on EC2, situation remains the same. (4 CPU/15 GB RAM)

How do we troubleshoot the issue? Is it related to queries or is it inherently slow on EC2 and thereby we need to upgrade the hardware?

How do we troubleshoot an issue with Amazon wherein my database queries (PHP/MySQL) are eating 100% CPU most of the day?

Traffic=200 connections average mySQL version = 5.5.40

The following specific query seems to be the culprit with each query being sorted. (Most of the time, the status is Sorting Result).

SELECT reports . * , domains.name AS dom_name, publisher.pub_name FROM reports, domains, publisher WHERE reports.domain_id = domains.dom_id AND publisher.pub_id = reports.pub_id AND reports.rep_type = 'N' AND reports.pub_id IN ( 3 ) AND reports.rep_status = 'Y' ORDER BY reports.rep_pub_date DESC LIMIT 1200 , 100

The database contains roughly 100 tables, and the biggest table is reports with roughly 3 GB size and 2,45,00 rows.

Based on the developers demands, we downgraded from being on MySQL 5.6 on RDS to mySQL 5.5 on EC2, situation remains the same. (4 CPU/15 GB RAM)

How do we troubleshoot the issue? Is it related to queries or is it inherently slow on EC2 and thereby we need to upgrade the hardware?

How do we troubleshoot an issue with Amazon wherein my database queries (PHP/MySQL) are eating 100% CPU most of the day?

Traffic=200 connections average

mySQL version = 5.5.40

The following specific query seems to be the culprit with each query being sorted. (Most of the time, the status is Sorting Result).

SELECT reports . * , domains.name AS dom_name, publisher.pub_name
FROM reports, domains, publisher
WHERE reports.domain_id = domains.dom_id
AND publisher.pub_id = reports.pub_id
AND reports.rep_type =  'N'
AND reports.pub_id
IN ( 3 ) 
AND reports.rep_status =  'Y'
ORDER BY reports.rep_pub_date DESC 
LIMIT 1200 , 100

The database contains roughly 100 tables, and the biggest table is reports with roughly 3 GB size and 2,45,00 rows.

Based on the developers demands, we downgraded from being on MySQL 5.6 on RDS to mySQL 5.5 on EC2, situation remains the same. (4 CPU/15 GB RAM)

How do we troubleshoot the issue? Is it related to queries or is it inherently slow on EC2 and thereby we need to upgrade the hardware?