Database issue not assigning token.

Report bugs and workarounds
henk
Posts: 517
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

Re: Database issue not assigning token.

Post by henk »

Thoughts? Yes. Somehow I like feedback comes to mind first and today it's not Monday.

Did you just cleared the /var/lib/mysql/Filter.globalvision.net.err file again as it is 0 bytes?
it was 5GB the last time, indicating some serious issues.

There should be at least 1 start message.

So show this file, (the last entries)

Somehow you reached the connection max!!!
[!!] Highest connection usage: 100% (152/151)
[!!] Aborted connections: 3.06% (10354/338367)

Did you optimize as mentioned before?

[--] Data in MyISAM tables: 69.2M (Tables: 14)
[--] Data in InnoDB tables: 12.4G (Tables: 21)
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `mailscanner`.`maillog`; -- can free 5961 MB
OPTIMIZE TABLE `sa_bayes`.`bayes_seen`; -- can free 7609 MB
Total freed space after theses OPTIMIZE TABLE : 13570 Mb


Did these jobs run?
/usr/local/bin/mailwatch/tools/Cron_jobs/mailwatch_db_clean.php
/usr/local/bin/mailwatch/tools/Cron_jobs/mailwatch_quarantine_maint.php --clean
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 »

Hello All,
Sorry for the frustration. This is no longer just happening on Monday.
Sometimes happens once every two weeks. Sometimes twice a week etc. Different days.
I did find the file that is calling the command that is getting hung up. The file is:
/usr/local/bin/mailwatch/tools/Cron_jobs/mailwatch_db_clean.php
and the Section is:
// Cleaning the mta_log and optionally the mta_log_id table
$sqlcheck = "SHOW TABLES LIKE 'mtalog_ids'";
$tablecheck = dbquery($sqlcheck);
$mta = get_conf_var('mta');
$optimize_mtalog_id = '';
if ($mta === 'postfix' && $tablecheck->num_rows > 0) {
//version for postfix with mtalog_ids enabled
dbquery(
'DELETE i.*, m.* FROM mtalog AS m
LEFT OUTER JOIN mtalog_ids AS i ON i.smtp_id = m.msg_id
WHERE m.timestamp < (NOW() - INTERVAL ' . RECORD_DAYS_TO_KEEP . ' DAY)'
);
$optimize_mtalog_id = ', mtalog_ids';
} else {
dbquery('DELETE FROM mtalog WHERE timestamp < (NOW() - INTERVAL ' . RECORD_DAYS_TO_KEEP . ' DAY)');
}

Could there be something wrong with this script? Is there a timer I can put on it to kill it when it hangs up?

Now to answer your questions.
Yes there is some info in .err not much but some. I think this wouldn't be an appropriate time to paste it since my server is functioning normally.
I think I know why I hit the connection max. The MTALOG db gets locked. See above.
I did run everything from your previous answer. And I appreciate the input thus far coming from everyone. Sorry for not being more verbose last time. I was in a hurry and I realize that's not your problem. Now I have a little more time to be more verbose.
henk
Posts: 517
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

Re: Database issue not assigning token.

Post by henk »

I think this wouldn't be an appropriate time to paste it since my server is functioning normally.
No, it's not, somehow you messed up your db. In pdwalker words:
The people helping you will ask questions. Some of those questions may not seem relevant. But here is the thing: if you knew what information was really relevant, then you could probably solve your own problem and you wouldn't be here asking.
As you have little more time to be more verbose, and I, being more polite, a little courteous and somewhat understanding, still like to see the
/var/lib/mysql/Filter.globalvision.net.err file.

The same for the actual mysqltuner output. ( assuming you did not clear it!)

As your cronjobs seem to 'hang'
Please show the output: ( the daily backup location) So we can see how long it takes to backup and the size of the backups.

Code: Select all

ls -l /var/EFA/backup
and total space free/used ( if you want to rebuid mtalog, you need to know available space)

Code: Select all

df -h
and the numer of files in /var/dcc/log

Code: Select all

