Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to optimize the configuration parameters in MySQL. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Mysql parameter optimization for beginners, it is difficult to understand things, in fact, this parameter optimization, is a very complex thing, for different websites, and its online traffic, visits, posts, network conditions, and machine hardware configuration are related, optimization can not be completed at one time, the need for continuous observation and debugging, it is possible to get the best results.

Let's start with the hardware of my server and the forum.

CPU: 2 quad-core Intel Xeon 2.00GHz

Memory: 4GB DDR

Hard disk: SCSI 146GB

Forum: the number of online members is generally about 5000-the highest record is 13264.

Next, we analyze the above hardware configuration combined with a my.cnf that has been optimized once: some parameters may have to be adjusted according to the changes in the forum and the programmer's program.

[mysqld]

Port = 3306

Serverid = 1

Socket = / tmp/mysql.sock

Skip-locking # avoids the external locking of MySQL, reduces the probability of error and enhances stability. Skip-name-resolve

Disable DNS parsing of external connections by MySQL, which eliminates the time it takes for MySQL to parse DNS. It should be noted, however, that if this option is turned on, all remote host connection authorizations must use IP addresses, otherwise MySQL will not be able to handle connection requests properly!

Back_log = 500,

The number of connections required for MySQL. This works when the main MySQL thread gets a lot of connection requests in a very short period of time, and then the main thread takes some (albeit short) time to check the connection and start a new thread.

The back_ log value indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops answering new requests. Only if you expect 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 listening queue for incoming TCP/IP connections. Your operating system has its own limits on the queue size. Trying to set the limit that back_log is higher than your operating system will not be valid. When you look at your list of host processes and find a large number of 264084 | unauthenticated user | xxx.xxx.xxx.xxx | Connect | NULL | login | NULL processes to be connected, you need to increase the value of back_log. The default value is 50, and I'll change it to 500.

Key_buffer_size = 384m

# key_buffer_size specifies the size of the buffer used for the index, increasing it for indexes that are better handled (for all reads and multiple overrides), as much as you can afford. If you make it too big, the system will start to change pages and really slow down. For servers with about 4GB memory, this parameter can be set to 384m or 512m. By checking the status values Key_read_requests and Key_reads, you can see whether the key_buffer_size setting is reasonable. The key_reads / key_read_requests ratio should be as low as possible, at least 1 SHOW STATUS LIKE 100 and 1 SHOW STATUS LIKE 1000 is better. (the above state values can be obtained using the state value). Note: the excessive setting of this parameter value will reduce the overall efficiency of the server!

Max_allowed_packet = 32m

It is safe to increase the value of this variable because additional memory is allocated only when needed. For example, mysqld allocates more memory only if you issue a long query or if mysqld must return a large result row. The smaller default value for this variable is a precaution to capture error packets between the client and server and to ensure that memory spills are not caused by accidental use of large packets.

Table_cache = 512

Table_cache specifies the size of the table cache. Whenever MySQL accesses a table, if there is space in the table buffer, the table is opened and placed in it, allowing faster access to the table contents. By checking the state values of peak time Open_tables and Opened_tables, you can decide whether you need to increase the value of table_cache. If you find that open_tables equals table_cache and opened_tables is growing, then you need to increase the value of table_cache (the above state values can be obtained using SHOW STATUS LIKE 'Open%tables'). Note that table_cache cannot be set to a large value blindly. If set too high, it may result in insufficient file descriptors, resulting in unstable performance or connection failure.

Sort_buffer_size = 4m

The size of the buffer that can be used when sorting queries. Note: the allocated memory corresponding to this parameter is exclusive per connection! If there are 100 connections, the total sort buffer size actually allocated is 100x4 = 400MB. Therefore, it is recommended to set it to 4-8m for servers with about 4GB memory.

Read_buffer_size = 4m

The size of the buffer that can be used by read query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!

Join_buffer_size = 8m

The size of the buffer that can be used by the federated query operation is the same as that of sort_buffer_size, and the allocated memory corresponding to this parameter is exclusive per connection!

Myisam_sort_buffer_size = 64m

