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.

Table cache hit rate 0%

Discussion in 'Workarounds and Optimization' started by and12345, Mar 8, 2014.

  1. and12345

    and12345 Registered

    Joined:
    Jan 19, 2013
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello,

    I'm new to mysql world and recently installed a new server with these specs:

    CPU Intel(R) Xeon(R) CPU X3440 @ 2.53GHz
    8 GB RAM
    500 GB HDD's in Software RAID 1

    Centos 6 + Cpanel

    Hosting around 20 accounts, out of which probably 15 are using mysql
    The websites don't get a lot of traffic. As an example, last month the server had around 250 GB of traffic usage.


    I've used Percona's my.cnf tool which worked out ok, apart for some fine tuning which I did with Mysqltuner and Tuning Primer .

    This is my current my.cnf:

    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/server.mygms.net.pid
    
    # MyISAM #
    key-buffer-size                = 128M
    myisam-recover-options         = 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               = 8M
    max-heap-table-size            = 32M
    query-cache-type               = 0
    query-cache-size               = 8M
    query_cache_limit              = 1M
    query_cache_type               = 1
    max-connections                = 300
    thread-cache-size              = 64
    open-files-limit               = 65535
    table-definition-cache         = 4096
    table-open-cache               = 5000
    table_cache                    = 64
    join_buffer                    = 1M
    
    # 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        = 2G
    
    # LOGGING #
    log-error                      = /var/lib/mysql/server.mygms.net.err
    log-queries-not-using-indexes  = 1
    slow-query-log                 = 1
    slow-query-log-file            = /var/lib/mysql/server-slow.log
    
    Code:
     >>  MySQLTuner 1.3.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
    [OK] Currently running supported MySQL version 5.5.35-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 451M (Tables: 3051)
    [--] Data in InnoDB tables: 1G (Tables: 520)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 31
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 17m 14s (5K q [5.605 qps], 227 conn, TX: 2M, RX: 460K)
    [--] Reads / Writes: 92% / 8%
    [--] Total buffers: 2.2G global + 3.6M per thread (300 max threads)
    [OK] Maximum possible memory usage: 3.2G (42% of installed RAM)
    [OK] Slow queries: 4% (248/5K)
    [OK] Highest usage of available connections: 1% (4/300)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/111.0M
    [!!] Key buffer hit rate: 15.5% (90K cached / 76K reads)
    [OK] Query cache efficiency: 50.4% (750 cached / 1K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 123 sorts)
    [!!] Joins performed without indexes: 13
    [OK] Temporary tables created on disk: 13% (164 on disk / 1K total)
    [OK] Thread cache hit rate: 98% (4 created / 227 connections)
    [!!] Table cache hit rate: 0% (64 open / 157K opened)
    [OK] Open file limit used: 0% (21/20K)
    [OK] Table locks acquired immediately: 100% (5K immediate / 5K locks)
    [OK] InnoDB buffer pool / data size: 2.0G/1.4G
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        join_buffer_size (> 1.0M, or always use indexes with joins)
        table_cache (> 64)
    

    Does anyone have any idea on how to fix this ? Besides that, should I be looking at anything else?

    Thank you.
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    you must run it at least for 24h to get reliable information,
    your mysql instance run for 17m before mysqltuner
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,668
    Likes Received:
    646
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  4. and12345

    and12345 Registered

    Joined:
    Jan 19, 2013
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi this is the latest result

    Code:
     >>  MySQLTuner 1.3.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
    [OK] Currently running supported MySQL version 5.5.35-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 475M (Tables: 3051)
    [--] Data in InnoDB tables: 1G (Tables: 529)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 46
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 17h 9m 5s (5M q [18.404 qps], 81K conn, TX: 5B, RX: 694M)
    [--] Reads / Writes: 90% / 10%
    [--] Total buffers: 2.2G global + 3.1M per thread (50 max threads)
    [OK] Maximum possible memory usage: 2.3G (30% of installed RAM)
    [!!] Slow queries: 6% (379K/5M)
    [OK] Highest usage of available connections: 46% (23/50)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/116.1M
    [OK] Key buffer hit rate: 99.5% (126M cached / 688K reads)
    [OK] Query cache efficiency: 82.1% (4M cached / 5M selects)
    [!!] Query cache prunes per day: 181730
    [OK] Sorts requiring temporary tables: 0% (675 temp sorts / 367K sorts)
    [!!] Joins performed without indexes: 3102
    [OK] Temporary tables created on disk: 14% (29K on disk / 208K total)
    [OK] Thread cache hit rate: 99% (23 created / 81K connections)
    [!!] Table cache hit rate: 0% (64 open / 7M opened)
    [OK] Open file limit used: 0% (121/65K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB buffer pool / data size: 2.0G/1.4G
    [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
        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 (> 512.0K, or always use indexes with joins)
        table_cache (> 64)
    
     
  5. and12345

    and12345 Registered

    Joined:
    Jan 19, 2013
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Can anybody please help me to figure this out :(
     
  6. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    remove this table_cache = 64

    and you can also increase query_cache_size to 50M

    and clean double repeated vars like:
    # CACHES AND LIMITS #
    tmp-table-size = 32M
    max-heap-table-size = 32M
    query-cache-type = 0
    query-cache-size = 8M
    max-heap-table-size = 32M
    query-cache-type = 0
    query-cache-size = 8M
    query_cache_limit = 1M
    query_cache_type = 1
    to this

    # CACHES AND LIMITS #
    tmp-table-size = 32M
    max-heap-table-size = 32M
    query-cache-size = 50M
    query_cache_limit = 1M
    query_cache_type = 1

    and restart mysql

    table_cache and this
    table-open-cache = 5000

    are the same, table-open-cache is new naming
    first you set the same value to 5000, then to 64, 5000 gets overwritten
    so you need to delete table_cache line

    you got over 3000 MyISAM tables, so 5000 is good number
     
Loading...

Share This Page