Page 2 of 2

Re: mysql overloading system cpu/IO

Posted: 20 Sep 2018 10:17
by BOOZy
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.

Re: mysql overloading system cpu/IO

Posted: 20 Sep 2018 10:33
by pdwalker
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.

Re: mysql overloading system cpu/IO

Posted: 20 Sep 2018 10:48
by BOOZy
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

Now what? :(

Re: mysql overloading system cpu/IO

Posted: 20 Sep 2018 14:55
by pdwalker
as root

Code: Select all

# netstat -plutn

Re: mysql overloading system cpu/IO

Posted: 20 Sep 2018 14:58
by pdwalker
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

Re: mysql overloading system cpu/IO

Posted: 21 Sep 2018 12:17
by BOOZy
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 |
+-------+-----------+-----------+-------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+----------+

Re: mysql overloading system cpu/IO

Posted: 21 Sep 2018 14:48
by BOOZy
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.

Re: mysql overloading system cpu/IO

Posted: 24 Sep 2018 07:30
by BOOZy
And it went away... All by itself.

Re: mysql overloading system cpu/IO

Posted: 30 May 2019 14:42
by ladylinux
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

Re: mysql overloading system cpu/IO

Posted: 31 May 2019 07:10
by henk
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