) MySQL optimization settings

(Linux parameters)

Open the/etc/My. CNF file and modify the following settings. If not, add it manually. When adjusting the settings, please do your best, which depends on the configuration of your server, especially the memory size. The following settings are suitable for servers with 1 GB of memory, but are not absolute.

# Specify the size of the index buffer, which determines the index processing speed, especially the index read speed. Check the status values key_read_requests and key_reads to check whether the key_buffer_size setting is reasonable. The ratio of key_reads/key_read_requests should be as low as possible, at least and (the above status values can be obtained using show status like 'key _ reads ). Key_buffer_size only applies to the MyISAM table. This value is used even if you do not use the MyISAM table, but the internal temporary disk table is a MyISAM table. You can use the check status value created_tmp_disk_tables to learn the details.
Key_buffer = 384 m

# 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 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. The default value is 50.
Back_log = 200

# Maximum size of a package. The message buffer is initialized to net_buffer_length, but can be added to max_allowed_packet as needed. By default, if this value is too small, a large (possibly incorrect) package will be captured. If you are using a large blob column, you must add this value. It should be as big as the largest blob you want to use.
Max_allowed_packet = 4 m

# Number of customers allowed at the same time. Increase the number of file descriptors required by mysqld. This number should be added. Otherwise, you will often see too many links. Please contact the space provider for errors. The default value is 100.
Max_connections= 1024

# Specify the table cache size. When MySQL accesses a table, if there is space in the table buffer, the table is opened and put into it, so that the table content can be accessed more quickly. Check the status values open_tables and opened_tables of the peak time to determine whether to addTable_cache. If you find that open_tables is equalTable_cacheAnd opened_tables is growing, so you need to addTable_cache(The above status values can be obtained using show status like 'open _ tables ). Note that you cannot blindlyTable_cacheSet it to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure.
Table_cache= 512

# Buffer required for sorting by each thread
Sort_buffer_size = 4 m

# When a query continuously scans a table, MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of the buffer. If you think continuous scanning is too slow, you can increase the performance by increasing the variable value and memory buffer size.
Read_buffer_size = 4 m

# Accelerate the read data after the sorting operation and increase the speed of reading the classified rows. If you are performing the group by or order by operation on a table that is far greater than the available memory, you should increase the value of read_rnd_buffer_size to accelerate row reading after the sorting operation. I still don't understand the usefulness of this option ......
Read_rnd_buffer_size = 8 m

# Used for repair table. Do not understand the usefulness of this option, Baidu also found a wide range of settings, 128 M, 64 m, 32 m, etc., choose one.
Myisam_sort_buffer_size = 64 m

# Number of threads that can be reused and saved in. 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 performance. By comparing variables in connections and threads_created states, you can see the role of this variable.
Thread _ cache_size = 128

# Cache query results. When a SELECT statement is executed for the first time, the server remembers the text content of the query and the returned results. When the server encounters this statement next time, it will not execute this statement again. Instead, it directly retrieves results from the query cache and returns the results to the client.
Query_cache_size = 32 m

# Maximum number of concurrent threads, CPU x 2
Thread_concurrency = 2

# Set the timeout time to avoid persistent connections
Wait_timeout = 120

# Disable unnecessary table types. Do not add this type if necessary.
Skip-InnoDB
Skip-bdb