Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "the optimization skills of MySQL under CentOS system". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

(1) the influence of server hardware on MySQL performance

A) disk seek capability (disk Icano). Take the current high-speed SCSI hard disk (7200 rpm / s) as an example, this kind of hard disk theoretically seeks 7200 times per second, which is determined by physical characteristics and cannot be changed. MySQL does a lot of complex query operations every second, so you can imagine the amount of reading and writing to the disk. Therefore, it is generally regarded as one of the factors that restrict the performance of MySQL, for Discuz with an average daily traffic of more than 1 million PV! Forum, due to the restriction of disk Imax O, the performance of MySQL will be very poor! To address this constraint in CentOS systems, consider the following solutions: use RAID-0+1 disk arrays and be careful not to try to use RAID-5,MySQL. The efficiency on RAID-5 disk arrays will not be as fast as you expect.

Abandon traditional hard drives and use faster flash storage devices. Pass the Discuz! According to the test of the company's technical engineering, the speed of using flash storage device is about 6-10 times higher than that of traditional hard disk.

B) CPU for MySQL applications, S.M.P is recommended. The multiplex symmetrical CPU of the architecture, for example, two Intel Xeon 3.6GHz CPU can be used.

C) physical memory for a Database Server using MySQL, it is recommended that the server memory is not less than 2GB, and physical memory above 4GB is recommended.

(2) MySQL's own factors after solving the above server hardware constraints, let's take a look at how MySQL's own optimization works.

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 a great impact on performance.

Because the optimized settings of the my.cnf file are closely related to the server hardware configuration, we specify a hypothetical server hardware environment:

CPU: 2 Intel Xeon 2.4GHz

Memory: 4GB DDR

Hard disk: SCSI 73GB

Next, we explain according to the above hardware configuration combined with an optimized my.cnf: # vi / etc/my.cnf

The following lists only the contents of the [mysqld] paragraph in the my.cnf file, and the contents of other paragraphs have little impact on the performance of MySQL, so ignore it for the time being.

[mysqld]

Port = 3306

Serverid = 1

Socket = / tmp/mysql.sock

Skip-locking

Avoid external locking of MySQL, reduce the probability of error and enhance stability.

Skip-name-resolve

Disable DNS parsing of external connections by MySQL, which eliminates the time it takes for MySQL to parse DNS. It should be noted, however, that if this option is turned on, all remote host connection authorizations must use IP addresses, otherwise MySQL will not be able to handle connection requests properly!

Back_log = 384

Specifies the number of possible connections to the MySQL. When the MySQL main thread receives a lot of connection requests in a very short period of time, this parameter takes effect, and the main thread takes a short time to check the connection and start a new thread. The value of the back_log parameter indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops responding to new requests. If the CentOS system has many connections in a short period of 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 limits on the queue size. Trying to set the limit that back_log is higher than your CentOS system will not be valid. The default value is 50. It is recommended to set an integer less than 512 for Linux systems.

Key_buffer_size = 256m

Key_buffer_size specifies the size of the buffer used for indexing, and increasing it results in better index processing performance. For servers with memory around 4GB, this parameter can be set to 256m or 384m. Note: the excessive setting of this parameter value will reduce the overall efficiency of the server!

Max_allowed_packet = 4m

Thread_stack = 256K

Table_cache = 128K

Sort_buffer_size = 6m

The size of the buffer that can be used when sorting queries. Note: the allocated memory corresponding to this parameter is exclusive per connection! If there are 100 connections, the total sort buffer size actually allocated is 100x6 = 600MB. Therefore, it is recommended to set it to 6-8m for servers with about 4GB memory.

Read_buffer_size = 4m

The size of the buffer that can be used by read query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!

Join_buffer_size = 8m

The size of the buffer that can be used by the federated query operation is the same as that of sort_buffer_size, and the allocated memory corresponding to this parameter is exclusive per connection!

Myisam_sort_buffer_size = 64m

Table_cache = 512

Thread_cache_size = 64

Query_cache_size = 64m

Specifies the size of the MySQL query buffer for the CentOS system. You can observe by executing the following command in the MySQL console:

> SHOW VARIABLES LIKE'% query_cache%'

> SHOW STATUS LIKE 'Qcache%'

If the value of Qcache_lowmem_prunes is very large, it indicates that insufficient buffering often occurs.

If the value of Qcache_hits is very large, it indicates that query buffering is used very frequently, and if the value is small and affects efficiency, then consider not query buffering; Qcache_free_blocks, if the value is very large, it indicates that there is a lot of fragmentation in the buffer.

Tmp_table_size = 256m

Max_connections = 768

Specifies the number of * connection processes allowed by MySQL. If there are frequent Too Many Connections errors when visiting the forum, you need to increase the value of this parameter.

Max_connect_errors = 10000000

Wait_timeout = 10

Specify the * * connection time for a request, which can be set to 5-10 for servers with memory around 4GB.

Thread_concurrency = 8

The value of this parameter is the number of server logical CPU × 2. In this example, the server has two physical CPU, and each physical CPU supports H.T hyperthreading, so the actual value is 4 × 2 = 8skip-networking.

Turn on this option to completely turn off the TCP/IP connection mode of MySQL. Do not turn on this option if the CentOS system WEB server accesses the MySQL database server by remote connection. Otherwise, you will not be able to connect properly!

This is the end of the content of "Optimization skills of MySQL under CentOS system". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.