efa-project.org • EFA - Mailwatch is painfully slow opening reports

Page 1 of 1

EFA - Mailwatch is painfully slow opening reports

Posted: 02 Feb 2018 09:19

by WCHGAdrian

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.

Re: EFA - Mailwatch is painfully slow opening reports

Posted: 03 Feb 2018 07:38

by pdwalker

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.

Re: EFA - Mailwatch is painfully slow opening reports

Posted: 06 Feb 2018 08:20

by WCHGAdrian

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

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

ls -l /var/dcc/log |wc -l

Code: Select all

ls -l /var/spool/MailScanner/incoming/SpamAssassin-Temp | wc -l

Code: Select all

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.

Re: EFA - Mailwatch is painfully slow opening reports

Posted: 06 Feb 2018 08:33

by WCHGAdrian

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.

Re: EFA - Mailwatch is painfully slow opening reports

Posted: 06 Feb 2018 08:37

by henk

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:

Code: Select all

dig +short 2.0.0.127.zen.spamhaus.org

127.0.0.10
127.0.0.4
127.0.0.2
SORBS DUL:

Code: Select all

dig 2.0.0.127.dul.dnsbl.sorbs.net +short

127.0.0.10
URIBL:

Code: Select all

dig test.uribl.com.multi.uribl.com txt +short

"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

Re: EFA - Mailwatch is painfully slow opening reports

Posted: 06 Feb 2018 08:45

by WCHGAdrian

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

Code: Select all

127.0.0.10
127.0.0.4
127.0.0.2

[root@wcdritef01 mailscanner]# dig 2.0.0.127.dul.dnsbl.sorbs.net +short

Code: Select all

127.0.0.10

[root@wcdritef01 mailscanner]# dig 2.0.0.127.dul.dnsbl.sorbs.net +short

Code: Select all

127.0.0.10

This is an appliance originally.

Re: EFA - Mailwatch is painfully slow opening reports

Posted: 06 Feb 2018 10:36

by henk

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?

Code: Select all

wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.pl --buffers

Re: EFA - Mailwatch is painfully slow opening reports

Posted: 06 Feb 2018 11:25

by WCHGAdrian

Still returns the same, still no queries. Recieved hundreds of emails already today.

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

mysql command

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)

Re: EFA - Mailwatch is painfully slow opening reports

Posted: 06 Feb 2018 11:31

by WCHGAdrian

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.

Re: EFA - Mailwatch is painfully slow opening reports

Posted: 06 Feb 2018 13:50

by WCHGAdrian

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.

Re: EFA - Mailwatch is painfully slow opening reports

Posted: 06 Feb 2018 14:43

by henk

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)

Code: Select all

package-cleanup --oldkernels --count=1

2. You have quite some files in /var/dcc/log . Check total size.

Code: Select all

find /var/dcc/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;

exec the cronjob for housekeeping your dcc/log files. (cron-dccd)
The important space used by current dbs

Code: Select all

find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;

You could also change the days to keep in the mail in mailscanner and run a cleanup cronjob to remove records

Code: Select all

/var/www/html/mailscanner/conf.php

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

Code: Select all

mysqlcheck --all-databases --analyze

perform inline optimize

Code: Select all

mysqlcheck   --all-databases  --optimize

on errors: perform inline optimize with auto repair

Code: Select all

mysqlcheck   --all-databases --auto-repair --optimize

Good luck and please let me know if you could fix the issue

Re: EFA - Mailwatch is painfully slow opening reports

Posted: 06 Feb 2018 14:48

by WCHGAdrian

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.