ls -l /var/dcc/log |wc -l
If there is enough free space you could rebuild table mtalog as a last option.
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 »

I have attached everything you asked.
Attachments
Filter Files.zip
(4.2 KiB) Downloaded 568 times
henk
Posts: 517
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

Re: Database issue not assigning token.

Post by henk »

As Mysql restarted 2018-12-17 7:49:00, the logfile was cleared before 2018-12-17 07:47:26, so only the start of mysql is logged...

You are using MySQL version 10.1.30-MariaDB. ( 10.1.37-MariaDB is the curent version, availble by yum update. The upgrade will also check the db for you, so I would first try yum update, and make sure you commented the exclude line in

Code: Select all

/etc/yum.conf

Code: Select all

# PUT YOUR REPOS HERE OR IN separate files named file.repo
# in /etc/yum.repos.d
#exclude=kernel* MariaDB* postfix* mailscanner* MailScanner* clamav* clamd* open-vm-tools*
and stop de mail flow before you update!)

All mysql modifications should be in:

Code: Select all

/etc/my.cnf.d/server.cnf
As you mentioned: " I did run everything from your previous answer." Mysqltuner clearly shows otherwise, so please post

Code: Select all

/etc/my.cnf.d/server.cnf
Your Msqltuner:
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `mailscanner`.`maillog`; -- can free 6743 MB
Total freed space after theses OPTIMIZE TABLE : 6743 Mb
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
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 schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: http://bit.ly/2wgkDvS
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
sort_buffer_size (> 2M)
read_rnd_buffer_size (> 256K)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 11.6G) if possible.
innodb_log_file_size should be (=512M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)


