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

Help with InnoDB configuration

Discussion in 'Server Configuration and Hosting' started by dbembibre, Jun 5, 2013.

  1. dbembibre

    dbembibre Active Member

    Hi to all, im finish my local test and im ready to start with the migration process.
    I need a help with the innodb configuration my actual config goes with skip-innodb because all my vb tables are MyISAM. I have experience with MyISAM configuration but no with InnoDB.
    Please give me some help with an initial config. I know about percona i compile many times years ago but for the moment i plan to do the migration with my current mysql community edition and later switch to mariadb or percona.My forum have 12 million of posts.

    The server is a dedicated DB server with only one database (vbulletin)
    • MySQL 5.5-30
    • Dual Dual Xeon 2.33GHZ (E5410)
    • SCSI 15K RPM Disk
    • 8 GB of RAM
    • CenOS 5.9 64 bits
    I planning use 4GB for MySQL and 4GB for elastic search

    My current my.cnf

    Code:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    symbolic-links=0
    port = 3306
    skip-name-resolve
    bind-address = 192.168.x.x
    #safe-show-database
    back_log = 50
    max_connections = 650
    key_buffer_size =3072M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    #table_cache = 8000
    table_cache = 10000
    thread_cache_size = 512
    wait_timeout = 85
    #wait_timeout=60
    interactive_timeout=175
    connect_timeout = 10
    tmp_table_size = 64M
    max_heap_table_size = 64M
    max_allowed_packet = 64M
    net_buffer_length = 16384
    max_connect_errors = 100000
    thread_concurrency = 16
    concurrent_insert = 2
    #table_lock_wait_timeout = 30
    read_rnd_buffer_size = 786432
    bulk_insert_buffer_size = 8M
    query_cache_limit = 6M
    query_cache_size = 256M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    default-storage-engine = MyISAM
    max_write_lock_count = 8
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    open_files_limit = 8192
    [mysqldump]
    quick
    max_allowed_packet = 64M
    [myisamchk]
    key_buffer = 1024M
    sort_buffer = 16M
    read_buffer = 16M
    write_buffer = 16M
     
  2. Slavik

    Slavik XenForo Moderator Staff Member

    add in

    Code:
    innodb_flush_method  = O_DIRECT
    innodb_log_files_in_group  = 2
    innodb_log_file_size  = 256M
    innodb_flush_log_at_trx_commit = 2
    innodb_file_per_table  = 1
    innodb_buffer_pool_size  = 4G
    
     
    Tracy Perry likes this.

Share This Page