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.

Help to optimize my.cnf file

Discussion in 'Workarounds and Optimization' started by Smaily, Jul 19, 2013.

  1. Smaily

    Smaily Well-Known Member

    Joined:
    Sep 19, 2011
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Total processors: 8 (2xQuad core Intel(R) Xeon(R) CPU E5405 @ 2.00GHz)
    RAM: 32GB (16x2GB)
    Interface is running bond mode 6.

    Current,
    /etc/my.cnf
    Code:
    # The following options will be passed to all MySQL clients
    [client]
    #password       = your_password
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    
    # Here follows entries for some specific programs
    
    # The MySQL server
    [mysqld]
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    skip-external-locking
    key_buffer_size = 768M
    max_allowed_packet = 64M
    table_open_cache = 5096
    sort_buffer_size = 4M
    read_buffer_size = 4M
    read_rnd_buffer_size = 16M
    myisam_sort_buffer_size = 128M
    thread_cache_size = 16
    query_cache_size = 128M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8
    max_connections = 1500
    log-slow-queries
    local-infile=0
    innodb_file_per_table=1
    
    server-id       = 1
    
    open_files_limit=46924
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    
    [myisamchk]
    key_buffer_size = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout
    
    Server used as webservice for domains, apache, mysql, mail.
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    run mysqltuner and show the results
     
  3. Smaily

    Smaily Well-Known Member

    Joined:
    Sep 19, 2011
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Code:
    root@ser-ver [~]# cd /usr/local/cpanel/3rdparty/mysqltuner/
    root@ser-ver [/usr/local/cpanel/3rdparty/mysqltuner]# ./mysqltuner.pl
    Name "XML::Simple::PREFERRED_PARSER" used only once: possible typo at ./mysqltun                                                                                        er.pl line 148.
    
     >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 72M (Tables: 480)
    [--] Data in InnoDB tables: 27M (Tables: 383)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 2M (Tables: 8)
    [!!] Total fragmented tables: 50
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4d 6h 51m 12s (3M q [8.244 qps], 198K conn, TX: 5B, RX: 383M)
    [--] Reads / Writes: 53% / 47%
    [--] Total buffers: 1.0G global + 24.4M per thread (1500 max threads)
    [!!] Maximum possible memory usage: 36.7G (117% of installed RAM)
    [OK] Slow queries: 0% (0/3M)
    [OK] Highest usage of available connections: 0% (10/1500)
    [OK] Key buffer size / total MyISAM indexes: 768.0M/16.1M
    [OK] Key buffer hit rate: 99.8% (2M cached / 4K reads)
    [OK] Query cache efficiency: 76.8% (1M cached / 2M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 128K sorts)
    [!!] Temporary tables created on disk: 41% (14K on disk / 36K total)
    [OK] Thread cache hit rate: 99% (10 created / 198K connections)
    [OK] Table cache hit rate: 36% (966 open / 2K opened)
    [OK] Open file limit used: 2% (1K/46K)
    [OK] Table locks acquired immediately: 99% (935K immediate / 936K locks)
    [OK] InnoDB data size / buffer pool: 28.0M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
    
    root@ser-ver [/usr/local/cpanel/3rdparty/mysqltuner]#
    
     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    its good in general, you can lower some values, becouse of too big max RAM usage in case it fills all connections, so

    max_connections = 300
    sort_buffer_size = 256K
    read_buffer_size = 128K
    read_rnd_buffer_size = 8M

    add this
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1

    this can be removed, not used anymore
    thread_concurrency = 8



    you barely have any queries, 8 qps very small
    [!!] Temporary tables created on disk: 41% (14K on disk / 36K total)
    but those quries are not much optimized, they use temporary tables on disk
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    Smaily Well-Known Member

    Joined:
    Sep 19, 2011
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    With updated suggested configuration,
    Code:
    # ./mysqltuner.pl
    Name "XML::Simple::PREFERRED_PARSER" used only once: possible typo at ./mysqltun                                                                                        er.pl line 148.
    
     >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 83M (Tables: 481)
    [--] Data in InnoDB tables: 124M (Tables: 405)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 248K (Tables: 8)
    [!!] Total fragmented tables: 58
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 4h 42m 7s (1M q [14.171 qps], 83K conn, TX: 2B, RX: 254M)
    [--] Reads / Writes: 56% / 44%
    [--] Total buffers: 1.0G global + 8.8M per thread (300 max threads)
    [OK] Maximum possible memory usage: 3.6G (11% of installed RAM)
    [OK] Slow queries: 0% (0/1M)
    [OK] Highest usage of available connections: 3% (9/300)
    [OK] Key buffer size / total MyISAM indexes: 768.0M/18.0M
    [OK] Key buffer hit rate: 99.8% (1M cached / 2K reads)
    [OK] Query cache efficiency: 75.0% (767K cached / 1M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 82K sorts)
    [!!] Temporary tables created on disk: 44% (6K on disk / 14K total)
    [OK] Thread cache hit rate: 99% (9 created / 83K connections)
    [OK] Table cache hit rate: 86% (977 open / 1K opened)
    [OK] Open file limit used: 2% (1K/46K)
    [OK] Table locks acquired immediately: 99% (486K immediate / 486K locks)
    [OK] InnoDB data size / buffer pool: 124.7M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
    
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    good, you can add there also

    innodb_buffer_pool_size = 1G
    innodb_flush_log_at_trx_commit = 2


    since your innodb data is growing

    about temporary tables created on disk, you would have to check and optimize queries that use temp tables on disk
     
Loading...

Share This Page