有台服务器ContOS+nginx+mysql。运行截图如下Tasks: 254 total, 1 running, 253 sleeping, 0 stopped, 0 zombie
Cpu(s): 2.3%us, 0.3%sy, 0.0%ni, 96.6%id, 0.8%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8150256k total, 8081236k used, 69020k free, 564908k buffers现在的问题是,总体上流畅,只在进行搜索时变慢,多个搜索并发时比较卡。搜索语句已进行过优化,请高手帮忙优化mysql
my.cnf如下:[client]
port = 3306
socket = /tmp/mysql.sock[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
net_buffer_length = 8K
sql-mode=""
connect_timeout=15
interactive_timeout=100
join_buffer_size=1M
key_buffer=256M
max_allowed_packet=16M
max_connections=4000
max_connect_errors=10
myisam_sort_buffer_size=64M
read_buffer_size=1M
read_rnd_buffer_size=768K
sort_buffer_size=8M
table_cache=1024
thread_cache_size=100
thread_concurrency=8
wait_timeout=100
query_cache_size=64M
query_cache_limit=1M
query_cache_type=1
tmp_table_size = 400M
#max_heap_table_size = 64Mlong_query_time = 5
log-slow-queries = /home/slow.log
log-queries-not-using-indexesskip-name-resolvelog-bin=mysql-binserver-id = 1
[mysqldump]
quick
max_allowed_packet = 16M[mysql]
no-auto-rehash[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M[mysqlhotcopy]
interactive-timeout
Cpu(s): 2.3%us, 0.3%sy, 0.0%ni, 96.6%id, 0.8%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8150256k total, 8081236k used, 69020k free, 564908k buffers现在的问题是,总体上流畅,只在进行搜索时变慢,多个搜索并发时比较卡。搜索语句已进行过优化,请高手帮忙优化mysql
my.cnf如下:[client]
port = 3306
socket = /tmp/mysql.sock[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
net_buffer_length = 8K
sql-mode=""
connect_timeout=15
interactive_timeout=100
join_buffer_size=1M
key_buffer=256M
max_allowed_packet=16M
max_connections=4000
max_connect_errors=10
myisam_sort_buffer_size=64M
read_buffer_size=1M
read_rnd_buffer_size=768K
sort_buffer_size=8M
table_cache=1024
thread_cache_size=100
thread_concurrency=8
wait_timeout=100
query_cache_size=64M
query_cache_limit=1M
query_cache_type=1
tmp_table_size = 400M
#max_heap_table_size = 64Mlong_query_time = 5
log-slow-queries = /home/slow.log
log-queries-not-using-indexesskip-name-resolvelog-bin=mysql-binserver-id = 1
[mysqldump]
quick
max_allowed_packet = 16M[mysql]
no-auto-rehash[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M[mysqlhotcopy]
interactive-timeout
好的,先设置key_buffer_size=2048M,重启了mysql还有需要优化的吗
http://blog.csdn.net/naola2001/article/details/7740615
还有什么参数需要优化?修改后的参数是:[client]
port = 3306
socket = /tmp/mysql.sock[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
net_buffer_length = 8K
sql-mode=""
connect_timeout=15
interactive_timeout=100
join_buffer_size=1M
key_buffer=256M
max_allowed_packet=16M
max_connections=4000
max_connect_errors=10
myisam_sort_buffer_size=64M
read_buffer_size=1M
read_rnd_buffer_size=8M #768K
sort_buffer_size=8M
table_cache=1024
thread_cache_size=64 #100
thread_concurrency=8
wait_timeout=100
query_cache_size=32M #64M
query_cache_limit=1M
query_cache_type=1
tmp_table_size = 400M
#max_heap_table_size = 64Mlong_query_time = 5
log-slow-queries = /home/data/slow.log
#log-queries-not-using-indexesskip-name-resolvekey_buffer_size=2048M #20121121table_open_cache = 512 #20121121log-bin=mysql-binserver-id = 1
[mysqldump]
quick
max_allowed_packet = 16M[mysql]
no-auto-rehash[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M[myisamchk]
#key_buffer = 20M
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M[mysqlhotcopy]
interactive-timeout
另外,注意监控最大并发连接数。
long_query_time = 1
log-slow-queries = 如果并发比较多,考虑INNODB引擎
key_buffer_size现在是 1/4, 观察下运行2小时后,最大并发连接数17
现在是访问高峰,最大并发连接数61。另外几个参数是:
| Variable_name | Value |
+-------------------+------------+
| Key_read_requests | 2131316624 |
| Key_reads | 424871 |
key_cache_miss_rate约为0.019%。 感谢rucypli和iihero对key_buffer_size的建议。
| Variable_name | Value |
+-------------------+---------+
| Key_blocks_unused | 1665280 |
| Key_blocks_used | 49819 |
还有缓存还有余地
另几个参数是:
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 57 |
| Threads_connected | 4 |
| Threads_created | 61 |
| Threads_running | 7 | 昨天也优化两处代码,多表联合查询和延迟更新点击数,上午高峰没有卡住。不过mysql进程一直保持很高的CPU占用