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 with tuning mySQL

Discussion in 'Workarounds and Optimization' started by Simon Lidster, Sep 25, 2014.

  1. Simon Lidster

    Simon Lidster Registered

    Joined:
    Jul 8, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Coomera, Queensland, Australia, Australia
    cPanel Access Level:
    Root Administrator
    Hi,

    I'm interested in tuning MySQL.

    I used MySQL tuner which came back with recommendations, although I believe it's not that reliable?

    I then ran through Percona config wizard which gave me a suggested my.cnf.

    I don't know much about MySQL, so could someone please check out the suggested settings and let me know they are ok or not?

    Server is VPS 1.5 Gb RAM, 40GB SSD, 2 CPU.

    About 30 websites, mostly low to moderate traffic, all Wordpress.

    Performance is good, but I like things to be run on best practices where possible.

    Code:
    MySQL tuner output - just for info
    
     >>  MySQLTuner 1.3.0 - Major Hayden 
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Logged in using credentials passed on the command line
    [OK] Currently running supported MySQL version 5.5.37-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 156M (Tables: 1078)
    [--] Data in InnoDB tables: 151M (Tables: 1181)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 106
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 1h 54m 24s (2M q [29.045 qps], 22K conn, TX: 9B, RX: 453M)
    [--] Reads / Writes: 93% / 7%
    [--] Total buffers: 81.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 496.2M (33% of installed RAM)
    [OK] Slow queries: 0% (52/2M)
    [OK] Highest usage of available connections: 19% (30/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/22.5M
    [OK] Key buffer hit rate: 98.7% (18M cached / 235K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 471K sorts)
    [!!] Joins performed without indexes: 1086
    [!!] Temporary tables created on disk: 38% (229K on disk / 595K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (400 open / 1M opened)
    [OK] Open file limit used: 10% (520/5K)
    [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
    [!!] InnoDB  buffer pool / data size: 41.0M/151.2M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 400)
        innodb_buffer_pool_size (>= 151M)
    
    Code:
    
    Percona suggestions - these are the ones I'm interested in implementing.
    
    # Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
    # Configuration name webhost generated for slidster@shadowpro.com.au at 2014-09-25 07:10:03
    
    [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        = 1G
    
    # 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
  3. Simon Lidster

    Simon Lidster Registered

    Joined:
    Jul 8, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Coomera, Queensland, Australia, Australia
    cPanel Access Level:
    Root Administrator
    Contents of my.cnf
    Code:
    [mysqld]
    innodb_file_per_table=1
    max_allowed_packet=268435456
    open_files_limit=5000
    innodb_buffer_pool_size=42991616
    
     
  4. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    I suggest that you increase the buffer pool value like the Percona Configuration Wizard report suggested. But I'm not sure if it's ok to use the 1GB value. It want hurt now, but it could hurt later on if your server has use for it but not enough RAM. So I suggest that you start with a much lower value of 256 MB or 512 MB. If you add more RAM to your server than you can increase this value further.

    You can use the suggested value for the key buffer size of 32MB
    You can also use the suggested value for the table open cache of 4096

    I suggest that you enable thread cache but I'm not sure if it's safe or recommendable to start with the Percona Configuration Wizard suggested value of 50. You could start with a much lower value or like 4 or 8.

    Watch this and see how this performs.

    Code:
    table-open-cache=4096
    innodb_buffer_pool_size=256M
    key_buffer_size=32M
    thread_cache_size=4
    table-open-cache=4096
     
    #4 Archmactrix, Sep 26, 2014
    Last edited: Sep 26, 2014
  5. Simon Lidster

    Simon Lidster Registered

    Joined:
    Jul 8, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Coomera, Queensland, Australia, Australia
    cPanel Access Level:
    Root Administrator
    Archmactrix, thanks very much for this.

    I'll check the performance in a week.
     
Loading...

Share This Page