The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Tune DB

Discussion in 'Workarounds and Optimization' started by Zerocard, Oct 16, 2015.

  1. Zerocard

    Zerocard Registered

    Joined:
    Oct 16, 2015
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Jordan
    cPanel Access Level:
    Root Administrator
    Hello,

    I'm trying to optimize our DB, we got dedicated server with the following specification:
    CPU: Intel Dual Xeon E5-2603 V2 1.8 GHZ, Quad Core
    RAM: 16GB DDR3 SDRAM

    And here's the current my.cnf after some of my changes:
    Code:
    
    [mysql]
    
    # CLIENT #
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    
    [mysqld]
    
    # GENERAL #
    user                           = mysql
    default_storage_engine         = InnoDB
    socket                         = /var/lib/mysql/mysql.sock
    pid_file                       = /var/lib/mysql/mysql.pid
    
    # MyISAM #
    key_buffer_size                = 213.1M
    myisam_recover                 = FORCE,BACKUP
    
    # SAFETY #
    max_allowed_packet             = 16M
    max_connect_errors             = 1000000
    skip_name_resolve
    sql_mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
    sysdate_is_now                 = 1
    innodb                         = FORCE
    
    # DATA STORAGE #
    datadir                        = /var/lib/mysql/
    
    # BINARY LOGGING #
    log_bin                        = /var/lib/mysql/mysql_bin
    expire_logs_days               = 14
    sync_binlog                    = 1
    
    # CACHES AND LIMITS #
    tmp_table_size                 = 32M
    max_heap_table_size            = 32M
    query_cache_type               = 1
    query_cache_size               = 8M
    max_connections                = 500
    thread_cache_size              = 50
    open_files_limit               = 65535
    table_definition_cache         = 4096
    table_open_cache               = 10240
    
    # INNODB #
    innodb_flush_method            = O_DIRECT
    innodb_log_files_in_group      = 2
    innodb_log_file_size           = 128M
    innodb_flush_log_at_trx_commit = 1
    innodb_file_per_table          = 1
    innodb_buffer_pool_size        = 8G
    innodb_io_capacity          = 2000
    innodb_read_io_threads          = 64
    innodb_thread_concurrency      = 0
    innodb_write_io_threads          = 64
    
    # LOGGING #
    log_error                      = /var/lib/mysql/mysql_error.log
    log_queries_not_using_indexes  = 1
    slow_query_log                 = 1
    slow_query_log_file            = /var/lib/mysql/mysql_slow.log
    
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page