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 mysqltuner configuration my mysql.

Discussion in 'Workarounds and Optimization' started by Bidi, Nov 17, 2015.

  1. Bidi

    Bidi Well-Known Member

    Joined:
    Oct 3, 2012
    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Romania, Transilvania
    cPanel Access Level:
    DataCenter Provider
    Hy guys, i`m having a small problem with my server, thinking is because of cloudlinux so i contact them, and they sugest me to use mysqltunner to optimize my mysql.

    So here is.

    Code:
    root@d1 [~]# ps -eo comm,rss|awk '{arr[$1]+=$2} END {for (i in arr) {print arr[i]/1024, i}}'|grep -v '^0 '|sort -n -r| head
    3910.73 mysqld
    306.254 clamd
    155.438 httpd
    153.207 python
    140.176 sc_trans
    135.598 php
    122.176 nginx
    105.793 named
    71.1016 /usr/local/cpan
    55.3398 python2.7
    My my.conf
    Code:
    [mysqld]
    slow-query-log=1
    long-query-time=1
    slow-query-log-file="/var/lib/mysql/slow.log"
    local-infile=0
    
    max_connections=200
    max_user_connections=50
    
    join_buffer_size=1M
    read_rnd_buffer_size=1M
    max_allowed_packet=314572800
    
    table_open_cache = 7500
    thread_cache_size = 25
    
    query_cache_type = 1
    query_cache_size = 128M
    query_cache_limit = 2M
    
    tmp_table_size=50M
    max_heap_table_size=50M
    
    #tmpdir = "/home/mysqltmp"
    
    open_files_limit=39000
    
    key_buffer_size = 500M
    myisam_sort_buffer_size = 256M
    
    innodb_file_per_table=1
    innodb_buffer_pool_size = 1G
    #innodb_log_file_size = 64M
    innodb_log_buffer_size = 8M
    default-storage-engine=MyISAM
    
    And infos from mysqltunner
    Code:
    root@d1 [~]# ./mysqltuner.pl
    
    >>  MySQLTuner 1.3.0 mod - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
    >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.6.27-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 3G (Tables: 12005)
    [--] Data in InnoDB tables: 873M (Tables: 4847)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in MEMORY tables: 0B (Tables: 39)
    [!!] Total fragmented tables: 1293
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 21d 10h 46m 55s (460M q [248.527 qps], 12M conn, TX: 4745B, RX: 69B)
    [--] Reads / Writes: 77% / 23%
    [--] Total buffers: 1.7G global + 2.6M per thread (200 max threads)
    [OK] Maximum possible memory usage: 2.2G (7% of installed RAM)
    [OK] Slow queries: 0% (54K/460M)
    [OK] Highest usage of available connections: 81% (162/200)
    [OK] Key buffer size / total MyISAM indexes: 500.0M/833.5M
    [OK] Key buffer hit rate: 100.0% (56B cached / 9M reads)
    [OK] Query cache efficiency: 74.4% (285M cached / 384M selects)
    [!!] Query cache prunes per day: 1417163
    [OK] Sorts requiring temporary tables: 0% (227K temp sorts / 28M sorts)
    [!!] Joins performed without indexes: 165274 (see join_buffer_size note below)
    [!!] Temporary tables created on disk: 37% (7M on disk / 19M total)
    [OK] Thread cache hit rate: 99% (7K created / 12M connections)
    [!!] Table cache hit rate: 0% (7K open / 2M opened)
    [OK] Open file limit used: 28% (11K/39K)
    [OK] Table locks acquired immediately: 99% (145M immediate / 145M locks)
    [OK] InnoDB buffer pool / data size: 1.0G/873.5M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes. Please note this
        calculation is made by adding Select_full_join + Select_range_check
        status values and triggered when the total >250
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        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 (> 128M)
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 50M, increase tmp_table_size)
        max_heap_table_size (> 50M, increase max_heap_table_size)
        table_cache (> 7500, table_open_cache hit rate <20%)
    
    root@d1 [~]#
    
    Please give me a hand .

    Thank you.
     
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,447
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    What problem is it that you're having, you don't mention it.
     
  3. Bidi

    Bidi Well-Known Member

    Joined:
    Oct 3, 2012
    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Romania, Transilvania
    cPanel Access Level:
    DataCenter Provider
    I had some issue, like when an acount was hiting the ClouxLinux limits (Package) all the server started to slow down but verry slow, whm,cpanel ...etc

    The guys from ClouxLinux respond about mysql problem and because on some packages i set the cpu limit to 56%, and they told me to run mysqltunner.

    my server config Intel Xeon 2 x CPU 2.63Ghz, 12 Cores, 24 Threads, 32gb ram.....
     
    #3 Bidi, Nov 17, 2015
    Last edited: Nov 17, 2015
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    You can modify/add values to the /etc/my.cnf file based on the "Variables to adjust" from the MySQL tuner. Let MySQL run for at least 24 hours after the change and then run the tuner again to see if additional adjustments are recommended.

    Thank you.
     
Loading...

Share This Page