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 300k

Discussion in 'Workarounds and Optimization' started by eliko, Dec 18, 2014.

  1. eliko

    eliko Registered

    Joined:
    Jun 11, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hey Everyone

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

    Stats:
    VPS
    24 cores
    4GB ram

    Below are the results from mysqltuner:

    Code:
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 5G (Tables: 162)
    [--] Data in InnoDB tables: 1G (Tables: 186)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [!!] Total fragmented tables: 23
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 11h 44m 58s (17M q [83.298 qps], 571K conn, TX: 53B, RX: 1B)
    [--] Reads / Writes: 81% / 19%
    [--] Total buffers: 1.4G global + 1.1M per thread (250 max threads)
    [OK] Maximum possible memory usage: 1.6G (41% of installed RAM)
    [OK] Slow queries: 0% (385/17M)
    [OK] Highest usage of available connections: 15% (38/250)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/19.4M
    [OK] Key buffer hit rate: 100.0% (90M cached / 9K reads)
    [OK] Query cache efficiency: 36.1% (5M cached / 14M selects)
    [!!] Query cache prunes per day: 299228
    [OK] Sorts requiring temporary tables: 4% (71K temp sorts / 1M sorts)
    [OK] Temporary tables created on disk: 2% (4K on disk / 173K total)
    [OK] Thread cache hit rate: 99% (94 created / 571K connections)
    [OK] Table cache hit rate: 41% (523 open / 1K opened)
    [OK] Open file limit used: 2% (415/20K)
    [OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)
    [OK] InnoDB buffer pool / data size: 1.1G/1.0G
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increasing the query_cache size over 128M may reduce performance
    Variables to adjust:
        query_cache_size (> 160M) [see warning above]

    Please advice?

    - - - Updated - - -

    The my.cnf :

    Code:
    [mysqld]
    slow-query-log=1
    long_query_time=1
    long_query_time=4
    default_storage_engine=InnoDB
    key_buffer_size=32M
    query_cache_type=1
    query_cache_size=160M
    query_cache_limit=2M
    max_allowed_packet=268435456
    
    max_connections=250
    table_open_cache=10000
    
    innodb_flush_method=O_DIRECT
    innodb_buffer_pool_size=1152M
    innodb_log_buffer_size=16M
    innodb_log_file_size=128M
    
    max_heap_table_size=32M
    tmp_table_size=64M
    
    connect_timeout=30
    max_connect_errors=1000
    open_files_limit=10000
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,814
    Likes Received:
    672
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. eliko

    eliko Registered

    Joined:
    Jun 11, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Query cache prunes per day 300K is ok ?
    Any suggestions to optimize the performance?
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,814
    Likes Received:
    672
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Note that you may need to post the results on another forum such as WHT if you don't receive enough user-feedback on our forums here.

    Thank you.
     
Loading...

Share This Page