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.
mysql overloading system cpu/IO
Re: mysql overloading system cpu/IO
When mysql is really busy, run this command and post the results here:
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.
Code: Select all
mysqladmin -uroot -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'` processlist
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
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
Now what?
Now what?
Re: mysql overloading system cpu/IO
as root
Code: Select all
# netstat -plutn
Re: mysql overloading system cpu/IO
One of those things listed will me the mysql process on port 3306 and the ip address
then you can run:
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
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 |
+-------+-----------+-----------+-------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+----------+
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
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.
+-------+-----------+-----------+-------------+---------+------+-------+------------------+----------+
| 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
And it went away... All by itself.
Re: mysql overloading system cpu/IO
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
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
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
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