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:
Please advice?
- - - Updated - - -
The my.cnf :
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