Query cache prunes per day 300k

[eliko]

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:

-------- 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 :

[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

 

[cPanelMichael]

Hello

Could you elaborate on the specific issue you are facing with MySQL? Or, do you simply want to optimize the performance?

Thank you.

 

[eliko]

Query cache prunes per day 300K is ok ?
Any suggestions to optimize the performance?

 

[cPanelMichael]

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.