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.

Need advice on how to handle high quantity of query cache prunes

Discussion in 'Workarounds and Optimization' started by Dan12345, Feb 20, 2013.

  1. Dan12345

    Dan12345 Registered

    Joined:
    Feb 20, 2013
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello!

    Could somebody suggest a correct configuration for my.cnf. Currently I run two stores - Zen Cart and one wordpress websites, which I believe is not much but I get a lot of cache prunes. I understand that stores use a lot of different queries but hoped it could be managed.

    My server:
    8 - processors, Intel(R) Xeon(R) CPU E31230 @ 3.20GHz
    Ram - 8GB

    My cnf:
    Code:
    [mysqld]
    innodb_buffer_pool_size=32M
    join_buffer_size=3M
    key_buffer_size=64M
    local-infile=1
    log_slow_queries=/var/log/mysql-slow.log
    max_allowed_packet=64M
    max_heap_table_size=128M
    myisam_sort_buffer_size=64M
    open_files_limit=3072
    query_cache_limit=2M
    query_cache_min_res_unit=4096
    query_cache_size=192M
    read_buffer_size=1M
    read_rnd_buffer_size=1M
    safe-show-database
    set-variable = max_connections=40
    sort_buffer_size=1M
    table_cache=512
    thread_cache_size=128
    tmp_table_size=200M
    wait_timeout=100
    interactive_timeout=400
    query_cache_type=1
    
    [isamchk]
    key_buffer_size = 96M
    sort_buffer_size = 96M
    read_buffer_size = 16M
    write_buffer_size = 16M
    
    [myisamchk]
    key_buffer_size = 96M
    sort_buffer_size = 96M
    read_buffer_size = 16M
    write_buffer_size = 16M
    
    And mysqltuner results:

    Code:
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 103M (Tables: 398)
    [--] Data in InnoDB tables: 9M (Tables: 81)
    [!!] Total fragmented tables: 112
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4h 36m 31s (2M q [161.047 qps], 9K conn, TX: 2B, RX: 485M)
    [--] Reads / Writes: 87% / 13%
    [--] Total buffers: 418.0M global + 6.2M per thread (40 max threads)
    [OK] Maximum possible memory usage: 668.0M (8% of installed RAM)
    [OK] Slow queries: 0% (0/2M)
    [OK] Highest usage of available connections: 12% (5/40)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/24.5M
    [OK] Key buffer hit rate: 99.9% (3M cached / 3K reads)
    [OK] Query cache efficiency: 89.3% (2M cached / 2M selects)
    [!!] Query cache prunes per day: 65428
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 15K sorts)
    [!!] Joins performed without indexes: 84
    [OK] Temporary tables created on disk: 20% (2K on disk / 13K total)
    [OK] Thread cache hit rate: 99% (5 created / 9K connections)
    [OK] Table cache hit rate: 56% (512 open / 900 opened)
    [OK] Open file limit used: 27% (852/3K)
    [OK] Table locks acquired immediately: 99% (345K immediate / 345K locks)
    [OK] InnoDB data size / buffer pool: 9.4M/32.0M
    
    And that's only after 4 hours at night when most of my visitors aren't there yet if I wait for 24 it gets up to 1mil or more.

    Any advices are welcome)

    Thank you,
    Dan!
     
Loading...

Share This Page