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

Server Upgrade, New my.cnf settings

Discussion in 'Server Configuration and Hosting' started by CivilWarTalk, Dec 7, 2013.

  1. CivilWarTalk

    CivilWarTalk Active Member

    Just upgraded my KnownHost VPS2 to an SSD2, and for just an additional $15 per month I've doubled my available memory, going from 1024mb to 2048mb

    Now I'd like to update my SQL memory settings for improved performance. I'm adding sonnb's XenGallery, and I want to make sure that everything functions well.

    My old my.cnf was set as follows:

    Code:
    [mysqld]
    local-infile=0
    #skip-innodb
    set-variable = max_connections=300
    safe-show-database
    max_tmp_tables=1
    query_cache_type=1
    query_cache_limit=4M
    query_cache_size=32M
    thread_cache_size=6
    table_cache=475
    tmp_table_size=256M
    max_heap_table_size=256M
    innodb_buffer_pool_size=384M
    interactive_timeout=60
    wait_timeout=60
    #key_buffer = 32M
    key_buffer_size=48M
    slow_query_log=1 # (1 = on, 0 = off)
    slow_query_log_file=slow_query_log.log # (optinal, file name, not full path)
    long_query_time=5 # (in seconds) 
    I've already made one change:
    innodb_buffer_pool_size=1152M

    I think that's a fairly safe change (but I'm not an expert!), does this seem ok? What other changes should I make?
     
  2. Slavik

    Slavik XenForo Moderator Staff Member

    How large are your tables?

    For the most part, giving innodb more to munch is probably all you'll need to do.
     
  3. CivilWarTalk

    CivilWarTalk Active Member

    SQL database is 2.35 GB. What tables do you need to know about?

    xf_search_index is 1 GB
    xf_post is nearly 1 GB
     
  4. Slavik

    Slavik XenForo Moderator Staff Member

    Basically work out how big all your innodb tables are, and give it enough room to store them all in the buffer pool.
     

Share This Page