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

XenForo MYSQL Optimisation help

Discussion in 'Server Configuration and Hosting' started by HazedOff, Mar 10, 2015.

  1. HazedOff

    HazedOff Member

    Currently something is causing this issue on my site;

    [​IMG]

    People are complaining saying the site is slow, which it is, when posting new messages and stuff it does take quite a while compared to how the site use to be, I run it on a VPS that I have command line access too.

    I also ran mysqltuner.pl and this is the output as far as I know the my.cnf file is also blank.

    Code:
    root@s1 [~]# mysqltuner.sh
    -bash: mysqltuner.sh: command not found
    root@s1 [~]# perl mysqltuner.sh
    Can't open perl script "mysqltuner.sh": No such file or directory
    root@s1 [~]# perl mysqltuner.pl
    
     >>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.42-cll
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 14M (Tables: 19)
    [--] Data in InnoDB tables: 116M (Tables: 326)
    [--] Data in MEMORY tables: 378K (Tables: 5)
    [!!] Total fragmented tables: 329
    ERROR 1018 (HY000) at line 1: Can't read dir of './performance_schema/' (errno: 13)
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6m 41s (4K q [12.185 qps], 332 conn, TX: 217M, RX: 1M)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 168.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 573.8M (14% of installed RAM)
    [OK] Slow queries: 0% (2/4K)
    [OK] Highest usage of available connections: 7% (12/151)
    [!!] Key buffer size / total MyISAM indexes: 8.0M/11.8M
    [!!] Key buffer hit rate: 90.3% (2K cached / 238 reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 661 sorts)
    [!!] Joins performed without indexes: 17
    [!!] Temporary tables created on disk: 99% (528 on disk / 531 total)
    [!!] Thread cache is disabled
    [OK] Table cache hit rate: 95% (139 open / 146 opened)
    [OK] Open file limit used: 3% (36/1K)
    [OK] Table locks acquired immediately: 99% (6K immediate / 7K locks)
    [OK] InnoDB buffer pool / data size: 128.0M/116.3M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        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 without LIMIT clauses
        Set thread_cache_size to 4 as a starting value
    Variables to adjust:
        key_buffer_size (> 11.8M)
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
    
     
  2. Marcus

    Marcus Well-Known Member

    First ask your host to tell you where your my.cnf file is. If there is none, create one (for the format just google it). Then take a look at the recommendations. I would simply double the values from the "Variables to adjust" section, then restart mysql and after some hours rund that optimization program again.
    Code:
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    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 without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Variables to adjust:
    key_buffer_size (> 11.8M)
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
     
  3. HazedOff

    HazedOff Member

    I've done the first recommendation, the rest I have no idea how to do. @Marcus
     
  4. Marcus

    Marcus Well-Known Member

    I have enhanced my post. From bash you can run
    Code:
    # mysqlcheck --auto-repair --all-databases -u USERNAME -pPASSWORD --optimize 
    
    The password is directly after the "-p", there is no space.

    Most of the general recommendations are not really there for an admin to change, some require php code changes from xenforo. Just simply double the variable values within my.cnf and restart again. And again. And ... :)
     
  5. batpool52!

    batpool52! Well-Known Member

  6. HazedOff

    HazedOff Member

    I already ran an optimise command, the problem is that the MySQL time still spikes and continues to just increase as soon as the mysql service is started and also something is causing the PHP to hang for like 15minutes ?
     
  7. Marcus

    Marcus Well-Known Member

    You can add this in config.php:

    Code:
    $config['debug'] = true;
    At the bottom of each page you will find some statistical information about how much time did it take php and mysql to process that page.

    You should first know why your page is too slow. Is it php or mysql?
     
  8. HazedOff

    HazedOff Member

    home page:
    Code:
    Timing:
    0.3116 seconds
    Memory:
    8.244 MB
    DB Queries:
    46
    Random Thread:
    Code:
    Timing:
    2.0493 seconds
    Memory:
    9.910 MB
    DB Queries:
    36
     
  9. Moscato

    Moscato Active Member

    their config wizard even works without using percona

    it asks you which version you use, and mysql is an option
     
    batpool52! likes this.
  10. HazedOff

    HazedOff Member

    @Marcus Used the wizard, didn't have a my.cnf file in /etc/ already so I did this;

    > service mysql stop
    > nano /etc/my.cnf
    > copy and paste from Percona Wizard
    > ctrl+o - save as my.cnf
    > service mysql start
    > starting MySQL.........................
    Starting MySQL.. ERROR! The server quit without updating PID file (/var/lib/mysql//xx.serverhostname.com.pid).
     
  11. Marcus

    Marcus Well-Known Member

    OK first thing is to remove the /etc/my.cnf file and start mysld again.

    Usually there are error logs, maybe you will find them in /var/log/ .... They are really helpful for guiding you which options did not work out. I would not copy and paste from the Percona Wizard. Actually the mysql tuning is not *that* easy, mysql is out of the box not really configured in a good way so you do have to change a lot of variables to get it working. I guess you could invest some time to google some best practises for "mysql innodb my.cnf"
     
  12. HazedOff

    HazedOff Member

    Guess it's something I've going to have to do as I'm going to have to maintain the site in the long run, I start my new job tomorrow though and my forum is just starting to take off, two good things happening at the wrong times!

    Thanks for your help @Marcus
     
  13. Sheratan

    Sheratan Well-Known Member

    Maybe help: https://xenforo.com/community/threads/problem-with-percona.65153/
     
  14. Tracy Perry

    Tracy Perry Well-Known Member

Share This Page