As the backups are in /var/log/EFA, I missed it in your zip file. ( i'd like to know the backup size and backup runtime)

Maintenance issues?
Quite some dcc log files
[root@Filter ~]# ls -l /var/dcc/log |wc -l
146657

#clear some old dcc log files

Code: Select all

find /var/dcc/log -mtime +1 -print | xargs rm -f
Freespace
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_00-lv_root 188G 54G 125G 31% /
tmpfs 7.8G 0 7.8G 0% /dev/shm
/dev/sda1 485M 165M 296M 36% /boot
/dev/mapper/vg_00-lv_tmp 3.0G 9.1M 2.8G 1% /tmp
/dev/mapper/vg_00-lv_var 158G 43G 108G 29% /var
none 7.8G 131M 7.7G 2% /var/spool/MailScanner/incoming

#clear some old files

Code: Select all

find /var/spool/MailScanner/incoming/SpamAssassin-Temp -mtime +1 -print | xargs rm -f

This could take some time
Stop mail flow.

Code: Select all

service mailscanner stop
service crond stop
Check Isam files

Code: Select all

myisamchk --check /var/lib/mysql/*/*.MYI
Analyze

Code: Select all

myisamchk --analyze /var/lib/mysql/*/*.MYI
Analyze

Code: Select all

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

Code: Select all

mysqlcheck --all-databases --optimize

If the above cmd's did not resolve the issue:
Login to mysql

Code: Select all

mysql -uroot 

OPTIMIZE TABLE mailscanner.maillog;

Start mail flow.

Code: Select all

service mailscanner start
service crond start
Did I mention a backup / snapshot?
Last edited by henk on 18 Dec 2018 17:30, edited 1 time in total.
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 »

I know for a fact I ran all of that stuff.
I'd put money on it actually. But I just checked the server.cnf and it is nonexistent in there. So it makes me wonder, did I actually suffer from a major vmware issue (unrelated to EFA specifically) that we had last week and revert to a snapshot or something.

That is, however, not your problem. So I will redo everything from your original post and from this most recent one. Then after 24 hours I will give you some fresh data.
P.S. I'm not trying to be difficult. When I say I ran everything from your post, I mean I scheduled an outage and I went line by line and did all of it. Why it is not there I do not know. But your suspicion that is not there is correct. And that is on me. Thanks for the help!

My LS data:

total 3389868
drwxr-xr-x 8 root root 4096 Jul 1 2015 3.0.0.8
drwxr-xr-x 4 root root 4096 Apr 25 2016 3.0.0.9
drwxr-xr-x 4 root root 4096 May 3 2016 3.0.1.0
drwxr-xr-x 2 root root 4096 Jul 24 2016 3.0.1.1
drwxr-xr-x 6 root root 4096 Sep 15 2016 3.0.1.2
drwxr-xr-x 4 root root 4096 Sep 22 2016 3.0.1.4
drwxr-xr-x 4 root root 4096 Apr 6 2017 3.0.1.6
drwxr-xr-x 4 root root 4096 Apr 13 2017 3.0.1.7
drwxr-xr-x 6 root root 4096 Apr 13 2017 3.0.1.9
drwxr-xr-x 4 root root 4096 Apr 25 2017 3.0.2.0
drwxr-xr-x 4 root root 4096 May 1 2017 3.0.2.1
drwxr-xr-x 4 root root 4096 Oct 23 2017 3.0.2.2
drwxr-xr-x 4 root root 4096 Nov 20 2017 3.0.2.3
drwxr-xr-x 5 root root 4096 Nov 20 2017 3.0.2.4
drwxr-xr-x 4 root root 4096 Nov 20 2017 3.0.2.5
-rw------- 1 root root 510186376 Dec 12 03:56 backup-20181212-035001.tar.gz
-rw------- 1 root root 493387438 Dec 13 06:43 backup-20181213-063702.tar.gz
-rw------- 1 root root 495100641 Dec 14 03:56 backup-20181214-035001.tar.gz
-rw------- 1 root root 498820595 Dec 15 03:18 backup-20181215-031101.tar.gz
-rw------- 1 root root 494444267 Dec 16 03:13 backup-20181216-030601.tar.gz
-rw------- 1 root root 490063471 Dec 17 03:17 backup-20181217-031002.tar.gz
-rw------- 1 root root 488558566 Dec 18 03:44 backup-20181218-033901.tar.gz
drwxr-xr-x. 2 root root 4096 Dec 14 04:02 KAM
-rw-r--r-- 1 root root 532662 Dec 18 04:23 phishing.bad.sites.conf.backup
-rw-r--r-- 1 root root 12468 Dec 18 04:24 phishing.safe.sites.conf.backup
drwxr-xr-x 6 root root 4096 Dec 18 03:44 tmp
henk
Posts: 517
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

Re: Database issue not assigning token.

Post by henk »

No harm done :)
Seems there was an issue on Dec 13.
rw------- 1 root root 493387438 Dec 13 06:43 backup-20181213-063702.tar.gz
Quite off topic, but a great (free) tool for vmware admins to monitor your vmware server(s) and VM's:
RVtools https://www.robware.net/rvtools/

Due the massive amount of dcc log files check if cron-dccd in present in /etc/cron.daily
I moved it from the montly cron to daily cron viewtopic.php?t=2610

Code: Select all

ls -l /etc/cron.daily
total 56
-rw-r--r--. 1 root root 530 May 24 2017 1
lrwxrwxrwx 1 root root 26 Aug 28 2017 cron-dccd -> /var/dcc/libexec/cron-dccd
-rwx------. 1 root root 1099 May 24 2017 EFA-Backup-cron
-rwx------. 1 root root 1107 May 24 2017 EFA-Daily-cron
-rwxr-xr-x. 1 root root 120 May 24 2017 eFa-SAClean
-rwx------. 1 root root 2161 May 24 2017 EFA-Tokens-Cron
-rwxr-xr-x 1 root root 396 Oct 23 20:19 freshclam
-rwx------ 1 root root 313 Jul 29 2017 logrotate
-rwxr-xr-x 1 root root 188 Jan 2 2018 mailscanner
-rwxr-xr-x 1 root root 289 Oct 3 2017 mailwatch
-rwx------. 1 root root 927 Mar 22 2017 makewhatis.cron
-rwx------. 1 root root 189 Jan 26 2015 mlocate.cron
-rwxr-xr-x. 1 root root 2126 Jul 19 2013 prelink
-rwxr-xr-x. 1 root root 563 Nov 23 2013 readahead.cron
-rwxr-xr-x 1 root root 848 Jul 29 2017 tmpwatch

