Parameter configuration for MySQL Performance Optimization

Parameter configuration for MySQL Performance Optimization

1. Purpose:

Modify the MySQL System Parameters Based on the current status of the server to reasonably utilize the existing resources of the server and improve the MySQL performance.

2. Server parameters:

32 GB memory, 4 CPUs, each with 8 cores.

3. Current MySQL installation status.

Currently, MySQL is installed with the maximum configuration supported by MySQL by default. Copy the my-huge.cnf. Code has been modified to UTF-8. Specific modification and installation of MySQL, you can refer to the <Linux system to install MySQL 5.5> help documentation.

4. Modify MySQL Configuration

Open MySQL configuration file my. cnf

Vi/etc/my. cnf
 

4.1 Introduction and modification of MySQL non-Cache parameter variables

4.1.1 modify the back_log parameter value from the default value of 50 to 500 (125 KB for each connection, occupied: MB)

Back_log = 500

The back_log value indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops answering new requests. That is to say, if the connection data of MySql reaches max_connections, the new request will be stored in the stack to wait for a connection to release the resource. The number of stacks is back_log, if the number of connections waiting exceeds back_log, connection resources are not granted. The following message is displayed: unauthenticated user | xxx. xxx | NULL | Connect | NULL | login | NULL to be connected to the process.

The back_log value cannot exceed the size of the listening queue connected by TCP/IP. If the number of listeners exceeds, the system will not be able to query the size of the listening queue of the current system's TCP/IP connection. Command: cat/proc/sys/net/ipv4/tcp_max_syn_backlog the current system is 1024. We recommend that you set the value to an integer smaller than 512 in Linux. Modify system kernel parameters ,)

View the default back_log value of mysql. command:

Show variables like 'back _ log'; view the current quantity
 

4.1.2 change the value of the wait_timeout parameter from 8 hours to 30 minutes by default. (Not required this time)

Wait_timeout = 1800 (unit: Wonderful)

My understanding of the parameter "wait-timeout" is the maximum idle time value for the MySQL client database connection.

To put it bluntly, when your MySQL connection is idle for more than a certain period of time, it will be forcibly closed. The default wait-timeout value of MySQL is 8 hours. You can run the show variables like 'wait _ timeout' command to view the result value ;.

Setting this value is very meaningful. For example, your website has a large number of MySQL connection requests (each MySQL connection requires memory resource overhead ), because of your program, there are a lot of connection requests idle and nothing to do, occupying the memory resources in vain, or, as a result, MySQL exceeds the maximum number of connections and cannot create a new connection, resulting in a "Too connections" error. You can check the status of your MYSQL before setting it. If you often find that there are a large number of Sleep processes in MYSQL, You need to modify the wait-timeout value.

Interactive_timeout: the number of seconds before the server closes the interactive connection. Interactive clients are defined as clients that use the CLIENT_INTERACTIVE option in mysql_real_connect.

Wait_timeout: the number of seconds before the server closes a non-interactive connection. When a thread starts, the wait_timeout value of the session is initialized based on the global wait_timeout value or the global interactive_timeout value, depending on the client type (defined by the CLIENT_INTERACTIVE connection option of mysql_real_connect ).

These two parameters must be used together. Otherwise, wait_timeout settings are invalid.

4.1.3 modify the max_connections parameter value from the default value of 151 to 3000 (750 M ).

Max_connections = 3000

Max_connections refers to the maximum number of connections of MySql. If the number of concurrent connection requests on the server is large, we recommend that you increase this value to increase the number of parallel connections. Of course, this is based on the support of the machine, because the higher the number of connections, MySql will provide a connection buffer for each connection, the higher the memory overhead will be. Therefore, you must adjust this value appropriately and do not blindly increase the setting value. You can use the 'conn' % 'wildcard to view the number of connections in the current status to determine the value size.

The maximum number of connections allowed by the MySQL server is 16384;

View the current maximum number of connections in the system:

Show variables like 'max _ connections ';

800. 4. Modify max_user_connections from the default value 0.

Max_user_connection= 800

Max_user_connections indicates the maximum connection of each database user.

Maximum number of concurrent connections of all clients in an account to the MYSQL service in parallel. Simply put, the maximum number of connections that a single account can connect to the mysql service at the same time. 0 indicates no restriction.

The default value is 0.

Max_used_connections: it refers to the maximum number of concurrent connections from this mysql service to the present. It is not the current connection, but a comparison value. If, at a certain time in the past, the MYSQL service had 1000 requests connected at the same time, and there were no such large concurrent requests, Max_used_connections = 1000. note the differences with max_user_connections in show variables. The default value is 0, indicating an infinite number.

View max_user_connections values

Show variables like 'max _ user_connections ';

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

 

For more details, please continue to read the highlights on the next page:

1 2 3 Next Page