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

Fine tuning my.cnf

Discussion in 'Server Configuration and Hosting' started by DaveL, Jun 9, 2013.

  1. DaveL

    DaveL Well-Known Member

    Just moved my first Xenforo site over to my new VPS and it seems pretty sluggish despite the VPS having twice as much as my last one.

    Checked the my.cnf file and this is what I got back

    I cant believe at how empty it is. Is there a default my.cnf I can use?
  2. DaveL

    DaveL Well-Known Member

    Ive seen this in another thread:

    Is it a case of just inputting the above details via SSH?
  3. CyclingTribe

    CyclingTribe Well-Known Member

    Copying and pasting a my.cnf isn't really the way to go - you need to tailor it to your available server resources.

    Post your OS, RAM, PHP and MySQL versions and you'll hopefully get some good "base level" suggestions you can use. (y)

    Shaun :D
    DaveL and Floren like this.
  4. Floren

    Floren Well-Known Member

    @Clickfinity +1. IMO, tuning MySQL is a long process.
    You cannot do it with a perl script or copy/paste from Google.
    DaveL likes this.
  5. DaveL

    DaveL Well-Known Member

    Hi Shaun,

    These are my details.

    Cent OS 6.4 x86
    2GB Ram
    Apache 2.2.24
    PHP - 5.4.15
    MySQL - 5.5.30

    I did see that xcache was available to add on via Easy Apache, however I wasnt too sure how to configure that so I havent installed it yet.

    Hope the above helps. Really appreciate your help.

  6. DaveL

    DaveL Well-Known Member

    Just wondered if anyone would be able to help me with a my.cnf based on the above server details please :)
  7. Tracy Perry

    Tracy Perry Well-Known Member

    This worked for me (could probably be fine tuned). NOTE: I run it on a non-standard port (where the x's are).
    port        = xxxxxxxxxx
    socket        = /var/run/mysqld/mysqld.sock
    socket        = /var/run/mysqld/mysqld.sock
    nice        = 0
    user        = mysql
    pid-file    = /var/run/mysqld/mysqld.pid
    socket        = /var/run/mysqld/mysqld.sock
    port        = xxxxxxxxx
    basedir        = /usr
    datadir        = /var/lib/mysql
    tmpdir        = /tmp
    bind-address        =
    thread_stack        = 192K
    thread_cache_size      = 8
    key_buffer_size        = 32M
    myisam-recover        = BACKUP
    max_connections        = 100
    table_cache            = 128000
    thread_concurrency    = 10
    query_cache_limit    = 54M
    query_cache_size        = 64M
    join_buffer_size    = 3M
    open_files_limit    = 8192
    expire_logs_days    = 10
    max_binlog_size        = 100M
    tmp_table_size                = 48M
    max_heap_table_size            = 32M
    innodb_flush_method            = O_DIRECT
    innodb_log_files_in_group      = 2
    innodb_log_file_size          = 128M
    innodb_flush_log_at_trx_commit = 2
    innodb_file_per_table          = 1
    innodb_buffer_pool_size        = 750M
    log_error                      = /var/log/mysql/mysql-error.log
    log_queries_not_using_indexes  = 1
    slow_query_log                = 1
    slow_query_log_file            = /var/log/mysql/mysql-slow.log
    max_allowed_packet    = 16M
    #no-auto-rehash    # faster start of mysql but no tab completition
    key_buffer        = 16M
    !includedir /etc/mysql/conf.d/
    Last edited: Jun 10, 2013
  8. Floren

    Floren Well-Known Member

    1306 is a standard port:
    # grep 1306 /etc/services
    re-conn-proto   1306/tcp                # RE-Conn-Proto
    re-conn-proto   1306/udp                # RE-Conn-Proto
    Is common to use high numbers when you assign custom ports (i.e. 33306).
  9. Tracy Perry

    Tracy Perry Well-Known Member

    I'm aware of that... I changed the port to something I don't use (don't like to put that info out on the web - even tho' there are ways to find it no need to advertise it). Should have probably put xxxxxx instead. ;)

    EDIT: Original post edited to reflect that. :D
    Floren and MattW like this.
  10. Freddie Agricola

    Freddie Agricola Active Member

  11. Tracy Perry

    Tracy Perry Well-Known Member

Share This Page