mysql 셋팅 좀 도와주세요
본문
-------- Performance Metrics -------------------------------------------------
[--] Up for: 29s (1K q [39.931 qps], 55 conn, TX: 904K, RX: 87K)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 1.4G global + 22.4M per thread (1000 max threads)
[OK] Maximum possible memory usage: 23.3G (74% of installed RAM)
[OK] Slow queries: 1% (14/1K)
[OK] Highest usage of available connections: 2% (21/1000)
[OK] Key buffer size / total MyISAM indexes: 384.0M/33.6M
[!!] Key buffer hit rate: 89.1% (8K cached / 959 reads)
[!!] Query cache efficiency: 3.7% (34 cached / 909 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 391 sorts)
[OK] Temporary tables created on disk: 3% (13 on disk / 406 total)
[OK] Thread cache hit rate: 61% (21 created / 55 connections)
[OK] Table cache hit rate: 84% (33 open / 39 opened)
[OK] Open file limit used: 0% (44/5K)
[OK] Table locks acquired immediately: 99% (892 immediate / 895 locks)
[OK] InnoDB buffer pool / data size: 1000.0M/369.3M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
query_cache_limit (> 16M, or use smaller result sets)
[!!] Key buffer hit rate: 89.1% (8K cached / 959 reads)
[!!] Query cache efficiency: 3.7% (34 cached / 909 selects)
이 두개가 셋팅을 아무리 다르게 해도 해결되지 않고 있는데.
셋팅 조언 좀 부탁드립니다.
메모리는 32GB입니다.
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
skip-bdb
max_connections = 1000
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 1024
query_cache_size = 32M
query_cache_limit = 16M
sort_buffer_size = 10M
read_rnd_buffer_size = 10M
thread_cache_size = 20
innodb_buffer_pool_size = 1000M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_concurrency = 16
skip-federated
log-bin=mysql-bin
server-id = 1
character-set-client-handshake=FALSE
init_connect="SET collation_connection = utf8_general_ci"
init_connect="SET NAMES utf8"
default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci
log-slow-queries = ******
long_query_time = 1