My. cnf cache optimization _ MySQL

My. cnf cache optimization tips: MYSQL Tutorial: my. cnf cache optimization.

1. check the machine configuration. The three main parts are cpu, memory, and hard disk.
2. check mysql configuration parameters.
3. check the mysql row status. you can use the mysqlreport tool to view the status.
4. View mysql slow queries
After solving the preceding problems in sequence, you can find the program problems.

My. cnf cache optimization
Add/modify the following options in my. cnf:

# Cancelling the external lock of the file system
Skip-locking

# Do not perform domain name anti-resolution, pay attention to the resulting permission/authorization issues
Skip-name-resolve

# Index cache, depending on the memory size. if it is an independent db server, you can set up to 80% of the total memory.
Key_buffer = 512 M

# Total number of connection queues
Back_log = 200
Max_allowed_packet = 2 M

# The total number of opened table caches to avoid overhead caused by frequent opening of data tables
Table_cache = 512

# Buffer required for sorting by each thread
Sort_buffer_size = 4 M

# Buffer required by each thread to read the index
Read_buffer_size = 4 M

# Cache required for re-sorting when the MyISAM table changes
Myisam_sort_buffer_size = 64 M

# Number of cache reusable threads
Thread_caching = 128

# Query result cache
Query_cache_size = 128 M

# Set the timeout time to avoid persistent connections
Set-variable = wait_timeout = 60

# Maximum number of concurrent threads, cpu x 2
Thread_concurrency = 4

# Record slow queries, and then optimize slow queries one by one
Log-slow-queries = slow. log
Long_query_time = 1

# Disable unnecessary table types. do not add this type if necessary.
Skip-innodb
Skip-bdb