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; Thu 12 Jul '18, 11:14pm.

  • #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://forum.vbulletin.com/node/3935816
    https://forum.vbulletin.com/node/3935819
    https://forum.vbulletin.com/node/3935820
    https://forum.vbulletin.com/node/3935821
    https://forum.vbulletin.com/node/3935822
    Last edited by Wayne Luke; Mon 6 Jul '20, 7:11am.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API - Full / Mobile
    Vote for your favorite feature requests and the bugs you want to see fixed.

    Comment


    • #3
      I tried to edit my.cnf this in the past with hopes to improve performance and help a bit for running hosting but it didn't help more, to be honest if you are facing with problems like that, you should change your hosting to a better or bigger one.

      Intel(R) Xeon(R) CPU E3-1246 v3 @ 3.50GHz
      32GB RAM
      cPanel Control Panel with MariaDB + Litespeed 1CPU PHP 5.6
      Seem you are having issues in configuring your server because with these specs, it should run as well for your forum.
      How many visitors do you have in a day on your forum?
      Web Hosting Forum - Learn web hosting and offering your web hosting plans for free!
      Tranh phong cảnh, tranh nghệ thuật đẹp, bếp từ nhập khẩu chất lượng cao!

      Comment


      • #4
        Originally posted by Wayne Luke View Post
        .htaccess fail. Not a single link is working. Sad.
        Paul M. 14.12.2012: You already know vB4 isnt being worked on atm, so of course no bugs are going to have been fixed in the last few months, nor are they going to get fixed "now".

        Comment


        • #5
          Originally posted by Niktator View Post

          .htaccess fail. Not a single link is working. Sad.
          Why would you expect links in a post from more than two years ago to work?

          And it's not an '.htaccess fail'. The person those blogs belonged to left the company many years ago and the blogs no longer exist.
          MARK.B | vBULLETIN SUPPORT

          TalkNewsUK - My vBulletin 5.6.2 Demo
          AdminAmmo - My Cloud Demo

          Comment


          • #6
            Originally posted by Mark.B View Post

            Why would you expect links in a post from more than two years ago to work?

            And it's not an '.htaccess fail'. The person those blogs belonged to left the company many years ago and the blogs no longer exist.
            Why? Thats one way of getting information. By the way: That`s how people use forums. Its a knowledge source. What you expect is called "chat".
            Too bad the blog is gone. I guess it was a worth a read.
            Paul M. 14.12.2012: You already know vB4 isnt being worked on atm, so of course no bugs are going to have been fixed in the last few months, nor are they going to get fixed "now".

            Comment


            • #7
              Originally posted by Niktator View Post

              Why? Thats one way of getting information. By the way: That`s how people use forums. Its a knowledge source. What you expect is called "chat".
              Too bad the blog is gone. I guess it was a worth a read.
              The point is, we can’t go through every old thread checking the links still work. By all means use old threads and try them, but if they don’t please don’t have a go at us. It’s not a realistic criticism.
              MARK.B | vBULLETIN SUPPORT

              TalkNewsUK - My vBulletin 5.6.2 Demo
              AdminAmmo - My Cloud Demo

              Comment


              • #8
                Originally posted by Mattwhf View Post
                I tried to edit my.cnf this in the past with hopes to improve performance and help a bit for running hosting but it didn't help more, to be honest if you are facing with problems like that, you should change your hosting to a better or bigger one.



                Seem you are having issues in configuring your server because with these specs, it should run as well for your forum.
                How many visitors do you have in a day on your forum?
                Ten year old content was moved within the software. I am guessing from within the AdminCP and 301 redirects weren't created. I've updated the links to be valid even if the content gets moved again.

                https://forum.vbulletin.com/node/3935816
                https://forum.vbulletin.com/node/3935819
                https://forum.vbulletin.com/node/3935820
                https://forum.vbulletin.com/node/3935821
                https://forum.vbulletin.com/node/3935822
                Translations provided by Google.

                Wayne Luke
                The Rabid Badger - a vBulletin Cloud demonstration site.
                vBulletin 5 API - Full / Mobile
                Vote for your favorite feature requests and the bugs you want to see fixed.

                Comment

                widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                Working...
                X