When more and more websites use MySQL database, the pressure will be greater and greater, so how to decompress MySQL database? That is optimization! There are three ways to optimize a single MySQL. They are: 1. Optimization of server physical hardware; Second, compile and optimize MySQL installation; Third, the optimization of my own configuration file my.cnf 1. Optimization of physical hardware of server 1. Disk seek ability (disk I/O) is one of the biggest factors that restrict MySQL performance. It is recommended to use RAID 1+0 disk array, and it is best not to try RAID-5 because MySQL is not very efficient on RAID-5 disk array. 2.CPU is also very important. For MySQL applications, it is recommended to use DELL R7 10, E5620 @2.40GHz(4 cores) * 2 or something with similar processing power. 3, physical memory, physical memory For a database server using MySQL, the server memory is recommended to be not less than 2GB, and it is recommended to use more than 4GB of physical memory. Secondly, it is suggested to optimize MySQL installation by compiling and installing, so that the performance can be greatly improved. 64-bit Centos5.5 is recommended for the server system. By default, the compilation parameters of the source package will generate the binary code in Debgu mode, and the performance loss brought by Debug mode to MySQL is relatively large, so when compiling the product code to be installed, don't forget to use the "-—without-Debug" parameter to disable Debug mode. If the two compilation parameters -with-mysqld-LD flags and -with-client-LD flags are set to -all-static, you can tell the compiler to compile statically and compile the generated code for the highest performance. Compared with dynamically compiled code, the performance gap using static compilation may be as high as 5% to 10%. Third, the optimization of its own configuration file my.cnf After solving the above server hardware constraints, let's take a look at how MySQL's own optimization is carried out. The optimization of MySQL itself is mainly to optimize and adjust the parameters in its configuration file my.cnf Let's introduce some parameters that have great influence on performance. Let's explain it according to the above hardware configuration combined with an optimized my.cnf: #vim /etc/my.cnf Only the contents of the [mysqld] section in the my.cnf file are listed below, and the contents of other sections have little influence on the running performance of MySQL, so we ignore them. [mysqld]port = 3306 serverid = 1 socket =/tmp/MySQL。 Sockskip-locking # avoids external locking of MySQL, reduces error probability and enhances stability. Skip-name-resolve# prohibits MySQL from resolving external connections. Using this option can reduce the time of MySQL to DNS resolution. However, it should be noted that if this option is enabled, all remote host connection authorizations must use IP addresses, otherwise MySQL will not be able to handle connection requests normally! The value of the back_log = 384#back_log parameter indicates how many requests can be stored in the stack in a short time before MySQL temporarily stops responding to new requests. If the system has many connections in a short time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have their own restrictions on the size of this queue. Attempting to set back_log to exceed the limit of the operating system will be invalid. The default value is 50. Linux system recommends setting it to an integer less than 5 12. Key _ buffer _ size = 384m # Key _ buffer _ size specifies the size of the buffer used for indexing, and increasing it can obtain better index processing performance. For servers with about 4GB of memory, this parameter can be set to 256M or 384M. Note: Setting this parameter value too large will reduce the overall efficiency of the server! Max _ allowed _ packet = 4m thread _ stack = 256k table _ cache = 614ksort _ buffer _ size = 6m # Buffer size available for query sorting. Note: The allocated memory corresponding to this parameter is dedicated to each connection. If there are 100 connections, the total * * * sort buffer size actually allocated is 100 × 6 = 600MB. Therefore, for servers with about 4GB of memory, it is recommended to set it to 6-8M. Read_buffer_size = 4M# The size of the buffer available for read query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection. Join_buffer_size = 8M# Buffer size available for joint query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection. Myisam _ sort _ buffer _ size = 64m table _ cache = 512thread _ cache _ size = 64query _ cache _ size = 64m # Specifies the size of MySQL query buffer. You can observe from the MySQL console that if the value of Qcache_lowmem_prunes is large, it means that the buffer is often insufficient; If the value of Qcache_hits is very large, it indicates that the query buffer is used very frequently. If the value is small, it will affect the efficiency, and you can consider not using the query buffer. Qcache_free_blocks, if this value is large, it means that there are many fragments in the buffer. Tmp _ table _ size = 256max _ connections = 768 # Specifies the maximum number of connection processes allowed by MySQL. If too many connections are often mentioned incorrectly when visiting the forum, you need to increase the value of this parameter. Max _ connect _ errors =1000 wait _ timeout =10 # specifies the maximum connection time requested, which can be set to 5- 10 for servers with about 4GB of memory. Thread_concurrency = 8# The value of this parameter is the logical CPU number of the server *2. In this case, the server has two physical CPUs, and each physical CPU supports H.T. Hyper-threading, so the actual value is 4 * 2 = 8; This is also the current dual quad-core mainstream server configuration. Skip-networking# Turn this option on to completely turn off the TCP/IP connection mode of MySQL. If the WEB server accesses the MySQL database server through a remote connection, do not turn this option on! Otherwise, you will not be able to connect normally! Table_cache= 1024# The larger the physical memory, the larger the setting. The default value is 2402. Adjust it to 5 12- 1024, and the best innodb _ additional _ mem _ pool _ size = 4m # defaults to 2m innodb _ flush _ log _ at _ Trx _ commit =1#. Setting it to 0 means to wait until the rows of innodb_log_buffer_size are full before unified storage. The default value is1innodb _ log _ buffer _ size = 2m #. The default value is1innodb _ thread _ concurrency = 8 #. You can set it to have as many CPUs as there are in the server. It is recommended to use the default value of 8key_buffer_size=256M#. The default value is 2 18. Adjust it to 128, and the best tmp_table_size=64M# defaults to 16M. Set it to 64-256, and read_buffer_size=4M# defaults to 64kread _ rnd _ buffer _ size =16m # defaults to 256kport _ buffer _ size = 32m # defaults to 256kthread _ cache _ size =120 #. 1. If Key_reads is too large, you should add Key_buffer_size to my.cnf, and keep Key_reads/Key_read_requests at least1100, and the smaller the better. 2. If Qcache_lowmem_prunes is very large, please increase the value of Query_cache_size. Performance optimization through parameter setting can improve performance more or less, but the effect may not be outstanding.