mysql overloading system cpu/IO
mysql overloading system cpu/IO
The VM was working fine for the past couple of months. Then, starting Sunday, Jan 28th, I had to restart mysqld twice since the mysqld was running unusually and consistently high cpu and I/O (can be seen via top). To the point that incoming mails were stuck in the queue... Only after restarting mysqld things went back to normal.
Any thoughts/ideas?
running 3.0.2.5., no update for mysql (checking via yum).
Any thoughts/ideas?
running 3.0.2.5., no update for mysql (checking via yum).
Re: mysql overloading system cpu/IO
Normally the default mysql install is just fine, but there are many factors that could affect performance. You could take a few steps to find out whats wrong -or not-.
First, before all, Check ou your vm environment (events/resources/vmware Tools).
On EFA VM
My basic check list:
1. check out messages in log files ( /var/log), including mysql log file: /var/lib/mysql/<YOUR FQDN>.err
2. check space available. df -h
3. check out space used by mysql: find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;
4. Use top to see what is going on. memory?
5. Update statistics. mysqlcheck --all-databases --analyze
6. install mysqltuner to get info on your Mysql db 'health'. (https://github.com/major/MySQLTuner-perl)
wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.pl --buffers
Be carefull changing mysql parameters if you have no clue what the are used for and don't believe anything they say about mysqltuning on the net.
Before you restart mysql you should stop the mail flow and stop mailscaner and crond (to prevent cronjob running )
There are no updates comming thru because they are blocked by yum.conf. That's with a very good reason.
Upgrade to the latest EFA version.(3.0.2.6) this will also update to the latest mysql version. (Ver 15.1 Distrib 10.1.30-MariaDB)
And always make snapshots before changes are done
First, before all, Check ou your vm environment (events/resources/vmware Tools).
On EFA VM
My basic check list:
1. check out messages in log files ( /var/log), including mysql log file: /var/lib/mysql/<YOUR FQDN>.err
2. check space available. df -h
3. check out space used by mysql: find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;
4. Use top to see what is going on. memory?
5. Update statistics. mysqlcheck --all-databases --analyze
6. install mysqltuner to get info on your Mysql db 'health'. (https://github.com/major/MySQLTuner-perl)
wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.pl --buffers
Be carefull changing mysql parameters if you have no clue what the are used for and don't believe anything they say about mysqltuning on the net.
Before you restart mysql you should stop the mail flow and stop mailscaner and crond (to prevent cronjob running )
There are no updates comming thru because they are blocked by yum.conf. That's with a very good reason.
Upgrade to the latest EFA version.(3.0.2.6) this will also update to the latest mysql version. (Ver 15.1 Distrib 10.1.30-MariaDB)
And always make snapshots before changes are done
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
Re: mysql overloading system cpu/IO
if mysqld is running hard, you need to find out why.
One command you can use is mysqladmin command as so:
run this a few times and see if there are any long running queries causing issues.
One command you can use is mysqladmin command as so:
Code: Select all
mysqladmin -uroot -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'` processlist
Re: mysql overloading system cpu/IO
Thanks guys, you gave me good pointers. I also setup a queue >3 alert, just in case.
Re: mysql overloading system cpu/IO
Update: anything related to mysql just kills my cpu and I/O wait. For example clicking around in mailwatch will timeout. Rebooting the VM does not help. mysqld is the culprit (can be seen in top). The VM was working fine for the past couple of months. I did not change anything on the VM, nor on the host ESXi. Before 3.0.2.5 I had an older version of EFA which worked fine for 3 years on the same ESXi.
Can it be that mysql is getting slower over time (as its db's are getting bigger)? What can it be?
Is there a way to reduce mysql role/activity to reduce impact?
Below is output while I clicked on mailwatch:
mysqladmin -uroot -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'` processlist
+----+-----------+-----------+-------------+---------+------+--------------+-----------------------------------------------------------------------------
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-----------+-----------+-------------+---------+------+--------------+-------------------------------------------------------------------------------
| 6 | mailwatch | localhost | mailscanner | Sleep | 530 | | | 0.000 |
| 33 | mailwatch | localhost | mailscanner | Query | 276 | Sending data | SELECT id AS id2, DATE_FORMAT(timestamp, '%d/%m/%y %H:%i:%s') AS datetime, from_address, to_ | 0.000 |
| 36 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
Top output: mysql is highest by far
top - 08:35:55 up 13 min, 2 users, load average: 1.31, 2.02, 1.27
Tasks: 161 total, 1 running, 160 sleeping, 0 stopped, 0 zombie
Cpu(s): 28.4%us, 4.4%sy, 0.0%ni, 22.0%id, 45.2%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 885080k total, 815408k used, 69672k free, 1416k buffers
Swap: 4194300k total, 829180k used, 3365120k free, 44776k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1650 mysql 20 0 809m 164m 3604 S 59.6 19.1 2:26.58 mysqld
Can it be that mysql is getting slower over time (as its db's are getting bigger)? What can it be?
Is there a way to reduce mysql role/activity to reduce impact?
Below is output while I clicked on mailwatch:
mysqladmin -uroot -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'` processlist
+----+-----------+-----------+-------------+---------+------+--------------+-----------------------------------------------------------------------------
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-----------+-----------+-------------+---------+------+--------------+-------------------------------------------------------------------------------
| 6 | mailwatch | localhost | mailscanner | Sleep | 530 | | | 0.000 |
| 33 | mailwatch | localhost | mailscanner | Query | 276 | Sending data | SELECT id AS id2, DATE_FORMAT(timestamp, '%d/%m/%y %H:%i:%s') AS datetime, from_address, to_ | 0.000 |
| 36 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
Top output: mysql is highest by far
top - 08:35:55 up 13 min, 2 users, load average: 1.31, 2.02, 1.27
Tasks: 161 total, 1 running, 160 sleeping, 0 stopped, 0 zombie
Cpu(s): 28.4%us, 4.4%sy, 0.0%ni, 22.0%id, 45.2%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 885080k total, 815408k used, 69672k free, 1416k buffers
Swap: 4194300k total, 829180k used, 3365120k free, 44776k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1650 mysql 20 0 809m 164m 3604 S 59.6 19.1 2:26.58 mysqld
Re: mysql overloading system cpu/IO
So yes, mysql is mostly just waiting around for the disk for some reason, which under normal circumstances it shouldn't be.
The only time I can slow mysql down long enough to see a query is to go to "Search and Reports" and make some kind of complex filter. Otherwise my message lists appear almost immediately.
When you say "clicked on mailwatch" can you be a bit more specific? I really need to know exactly what you are doing so I can figure out what mysql is actually trying to do.
MySQL is in 45% wait state. When that happens, it's usually related to one of the following:
- problems with the hard drive
- contention for the disk controller
- misconfigured disk controller
- improper indexes, or missing indexes
- really horrible and complex queries
- something else?
Without more information, I cannot diagnose the problem. You can start by finding the answers to these questions:
- has it always been this slow? or did it change recently?
- is your efa in a vm, or running on bare hardware?
- if it is in a vm, does the vm host have performance issues?
- is the drive controller cache configured properly?
- are any of the hard drives failing?
- are your indexes in place?
- does anything show up in your slow query log? if so, what is it?
- how is your mysql configured? is it configured optimally?
- how many records are in in the mailscanner maillog table?
A couple of years ago, I was having some serious performance problems in my efa vm. It turns out that my problem was a misconfigured raid controller. During a firmware update, the read cache got turned off and performance just died. It took almost a year before I got smart enough to check everything in detail (comparing to otherwise identical servers) before I found the one tiny thing that was causing me grief.
Let us know what you find out.
The only time I can slow mysql down long enough to see a query is to go to "Search and Reports" and make some kind of complex filter. Otherwise my message lists appear almost immediately.
When you say "clicked on mailwatch" can you be a bit more specific? I really need to know exactly what you are doing so I can figure out what mysql is actually trying to do.
MySQL is in 45% wait state. When that happens, it's usually related to one of the following:
- problems with the hard drive
- contention for the disk controller
- misconfigured disk controller
- improper indexes, or missing indexes
- really horrible and complex queries
- something else?
Without more information, I cannot diagnose the problem. You can start by finding the answers to these questions:
- has it always been this slow? or did it change recently?
- is your efa in a vm, or running on bare hardware?
- if it is in a vm, does the vm host have performance issues?
- is the drive controller cache configured properly?
- are any of the hard drives failing?
- are your indexes in place?
- does anything show up in your slow query log? if so, what is it?
- how is your mysql configured? is it configured optimally?
- how many records are in in the mailscanner maillog table?
A couple of years ago, I was having some serious performance problems in my efa vm. It turns out that my problem was a misconfigured raid controller. During a firmware update, the read cache got turned off and performance just died. It took almost a year before I got smart enough to check everything in detail (comparing to otherwise identical servers) before I found the one tiny thing that was causing me grief.
Let us know what you find out.
Re: mysql overloading system cpu/IO
As mentioned, it's a VM under Esxi that has been running fine for two months. It's a relatively new EFA, about 2 months old with 3.0.2.5. The previous EFA (used to be called something else, can't recall) was also a VM that ran under the same hardware/Esxi for 3 years without an issue (also with mysql). So, I don't think it's something with the the underlying hardware/Esxi which also runs MS Exchange without an issue for the last 7 years.
What I did was click on a specific mail item from the recent message screen (which came up a few seconds later) and then what caused it to grind was, in the "Received Via:" row, clicking on the "Spam" column to mark the IP as spam. That's when mysql goes nuts. I can recreate it and can't finish the task.
What I did was click on a specific mail item from the recent message screen (which came up a few seconds later) and then what caused it to grind was, in the "Received Via:" row, clicking on the "Spam" column to mark the IP as spam. That's when mysql goes nuts. I can recreate it and can't finish the task.
Re: mysql overloading system cpu/IO
So, clicking on a specific mail causes the issue, but clicking on other mail doesn't cause the issue?
Re: mysql overloading system cpu/IO
Just to be sure (and to exclude) it has absolutely nothing to do with the hardware, is it possible to vmotion the server to another host with other disks (or maybe to the local disks of another host)
Re: mysql overloading system cpu/IO
As I mentioned:
1. I have an Exchange VM server running on that ESX and it's working just fine.
2. The EFA has been working fine for the first two months.
It is not the hardware nor the underline ESXi.
1. I have an Exchange VM server running on that ESX and it's working just fine.
2. The EFA has been working fine for the first two months.
It is not the hardware nor the underline ESXi.
Re: mysql overloading system cpu/IO
I'm still trying to classify this one as it tuned out there was no class available for remarks like this oneThe previous EFA (used to be called something else, can't recall) was also a VM that ran under the same hardware/Esxi for 3 years without an issue (also with mysql).
As you seem to be sure its mysql, you should provide some info as most of us lack devine powers, but just want to help.
Did you check all log files mentioned before?
Do you have modsecurity enabled? if so, check logs
Code: Select all
ls -l /var/log/httpd/mod*
Code: Select all
mysqltuner.pl --buffers
Code: Select all
/etc/my.cnf.d/server.cnf
Code: Select all
myisamchk --check /var/lib/mysql/*/*.MYI
Code: Select all
find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;
The next commands run without errors? (Mailscanner uses the compiled rules)
Code: Select all
freshclam -v
sa-update -v
sa-compile
spamassassin --lint
MailScanner --lint
DNS
Can you resolve any (reverse) hostname on this machine? ( use dig or host)
Check statistics
Code: Select all
unbound-control stats_noreset |grep total
Houskeeping working? -amount of files present-
Code: Select all
/var/dcc/log
/var/spool/MailScanner/incoming/SpamAssassin-Temp
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
Re: mysql overloading system cpu/IO
Have you moved the server to be sure?
I have seen more problems solved by just moving a server to another host, while other servers on that same host did not have any issue, and there was also nothing found in the log or monitoring.
Just give it a try.......
Btw. also check your snapshots, maybe there is an old one you did not noticed.
Re: mysql overloading system cpu/IO
henk wrote: ↑04 Feb 2018 16:16I'm still trying to classify this one as it tuned out there was no class available for remarks like this oneThe previous EFA (used to be called something else, can't recall) was also a VM that ran under the same hardware/Esxi for 3 years without an issue (also with mysql).
As you seem to be sure its mysql, you should provide some info as most of us lack devine powers, but just want to help.
Did you check all log files mentioned before?
Do you have modsecurity enabled? if so, check logsCode: Select all
ls -l /var/log/httpd/mod*
Can you post theand yourCode: Select all
mysqltuner.pl --buffers
and check myisamCode: Select all
/etc/my.cnf.d/server.cnf
total size db files.Code: Select all
myisamchk --check /var/lib/mysql/*/*.MYI
Code: Select all
find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;
The next commands run without errors? (Mailscanner uses the compiled rules)Some additional checks that impact mail processingCode: Select all
freshclam -v sa-update -v sa-compile spamassassin --lint MailScanner --lint
DNS
Can you resolve any (reverse) hostname on this machine? ( use dig or host)
Check statisticsYou could disable munin to free some resources, if you don't use it. (just monitor munin to see the load on your system when it kicks in)Code: Select all
unbound-control stats_noreset |grep total
Houskeeping working? -amount of files present-Code: Select all
/var/dcc/log /var/spool/MailScanner/incoming/SpamAssassin-Temp
Here is the info:
No modsecurity
mysqltuner --buffers - was not able to login - do you know the default user/password?
Code: Select all
/etc/my.cnf.d/server.cnf - nothing special or tuneable listed
Code: Select all
myisamchk: error: File '/var/lib/mysql/*/*.MY' doesn't exist
Code: Select all
[root@mail2 ~]# find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;
3.5G /var/lib/mysql/
4.0K /var/lib/mysql/temp
8.0K /var/lib/mysql/performance_schema
3.3G /var/lib/mysql/mailscanner
16M /var/lib/mysql/sa_bayes
872K /var/lib/mysql/sqlgrey
20K /var/lib/mysql/efa
1.2M /var/lib/mysql/mysql
Code: Select all
[root@mail2 ~]# freshclam -v
Current working dir is /var/lib/clamav
Max retries == 3
ClamAV update process started at Mon Feb 5 09:50:13 2018
Using IPv6 aware code
Querying current.cvd.clamav.net
TTL: 1800
Software version from DNS: 0.99.3
WARNING: Your ClamAV installation is OUTDATED!
WARNING: Local version: 0.99.2 Recommended version: 0.99.3
DON'T PANIC! Read http://www.clamav.net/documents/upgrading-clamav
main.cvd version from DNS: 58
main.cld is up to date (version: 58, sigs: 4566249, f-level: 60, builder: sigmgr)
daily.cvd version from DNS: 24286
daily.cld is up to date (version: 24286, sigs: 1844820, f-level: 63, builder: neo)
bytecode.cvd version from DNS: 319
bytecode.cld is up to date (version: 319, sigs: 75, f-level: 63, builder: neo)
Code: Select all
root@mail2 ~]# spamassassin --lint
[root@mail2 ~]# MailScanner --lint
Trying to setlogsock(unix)
Reading configuration file /etc/MailScanner/MailScanner.conf
Reading configuration file /etc/MailScanner/conf.d/README
Read 1500 hostnames from the phishing whitelist
Read 12891 hostnames from the phishing blacklists
Config: calling custom init function SQLBlacklist
MailWatch: Starting up MailWatch SQL Blacklist
MailWatch: Read 1 blacklist entries
Config: calling custom init function MailWatchLogging
MailWatch: Started MailWatch SQL Logging child
Config: calling custom init function SQLWhitelist
MailWatch: Starting up MailWatch SQL Whitelist
MailWatch: Read 1 whitelist entries
Checking version numbers...
Version number in MailScanner.conf (5.0.6) is correct.
Your envelope_sender_header in spamassassin.conf is correct.
MailScanner setting GID to (89)
MailScanner setting UID to (89)
Checking for SpamAssassin errors (if you use it)...
Using SpamAssassin results cache
Connected to SpamAssassin cache database
SpamAssassin reported no errors.
Connected to Processing Attempts Database
Created Processing Attempts Database successfully
There are 7 messages in the Processing Attempts Database
Using locktype = posix
MailScanner.conf says "Virus Scanners = clamd"
Found these virus scanners installed: clamavmodule, clamd
===========================================================================
Looked up unknown string nonpasswordedarchive in language translation file /usr/share/MailScanner/reports/en/languages.conf at /usr/share/MailScanner/perl/MailScanner/Config.pm line 1364
Filename Checks: Windows/DOS Executable (1 eicar.com)
Other Checks: Found 1 problems
Virus and Content Scanning: Starting
Clamd::INFECTED:: Eicar-Test-Signature :: ./1/eicar.com
Virus Scanning: Clamd found 1 infections
Infected message 1 came from 10.1.1.1
Virus Scanning: Found 1 viruses
===========================================================================
Virus Scanner test reports:
Clamd said "eicar.com was infected: Eicar-Test-Signature"
If any of your virus scanners (clamavmodule,clamd)
are not listed there, you should check that they are installed correctly
and that MailScanner is finding them correctly via its virus.scanners.conf.
Config: calling custom end function SQLBlacklist
MailWatch: Closing down MailWatch SQL Blacklist
Config: calling custom end function MailWatchLogging
Config: calling custom end function SQLWhitelist
MailWatch: Closing down MailWatch SQL Whitelist
Code: Select all
[root@mail2 ~]# unbound-control stats_noreset |grep total
total.num.queries=35294
total.num.cachehits=16293
total.num.cachemiss=19001
total.num.prefetch=684
total.num.recursivereplies=19001
total.requestlist.avg=9.35164
total.requestlist.max=79
total.requestlist.overwritten=0
total.requestlist.exceeded=0
total.requestlist.current.all=0
total.requestlist.current.user=0
total.recursion.time.avg=0.124652
total.recursion.time.median=0.079683
Code: Select all
[root@mail2 ~]# ls -l /var/dcc/log |wc -l
1607
Code: Select all
[root@mail2 ~]# ls -l /var/spool/MailScanner/incoming/SpamAssassin-Temp | wc -l
2
P.S. Is it recommended to upgrade to 3.0.2.6? Are there any known issues with it?
Re: mysql overloading system cpu/IO
to be able to use mysql without typing passwords:
MYSQLROOTPWD:>>>>this is your mysqlpwd<<<
create .my.cnf file as user root ( notice the dot in the file name)
Now run
mysqltuner will use the same ini file.
Code: Select all
cat /etc/EFA-Config |grep MYSQLROOTPWD
create .my.cnf file as user root ( notice the dot in the file name)
Code: Select all
vi ~/.my.cnf
[client]
user=root
password=< your mysqlpwd>
Code: Select all
myisamchk --check /var/lib/mysql/*/*.MYI
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
Re: mysql overloading system cpu/IO
Here is it:
Based on the above I'm increasing the VM memory and retry to recreate the issue.
Code: Select all
Checking MyISAM file: /var/lib/mysql/efa/tokens.MYI
Data records: 0 Deleted blocks: 2
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
---------
Checking MyISAM file: /var/lib/mysql/mysql/columns_priv.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
---------
Checking MyISAM file: /var/lib/mysql/mysql/column_stats.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links
---------
Checking MyISAM file: /var/lib/mysql/mysql/db.MYI
Data records: 6 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
---------
Checking MyISAM file: /var/lib/mysql/mysql/event.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links
---------
Checking MyISAM file: /var/lib/mysql/mysql/func.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
---------
Checking MyISAM file: /var/lib/mysql/mysql/help_category.MYI
Data records: 39 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check record links
---------
Checking MyISAM file: /var/lib/mysql/mysql/help_keyword.MYI
Data records: 464 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
---------
Checking MyISAM file: /var/lib/mysql/mysql/help_relation.MYI
Data records: 1028 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
---------
Checking MyISAM file: /var/lib/mysql/mysql/help_topic.MYI
Data records: 508 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check record links
---------
Checking MyISAM file: /var/lib/mysql/mysql/host.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
---------
Checking MyISAM file: /var/lib/mysql/mysql/index_stats.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links
---------
Checking MyISAM file: /var/lib/mysql/mysql/plugin.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links
---------
Checking MyISAM file: /var/lib/mysql/mysql/proc.MYI
Data records: 2 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links
---------
Checking MyISAM file: /var/lib/mysql/mysql/procs_priv.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
---------
Checking MyISAM file: /var/lib/mysql/mysql/proxies_priv.MYI
Data records: 2 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
---------
Checking MyISAM file: /var/lib/mysql/mysql/roles_mapping.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
---------
Checking MyISAM file: /var/lib/mysql/mysql/servers.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
---------
Checking MyISAM file: /var/lib/mysql/mysql/tables_priv.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
---------
Checking MyISAM file: /var/lib/mysql/mysql/table_stats.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links
---------
Checking MyISAM file: /var/lib/mysql/mysql/time_zone_leap_second.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
---------
Checking MyISAM file: /var/lib/mysql/mysql/time_zone.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
---------
Checking MyISAM file: /var/lib/mysql/mysql/time_zone_name.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
---------
Checking MyISAM file: /var/lib/mysql/mysql/time_zone_transition.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
---------
Checking MyISAM file: /var/lib/mysql/mysql/time_zone_transition_type.MYI
Data records: 0 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
---------
Checking MyISAM file: /var/lib/mysql/mysql/user.MYI
Data records: 5 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links
Code: Select all
[root@mail2 ~]# mysqltuner --buffers
>> MySQLTuner 1.6.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.28-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MRG_MyISAM +SEQUENCE
[--] Data in MyISAM tables: 1K (Tables: 1)
[--] Data in InnoDB tables: 3G (Tables: 29)
[!!] Total fragmented tables: 5
-------- Security Recommendations -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] There is not basic password file list !
-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 6h 52m 1s (151K q [0.533 qps], 4K conn, TX: 6B, RX: 28M)
[--] Reads / Writes: 17% / 83%
[--] Binary logging is disabled
[--] Total buffers: 425.0M global + 2.9M per thread (151 max threads)
[--] Global Buffers
[--] +-- Key Buffer: 128.0M
[--] +-- Max Tmp Table: 16.0M
[--] Query Cache Buffers
[--] +-- Query Cache: OFF - DISABLED
[--] +-- Query Cache Size: 1.0M
[--] Per Thread Buffers
[--] +-- Read Buffer: 128.0K
[--] +-- Read RND Buffer: 256.0K
[--] +-- Sort Buffer: 2.0M
[--] +-- Thread stack: 289.0K
[--] +-- Join Buffer: 256.0K
[OK] Maximum reached memory usage: 433.7M (50.18% of installed RAM)
[!!] Maximum possible memory usage: 864.0M (99.96% of installed RAM)
[OK] Slow queries: 0% (0/151K)
[OK] Highest usage of available connections: 1% (3/151)
[!!] Aborted connections: 36.36% (1582/4351)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10 sorts)
[!!] Temporary tables created on disk: 62% (376 on disk / 598 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 71% (113 open / 158 opened)
[OK] Open file limit used: 2% (87/4K)
[OK] Table locks acquired immediately: 100% (141K immediate / 141K locks)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.3% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/124.0K
[!!] Read Key buffer hit rate: 91.3% (207 cached / 18 reads)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Buffers
[--] +-- InnoDB Buffer Pool: 128.0M
[--] +-- InnoDB Buffer Pool Instances: 8
[--] +-- InnoDB Additional Mem Pool: 8.0M
[--] +-- InnoDB Log Buffer: 16.0M
[--] +-- InnoDB Log Buffer Free: 1.0K
[--] +-- InnoDB Log Buffer Used: 8.0K
[!!] InnoDB buffer pool / data size: 128.0M/3.2G
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[OK] InnoDB Used buffer: 87.50% (7167 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 98.96% (114091834 hits/ 115285257 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 124426 writes)
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate unclosed connections and network issues
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Set thread_cache_size to 4 as a starting value
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_type (=1)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
innodb_buffer_pool_size (>= 3G) if possible.
innodb_buffer_pool_instances (=1)
Re: mysql overloading system cpu/IO
Added 130MB RAM to VM. mailwatch still timing out when clicking on SPAM column of a message IP.
There are some warnings on unoptimized index/queries/databases in the report below. Is there some house cleaning I can run on mysql to reduce/minimize I/O impact??
There are some warnings on unoptimized index/queries/databases in the report below. Is there some house cleaning I can run on mysql to reduce/minimize I/O impact??
Code: Select all
mysqltuner --buffers
>> MySQLTuner 1.6.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.28-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MRG_MyISAM +SEQUENCE
[--] Data in MyISAM tables: 1K (Tables: 1)
[--] Data in InnoDB tables: 3G (Tables: 29)
[!!] Total fragmented tables: 5
-------- Security Recommendations -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] There is not basic password file list !
-------- Performance Metrics -------------------------------------------------
[--] Up for: 5m 50s (1K q [4.023 qps], 158 conn, TX: 566K, RX: 289K)
[--] Reads / Writes: 64% / 36%
[--] Binary logging is disabled
[--] Total buffers: 425.0M global + 2.9M per thread (151 max threads)
[--] Global Buffers
[--] +-- Key Buffer: 128.0M
[--] +-- Max Tmp Table: 16.0M
[--] Query Cache Buffers
[--] +-- Query Cache: OFF - DISABLED
[--] +-- Query Cache Size: 1.0M
[--] Per Thread Buffers
[--] +-- Read Buffer: 128.0K
[--] +-- Read RND Buffer: 256.0K
[--] +-- Sort Buffer: 2.0M
[--] +-- Thread stack: 289.0K
[--] +-- Join Buffer: 256.0K
[OK] Maximum reached memory usage: 433.7M (43.88% of installed RAM)
[!!] Maximum possible memory usage: 864.0M (87.42% of installed RAM)
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 1% (3/151)
[OK] Aborted connections: 1.90% (3/158)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 11 sorts)
[!!] Joins performed without indexes: 6
[OK] Temporary tables created on disk: 4% (4 on disk / 81 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 95% (114 open / 120 opened)
[OK] Open file limit used: 1% (61/4K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/124.0K
[OK] Read Key buffer hit rate: 95.0% (40 cached / 2 reads)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Buffers
[--] +-- InnoDB Buffer Pool: 128.0M
[--] +-- InnoDB Buffer Pool Instances: 8
[--] +-- InnoDB Additional Mem Pool: 8.0M
[--] +-- InnoDB Log Buffer: 16.0M
[--] +-- InnoDB Log Buffer Free: 1007B
[--] +-- InnoDB Log Buffer Used: 8.0K
[!!] InnoDB buffer pool / data size: 128.0M/3.2G
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[OK] InnoDB Used buffer: 87.71% (7184 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 96.82% (4808552 hits/ 4966487 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 406 writes)
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_type (=1)
join_buffer_size (> 256.0K, or always use indexes with joins)
thread_cache_size (start at 4)
innodb_buffer_pool_size (>= 3G) if possible.
innodb_buffer_pool_instances (=1)
Re: mysql overloading system cpu/IO
What is your current memory:885080k ????
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
Re: mysql overloading system cpu/IO
The VM memory size is now 1.1GB
Below is a "top" snapshot (quiet time, not when mysql queries are timing out):
top - 10:00:08 up 18:08, 1 user, load average: 0.00, 0.02, 0.04
Tasks: 191 total, 2 running, 189 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.1%us, 2.0%sy, 13.8%ni, 80.2%id, 0.8%wa, 0.1%hi, 0.1%si, 0.0%st
Mem: 1059156k total, 968232k used, 90924k free, 5288k buffers
Swap: 4194300k total, 765596k used, 3428704k free, 129540k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
41280 munin 30 10 216m 23m 5448 R 13.0 2.2 0:00.39 munin-graph
49 root 20 0 0 0 0 S 0.3 0.0 0:26.06 kblockd/3
39200 root 20 0 15028 1316 948 R 0.3 0.1 0:01.48 top
1 root 20 0 19348 532 348 S 0.0 0.1 0:01.07 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd
Below is a "top" snapshot (quiet time, not when mysql queries are timing out):
top - 10:00:08 up 18:08, 1 user, load average: 0.00, 0.02, 0.04
Tasks: 191 total, 2 running, 189 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.1%us, 2.0%sy, 13.8%ni, 80.2%id, 0.8%wa, 0.1%hi, 0.1%si, 0.0%st
Mem: 1059156k total, 968232k used, 90924k free, 5288k buffers
Swap: 4194300k total, 765596k used, 3428704k free, 129540k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
41280 munin 30 10 216m 23m 5448 R 13.0 2.2 0:00.39 munin-graph
49 root 20 0 0 0 0 S 0.3 0.0 0:26.06 kblockd/3
39200 root 20 0 15028 1316 948 R 0.3 0.1 0:01.48 top
1 root 20 0 19348 532 348 S 0.0 0.1 0:01.07 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd
Re: mysql overloading system cpu/IO
Yes, your memory report looks wrong.
Type this command at the efa command prompt: free
Type this command at the efa command prompt: free
Code: Select all
[root@efa mysql]# free
total used free shared buffers cached
Mem: 8061012 6046144 2014868 884 270716 3441012
-/+ buffers/cache: 2334416 5726596
Swap: 0 0 0
Re: mysql overloading system cpu/IO
Here you go:
Code: Select all
free
total used free shared buffers cached
Mem: 1059156 956620 102536 72 6920 121796
-/+ buffers/cache: 827904 231252
Swap: 4194300 762840 3431460
Re: mysql overloading system cpu/IO
Set your memory to 4096 MB and see what happens
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
Re: mysql overloading system cpu/IO
No bloody wonder it's slow!
Set it to 8GB, or leave it at 4GB and turn off all the extra services.
Set it to 8GB, or leave it at 4GB and turn off all the extra services.
Re: mysql overloading system cpu/IO
The most I have available is 2GB. I'll try it and post update.
One note I'd like to keep in mind: The previous EFA VM was working fine with just 800mb ram, including mysql. Same hardware/Esx. Can't imagine that this VM needs 300%/400% more RAM. There is something else going on with mysql. Any thoughts from the tuning recommendations above?
One note I'd like to keep in mind: The previous EFA VM was working fine with just 800mb ram, including mysql. Same hardware/Esx. Can't imagine that this VM needs 300%/400% more RAM. There is something else going on with mysql. Any thoughts from the tuning recommendations above?
Re: mysql overloading system cpu/IO
According to the system requirements for v3: https://wiki.efa-project.org/doku.php?id=setup_guide you need 8GB of ram to run EFA comfortably.
You seriously need to get more ram.
As you are restricted to 2GB, then you will have to turn your system heavily for your limited environment - that means you'll have to configure mysql with the tiny memory configuration. You'll have to disable the system monitor munin, and disable webadmin. You'll need to increase your swap space, and you may even have to disable your ClamAV. I believe someone made a post about that in the forums at some point in time, so use google to search for it.
Your system is still likely to swap under load and swapping is a performance killer. That cannot be avoided, so you will likely always have performance issues. Although 8GB is the minimum recommended, you probably get away with 4GB and not swap, assuming your system is not a busy one.
Use the command "vmstat 3" to keep track of how your system is performing. It's provided with the sysstat package. (sudo yum install sysstat) and it's one of the monitoring tools you should keep handy.
Good luck.
You seriously need to get more ram.
As you are restricted to 2GB, then you will have to turn your system heavily for your limited environment - that means you'll have to configure mysql with the tiny memory configuration. You'll have to disable the system monitor munin, and disable webadmin. You'll need to increase your swap space, and you may even have to disable your ClamAV. I believe someone made a post about that in the forums at some point in time, so use google to search for it.
Your system is still likely to swap under load and swapping is a performance killer. That cannot be avoided, so you will likely always have performance issues. Although 8GB is the minimum recommended, you probably get away with 4GB and not swap, assuming your system is not a busy one.
Use the command "vmstat 3" to keep track of how your system is performing. It's provided with the sysstat package. (sudo yum install sysstat) and it's one of the monitoring tools you should keep handy.
Good luck.
Re: mysql overloading system cpu/IO
Something here doesn't add up.
Other than the mailwatch web GUI functionality the VM is doing just fine with less than 0.9GB. Having to add 3 times that amount just for the mailwatch web gui seems unreasonable. I would argue that the system could benefit from mailwatch/mysql optimization..
Other than the mailwatch web GUI functionality the VM is doing just fine with less than 0.9GB. Having to add 3 times that amount just for the mailwatch web gui seems unreasonable. I would argue that the system could benefit from mailwatch/mysql optimization..