XF 1.4 Higher server load compare to Vbb 3.8

PeterChen · Jun 5, 2015

Hello,

I just made the move from VBB 3.8 to Xenforo 1.4.7. I like the functionalities and design, but some how. With the same database, same user online. Xenforo seems to use 5 times more resources ( server load 5 time higher). My server load used to be round .6 to 1. Now with Xenforo ( with litespeed cache mod installed) is always around 2.5 to 5.

So, is there anything i can do to optimize Xenforo more?
My board has 4 million posts, 180,000 members, 4000 users online peak

Paul B · Jun 5, 2015

Do you have any add-ons installed?

Have you done any server tuning since switching to XF?
Which will definitely be required for that many users online simultaneously.

thedude · Jun 5, 2015

Vb uses the MyISAM engine of mysql. XenForo uses the InnoDB engine instead, which requires its own tuning in your my.cnf file.

Also, like Brogan mentioned, it could be an add-on you're using that's not designed for high traffic sites. We've had to completely rewrite a few that we use so they could scale properly.

PeterChen · Jun 5, 2015

At the time of posting, i had not addon install beside Litespeed Cache to help. But it does not help much.

And no, i have not done any server tuning yet. And i will do it now.

PeterChen · Jun 7, 2015

I just tried to convert MyISAM to innoDB for my database, and the result is it took 4 hours to complete, then the database is 3 time bigger( from 5GB to 15GB. And now my site is even slower.

PeterChen · Jun 7, 2015

I also see alot of error log to /var/lib/mysql/mysqld.log

This could be error, or something wrong that slow things down? That mysqld.log file is filling up quick and over 20GB with content like below.

Code:

tail -50 mysqld.log
                        FROM xf_post AS post

                        WHERE post.post_id = '22203934'
                44541 Close stmt
                44541 Prepare   SELECT thread.*
                                ,
                                        IF(reply_ban.user_id IS NULL, 0,
                                                IF(reply_ban.expiry_date IS NULL OR reply_ban.expiry_date > 1433639338, 1, 0)) AS thread_reply_banned
                        FROM xf_thread AS thread

                                        LEFT JOIN xf_thread_reply_ban AS reply_ban
                                                ON (reply_ban.thread_id = thread.thread_id
                                                AND reply_ban.user_id = 1458450)
                        WHERE thread.thread_id = ?
                44541 Execute   SELECT thread.*
                                ,
                                        IF(reply_ban.user_id IS NULL, 0,
                                                IF(reply_ban.expiry_date IS NULL OR reply_ban.expiry_date > 1433639338, 1, 0)) AS thread_reply_banned
                        FROM xf_thread AS thread

                                        LEFT JOIN xf_thread_reply_ban AS reply_ban
                                                ON (reply_ban.thread_id = thread.thread_id
                                                AND reply_ban.user_id = 1458450)
                        WHERE thread.thread_id = '942379'
                44541 Close stmt
                44541 Prepare   SELECT node.*, forum.*
                                ,
                                permission.cache_value AS node_permission_cache
                        FROM xf_forum AS forum
                        INNER JOIN xf_node AS node ON (node.node_id = forum.node_id)

                                LEFT JOIN xf_permission_cache_content AS permission
                                        ON (permission.permission_combination_id = 2
                                                AND permission.content_type = 'node'
                                                AND permission.content_id = forum.node_id)
                        WHERE node.node_id = ?
                44541 Execute   SELECT node.*, forum.*
                                ,
                                permission.cache_value AS node_permission_cache
                        FROM xf_forum AS forum
                        INNER JOIN xf_node AS node ON (node.node_id = forum.node_id)

                                LEFT JOIN xf_permission_cache_content AS permission
                                        ON (permission.permission_combination_id = 2
                                                AND permission.content_type = 'node'
                                                AND permission.content_id = forum.node_id)
                        WHERE node.node_id = '147'
                44541 Close stmt
                44541 Prepare   UPDATE `xf_session` SET `expiry_date` = ? WHERE (session_id = '356c8a480aa5e4cfe27bb6d4bc4778e8')

m1ne · Jun 7, 2015

Paste your my.cnf

PeterChen · Jun 7, 2015

Here is my.cnf

Code:

[mysqld]
#character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#tmpdir=/home/mysqltmp

#skip-innodb
skip-federated
#skip-pbxt
#skip-pbxt_statistics
skip-archive
#skip-name-resolve
#old_passwords
back_log = 75
max_connections = 500
key_buffer_size = 1024M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 2048M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 512K
table_definition_cache = 4096
table_open_cache = 4096
thread_cache_size = 64
wait_timeout = 120
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 32M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 256K
bulk_insert_buffer_size = 8M
query_cache_limit = 512K
query_cache_size = 32M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=0
slow_query_log=1
long_query_time=5
slow_query_log_file=/var/lib/mysql/slowq.log
#log-error=/var/log/mysqld.log

# innodb settings
#innodb_extra_rsegments = 4
innodb_purge_threads=1
innodb_doublewrite = 1

innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 32M

innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 100
innodb_read_io_threads = 2
innodb_write_io_threads = 2


# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 32M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 8M
aria_sort_buffer_size = 8M

[mariadb-5.5]
#ignore_db_dirs=
query_cache_strip_comments=0

#innodb_lazy_drop_table=1
innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 32M

[myisamchk]
key_buffer = 32M
sort_buffer = 16M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout


# Enable logging by default to help find problems
general-log
log-slow-queries

PeterChen · Jun 7, 2015

I am getting high io load result in high serverload because lots of content being output to mysqld.log and it is not error, seem like just logging all query there. This file get approximately 1MB bigger every seconds.

m1ne · Jun 7, 2015

How much RAM you got? You can easily set innodb_buffer_pool_size to 8G or more.
Here's my setup for a forum a lot smaller (just 1 million posts),

Code:

log-error=/var/log/mysqld.log
log_slow_queries=/var/log/mysql/slow-query.log
long_query_time=8
skip-external-locking
query_cache_type = 1
query_cache_size = 768M
query_cache_limit = 1M
query_cache_strip_comments = 0
thread_cache_size = 256
max_heap_table_size = 128M
tmp_table_size = 128M
connect_timeout = 10
max_connections = 150
interactive_timeout = 300
wait_timeout = 300
#thread_stack = 256k
table_open_cache = 12000
open_files_limit = 24000
key_buffer_size = 512M
max_allowed_packet = 4M
sort_buffer_size = 2M
join_buffer_size = 128k
#net_buffer_length = 64K
read_buffer_size = 512K
#read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 128M
innodb_buffer_pool_size = 4G
innodb_thread_concurrency = 0
innodb_buffer_pool_instances = 4
innodb_flush_method=O_DIRECT
innodb_file_per_table=1
innodb_log_buffer_size=8M

A lot of your settings should be increased..

query_cache_size
join_buffer_size
read_buffer_size
sort_buffer_size
table_open_cache

thedude · Jun 7, 2015

PeterChen said:

Here is my.cnf

Code:

[mysqld]
#character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#tmpdir=/home/mysqltmp

#skip-innodb
skip-federated
#skip-pbxt
#skip-pbxt_statistics
skip-archive
#skip-name-resolve
#old_passwords
back_log = 75
max_connections = 500
key_buffer_size = 1024M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 2048M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 512K
table_definition_cache = 4096
table_open_cache = 4096
thread_cache_size = 64
wait_timeout = 120
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 32M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 256K
bulk_insert_buffer_size = 8M
query_cache_limit = 512K
query_cache_size = 32M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=0
slow_query_log=1
long_query_time=5
slow_query_log_file=/var/lib/mysql/slowq.log
#log-error=/var/log/mysqld.log

# innodb settings
#innodb_extra_rsegments = 4
innodb_purge_threads=1
innodb_doublewrite = 1

innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 32M

innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 100
innodb_read_io_threads = 2
innodb_write_io_threads = 2


# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 32M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 8M
aria_sort_buffer_size = 8M

[mariadb-5.5]
#ignore_db_dirs=
query_cache_strip_comments=0

#innodb_lazy_drop_table=1
innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 32M

[myisamchk]
key_buffer = 32M
sort_buffer = 16M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout


# Enable logging by default to help find problems
general-log
log-slow-queries

You have general logging turned on. In your my.cnf, near the bottom find

Code:

general-log

and change it to

Code:

#general-log

then restart mysql

m1ne · Jun 7, 2015

A few of your settings should just be removed too.

innodb_thread_concurrency (you have 2)
table_definition_cache
group_concat_max_len

You should follow this to optimize thread_cache_size : http://anothermysqldba.blogspot.co.uk/2013/09/mysql-optimization-tip-threadcachesize.html

PeterChen · Jun 7, 2015

Thanks, comment out
#general-log
solve the problem of logging all queries.

But still wondering about the database size after converted to InnoDB is triple in size. And it also seem like slower when i was with MyISAM and database smaller.

PeterChen · Jun 7, 2015

I have 16GB of RAM, i will try increase pool size to 8GB see how it goes.

Solidus said:

How much RAM you got? You can easily set innodb_buffer_pool_size to 8G or more.
Here's my setup for a forum a lot smaller (just 1 million posts),

Code:

log-error=/var/log/mysqld.log
log_slow_queries=/var/log/mysql/slow-query.log
long_query_time=8
skip-external-locking
query_cache_type = 1
query_cache_size = 768M
query_cache_limit = 1M
query_cache_strip_comments = 0
thread_cache_size = 256
max_heap_table_size = 128M
tmp_table_size = 128M
connect_timeout = 10
max_connections = 150
interactive_timeout = 300
wait_timeout = 300
#thread_stack = 256k
table_open_cache = 12000
open_files_limit = 24000
key_buffer_size = 512M
max_allowed_packet = 4M
sort_buffer_size = 2M
join_buffer_size = 128k
#net_buffer_length = 64K
read_buffer_size = 512K
#read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 128M
innodb_buffer_pool_size = 4G
innodb_thread_concurrency = 0
innodb_buffer_pool_instances = 4
innodb_flush_method=O_DIRECT
innodb_file_per_table=1
innodb_log_buffer_size=8M

A lot of your settings should be increased..

query_cache_size
join_buffer_size
read_buffer_size
sort_buffer_size
table_open_cache

m1ne · Jun 7, 2015

Running mysqltuner gives you a rough idea how to optimize too.
https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

PeterChen · Jun 7, 2015

Solidus said:
Running mysqltuner gives you a rough idea how to optimize too.
https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

i will, thanks

PeterChen · Jun 7, 2015

is it normal for XF database double in size compare to VB? and triple in size after convert to InnoDB?

m1ne · Jun 7, 2015

Probably. Mine doubled converting from MyBB.

XF 1.4 Higher server load compare to Vbb 3.8

PeterChen

Member

Paul B

XenForo moderator

thedude

Well-known member

PeterChen

Member

PeterChen

Member

PeterChen

Member

m1ne

Well-known member

PeterChen

Member

PeterChen

Member

m1ne

Well-known member

thedude

Well-known member

m1ne

Well-known member

PeterChen

Member

PeterChen

Member

m1ne

Well-known member

PeterChen

Member

PeterChen

Member

m1ne

Well-known member

Similar threads

We value your privacy