MY.CNF configuration files and parameters for MySQL

[Mysqld]
Port = 3306
ServerID = 1
Socket =/tmp/mysql. sock

skip-locking Avoid the external locking of MySQL and reduce the chance of error to enhance stability.

Skip-name-resolve
Disable MySQL for DNS resolution of external connections, and Use this option to eliminate the time for DNS resolution for MySQL .

Note that if you turn on this option, all remote host connection authorizations use IP address mode, otherwise MySQL will not be able to handle connection requests properly!

Back_log = 384 Specifies the number of possible connections for MySQL . When the MySQL main thread receives very many connection requests in a very short 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 present in the stack for a short period of time before MySQL temporarily stops responding to a new request. If the 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 its own limitations on this queue size.
Attempting to set a limit of back_log above your operating system will be invalid. The default value is 50. For Linux systems, the recommended setting is an integer less than 512.

Key_buffer_size = 256M
# KEY_BUFFER_SIZE Specifies the buffer size to use for the index, increasing it to get better index processing performance.
The parameter can be set to 256M or 384M for a server that has around 4GB.
Note: This parameter value setting is too large to be the overall efficiency of the server down!

Max_allowed_packet = 4M
Thread_stack = 256K
Table_cache = 128K

Sort_buffer_size = 6M
The size of the buffer that can be used when the query is sorted. Note: This parameter corresponds to the allocated memory that is per connection exclusive! If there are 100 connections, the actual allocated total sort buffer size is 100x6 = 600MB. Therefore, the recommended setting for a server that has around 4GB is 6-8m.

Read_buffer_size = 4M
The size of the buffer that can be used by the read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!

Join_buffer_size = 8M
The size of the buffer that the Federated query operation can use, like sort_buffer_size, which allocates memory for each connection alone!

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 this by executing the following commands in the MySQL console:
Code: # > SHOW VARIABLES like '%query_cache% ';
# > Show status Like ' qcache% '; if the value of Qcache_lowmem_prunes is very large, it indicates that buffering is often insufficient;
If the value of qcache_hits is very large, it indicates that the query buffer is used very frequently, and if the value is smaller it will affect efficiency, then you can consider not querying the buffer, qcache_free_blocks, if the value is very large, it indicates that there are many fragments in the buffer.

Tmp_table_size = 256M

Max_connections = 768
Specifies the maximum number of connection processes allowed by MySQL . If the too many connections error is frequently encountered when accessing the forum, you need to increase the parameter value.

Max_connect_errors = 10000000

Wait_timeout = 10
Specifies the maximum connection time for a request, which can be set to 5-10 for a server with about 4GB of memory.

Thread_concurrency = 8
The parameter value is the number of server logical CPUs x2, in this case, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4x2 = 8

Skip-networking
Turn on this option to completely turn off MySQL 's TCP/IP connection and do not turn on this option if the Web server is accessing the MySQL database server remotely. Otherwise it will not connect properly!

MY.CNF configuration files and parameters for MySQL