EFA - Mailwatch is painfully slow opening reports
-
- Posts: 8
- Joined: 02 Feb 2018 09:04
EFA - Mailwatch is painfully slow opening reports
Versions: MailWatch for MailScanner v1.2.7-dev running on EFA-3.0.2.5
VMware hosts runnign 6.0.
Before Christmas this problem definitely didn't exist, and even until min January I am fairly sure it wasn't an issue, and we were returning lists of 7000+ emails
However, recently then using the Mailwatch inteface, then using the Search and Reports feature to look at an emails or find a blocked one to release, it is painfully slow. It can take upto a minute to even complete one variable. Then when clicking on the mail list, can be upto say 5000 emails, it takes minutes to open, and initally only returns half the list anyway, you have to wait for the rest of the emails to appear. If you want to then go to page 2 of the returned list you might as well go and get a brew!
Anyway I digress.
We also had some further issues recently with the clamav stopping running, having searched through the forums this was likely caused by a lack of RAM, so I increased the RAM from 4Gb to 8Gb, while doing this I also opted to go from 2 cores to 4 cores, since the box (well VM appliance) was on original setting from when it was installed.
The VAR folder was also small and almost full, showing on 10% space which meant we couldn't update but that has also been resolved now, there is 20GB free instead of 2GB!
All of te above has made no difference and finding mail to release is enfuriating and that is ignoring the issues of someone having a quick spot check for false positives (not everyone gets quarantine report).
Running top on the EFA box returns generally mysql at the top, for CPU usage as oppose to Mailscanner but still aroudn 70% idle.
In the time it has taken me to write this post, clicking mail listing on a filter I did still hasn't loaded.
VMware hosts runnign 6.0.
Before Christmas this problem definitely didn't exist, and even until min January I am fairly sure it wasn't an issue, and we were returning lists of 7000+ emails
However, recently then using the Mailwatch inteface, then using the Search and Reports feature to look at an emails or find a blocked one to release, it is painfully slow. It can take upto a minute to even complete one variable. Then when clicking on the mail list, can be upto say 5000 emails, it takes minutes to open, and initally only returns half the list anyway, you have to wait for the rest of the emails to appear. If you want to then go to page 2 of the returned list you might as well go and get a brew!
Anyway I digress.
We also had some further issues recently with the clamav stopping running, having searched through the forums this was likely caused by a lack of RAM, so I increased the RAM from 4Gb to 8Gb, while doing this I also opted to go from 2 cores to 4 cores, since the box (well VM appliance) was on original setting from when it was installed.
The VAR folder was also small and almost full, showing on 10% space which meant we couldn't update but that has also been resolved now, there is 20GB free instead of 2GB!
All of te above has made no difference and finding mail to release is enfuriating and that is ignoring the issues of someone having a quick spot check for false positives (not everyone gets quarantine report).
Running top on the EFA box returns generally mysql at the top, for CPU usage as oppose to Mailscanner but still aroudn 70% idle.
In the time it has taken me to write this post, clicking mail listing on a filter I did still hasn't loaded.
Re: EFA - Mailwatch is painfully slow opening reports
The questions in this post: viewtopic.php?p=11633#p11633 are relevant. We can't help without actual information.
In your case, since it used to work find and now doesn't, you have to ask yourself what has changed between those two events?
Once you know that, you'll have the solution to your problem in reach.
In your case, since it used to work find and now doesn't, you have to ask yourself what has changed between those two events?
Once you know that, you'll have the solution to your problem in reach.
-
- Posts: 8
- Joined: 02 Feb 2018 09:04
Re: EFA - Mailwatch is painfully slow opening reports
Apologies, for some reason I didn't recieve any notification to say someone had replied.
I've looked at the mod_security logs and there is nothing obvious.
myisamchk --check /var/lib/mysql/*/*.MYI
freshclam -v
sa-update -v
sa-compile
spamassassin --lint
MailScanner --lint
All the lint tests etc go through fine
unbound-control stats_noreset |grep total
ls -l /var/dcc/log |wc -l
ls -l /var/spool/MailScanner/incoming/SpamAssassin-Temp | wc -l
There is nothing special/ unusual in server.cnf
I understand entirely what you are saying but there has been no config change by us at all, I have noticed that it did seem to attempt an auto-update that has failed due to var space as mentioned in my previous post. I have this morning though come in and updated to 3.0.2.6 and the same issue is occuring.
I've looked at the mod_security logs and there is nothing obvious.
myisamchk --check /var/lib/mysql/*/*.MYI
Code: Select all
Checking MyISAM file: /var/lib/mysql/efa/tokens.MYI
Data records: 0 Deleted blocks: 0
- 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: 40 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: 453 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: 1009 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: 510 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/ndb_binlog_index.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: 0 Deleted blocks: 1
- 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: 1 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: 1
- 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/sqlgrey/config.MYI
Data records: 1 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/sqlgrey/connect.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
- check record links
---------
Checking MyISAM file: /var/lib/mysql/sqlgrey/domain_awl.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
- check record links
---------
Checking MyISAM file: /var/lib/mysql/sqlgrey/from_awl.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
- check record links
---------
Checking MyISAM file: /var/lib/mysql/sqlgrey/optin_domain.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/sqlgrey/optin_email.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/sqlgrey/optout_domain.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/sqlgrey/optout_email.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[/quote]
find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;
[quote]8.4G /var/lib/mysql/
1.3M /var/lib/mysql/mysql
24K /var/lib/mysql/efa
92K /var/lib/mysql/sa_bayes
8.0K /var/lib/mysql/performance_schema
4.0K /var/lib/mysql/temp
8.1G /var/lib/mysql/mailscanner
140K /var/lib/mysql/sqlgrey
freshclam -v
sa-update -v
sa-compile
spamassassin --lint
MailScanner --lint
All the lint tests etc go through fine
unbound-control stats_noreset |grep total
Code: Select all
total.num.queries=0
total.num.cachehits=0
total.num.cachemiss=0
total.num.prefetch=0
total.num.recursivereplies=0
total.requestlist.avg=0
total.requestlist.max=0
total.requestlist.overwritten=0
total.requestlist.exceeded=0
total.requestlist.current.all=0
total.requestlist.current.user=0
total.recursion.time.avg=0.000000
total.recursion.time.median=0
mem.total.sbrk=6795264
Code: Select all
11715
Code: Select all
3
There is nothing special/ unusual in server.cnf
I understand entirely what you are saying but there has been no config change by us at all, I have noticed that it did seem to attempt an auto-update that has failed due to var space as mentioned in my previous post. I have this morning though come in and updated to 3.0.2.6 and the same issue is occuring.
-
- Posts: 8
- Joined: 02 Feb 2018 09:04
Re: EFA - Mailwatch is painfully slow opening reports
Further to my above post, it is like a process is being locked out and can't complete.
So say I am looking at a report of spam after 5 minutes waiting for it to open, I could open 1 message or open 5 in different tabs and they will all open at the same time, then same again with the release. The wait is the same no matter what.
So say I am looking at a report of spam after 5 minutes waiting for it to open, I could open 1 message or open 5 in different tabs and they will all open at the same time, then same again with the release. The wait is the same no matter what.
Re: EFA - Mailwatch is painfully slow opening reports
As you can see in unbound-control stats_noreset |grep total
DNS is not caching anything, (or did you just reboot your system?).
Dns is essential, that is a working DNS
Use dig to test DNS queries. If you don't get a response then the local DNS server could:
not have proper connectivity outbound to the Internet - a firewall could be blocking UDP/TCP 53
have reached the free usage limit of the DNSBlockList - remove the "+short" to see more detail in the ANSWER section
may not be configured correctly - search for articles on how to setup your specific DNS caching server on your specific OS
Spamhaus Zen:
127.0.0.10
127.0.0.4
127.0.0.2
SORBS DUL:
127.0.0.10
URIBL:
"permanent testpoint"
You need to fix this before looking at other things
Also take a look at this post about housekeeping on DCCviewtopic.php?f=13&t=2610
DNS is not caching anything, (or did you just reboot your system?).
Dns is essential, that is a working DNS
Use dig to test DNS queries. If you don't get a response then the local DNS server could:
not have proper connectivity outbound to the Internet - a firewall could be blocking UDP/TCP 53
have reached the free usage limit of the DNSBlockList - remove the "+short" to see more detail in the ANSWER section
may not be configured correctly - search for articles on how to setup your specific DNS caching server on your specific OS
Spamhaus Zen:
Code: Select all
dig +short 2.0.0.127.zen.spamhaus.org
127.0.0.4
127.0.0.2
SORBS DUL:
Code: Select all
dig 2.0.0.127.dul.dnsbl.sorbs.net +short
URIBL:
Code: Select all
dig test.uribl.com.multi.uribl.com txt +short
You need to fix this before looking at other things
Also take a look at this post about housekeeping on DCCviewtopic.php?f=13&t=2610
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
-
- Posts: 8
- Joined: 02 Feb 2018 09:04
Re: EFA - Mailwatch is painfully slow opening reports
Ofcourse DNS is working, its been working for years this box. I've double checked anyway.
I restarted it this morning as part of the update, so probably been online 10-15 minutes when I ran the commands.
[root@wcdritef01 mailscanner]# dig +short 2.0.0.127.zen.spamhaus.org
[root@wcdritef01 mailscanner]# dig 2.0.0.127.dul.dnsbl.sorbs.net +short
[root@wcdritef01 mailscanner]# dig 2.0.0.127.dul.dnsbl.sorbs.net +short
This is an appliance originally.
I restarted it this morning as part of the update, so probably been online 10-15 minutes when I ran the commands.
[root@wcdritef01 mailscanner]# dig +short 2.0.0.127.zen.spamhaus.org
Code: Select all
127.0.0.10
127.0.0.4
127.0.0.2
Code: Select all
127.0.0.10
Code: Select all
127.0.0.10
Re: EFA - Mailwatch is painfully slow opening reports
Running the appliance originally does not say to much. It's the configuration that matters.
Also assuming you did have a look at viewtopic.php?p=11633#p11633
Ok. run the Unbound check again (assuming you received some mail today)
As you have quite some memory assignd to this appliance.
Can you post the output of the latest mysqltuner?
Also assuming you did have a look at viewtopic.php?p=11633#p11633
Ok. run the Unbound check again (assuming you received some mail today)
As you have quite some memory assignd to this appliance.
Can you post the output of the latest mysqltuner?
Code: Select all
wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.pl --buffers
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
-
- Posts: 8
- Joined: 02 Feb 2018 09:04
Re: EFA - Mailwatch is painfully slow opening reports
Still returns the same, still no queries. Recieved hundreds of emails already today.
unbound-control stats_noreset |grep total
mysql command
unbound-control stats_noreset |grep total
Code: Select all
total.num.queries=0
total.num.cachehits=0
total.num.cachemiss=0
total.num.prefetch=0
total.num.recursivereplies=0
total.requestlist.avg=0
total.requestlist.max=0
total.requestlist.overwritten=0
total.requestlist.exceeded=0
total.requestlist.current.all=0
total.requestlist.current.user=0
total.recursion.time.avg=0.000000
total.recursion.time.median=0
mem.total.sbrk=6795264
Code: Select all
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 10.1.30-MariaDB
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/SERVER.err(1M)
[OK] Log file /var/lib/mysql/SERVER.err exists
[OK] Log file /var/lib/mysql/SERVER.err is readable.
[OK] Log file /var/lib/mysql/SERVER.err is not empty
[OK] Log file /var/lib/mysql/SERVER.err is smaller than 32 Mb
[!!] /var/lib/mysql/SERVER.err contains 4 warning(s).
[!!] /var/lib/mysql/SERVER.err contains 1065 error(s).
[--] 11 start(s) detected in /var/lib/mysql/wcdritef01.wchg.org.uk.err
[--] 1) 2018-02-06 7:44:32 139938613844000 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-02-02 12:49:23 140705980479520 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-02-02 8:33:15 139655987599392 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-02-01 17:20:09 140373065410592 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-01-30 9:29:22 139664377067552 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-01-29 8:57:55 140586427803680 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2017-10-17 11:04:30 139731006892064 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2017-10-15 4:15:23 140638523471904 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2017-10-09 16:01:12 140369017952288 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2017-03-31 18:18:54 139685519063072 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10 shutdown(s) detected in /var/lib/mysql/SERVER.err
[--] 1) 2018-02-06 7:42:44 140705103825664 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-02-02 12:49:19 139655986072320 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-02-02 8:31:24 140373064489728 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-02-01 17:07:50 139664375704320 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-01-30 9:26:57 140586409192192 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-01-29 8:55:52 139730987535104 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2017-10-17 11:04:27 140638505163520 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2017-10-15 4:15:19 140369017940736 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2017-10-09 15:59:25 139685518445312 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 170331 18:16:52 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 19K (Tables: 9)
[--] Data in InnoDB tables: 8G (Tables: 21)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3h 40m 7s (317K q [24.008 qps], 2K conn, TX: 60M, RX: 65M)
[--] Reads / Writes: 46% / 54%
[--] Binary logging is disabled
[--] Physical Memory : 7.7G
[--] Max MySQL memory : 864.0M
[--] Other process memory: 1.9G
[--] Total buffers: 425.0M global + 2.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[--] 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: 442.4M (5.62% of installed RAM)
[OK] Maximum possible memory usage: 864.0M (10.98% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/317K)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Aborted connections: 0.41% (9/2219)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 144K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 24 sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 50% (29 on disk / 57 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 85% (36 open / 42 opened)
[OK] Open file limit used: 0% (25/4K)
[OK] Table locks acquired immediately: 100% (310K immediate / 310K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (10.1.30-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/135.0K
-------- 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 File Size: 48.0M
[--] +-- InnoDB Log File In Group: 2
[--] +-- InnoDB Total Log File Size: 96.0M(75 % of buffer pool)
[--] +-- InnoDB Log Buffer: 16.0M
[--] +-- InnoDB Log Buffer Free: 1.0K
[--] +-- InnoDB Log Buffer Used: 8.0K
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/8.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[!!] InnoDB Read buffer efficiency: 86.55% (47485654 hits/ 54864430 total)
[!!] InnoDB Write Log efficiency: 73.56% (448153 hits/ 609223 total)
[OK] InnoDB log waits: 0.00% (0 waits / 161070 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 0.0% (24 cached / 24 reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/lib/mysql/SERVER.err file
Control error line(s) into /var/lib/mysql/SERVER.err file
MySQL started within last 24 hours - recommendations may be inaccurate
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
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
Performance should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 8G) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances (=1)
-
- Posts: 8
- Joined: 02 Feb 2018 09:04
Re: EFA - Mailwatch is painfully slow opening reports
Just had a look at the errors in the log file, as referenced. They are from March 2017 that shows incorrect table structure.
Given that the scanner has been updated since then, the errors are likely legacy, they have never shown again.
Then last error in 2017 was October then nothing until:
Further errors referencing no space left on the disk (30/01/2018), this will be true when the var was full, which I corrected last week. Since then there is only reboot logs.
Given that the scanner has been updated since then, the errors are likely legacy, they have never shown again.
Then last error in 2017 was October then nothing until:
Further errors referencing no space left on the disk (30/01/2018), this will be true when the var was full, which I corrected last week. Since then there is only reboot logs.
-
- Posts: 8
- Joined: 02 Feb 2018 09:04
Re: EFA - Mailwatch is painfully slow opening reports
Right we hav been investigating ourselves more, and it appears that when ClamAV stopped, the mails appear to have been looping round within Mailscanner for some reason.
This has resulted in just under 1 million emails shpowing logged being within the system for two days. There isn't actually that many emails though as you cna imagine but the SQL database shows each message ID within it about 2700 times. So it's each to see where 450k emails per day has come from.
This is most likely what is causing the issue, we are currently working out the best way to remove all the extra rows, so keeping 1 row per email as oppose to 2700, which is the likely cause of the slowness, the emails were only delivered once per user, so the issue is purely within the EFA itself.
This has resulted in just under 1 million emails shpowing logged being within the system for two days. There isn't actually that many emails though as you cna imagine but the SQL database shows each message ID within it about 2700 times. So it's each to see where 450k emails per day has come from.
This is most likely what is causing the issue, we are currently working out the best way to remove all the extra rows, so keeping 1 row per email as oppose to 2700, which is the likely cause of the slowness, the emails were only delivered once per user, so the issue is purely within the EFA itself.
Re: EFA - Mailwatch is painfully slow opening reports
You never know what will happen on 0 bytes free.
1. DNS -Unbound-. This makes no sense to me. I would spend some ( read a lot) time in investigating this.
2. Mysql. As you didn't mention the exact errors messages itself, leaving me assuming things.
Also check the latest EFA version upgrade logfiles, if you did an upgrade lately.
As your mailscanner db is 8 GB, you need some space to be able to repair/optimize the db.
make snapshot or whatever backup
1 remove old kernels. This will give you some space. (If not installed -yum install yum-utils)
2. You have quite some files in /var/dcc/log . Check total size.
exec the cronjob for housekeeping your dcc/log files. (cron-dccd)
The important space used by current dbs
You could also change the days to keep in the mail in mailscanner and run a cleanup cronjob to remove records
3. remove some of the daily backups files from efa.
Check space available!
You could try to check and repair the db.
To be save you need the next entries in de server.cnf ( and restart Mysql)
# this sets the inline defrag mode ( no recreate table)
innodb-defragment = 1
#use table space per table
innodb_file_per_table = 1
# if innodb_buffer_pool_size <= 1GB
innodb_buffer_pool_instances = 1
# the main buffer
iinnodb_buffer_pool_size = 1G
The optimal (log_file_size * 2) should be 25 % iinnodb_buffer_pool_size
If you activate this stop mysql an move the logfiles to a different location and start mysql
#innodb_log_file_size = 125M
Stop the mail flow
stop crond
stop mailscanner
to be sure no connections active
restart mysql
analyze
perform inline optimize
on errors: perform inline optimize with auto repair
Good luck and please let me know if you could fix the issue
1. DNS -Unbound-. This makes no sense to me. I would spend some ( read a lot) time in investigating this.
2. Mysql. As you didn't mention the exact errors messages itself, leaving me assuming things.
Also check the latest EFA version upgrade logfiles, if you did an upgrade lately.
As your mailscanner db is 8 GB, you need some space to be able to repair/optimize the db.
make snapshot or whatever backup
1 remove old kernels. This will give you some space. (If not installed -yum install yum-utils)
Code: Select all
package-cleanup --oldkernels --count=1
Code: Select all
find /var/dcc/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;
The important space used by current dbs
Code: Select all
find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;
Code: Select all
/var/www/html/mailscanner/conf.php
Check space available!
You could try to check and repair the db.
To be save you need the next entries in de server.cnf ( and restart Mysql)
# this sets the inline defrag mode ( no recreate table)
innodb-defragment = 1
#use table space per table
innodb_file_per_table = 1
# if innodb_buffer_pool_size <= 1GB
innodb_buffer_pool_instances = 1
# the main buffer
iinnodb_buffer_pool_size = 1G
The optimal (log_file_size * 2) should be 25 % iinnodb_buffer_pool_size
If you activate this stop mysql an move the logfiles to a different location and start mysql
#innodb_log_file_size = 125M
Stop the mail flow
stop crond
stop mailscanner
to be sure no connections active
restart mysql
analyze
Code: Select all
mysqlcheck --all-databases --analyze
Code: Select all
mysqlcheck --all-databases --optimize
Code: Select all
mysqlcheck --all-databases --auto-repair --optimize
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
-
- Posts: 8
- Joined: 02 Feb 2018 09:04
Re: EFA - Mailwatch is painfully slow opening reports
I will update you once we ave removed the extra rows.
There might be other minor config issues but this has all stemmed from clamav stopping on a Friday, as that has caused the mail to loop round for some reason meaning each mail appears so many times.
Resulting in more space taken up, then the EFA trying to auto-update and failing due to the space due to the above issue.
There might be other minor config issues but this has all stemmed from clamav stopping on a Friday, as that has caused the mail to loop round for some reason meaning each mail appears so many times.
Resulting in more space taken up, then the EFA trying to auto-update and failing due to the space due to the above issue.