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

XF 1.4 Higher server load compare to Vbb 3.8

#1
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
 

Brogan

XenForo moderator
Staff member
#2
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

Well-known member
#3
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.
 
Last edited:
#4
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.
 
#5
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.
 
Last edited:
#6
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')
 
Last edited:
#8
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
 
Last edited:
#9
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.
 

Solidus

Well-known member
#10
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

Well-known member
#11
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
 
#13
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.
 
#14
I have 16GB of RAM, i will try increase pool size to 8GB see how it goes.

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