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.

mysqltuner result help/info Table cache hit rate: 0%

Discussion in 'Workarounds and Optimization' started by Schwibby, Jun 12, 2013.

  1. Schwibby

    Schwibby Member

    Joined:
    Apr 4, 2013
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    hello ! i've read rthe forum and see the mysql video but not understand very well....

    can i ask if my.cnf is ok for my server ? i've shared hosting server 6gb ram 6vcpu xeon e5504 2.2ghz

    this is my.cnf

    Code:
    root@cp1 [/usr/local/cpanel/3rdparty/mysqltuner]# cat /etc/my.cnf
    [mysqld]
    set-variable = max_connections=200
    query_cache_type=1
    query_cache_size=64M
    query_cache_limit=1M
    sort_buffer_size=2M
    read_rnd_buffer_size=512K
    tmp_table_size=64M
    max_heap_table_size=64M
    thread_cache_size=24
    key_buffer_size=16M
    table_cache=192
    join_buffer_size=2M
    log-slow-queries=/var/lib/mysql/slow.log
    innodb_buffer_pool_size=80M
    innodb_file_per_table=1
    open_files_limit=13460
    max_allowed_packet=128M
    
    and this the result of mysql tuner

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.69-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 461M (Tables: 2032)
    [--] Data in InnoDB tables: 69M (Tables: 239)
    [!!] Total fragmented tables: 119
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 0h 32m 26s (7M q [82.111 qps], 118K conn, TX: 25B, RX: 1B)
    [--] Reads / Writes: 86% / 14%
    [--] Total buffers: 226.0M global + 4.9M per thread (200 max threads)
    [OK] Maximum possible memory usage: 1.2G (20% of installed RAM)
    [OK] Slow queries: 0% (2/7M)
    [OK] Highest usage of available connections: 10% (20/200)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/130.2M
    [OK] Key buffer hit rate: 99.3% (131M cached / 917K reads)
    [OK] Query cache efficiency: 82.3% (5M cached / 6M selects)
    [!!] Query cache prunes per day: 474342
    [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 183K sorts)
    [!!] Joins performed without indexes: 1211
    [!!] Temporary tables created on disk: 33% (75K on disk / 222K total)
    [OK] Thread cache hit rate: 99% (20 created / 118K connections)
    [!!] Table cache hit rate: 0% (192 open / 1M opened)
    [OK] Open file limit used: 2% (367/13K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 69.1M/80.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        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
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 2.0M, or always use indexes with joins)
        tmp_table_size (> 64M)
        max_heap_table_size (> 64M)
        table_cache (> 192)
    
    yesterday i've adjust setting but the result are the same... can i modyfy add or delete something ??

    thanks
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Code:
    [mysqld]
    
    wait_timeout = 30
    connect_timeout = 1
    local-infile=0
    
    open_files_limit=20000
    
    max_connections=200
    
    key_buffer_size = 1G
    max_allowed_packet=20M
    
    query_cache_size=30M
    query_cache_limit=1M
    tmp_table_size=50M
    max_heap_table_size=50M
    thread_cache_size=50
    
    table_open_cache = 3000
    table_definition_cache = 2000
    
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes
    
    sort_buffer_size=256K
    join_buffer_size=2M
    read_rnd_buffer_size=4M
    
    max_write_lock_count = 10
    concurrent_insert=2
    
    
    innodb_buffer_pool_size=200M
    innodb_file_per_table=1
    innodb_buffer_pool_instances = 4
    innodb_flush_log_at_trx_commit = 2
    
    

    table cache hit rate 0% means that your script is creating many temp tables and you need to review your queries

    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

    or for old slow file
    ./pt-query-digest /var/lib/mysql/slow.log > slow.txt
     
  3. Schwibby

    Schwibby Member

    Joined:
    Apr 4, 2013
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    hello thinkbot,

    first of all thank you very much for the quick response.

    the code you have posted is to replace the mine to give to make a test?

    the pt-query-digest is a tool to test the slow query ?
     
  4. thinkbot

    thinkbot Well-Known Member

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

    pt-query-digest is to review slow queries
     
Loading...

Share This Page