XF 1.4 MySQL lag every 24 hours.

[PrinceReborn]

Hi,

I have noticed that every 24 hours at exactly 12:10 GMT my forum is experiencing major MySQL lag which also shows in the slow-query log. I presume this is related to some sort of forum cron job. The slow queries that appear in the log are from other processes so it seems that XenForo is hogging the MySQL usage during this time.

Does anyone have any advice on any ways we could resolve this issue? The main issue is that we have other processes which use MySQL running on the same server which are being badly affected by whatever is going on.

Regards,

Matt

 

[Paul B]

What cron tasks are scheduled to run at the time the slowdown occurs?

Are there any server batch jobs which also happen at that time?

 

[PrinceReborn]

The only cron which is scheduled to run at this time is the Hourly Cleanup, that doesn't really explain why every 24 hours there is a much heavier load caused by that - if it is that. Apart from this there are no other XF, cPanel or WHM crons which occur within 20 mins either way of 12:10 GMT

 

[Paul B]

Are you on a shared server?

 

[PrinceReborn]

I am using a dedicated server. Below is some information which may or may not be useful. Server time is GMT+1, so the time of lag would be around 13:10.

I am pretty certain the issue started happening since I have installed XenForo, although maybe I should disable the XenForo cron jobs before the next 13:10 just to make sure?

Specifications:

Intel Xeon E5-2650 Sandy Bridge-EP 2.0GHz 20MB L3 Cache LGA 2011 95W 8-Core Server Processor
Intel Xeon E5-2650 Sandy Bridge-EP 2.0GHz 20MB L3 Cache LGA 2011 95W 8-Core Server Processor
Western Digital RE4 WD2003FYYS 2TB 7200 RPM 64MB Cache SATA 3.0Gb/s 3.5" Internal Hard Drive
SAMSUNG 840 Pro Series MZ-7PD128BW 2.5" 128GB SATA III MLC Internal Solid State Drive (SSD)
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
Kingston Technology 8GB 1333MHZ DDR3 ECC Reg Parity CL9 240-Pin DIMM Quad Rank
SUPERMICRO SYS-6017R-NTF 1U Rackmount Server Barebone Dual LGA 2011 Intel C602 DDR3 1600/1333/1066/800 Chassis
SUPERMICRO MBD-X9DRW-IF-O Proprietary WIO Server Motherboard Dual LGA 2011 DDR3 1600/1333/1066/800
Integrated IPMI 2.0 X9DRW-iF Winbond KVM/LAN card

crontab -l

root@server [/]# crontab -l
0 6 * * * /usr/local/cpanel/scripts/exim_tidydb > /dev/null 2>&1
30 5 * * * /usr/local/cpanel/scripts/optimize_eximstats > /dev/null 2>&1
2,58 * * * * /usr/local/bandmin/bandmin
0 0 * * * /usr/local/bandmin/ipaddrmap
0 6 * * * /usr/local/cpanel/scripts/cpbackup
0 2 * * * /usr/local/cpanel/bin/backup
35 * * * * /usr/bin/test -x /usr/local/cpanel/bin/tail-check && /usr/local/cpanel/bin/tail-check
45 */4 * * * /usr/bin/test -x /usr/local/cpanel/scripts/update_mailman_cache && /usr/local/cpanel/scripts/update_mailman_cache
30 */4 * * * /usr/bin/test -x /usr/local/cpanel/scripts/update_db_cache && /usr/local/cpanel/scripts/update_db_cache
45 */8 * * * /usr/bin/test -x /usr/local/cpanel/bin/optimizefs && /usr/local/cpanel/bin/optimizefs
30 */2 * * * /usr/local/cpanel/bin/mysqluserstore >/dev/null 2>&1
15 */2 * * * /usr/local/cpanel/bin/dbindex >/dev/null 2>&1
15 */6 * * * /usr/local/cpanel/scripts/autorepair recoverymgmt >/dev/null 2>&1
*/5 * * * * /usr/local/cpanel/bin/dcpumon >/dev/null 2>&1
3 5 * * * /usr/local/cpanel/whostmgr/docroot/cgi/cpaddons_report.pl --notify
7,22,37,52 * * * * /usr/local/cpanel/whostmgr/bin/dnsqueue > /dev/null 2>&1
34 5 * * * /usr/local/cpanel/scripts/upcp --cron
10 21 * * * /usr/local/cpanel/3rdparty/bin/freshclam --quiet --no-warnings
0 */4 * * * /usr/sbin/tmpwatch -am 12 /tmp

List of XF cron jobs:

Slow query log graph:

my.cnf:

[mysqld]
max_allowed_packet=268435456
max_user_connections=100000
max_connections=100000
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow-queries.log
long_query_time=1
innodb_buffer_pool_size = 5120M
query_cache_limit=16M
query_cache_size=200M
query_cache_type=1
interactive_timeout=500
wait_timeout=3600
connect_timeout=250
thread_cache_size=512
key_buffer=32M
join_buffer=200M
sort_buffer_size=4M
read_buffer_size=4M
max_connect_errors=100
thread_concurrency=8

 

[llk]

@PrinceReborn, did you ever figure this out?

 

[PrinceReborn]

@PrinceReborn, did you ever figure this out?

If I recall correctly this was unrelated to Xenforo and to do with our own software application running on the server which did a very inefficient SQL query. I could be wrong though as this was a long time ago.

If a moderator wants to close this thread feel free.