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.

Query cache prunes per day 50k and cache hit rate 0%

Discussion in 'Workarounds and Optimization' started by josuablirup, Mar 21, 2013.

  1. josuablirup

    josuablirup Registered

    Joined:
    Feb 8, 2013
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hey Everyone

    We currently have an issue with the settings in our MySQL database:

    Stats:
    4 cores
    16GB ram

    Below are the results from mysqltuner:

    [OK] Currently running supported MySQL version 5.5.30-cll
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 626M (Tables: 919)
    [--] Data in InnoDB tables: 360M (Tables: 2342)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 1M (Tables: 75)
    [!!] Total fragmented tables: 79

    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 14h 35m 41s (7M q [31.747 qps], 68K conn, TX: 30B, RX: 1B)
    [--] Reads / Writes: 51% / 49%
    [--] Total buffers: 8.7G global + 3.2M per thread (650 max threads)
    [OK] Maximum possible memory usage: 10.8G (69% of installed RAM)
    [OK] Slow queries: 0% (3/7M)
    [OK] Highest usage of available connections: 3% (20/650)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/53.1M
    [OK] Key buffer hit rate: 100.0% (38M cached / 17K reads)
    [OK] Query cache efficiency: 89.7% (5M cached / 6M selects)
    [!!] Query cache prunes per day: 51806
    [OK] Sorts requiring temporary tables: 0% (20 temp sorts / 75K sorts)
    [!!] Joins performed without indexes: 9480
    [OK] Temporary tables created on disk: 13% (34K on disk / 258K total)
    [OK] Thread cache hit rate: 99% (20 created / 68K connections)
    [!!] Table cache hit rate: 0% (3K open / 2M opened)
    [OK] Open file limit used: 12% (2K/16K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 360.6M/8.0G

    -------- 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
    Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    table_cache (> 8000)

    The my.cnf looks like this:

    [mysqld]
    default_storage_engine=InnoDB
    innodb_file_per_table=1
    local-infile=0
    tmpdir =/dev/shm
    max_connections = 650
    max_user_connections=300
    key_buffer_size = 512M
    myisam_sort_buffer_size = 64M
    log-slow-queries
    innodb_file_per_table=1
    key_buffer_size = 512M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 256K
    table_cache = 8000 #was 4000
    thread_cache_size = 384
    wait_timeout = 120 #was 20
    connect_timeout = 120 #was 10
    tmp_table_size = 64M
    max_heap_table_size = 64M
    max_allowed_packet = 64M
    net_buffer_length = 16384
    max_connect_errors = 10
    thread_concurrency = 8
    concurrent_insert = 2
    read_rnd_buffer_size = 786432
    bulk_insert_buffer_size = 8M
    query_cache_limit = 7M
    query_cache_size = 128M #was 64M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    max_write_lock_count = 16
    innodb_buffer_pool_size=8G #75 % of RAM
    innodb_flush_log_at_trx_commit=2
    innodb_thread_concurrency=4 # of cpus
    innodb_flush_method=O_DIRECT
    innodb_additional_mem_pool_size=20M
    innodb_log_file_size = 256M
    innodb_log_buffer_size = 16M

    [mysqld_safe]
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer = 384M
    sort_buffer = 384M
    read_buffer = 256M
    write_buffer = 256M

    Please advice?
     
  2. arunsv84

    arunsv84 Well-Known Member

    Joined:
    Oct 20, 2008
    Messages:
    373
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    127.0.0.1
    cPanel Access Level:
    Root Administrator
    Your Maximum possible memory usage is already 69% of installed RAM. So I wont recommend increasing the value. "query_cache_size" is the amount of RAM MySQL can use to cache the results of database queries. Results stored in the query cache are returned much faster than normal selects, so this variable can significantly speed things up. I would recommend you to keep this setting. But If you have enough free memory, it's safe to increase query_cache size.

    Cheers!!!
     
Loading...

Share This Page