The optimization example of MySQL is explained in detail in Linux

The MySQL optimization example is explained in detail in Linux

vim /etc/ my.cnf lists only the contents of the [mysqld] section of the ES9en.cnf file below. The contents of the other sections have little impact on MySQL's performance and are therefore ignored.

[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking

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

skip-name-resolve

Disable MySQL for DNS parsing of external connections. Use this option to eliminate the time it takes MySQL to parse DNS. However, it is important to note that if this option is enabled, all remote host connection authorizations use the IP address, otherwise MySQL will not be able to handle connection requests properly!

back_log = 384

The value of the back_log parameter indicates how many requests can be stored on the stack in the 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 limits on the size of this queue. Attempting to set back_log above your operating system limit will not work. The default value is 50. An integer less than 512 is recommended for Linux systems.

key_buffer_size = 256M

key_buffer_size specifies the size of the buffer used for the index, increasing it for better index processing performance. This parameter can be set to 256M or 384M for servers with memory around 4GB. Note: setting this parameter too high 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 querying sort. Note: The allocated memory for this parameter is per connection exclusive. If there are 100 connections, then the total allocated sort buffer size is 100 × 6 = 600MB. Therefore, 6-8ES51en is recommended for servers with memory around 4GB.

read_buffer_size = 4M

The size of the buffer that can be used by read query operations. As with sort_buffer_size1, the allocated memory for this parameter is also reserved per connection.

join_buffer_size = 8M

The size of the buffer used by the federated query operation is similar to sort_buffer_size1, and the allocated memory for this parameter is also reserved 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. You can observe from the MySQL console that 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. If the value is small, it will affect efficiency, then you can consider not using query buffering. Qcache_free_blocks. If this value is very large, then there is a lot of fragmentation in the buffer.

tmp_table_size = 256M
max_connections = 768

Specifies the maximum number of connections allowed by MySQL. If the error message from Too ManyConnections is frequent when accessing the forum, you need to increase this parameter value.

max_connect_errors = 10000000
wait_timeout = 10

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

skip-name-resolve

0

This parameter is the number *2 of server logic CPU. In this case, the server has two physical CPU, and each physical CPU supports H. T hyperthread, so the actual value is 4*2=8

skip-name-resolve

1

Turn this option on to completely turn off the TCP/IP connection of MySQL. Do not turn this option on if the WEB server is accessing the MySQL database server as a remote connection! Otherwise the connection will not work!

skip-name-resolve

2

The larger the physical memory, the larger the Settings will be. The default is 2402, and the best range is 512-1024

innodb_additional_mem_pool_size=4M

The default value is 2 M

skip-name-resolve

4

To set 0 is to wait until innodb_log_buffer_size queue is full before storing 1. The default is 1

skip-name-resolve

5

The default value is 1 M

skip-name-resolve

6

Your server CPU has a few on set to a few, the recommended default is 1 general to 8

skip-name-resolve

7

It is worth noting that:

Many cases need to be analyzed on a case-by-case basis

1. If Key_reads is too large, make Key_buffer_size in ES137en.cnf larger, keeping Key_reads/ es144EN_ES146en at least 1/100 above, the smaller the better.

2. If Qcache_lowmem_prunes is large, increase the value of Query_cache_size.

Thank you for reading, I hope to help you, thank you for your support to this site!