MySQL optimization steps and my.cnf cache optimization

1, look at the machine configuration, refers to three piece: CPU, memory, hard disk

2, look at the MySQL configuration parameters

3, check the MySQL line state, you can use the Mysqlreport tool to view

4, view the MySQL slow query

After solving the above problem in sequence, we can find the problem of the program.

MY.CNF Cache Optimization

Add/Modify the following options in MY.CNF:

#取消文件系统的外部锁

Skip-locking

#不进行域名反解析, pay attention to the permissions/authorization issues that arise

Skip-name-resolve

#索引缓存, depending on the size of the memory, if it is a stand-alone DB server, you can set up to 80% of the total memory

Key_buffer = 512M

#连接排队列表总数

Back_log = 200

Max_allowed_packet = 2M

#打开表缓存总数, you can avoid the overhead of frequently opening data tables

Table_cache = 512

#每个线程排序所需的缓冲

Sort_buffer_size = 4M

#每个线程读取索引所需的缓冲

Read_buffer_size = 4M

#MyISAM表发生变化时重新排序所需的缓冲

Myisam_sort_buffer_size = 64M

#缓存可重用的线程数

Thread_cache = 128

#查询结果缓存

Query_cache_size = 128M

#设置超时时间, can avoid long connection

Set-variable = wait_timeout=60

#最大并发线程数, the number of CPUs *2

Thread_concurrency = 4

#记录慢查询, and then on slow query one by one optimization

Log-slow-queries = Slow.log

Long_query_time = 1

#关闭不需要的表类型, don't add this if you need it.

Skip-innodb

Skip-bdb