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

Discussion in 'Troubleshooting and Problems' started by PeterChen, Jun 5, 2015.

  1. PeterChen

    PeterChen Member

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

    Brogan XenForo Moderator Staff Member

    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.
     
  3. thedude

    thedude Well-Known Member

    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: Jun 5, 2015
  4. PeterChen

    PeterChen Member

    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. PeterChen

    PeterChen Member

    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: Jun 7, 2015
  6. PeterChen

    PeterChen Member

    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: Jun 7, 2015
  7. Solidus

    Solidus Well-Known Member

    Paste your my.cnf
     
  8. PeterChen

    PeterChen Member

    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: Jun 7, 2015
  9. PeterChen

    PeterChen Member

    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.
     
  10. Solidus

    Solidus Well-Known Member

    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
     
  11. thedude

    thedude Well-Known Member

    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
     
  12. Solidus

    Solidus Well-Known Member

  13. PeterChen

    PeterChen Member

    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. PeterChen

    PeterChen Member

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

     
  15. Solidus

    Solidus Well-Known Member

  16. PeterChen

    PeterChen Member

  17. PeterChen

    PeterChen Member

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

    Solidus Well-Known Member

    Probably. Mine doubled converting from MyBB.
     

Share This Page