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

MySQL Tuning

Discussion in 'Server Configuration and Hosting' started by Volion, May 30, 2012.

  1. Volion

    Volion Active Member

    Looking for feedback, suggestions for best performance for the innodb settings for 4GB - 6 GB of ram with Xenforo.

    innodb_data_home_dir = /var/lib/mysql/
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /var/lib/mysql/
    innodb_buffer_pool_size = 64M
    innodb_additional_mem_pool_size = 10M
    innodb_log_file_size = 32M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50

    Thanks in advance.
  2. BamaStangGuy

    BamaStangGuy Well-Known Member

  3. Volion

    Volion Active Member

    Thanks, but I was hoping for hands on advice, experience. ;)
  4. BamaStangGuy

    BamaStangGuy Well-Known Member

    I understand, just wanted to throw it out there.

    This is in my.cnf FWIW. (2GB Dedicated MySQL server)

    max_connections = 150
    max_allowed_packet= 32M
    innodb_buffer_pool_size= 1G
    thread_cache_size = 4
    key_buffer_size = 768M
    table_cache = 8192
    max_heap_table_size = 256M
    tmp_table_size = 256M
    AndyB likes this.
  5. Volion

    Volion Active Member

    Interesting, I've seen a few people set the innodb_buffer_pool_size= to 1G what do you have your log_file_size set to?
  6. Ghan_04

    Ghan_04 Active Member

    The most important setting when using innodb tables is probably going to be the buffer pool. That's basically where MySQL will cache data related to indexing on all innodb tables on the server.
    That being said, you should still also pay attention to the key_buffer because Xenforo's search index is still a MyISAM table regardless due to the fulltext indexing. You optimally want to have a key_buffer that is large enough to hold all the indices of your search index table for higher search performance. max_heap and tmp_table sizes should be large enough for MySQL to use in-memory temp tables for as much as possible (try some different values and check the tuning script to see what % of your temp tables are being created in memory to get a good feel for it).

    Keep in mind that all of this depends on how big your database is, so there is no set of magical numbers that will get you going, but it sounds like you have enough RAM to do what you need.
  7. Volion

    Volion Active Member

    Thanks, yeah I have those all set large enough. Changed the innodb_buffer_pool_size= to 1G , still no noticeable difference in performance. I'm going to have to break down and use the tuning script. ;)
  8. craigiri

    craigiri Well-Known Member

    Performance issues are hard to suss out unless you have a lot of people beating on the server. PhpMyAdmin also gives some tuning advice...or at least shows if anything is terribly wrong.

    Also, chances are that cache and other such issues (apache tuning, etc.) can help as much or more than mysql tunings.

    I guess the first questions are:
    Is there a problem with speed or response you are trying to fix?
    What is the amount of data you are dealing with (posts, members, size of xf db) - and is there anything else of note on the server.
  9. Ghan_04

    Ghan_04 Active Member

    I have noticed huge performance improvements from using the standard MySQL query cache. If you don't already have it set, try that. This is the setting:

    query_cache_size = 64M
  10. Volion

    Volion Active Member

    Speed and response times are all over the place (pingdom report) the site actually seems to run faster when we have 400+ online, rather then the normal 200 or so.
    Relatively new site ( 2 months old ) 63k, 3000+, 25m.

    Here is my updated .cnf - mySQLtuner reported no suggestions, will recheck in 48 hours.

    key_buffer_size = 500M
    max_allowed_packet = 16M
    table_open_cache = 4096
    sort_buffer_size = 4M
    read_buffer_size = 2M
    join_buffer_size = 2M
    myisam_sort_buffer_size = 32M
    thread_cache_size = 64
    query_cache_limit = 5M
    query_cache_size = 128M
    query_cache_type = 1
    thread_concurrency = 4
    tmp_table_size = 256M
    max_heap_table_size = 256M
    server-id = 1
    max_connections = 384
    max_user_connections = 100
    interactive_timeout = 30
    wait_timeout = 300
    connect_timeout = 10
    max_connect_errors = 5
    innodb_additional_mem_pool_size = 20M
    innodb_log_file_size = 32M
    innodb_log_buffer_size = 8M
    innodb_lock_wait_timeout = 50
    max_allowed_packet = 32M
    BamaStangGuy likes this.
  11. craigiri

    craigiri Well-Known Member

    That is a relatively small db size so speed should not be a problem.
    For comparisons sake, we have a 2 gig db and 1.2 million posts with about the same settings in mysql and it's running quick. Server load has not gone over .5 since we've had XF running (it's our off-season, so only about 200 online at one time).

    If you get slowdowns at this point, I'd look closer at other things.......

    All your RAM (or most) should be getting used - check the top command
    (Nix uses RAM as cache/buffers by default)
    Apache or whichever web server you use should be set to spawn plenty of servers and children
    You can set the basic front and back end caches in XF if you like

    Of course, it can also be your hosts network, but regular ping tests should tell you about some of that.

    Good Luck!
  12. simbolo

    simbolo Well-Known Member

    Since you reference pingdom, are you sure the slow responses are due to db issues and not network/web server/etc?
  13. Volion

    Volion Active Member

    Looking into that for a few days now, very well could be.

Share This Page