1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.4 MySQL lag every 24 hours.

Discussion in 'Troubleshooting and Problems' started by PrinceReborn, Oct 20, 2014.

  1. PrinceReborn

    PrinceReborn Member

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

    Brogan XenForo Moderator Staff Member

    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?
     
  3. PrinceReborn

    PrinceReborn Member

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

    Brogan XenForo Moderator Staff Member

    Are you on a shared server?
     
  5. PrinceReborn

    PrinceReborn Member

    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:
    Code:
    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
    Code:
    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:
    [​IMG]

    Slow query log graph:
    [​IMG]

    my.cnf:
    Code:
    [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
    
     

Share This Page