mysql overloading system cpu/IO

Report bugs and workarounds
jheffez
Posts: 18
Joined: 01 Jan 2018 14:55

mysql overloading system cpu/IO

Post by jheffez » 30 Jan 2018 07:34

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).

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

Re: mysql overloading system cpu/IO

Post by henk » 30 Jan 2018 13:59

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 :idea:

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

Re: mysql overloading system cpu/IO

Post by pdwalker » 31 Jan 2018 10:17

if mysqld is running hard, you need to find out why.

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
run this a few times and see if there are any long running queries causing issues.

jheffez
Posts: 18
Joined: 01 Jan 2018 14:55

Re: mysql overloading system cpu/IO

Post by jheffez » 31 Jan 2018 12:55

Thanks guys, you gave me good pointers. I also setup a queue >3 alert, just in case.

jheffez
Posts: 18
Joined: 01 Jan 2018 14:55

Re: mysql overloading system cpu/IO

Post by jheffez » 02 Feb 2018 06:37

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

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

Re: mysql overloading system cpu/IO

Post by pdwalker » 03 Feb 2018 07:32

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.

jheffez
Posts: 18
Joined: 01 Jan 2018 14:55

Re: mysql overloading system cpu/IO

Post by jheffez » 03 Feb 2018 08:26

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.

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

Re: mysql overloading system cpu/IO

Post by pdwalker » 03 Feb 2018 18:15

So, clicking on a specific mail causes the issue, but clicking on other mail doesn't cause the issue?

Zwabber
Posts: 55
Joined: 14 Feb 2016 21:26

Re: mysql overloading system cpu/IO

Post by Zwabber » 03 Feb 2018 19:14

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)

jheffez
Posts: 18
Joined: 01 Jan 2018 14:55

Re: mysql overloading system cpu/IO

Post by jheffez » 04 Feb 2018 06:58

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.

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

Re: mysql overloading system cpu/IO

Post by henk » 04 Feb 2018 16:16

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).
I'm still trying to classify this one as it tuned out there was no class available for remarks like this one :o

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*
Can you post the

Code: Select all

mysqltuner.pl --buffers
and your

Code: Select all

/etc/my.cnf.d/server.cnf
and check myisam

Code: Select all

myisamchk --check /var/lib/mysql/*/*.MYI
total size db files.

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
Some additional checks that impact mail processing
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
You 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)

Houskeeping working? -amount of files present-

Code: Select all

/var/dcc/log
/var/spool/MailScanner/incoming/SpamAssassin-Temp

Zwabber
Posts: 55
Joined: 14 Feb 2016 21:26

Re: mysql overloading system cpu/IO

Post by Zwabber » 04 Feb 2018 21:08

jheffez wrote:
04 Feb 2018 06:58
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.
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.

jheffez
Posts: 18
Joined: 01 Jan 2018 14:55

Re: mysql overloading system cpu/IO

Post by jheffez » 05 Feb 2018 08:14

henk wrote:
04 Feb 2018 16:16
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).
I'm still trying to classify this one as it tuned out there was no class available for remarks like this one :o

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*

Can you post the

Code: Select all

mysqltuner.pl --buffers
and your

Code: Select all

/etc/my.cnf.d/server.cnf
and check myisam

Code: Select all

myisamchk --check /var/lib/mysql/*/*.MYI
total size db files.

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
Some additional checks that impact mail processing
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
You 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)

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
No DNS issue.

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
Thanks for you looking into it!

P.S. Is it recommended to upgrade to 3.0.2.6? Are there any known issues with it?

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

Re: mysql overloading system cpu/IO

Post by henk » 05 Feb 2018 13:20

to be able to use mysql without typing passwords:

Code: Select all

cat /etc/EFA-Config |grep MYSQLROOTPWD
MYSQLROOTPWD:>>>>this is your mysqlpwd<<<

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>
Now run

Code: Select all

myisamchk --check /var/lib/mysql/*/*.MYI
mysqltuner will use the same ini file.

jheffez
Posts: 18
Joined: 01 Jan 2018 14:55

Re: mysql overloading system cpu/IO

Post by jheffez » 05 Feb 2018 13:33

Here is it:

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)
    
Based on the above I'm increasing the VM memory and retry to recreate the issue.

jheffez
Posts: 18
Joined: 01 Jan 2018 14:55

Re: mysql overloading system cpu/IO

Post by jheffez » 05 Feb 2018 13:46

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??

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)

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

Re: mysql overloading system cpu/IO

Post by henk » 05 Feb 2018 13:59

What is your current memory:885080k ????
spamProperties.png
spamProperties.png (17.86 KiB) Viewed 4752 times

jheffez
Posts: 18
Joined: 01 Jan 2018 14:55

Re: mysql overloading system cpu/IO

Post by jheffez » 06 Feb 2018 08:00

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

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

Re: mysql overloading system cpu/IO

Post by pdwalker » 06 Feb 2018 08:10

Yes, your memory report looks wrong.

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

jheffez
Posts: 18
Joined: 01 Jan 2018 14:55

Re: mysql overloading system cpu/IO

Post by jheffez » 06 Feb 2018 08:16

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

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

Re: mysql overloading system cpu/IO

Post by henk » 06 Feb 2018 08:24

Set your memory to 4096 MB and see what happens :roll:

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

Re: mysql overloading system cpu/IO

Post by pdwalker » 06 Feb 2018 11:07

No bloody wonder it's slow!

Set it to 8GB, or leave it at 4GB and turn off all the extra services.

jheffez
Posts: 18
Joined: 01 Jan 2018 14:55

Re: mysql overloading system cpu/IO

Post by jheffez » 06 Feb 2018 11:29

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?

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

Re: mysql overloading system cpu/IO

Post by pdwalker » 06 Feb 2018 12:11

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.

jheffez
Posts: 18
Joined: 01 Jan 2018 14:55

Re: mysql overloading system cpu/IO

Post by jheffez » 06 Feb 2018 14:05

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..

Post Reply