Number of days to keep in /var/dcc/dcc_conf

Code: Select all

# days to keep files in DCC log directories
DBCLEAN_LOGDAYS=1
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 »

Everything functioning normal. Just sending this for reference.

[root@Filter ~]# perl mysqltuner.pl
>> MySQLTuner 1.7.13 - 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.30-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/Filter.globalvision.net.err(0B)
[OK] Log file /var/lib/mysql/Filter.globalvision.net.err exists
[OK] Log file /var/lib/mysql/Filter.globalvision.net.err is readable.
[!!] Log file [root@Filter ~]# perl mysqltuner.pl
>> MySQLTuner 1.7.13 - 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.30-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/Filter.globalvision.net.err(0B)
[OK] Log file /var/lib/mysql/Filter.globalvision.net.err exists
[OK] Log file /var/lib/mysql/Filter.globalvision.net.err is readable.
[!!] Log file /var/lib/mysql/Filter.globalvision.net.err is empty
[OK] Log file /var/lib/mysql/Filter.globalvision.net.err is smaller than 32 Mb
[OK] /var/lib/mysql/Filter.globalvision.net.err doesn't contain any warning.
[OK] /var/lib/mysql/Filter.globalvision.net.err doesn't contain any error.
[--] 0 start(s) detected in /var/lib/mysql/Filter.globalvision.net.err
[--] 0 shutdown(s) detected in /var/lib/mysql/Filter.globalvision.net.err

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 66.6M (Tables: 14)
[--] Data in InnoDB tables: 11.6G (Tables: 21)
[!!] Total fragmented tables: 2

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- 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: 6d 0h 47m 1s (11M q [21.422 qps], 87K conn, TX: 89G, RX: 2G)
[--] Reads / Writes: 31% / 69%
[--] Binary logging is disabled
[--] Physical Memory : 15.6G
[--] Max MySQL memory : 1.7G
[--] Other process memory: 3.6G
[--] Total buffers: 1.2G global + 3.2M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.3G (8.17% of installed RAM)
[OK] Maximum possible memory usage: 1.7G (10.96% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (76K/11M)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Aborted connections: 0.00% (1/87417)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 353 sorts)
[!!] Joins performed without indexes: 4496
[!!] Temporary tables created on disk: 64% (2K on disk / 3K total)
[OK] Thread cache hit rate: 99% (10 created / 87K connections)
[OK] Table cache hit rate: 89% (190 open / 212 opened)
[OK] Open file limit used: 3% (131/4K)
[OK] Table locks acquired immediately: 99% (10M immediate / 10M 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: 8 thread(s).
[--] Using default value is good enough for your version (10.1.30-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 64.2% (32M used / 50M cache)
[OK] Key buffer size / total MyISAM indexes: 48.0M/35.4M
[OK] Read Key buffer hit rate: 100.0% (26M cached / 1K reads)
[!!] Write Key buffer hit rate: 4.8% (6M cached / 299K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 1.0G/11.6G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 125.0M * 2/1.0G should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (438537348780 hits/ 438552853575 total)
[!!] InnoDB Write Log efficiency: 82.1% (34144027 hits/ 41586679 total)
[OK] InnoDB log waits: 0.00% (0 waits / 7442652 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.8% (915K cached / 2K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `mailscanner`.`maillog`; -- can free 7015 MB
OPTIMIZE TABLE `sa_bayes`.`bayes_seen`; -- can free 7413 MB
Total freed space after theses OPTIMIZE TABLE : 14428 Mb
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
join_buffer_size (> 512.0K, or always use indexes with JOINs)
tmp_table_size (> 32M)
max_heap_table_size (> 32M)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 11.6G) if possible.
is empty
[OK] Log file /var/lib/mysql/Filter.globalvision.net.err is smaller than 32 Mb
[OK] /var/lib/mysql/Filter.globalvision.net.err doesn't contain any warning.
[OK] /var/lib/mysql/Filter.globalvision.net.err doesn't contain any error.
[--] 0 start(s) detected in /var/lib/mysql/Filter.globalvision.net.err
[--] 0 shutdown(s) detected in /var/lib/mysql/Filter.globalvision.net.err

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 66.6M (Tables: 14)
[--] Data in InnoDB tables: 11.6G (Tables: 21)
[!!] Total fragmented tables: 2

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- 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: 6d 0h 47m 1s (11M q [21.422 qps], 87K conn, TX: 89G, RX: 2G)
[--] Reads / Writes: 31% / 69%
[--] Binary logging is disabled
[--] Physical Memory : 15.6G
[--] Max MySQL memory : 1.7G
[--] Other process memory: 3.6G
[--] Total buffers: 1.2G global + 3.2M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.3G (8.17% of installed RAM)
[OK] Maximum possible memory usage: 1.7G (10.96% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (76K/11M)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Aborted connections: 0.00% (1/87417)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 353 sorts)
[!!] Joins performed without indexes: 4496
[!!] Temporary tables created on disk: 64% (2K on disk / 3K total)
[OK] Thread cache hit rate: 99% (10 created / 87K connections)
[OK] Table cache hit rate: 89% (190 open / 212 opened)
[OK] Open file limit used: 3% (131/4K)
[OK] Table locks acquired immediately: 99% (10M immediate / 10M 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: 8 thread(s).
[--] Using default value is good enough for your version (10.1.30-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 64.2% (32M used / 50M cache)
[OK] Key buffer size / total MyISAM indexes: 48.0M/35.4M
[OK] Read Key buffer hit rate: 100.0% (26M cached / 1K reads)
[!!] Write Key buffer hit rate: 4.8% (6M cached / 299K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 1.0G/11.6G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 125.0M * 2/1.0G should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (438537348780 hits/ 438552853575 total)
[!!] InnoDB Write Log efficiency: 82.1% (34144027 hits/ 41586679 total)
[OK] InnoDB log waits: 0.00% (0 waits / 7442652 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.8% (915K cached / 2K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `mailscanner`.`maillog`; -- can free 7015 MB
OPTIMIZE TABLE `sa_bayes`.`bayes_seen`; -- can free 7413 MB
Total freed space after theses OPTIMIZE TABLE : 14428 Mb
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
join_buffer_size (> 512.0K, or always use indexes with JOINs)
tmp_table_size (> 32M)
max_heap_table_size (> 32M)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 11.6G) if possible.
ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 »

Ok,
So I had some more time to look at this Mon and Tues.
I realized that this section was never completing. Ever.
dbquery(
'DELETE i.*, m.* FROM mtalog AS m
LEFT OUTER JOIN mtalog_ids AS i ON i.smtp_id = m.msg_id
WHERE m.timestamp < (NOW() - INTERVAL ' . RECORD_DAYS_TO_KEEP . ' DAY)'
);

A line towards the bottom of /usr/local/bin/mailwatch/tools/Cron_jobs/mailwatch_db_clean.php

Since I'm not a mysql pro this took me a while to dissect. But I looked at the two tables mentioned and they were a mess one was 6mil entries and one was 3mil. Clearly they're meant to be more similar and I still had entries from 2017 in there. So to clean it up, (after hours of trying to learn the language), I ran these two:
// DELETE FROM mtalog WHERE timestamp < (NOW() - INTERVAL 60 DAY)
// DELETE mtalog_ids.* FROM mtalog_ids LEFT JOIN mtalog ON mtalog.msg_id = mtalog_ids.smtp_id WHERE mtalog.msg_id IS NULL
The first command to cleanup the mtalog with no join.
And the second to join and delete the NULL entries from mtalog_ids. To make them more similar.
This trimmed them up quite a bit. Down in the 400k to 500k range now.
And this morning I came in to see the mailwatch_db_clean script had run successfully before I got in.
So I will continue to keep an eye on it.
Thank you all for the input.
Post Reply