CPU usage goes up at specific time of day probably because of Mysql

Question

My server is showing high cpu usage between 10 am and 2 pm (IST) since last few days. The traffic is prretty much constant throughout the day with no significant jump or fall at any of these time. Even the background tasks are not running at that time. Analyzing the access logs, there is nothing specific about these time. Yet the cpu usage remains near 140% for 3-4 hours continuously.

We are using ubuntu 14.04, mysql 5.7, apache2, django1.10

Using top, I figured out the most of the CPU (around 90% +) is being used by Mysql continuously and the RAM usage is also too high.

Can anyone suggest me what to look for and how to reach a solution or even find the culprit.

Including the content of my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    = 0.0.0.0

log-error       = /var/log/mysql/error.log

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Also, conf.d/ is empty. Seems that they have removed most of the default options from my.cnf

RAM: 2GB. Earlier in django settings there was CONN_MAX_AGE = 100 but i have removed it as i read that almost always persistent connection serve no good now and will try to see the effect tomorrow afternoon.

Answer 1

100% CPU very often means one particular query is running. And it needs a better index. Or a better formulation.

Turn on the slowlog, with long_query_time = 1. If you can get to the file, then use mysqldumpslow -s t or pt-query-digest to find the worst coupld of queries. Then we can discuss what 'composite' index to add (or whatever).

Swapping is terrible for MySQL. Lower some settings to avoid it. Let's see my.cnf and RAM size to help advise.

Answer 2

Please check the MySQL General Log if it is enabled to understand what is running during this time in MySQL.