Announcement

Collapse
No announcement yet.

MySQL my.cnf for big forum

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL my.cnf for big forum

    Hello there,

    I have a customer with a huge forum vBulletin 4 (DB wise, visitors are quite low), he's using vbSEO and some additional addons but what I have observed is that MySQL within the server is using quite high CPU.

    When it was installed first time out of box MySQL config, MySQL sometimes get stucks for almost 20-45 seconds due to wait locked tables and now after some small changes on my.cnf CPU usage has increased but no more stucking.

    Load avg. between 2.00 - 4.00
    Estimated visitors: real-time 15-30

    I would really appreciate if someone could advise some best practices for vBulletin my.cnf.

    Database details:

    Threads: 1,109,946
    Posts: 4,651,827
    Members: 2,295,130

    Server Specs:

    Intel(R) Xeon(R) CPU E3-1246 v3 @ 3.50GHz
    32GB RAM
    cPanel Control Panel with MariaDB + Litespeed 1CPU PHP 5.6

    Current my.cnf:

    Code:
    [mysqld]
    performance_schema=off
    skip-networking
    innodb_file_per_table=1
    tmpdir=/tmp
    max_connections=300
    max_user_connections=300
    key_buffer_size=384M
    myisam_sort_buffer_size=192M
    join_buffer_size=1M
    read_buffer_size=128K
    sort_buffer_size=128K
    table_open_cache=3072
    thread_cache_size=1024
    table_definition_cache=2048
    open_files_limit=52000
    wait_timeout=60
    connect_timeout=60
    tmp_table_size=128M
    max_heap_table_size=128M
    max_allowed_packet=268435456
    thread_concurrency=8
    query_cache_limit=4M
    query_cache_size=128M
    query_cache_type=1
    interactive_timeout=60
    max_connect_errors=15
    local-infile=0
    innodb_buffer_pool_size=1G
    innodb_thread_concurrency=4
    innodb_log_file_size=128M
    innodb_log_buffer_size=10M
    innodb_flush_method=O_DIRECT
    low_priority_updates=1
    
    default-storage-engine=MyISAM
    
    [isamchk]
    key_buffer=256M
    sort_buffer_size=2M
    read_buffer=2M
    write_buffer=2M
    
    [myisamchk]
    tmpdir=/tmp
    key_buffer=256M
    sort_buffer=2M
    sort_buffer=2M
    read_buffer=2M
    write_buffer=2M
    Any suggestion / feedback is highly appreciated.

    Thank you!
    Last edited by RoTrKO; Fri 13th Jul '18, 12:14am.

  • #2
    You should be using MySQL 5.7 with INNODB tables for the best performance. Each table should be in its own file and large_prefix support needs to be enabled.

    https://www.vbulletin.com/forum/blog...nders/3935816-
    https://www.vbulletin.com/forum/blog...nders/3935819-
    https://www.vbulletin.com/forum/blog...nders/3935820-
    https://www.vbulletin.com/forum/blog...nders/3935821-
    https://www.vbulletin.com/forum/blog...nders/3935822-
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud customization and demonstration site.
    vBulletin 5 Documentation - Updated every Friday. Report issues here.
    vBulletin 5 API - Full / Mobile
    I am not currently available for vB Messenger Chats.

    Comment

    Related Topics

    Collapse

    Working...
    X