The buffer required to reorder when the MyISAM table changes

Query_cache_size = 64m

Specifies the size of the MySQL query buffer. You can observe by executing the following command in the MySQL console:

# > SHOW VARIABLES LIKE'% query_cache%'; # > SHOW STATUS LIKE 'Qcache%'; # if the value of Qcache_lowmem_prunes is very large, it indicates that the buffer is often insufficient.

If the value of Qcache_hits is very large, it indicates that query buffering is used very frequently, and if the value is small and affects efficiency, then consider not query buffering; Qcache_free_blocks, if the value is very large, it indicates that there is a lot of fragmentation in the buffer.

Thread_cache_size = 64

The number of threads saved in that can be reused. If so, the new thread is fetched from the cache, and the customer's line is placed in the cache if there is space when the connection is disconnected. If there are many new threads, the value of this variable can be used to improve performance. By comparing the variables of Connections and Threads_created states, we can see the role of this variable.

Tmp_table_size = 256m

Max_connections = 1000

Specifies the maximum number of connected processes allowed by MySQL. If there are frequent Too Many Connections errors when visiting the forum, you need to increase the value of this parameter.

Max_connect_errors = 10000000

For the same host, if there is an incorrect connection that exceeds the number of values for this parameter, the host will be disabled from connecting. To unblock the host, execute: FLUSH HOST;.

Wait_timeout = 10

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

Thread_concurrency = 8

The value of this parameter is the number of server logical CPU × 2. In this example, the server has two physical CPU, and each physical CPU supports H.T hyperthreading, so the actual value is 4 × 2 = 8.

Skip-networking

Turn on this option to completely turn off MySQL's TCP/IP connection mode, but do not turn on this option if the WEB server accesses the MySQL database server remotely! Otherwise, you will not be able to connect properly!

Long_query_time = 10

Log-slow-queries =

Log-queries-not-using-indexes

Enable slow log (slow query log)

Slow query logs are useful for tracking problematic queries. It records all queries that have checked long_query_time and, if necessary, records that do not use indexes. Here is an example of a slow query log:

To enable slow log, you need to set parameters log_slow_queries, long_query_times, and log-queries-not-using-indexes.

Log_slow_queries specifies the log file, and if you don't provide a file name, MySQL will generate the default file name itself. Long_query_times specifies the threshold for slow queries, which defaults to 10 seconds. Log-queries-not-using-indexes is a parameter introduced after 4.1.0 that instructs records for queries that do not use indexes. Set up long_query_time=10

Also attached is the use of the show status command to view the values related to mysql status and their implications:

Use the show status command

The meaning is as follows:

The number of times the aborted_clients client illegally interrupted the connection

Number of aborted_connects connection mysql failures

The number of com_xxx xxx commands executed, there are many

Number of connections connected mysql

Temporary tables created by Created_tmp_disk_tables on disk

Temporary tables created in memory by Created_tmp_tables

Number of temporary Created_tmp_files files

Key_read_requests The number of requests to read a key block from the cache

Key_reads The number of physical reads of a key block from disk

Number of connections used simultaneously by Max_used_connections

Open_tables Open Table

Open_files open files

Table opened by Opened_tables

Number of queries submitted by Questions to server

Sort_merge_passes if this value is large, you should increase the sort_ buffer value in my.cnf

Number of seconds that the Uptime server has been working

Recommendations for improving performance:

1. If the opened_tables is too large, you should make the table_cache in the my.cnf larger

two。 If the Key_reads is too large, you should make the key_buffer_size in the my.cnf larger. The cache failure rate can be calculated with Key_reads/Key_read_requests

3. If the Handler_read_rnd is too large, many queries in the SQL statements you write scan the entire table without playing the role of index keys

4. If Threads_created is too large, increase the value of thread_cache_size in my.cnf. You can use Threads_created/Connections to calculate the cache hit rate

5. If Created_tmp_disk_tables is too large, increase the value of tmp_table_size in my.cnf and replace disk-based temporary tables with memory-based temporary tables

The above is how to optimize the configuration parameters in the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.