MySQL performance optimization parameters

This article mainly introduces the actual application parameters in MySQL performance optimization. If you are crazy about the actual application parameters in MySQL performance optimization, do not miss the following articles. I believe the following articles will provide you with more comprehensive knowledge.

Description of MySQL installation parameters in linux

MySQL database server optimization details

Discuss MySQL variables and Optimization

Recommendation circle: D Language

More recommended Java code

1) back_log:

The number of connections required for MySQL performance optimization. When the main MySQL thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread.

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. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid.

When you observe the process list of your host and find a large number of 264084 | unauthenticated user | xxx. xxx. xxx. xxx | NULL | Connect | NULL | login | when a NULL process is to be connected, increase the value of back_log. The default value is 50. I will change it to 500.

(2) interactive_timeout:

The number of seconds that the server waits for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the CLIENT_INTERACTIVE option for MySQL_real_connect. The default value is 28800. I will change it to 7200.

(3), key_buffer_size:

The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for index blocks. You can increase the size of indexes that can be better processed (for all reads and multi-Rewrite) so that you can afford that much. If you make it too large, the system will begin to change pages and it will really slow down. The default value is 8388600 (8 M). My MySQL performance optimization host has 2 GB of memory, so I changed it to 402649088 (400 MB ).

(4) max_connections:

Number of customers allowed simultaneously. Increase the number of file descriptors required by MySQLd. This number should be added. Otherwise, you will often see the Too connector connections error. The default value is 100. I will change it to 1024.

(5), record_buffer:

Each thread that performs an ordered scan allocates a buffer of this size to each table it scans. If you perform many sequential scans, you may want to increase the value. The default value is 131072 (128 K). I changed it to 16773120 (16 M)

(6) sort_buffer:

Each thread that needs to be sorted allocates a buffer of this size. Add this value to accelerate the order by or group by operation. The default value is 2097144 (2 M). I changed it to 16777208 (16 M ).

(7), table_cache:

Number of tables opened for all threads. Adding this value can increase the number of file descriptors required by MySQL. MySQL requires two file descriptors for each unique opened table. The default value is 64. I changed it to 512.

(8), thread_cache_size:

The number of threads that can be reused. If yes, the new thread is obtained from the cache. If there is space when the connection is disconnected, the customer's thread is placed in the cache. If there are many new threads, this variable value can be used to improve MySQL performance optimization. By comparing variables in Connections and Threads_created states, you can see the role of this variable. I set it to 80.

(10), wait_timeout:

The number of seconds that the server waits for action on a connection before closing it. The default value is 28800. I will change it to 7200.

Note: you can modify the parameters by modifying the/etc/my. cnf file and restarting MySQL. This is a relatively cautious job. The above results are just some of my views. You can further modify them based on your host's Hardware situation, especially the memory size. Asp? Id = 482 "width = 1 border = 0>

1) back_log:

The number of connections that MySQL can have. When the main MySQL thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread.

The back_log value indicates the number of requests that can be stored in the stack within a short period of time before MySQL performance optimization temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid.

When you observe the process list of your host and find a large number of 264084 | unauthenticated user | xxx. xxx. xxx. xxx | NULL | Connect | NULL | login | when a NULL process is to be connected, increase the value of back_log. The default value is 50. I will change it to 500.

(2) interactive_timeout:

The number of seconds that the server waits for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the CLIENT_INTERACTIVE option for MySQL_real_connect. The default value is 28800. I will change it to 7200.

(3), key_buffer_size:

The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for index blocks. You can increase the size of indexes that can be better processed (for all reads and multi-Rewrite) so that you can afford that much. If you make it too large, the system will begin to change pages and it will really slow down. The default value is 8388600 (8 M). My MySQL host has 2 GB of memory, so I changed it to 402649088 (400 MB ).

(4) max_connections:

Number of customers allowed simultaneously. Add this value to increase the number of file descriptors required by MySQL performance optimization. This number should be added. Otherwise, you will often see the Too connector connections error. The default value is 100. I will change it to 1024.

(5), record_buffer:

Each thread that performs an ordered scan allocates a buffer of this size to each table it scans. If you perform many sequential scans, you may want to increase the value. The default value is 131072 (128 K). I changed it to 16773120 (16 M)

(6) sort_buffer:

MySQL master-slaveMySQL replication settings in windows

Set up MySQL replication and control slave thread

MySQL master-slave backup

Recommendation circle: Database circle

For more information, we recommend that you install MySQL

1. First install two MySQL performance optimizations, that is, copy a MySQL (MySQL5.1 and MySQL5.2)

2. Modify the port number and installation address (my. ini). Here we do not use the default port 3306 for main tests.

Xml Code

 
 

  
  
MySQL5.1   
  
  
[client]   
  
  
port=3307   
  
  
[MySQLd]   
  
  
port=3307   
  
  
basedir="E:/MySQL5.1/"   
  
  
datadir="E:/MySQL5.1/Data/"   
  
  
MySQL5.2   
  
  
[client]   
  
  
port=3308   
  
  
[MySQLd]   
  
  
port=3308   
  
  
basedir="E:/MySQL5.2/"   
  
  
datadir="E:/MySQL5.2/Data/"   
  
  
MySQL5.1  
  
  
[client]  
  
  
port=3307 
  
  
[MySQLd]  
  
  
port=3307 
  
  
basedir="E:/MySQL5.1/" 
  
  
datadir="E:/MySQL5.1/Data/" 
  
  
MySQL5.2  
  
  
[client]  
  
  
port=3308 
  
  
[MySQLd]  
  
  
port=3308 
  
  
basedir="E:/MySQL5.2/" 
  
  
datadir="E:/MySQL5.2/Data/" 
 
 

3. Create a backup account in the Master (MySQL5.1) database. The command is as follows:

SQL code

 
 

  
  
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'localhost' IDENTIFIED BY '123456';   
  
  
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'localhost' IDENTIFIED BY '123456';  
 
 

4. Modify Master to MySQL5.1 as follows (my. ini ):

Xml Code

 
 

  
  
[MySQLd]   
  
  
Master start  
 
 

Log output addresses are mainly used for synchronization.

 
 

  
  
log-bin=E:\MySQL5.1\Data\log-bin.log  
 
 

Synchronize Databases

 
 

  
  
binlog-do-db=cnb  
 
 

The host id cannot be the same as the slave id.

 
 

  
  
server-id=1   
  
  
Master end   
  
  
[MySQLd]  
  
  
Master start  
 
 

Log output addresses are mainly used for synchronization.

 
 

  
  
log-bin=E:\MySQL5.1\Data\log-bin.log 
 
 

Synchronize Databases

 
 

  
  
binlog-do-db=cnb 
 
 

The host id cannot be the same as the slave id.

 
 

  
  
server-id=1   
  
  
Master end 
 
 

5. Modify Slave for performance optimization as follows (my. ini ):

The above content is an introduction to the MySQL performance optimization part. I hope you will get some benefits.