XenForo MYSQL Optimisation help

  • Thread starter Thread starter Deleted member 91401
  • Start date Start date
D

Deleted member 91401

Guest
Currently something is causing this issue on my site;

RlSJbIc.png


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)
 
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)
 
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 ... :)
 
Marcus said:
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 ... :)
Click to expand...

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 ?
 
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?
 
Marcus said:
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?
Click to expand...

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
 
Moscato said:
their config wizard even works without using percona

it asks you which version you use, and mysql is an option
Click to expand...

@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).
 
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"
 
Marcus said:
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"
Click to expand...

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
 
You must log in or register to reply here.

Similar threads

H
Improve MySQL write log efficiency
Replies
0
Views
495
hellreturn
H
D
Need help importing vbulettin forum to xenforo, can someone check mysql config?
Replies
3
Views
331
Black Tiger
Black Tiger
PaulB
Fixed xf_session_activity: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction
Replies
13
Views
2K
PaulB
PaulB
PeterChen
Extreme high open file limit used - MariaDB 10.3
Replies
7
Views
2K
MySiteGuy
MySiteGuy
I
My.cnf Suggestions after Mysql 5.7 Upgrade
Replies
7
Views
3K
jeffatrackaid
jeffatrackaid
Top Bottom