mysql overloading system cpu/IO

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

Re: mysql overloading system cpu/IO

Post 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.
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: mysql overloading system cpu/IO

Post 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.
User avatar
BOOZy
Posts: 39
Joined: 04 Oct 2017 13:17

Re: mysql overloading system cpu/IO

Post by BOOZy »

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

Now what? :(
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: mysql overloading system cpu/IO

Post by pdwalker »

as root

Code: Select all

# netstat -plutn
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: mysql overloading system cpu/IO

Post 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
User avatar
BOOZy
Posts: 39
Joined: 04 Oct 2017 13:17

Re: mysql overloading system cpu/IO

Post 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 |
+-------+-----------+-----------+-------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+----------+
User avatar
BOOZy
Posts: 39
Joined: 04 Oct 2017 13:17

Re: mysql overloading system cpu/IO

Post 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.
User avatar
BOOZy
Posts: 39
Joined: 04 Oct 2017 13:17

Re: mysql overloading system cpu/IO

Post by BOOZy »

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 »

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: 517
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

Re: mysql overloading system cpu/IO

Post 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
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
Post Reply