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 requested optimizing MySQL

Discussion in 'Workarounds and Optimization' started by Ebridge, Apr 11, 2014.

  1. Ebridge

    Ebridge Member

    Joined:
    May 3, 2012
    Messages:
    16
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    Requesting help optimizing my.conf

    Code:
     >>  MySQLTuner 1.2.0 - 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.6.16-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 14M (Tables: 76)
    [--] Data in InnoDB tables: 33M (Tables: 424)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [!!] Total fragmented tables: 25
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 14h 9m 42s (310K q [6.089 qps], 3K conn, TX: 1B, RX: 165M)
    [--] Reads / Writes: 93% / 7%
    [--] Total buffers: 680.0M global + 1.1M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.2G (32% of installed RAM)
    [!!] Slow queries: 8% (25K/310K)
    [OK] Highest usage of available connections: 1% (8/500)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/2.5M
    [OK] Key buffer hit rate: 98.8% (27K cached / 321 reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 23K sorts)
    [OK] Temporary tables created on disk: 14% (3K on disk / 27K total)
    [OK] Thread cache hit rate: 99% (8 created / 3K connections)
    [!!] Table cache hit rate: 0% (756 open / 99K opened)
    [OK] Open file limit used: 2% (217/8K)
    [OK] Table locks acquired immediately: 100% (378K immediate / 378K locks)
    [OK] InnoDB data size / buffer pool: 34.0M/600.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        table_cache (> 4096)
    Note: MySQL didn't run for 24 hours, but it did run a full working day in this timezone (weekend ahead with 0 activity at all)

    my.conf:

    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                = 32M
    myisam-recover                 = FORCE,BACKUP
    
    # SAFETY #
    max-allowed-packet             = 16M
    max-connect-errors             = 1000000
    
    # 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               = 0
    query-cache-size               = 0
    max-connections                = 500
    thread-cache-size              = 50
    open-files-limit               = 65535
    table-definition-cache         = 4096
    table-open-cache               = 4096
    
    # 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        = 600M
    
    # 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
    
    open_files_limit=7472
    
    Server: VPS with 4GB ram, 2 cores, CentOS running some small Drupal projects (traffic is expected to increase tenfold in the comings months however)

    I didn't follow the mysqltuner recommendations this time as I had much better results asking in this forum for help ;)
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,830
    Likes Received:
    672
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    It still might be a good idea to let MySQL run for 24 hours before running the tuner. Feel free to post the tuner results again at the 24-hour mark if you have not received any user feedback by then.

    Thank you.
     
  3. Ebridge

    Ebridge Member

    Joined:
    May 3, 2012
    Messages:
    16
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    Thanks for your reply Michael! Here you are...

    Code:
     >>  MySQLTuner 1.2.0 - 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.6.16-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 14M (Tables: 76)
    [--] Data in InnoDB tables: 39M (Tables: 424)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [!!] Total fragmented tables: 27
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 11h 34m 23s (407K q [3.180 qps], 6K conn, TX: 2B, RX: 198M)
    [--] Reads / Writes: 93% / 7%
    [--] Total buffers: 680.0M global + 1.1M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.2G (32% of installed RAM)
    [!!] Slow queries: 8% (34K/407K)
    [OK] Highest usage of available connections: 1% (8/500)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/2.5M
    [OK] Key buffer hit rate: 99.4% (58K cached / 376 reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 30K sorts)
    [OK] Temporary tables created on disk: 14% (9K on disk / 66K total)
    [OK] Thread cache hit rate: 99% (8 created / 6K connections)
    [!!] Table cache hit rate: 0% (767 open / 249K opened)
    [OK] Open file limit used: 2% (218/8K)
    [OK] Table locks acquired immediately: 99% (489K immediate / 489K locks)
    [OK] InnoDB data size / buffer pool: 39.6M/600.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        table_cache (> 4096)
    
     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    adjust

    query-cache-type = 1
    query-cache-size = 50M
     
  5. Ebridge

    Ebridge Member

    Joined:
    May 3, 2012
    Messages:
    16
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    Thanks :)

    Any chance of reducing the slow queries some more? Other recommendations?

    Code:
     >>  MySQLTuner 1.2.0 - 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.6.16-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 13M (Tables: 76)
    [--] Data in InnoDB tables: 62M (Tables: 579)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [!!] Total fragmented tables: 35
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 20h 18m 14s (192K q [1.208 qps], 6K conn, TX: 968M, RX: 317M)
    [--] Reads / Writes: 62% / 38%
    [--] Total buffers: 730.0M global + 1.1M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.3G (33% of installed RAM)
    [OK] Slow queries: 2% (4K/192K)
    [OK] Highest usage of available connections: 1% (9/500)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/2.1M
    [OK] Key buffer hit rate: 99.1% (66K cached / 586 reads)
    [OK] Query cache efficiency: 63.5% (90K cached / 141K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
    [OK] Temporary tables created on disk: 15% (12K on disk / 81K total)
    [OK] Thread cache hit rate: 99% (9 created / 6K connections)
    [!!] Table cache hit rate: 0% (1K open / 330K opened)
    [OK] Open file limit used: 2% (219/8K)
    [OK] Table locks acquired immediately: 99% (82K immediate / 82K locks)
    [OK] InnoDB data size / buffer pool: 62.9M/600.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        table_cache (> 4096)
    
     
  6. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    check your slow log and review the queries
     
Loading...

Share This Page