mysql overloading system cpu/IO

Report bugs and workarounds
User avatar
BOOZy
Posts: 20
Joined: 04 Oct 2017 13:17

Re: mysql overloading system cpu/IO

Post by BOOZy » 20 Sep 2018 10:17

Since about a week I'm seeing the same thing, MySQL I/O and CPU are really high.

My setup is running on VMWare ESXi as well, with 8GB of RAM. ESXi reports that slightly more than 2GB is actively used.

One thing I have noticed that it's not constant but with a certain interval.
I see 40 minutes of high load then 20 minutes of calm and then it repeats itself.
I suspect that 40 minutes length isn't fixed but correlated with CPU and I/O bottlenecks, but the one hour (40+20=60) cycle is interesting.

ls -la /etc/cron.hourly/ lists three tasks:
0anacron
mailscanner
mailwatch_relay.sh

Maybe one of those has something to do with it.

User avatar
pdwalker
Posts: 1137
Joined: 18 Mar 2015 09:16

Re: mysql overloading system cpu/IO

Post by pdwalker » 20 Sep 2018 10:33

When mysql is really busy, run this command and post the results here:

Code: Select all

mysqladmin -uroot -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'` processlist
Sure, mysql might be busy, but it'd be good to find out what it is doing at the time it is doing something.

Also, perhaps your mysql configuration is not optimal. We may need to see the contents of your mysql configuration files.

User avatar
BOOZy
Posts: 20
Joined: 04 Oct 2017 13:17

Re: mysql overloading system cpu/IO

Post by BOOZy » 20 Sep 2018 10:48

error: 'Access denied for user 'root'@'localhost' (using password: YES)'

Now what? :(

User avatar
pdwalker
Posts: 1137
Joined: 18 Mar 2015 09:16

Re: mysql overloading system cpu/IO

Post by pdwalker » 20 Sep 2018 14:55

as root

Code: Select all

# netstat -plutn

User avatar
pdwalker
Posts: 1137
Joined: 18 Mar 2015 09:16

Re: mysql overloading system cpu/IO

Post by pdwalker » 20 Sep 2018 14:58

One of those things listed will me the mysql process on port 3306 and the ip address

then you can run:

Code: Select all

mysqladmin -uroot -h<ip address> -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'` processlist

User avatar
BOOZy
Posts: 20
Joined: 04 Oct 2017 13:17

Re: mysql overloading system cpu/IO

Post by BOOZy » 21 Sep 2018 12:17

Ok. It took some time to unpack the mysqladmin line and why it wouldn't work but I got it now.

Result:

+-------+-----------+-----------+-------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+-----------+-----------+-------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+----------+
| 14 | sqlgrey | localhost | sqlgrey | Sleep | 87 | | | 0.000 |
| 11222 | mailwatch | localhost | mailscanner | Query | 0 | query end | REPLACE INTO mtalog (`timestamp`,`host`,`type`,`msg_id`,`relay`,`dsn`,`status`,`delay`) VALUES (FROM | 0.000 |
| 11272 | mailwatch | localhost | mailscanner | Sleep | 3 | | | 0.000 |
| 11339 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
+-------+-----------+-----------+-------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+----------+

User avatar
BOOZy
Posts: 20
Joined: 04 Oct 2017 13:17

Re: mysql overloading system cpu/IO

Post by BOOZy » 21 Sep 2018 14:48

And when I/O is low:

+-------+-----------+-----------+-------------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+-----------+-----------+-------------+---------+------+-------+------------------+----------+
| 14 | sqlgrey | localhost | sqlgrey | Sleep | 117 | | | 0.000 |
| 12007 | mailwatch | localhost | mailscanner | Sleep | 3 | | | 0.000 |
| 12255 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
+-------+-----------+-----------+-------------+---------+------+-------+------------------+----------+


So it looks like the mailwatch process is doing something strange:

REPLACE INTO mtalog (`timestamp`,`host`,`type`,`msg_id`,`relay`,`dsn`,`status`,`delay`) VALUES (FROM | ----- trunkated.

User avatar
BOOZy
Posts: 20
Joined: 04 Oct 2017 13:17

Re: mysql overloading system cpu/IO

Post by BOOZy » 24 Sep 2018 07:30

And it went away... All by itself.

ladylinux
Posts: 6
Joined: 22 May 2019 11:45

Re: mysql overloading system cpu/IO

Post by ladylinux » 30 May 2019 14:42

Hello,

Centos 7 EFA 4

I added this to mysql and load went from 100% to almost nothing.

skip-name-resolve

I can't imagine since this is only for EFA why anyone would do host name lookups with Mysql

LadyLinux

henk
Posts: 386
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

Re: mysql overloading system cpu/IO

Post by henk » 31 May 2019 07:10

From the mysql documentation : https://dev.mysql.com/doc/refman/5.7/en/host-cache.html

You should check your EFA Unbound dns. As the dns seems to be a problem. Mysqltuner provides usefull info.

To bypass dns issues with mysql and bump up security by using localhost for connections only

1. /etc/my.cnf.d/server.cnf

Code: Select all

bind-address = 127.0.0.1
skip-name-resolve
skip-host-cache

Post Reply