My. cnf cache optimization _ MySQL

Source: Internet
Author: User
Tags mysql tutorial
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.