MySQL configuration file my.cnf Chinese explanation with mysql performance optimization method sharing
- 2020-05-12 06:20:27
- OfStack
skip-name-resolve
MySQL is not allowed to DNS an external connection; using this 1 option eliminates the time MySQL takes to DNS. However, it is important to note that if this option is enabled, all remote host connection authorizations must be made using the IP address mode, otherwise MySQL will not be able to properly process connection requests! back_log = 500 requires the number of connections that MySQL can have. This works when the main MySQL thread gets a very large number of connection requests in a very short time, and then the main thread takes some time (albeit a 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 for a short period of time before MySQL temporarily stops answering new requests. Only if you expect a lot of connections in a short time, you need to increase it, in other words, the size of the listening queue for the incoming TCP/IP connection. Your operating system has its own limits on the size of the queue. Trying to set back_log higher than your operating system limit will not work. When you look at your host process list, found that a large number of 264084 | unauthenticated user | xxx. xxx. xxx. xxx | NULL | Connect | NULL | login | NULL stay connected, will increase the value of back_log. The default value is 50. I'll change it to 500. key_buffer_size = 384M# key_buffer_size specify the size of the buffer for the index, increasing the number of indexes it can handle better (for all reads and overwrites) to as much as you can afford. If you make it too big, the system will start changing pages and really slow down. This parameter can be set to 384M or 512M for servers with 4GB or so. By checking the status values Key_read_requests and Key_reads, you can see if the key_buffer_size Settings are reasonable. The ratio key_reads/key_read_requests should be as low as possible, at least 1:100, 1:1000 is better (the above status value can be obtained using SHOW STATUS LIKE 'key_read%'). Note: if the parameter value is set too high, the overall efficiency of the server will be reduced. max_allowed_packet = 32M increases the value of the variable by 10 points, because additional memory is allocated only when needed. For example, mysqld allocates more memory only if you issue a long query or if mysqld has to return a large result row. The small default value for this variable is a precautionary measure to capture error packets between the client and the server and to ensure that large packets are not accidentally used and run out of memory. table_cache = 512table_cache specifies the size of the table cache. Every time MySQL accesses a table, if there is room in the table buffer, the table is opened and placed in it for faster access to the table contents. By checking the status values Open_tables and Opened_tables for the peak time, you can determine whether you need to increase the value of table_cache. If you find that open_tables is equal to table_cache, and opened_tables is growing, then you will need to increase the value of table_cache (the above status can be obtained using SHOW STATUS LIKE 'Open%tables'). Note that you cannot blindly set table_cache to a very large value. If you set it too high, you may end up with insufficient file descriptors, resulting in unstable performance or failed connections. sort_buffer_size = 4M buffer size available for sorting queries. Note: the allocated memory for this parameter is exclusive per connection! If there are 100 connections, then the actual total sort buffer size allocated is 100 × 4 = 400MB. Therefore, the recommended setting for servers with memory of 4GB is 4-8M. read_buffer_size = the buffer size that can be used by 4M read query operations. Like sort_buffer_size1, the allocated memory corresponding to this parameter is also exclusive to each connection! The buffer size that join_buffer_size = 8M joint query operation can use, like sort_buffer_size1, the allocated memory corresponding to this parameter is also exclusive to each connection! myisam_sort_buffer_size = 64MMyISAM = 64M specify the size of the MySQL query buffer. You can observe this by executing the following command on 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 insufficient buffering often occurs; If the value of Qcache_hits is very large, it indicates that the query buffer is used very frequently. If the value of Qcache_hits is small, it will affect the efficiency, so you can consider not using the query buffer. Qcache_free_blocks, if this value is very large, it indicates that there is a lot of fragmentation in the buffer. thread_cache_size = 64 the number of threads that can be reused. If so, the new thread is fetched from the cache, and if there is space when the connection is disconnected, the client's line is placed in the cache. If there are many new threads, this variable value can be used to improve performance. By comparing the Connections and Threads_created state variables, you can see the effect of this variable. tmp_table_size = 256Mmax_connections = 1000 specifies the maximum number of connection processes allowed by MySQL. If you frequently get an Too Many Connections error when visiting the forum, you need to increase this parameter value. max_connect_errors = 10000000 for the same host, if there is an interrupt error connection that exceeds the number of the parameter values, the host will be disabled. If the host needs to be unblocked, execute: FLUSH HOST; . wait_timeout = 10 specifies a maximum connection time of 1 request, which can be set to 5-10 for servers in memory around 4GB. The parameter thread_concurrency = 8 is the number of logical CPU of the server ×2. In this case, the server has 2 physical CPU, and each physical CPU supports H.T hyperthreading, so the actual value is 4 ×2 = 8skip-T. Do not turn this option on if the WEB server is accessing the MySQL database server via a remote connection! Otherwise you will not be able to connect properly! long_query_time = 10log-slow-queries = log-queries-not-using-indexes turn on the slow query log (slow query log). The slow query log is very useful for tracking problematic queries. It records all queries that have looked up long_query_time and, if necessary, records records that do not use an index. Here is an example of a slow query log: to enable the slow query log, you need to set the parameters log_slow_queries, long_query_times, log-queries-using-indexes. log_slow_queries specifies the log file, and if no file name is provided, MySQL will generate the default file name itself. long_query_times specifies the threshold for a slow query, which defaults to 10 seconds. log-queries-not-using-indexes is a parameter introduced after 4.1.0 that indicates the record of a query that does not use an index. Set long_query_time=10 attach the show show status command to view mysql status related values and their meanings: The show status command has the following meaning: the number of times aborted_clients clients have illegally disconnected aborted_connects failed to connect mysql, the number of times com_xxx xxx command has been executed, and there are many connections connections to mysql. Created_disk_tables temporary table Created_tmp_tables created on disk Temporary table Created_tmp_files temporary number of files created in memory Key_read_requests number number requests a a reads of block from diskMax_used_connections tables diskMax_tables If this value is large, increase the number of seconds sort_buffer in my.cnf the Uptime server has been working. Suggestions for performance improvement :1. If opened_tables is too large, increase table_cache in my.cnf
2. If Key_reads is too large, then the key_buffer_size in my.cnf should be enlarged, and the failure rate of cache can be calculated using Key_reads/Key_read_requests
3. If Handler_read_rnd is too large, many of the queries in your SQL statement scan the entire table without serving as the key of the index
4. If Threads_created is too large, increase the value of thread_cache_size in my.cnf. Threads_created/Connections can be used to calculate cache hit ratio
5. If Created_tmp_disk_tables is too large, increase the value of tmp_table_size in my.cnf and replace the disk-based temporary table with the memory-based one
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = what is the storage engine? The data in MySQL is stored in files using various techniques (or the correct compilation method is important, but it is only part of the effort to improve the performance of the MySQL server. Many of the parameters of the MySQL server affect the performance of the server, and we can save these parameters to the configuration file so that they are automatically activated every time the MySQL server is started. The configuration file is my.cnf. MySQL server provides my. cnf file several examples, they can be in/usr/local/mysql/share/mysql/directory found, name of my respectively - small. cnf, my - medium. cnf, my - large. cnf and my - huge. cnf. The size description in the file name describes the type of system for which the profile is applicable. For example, if the system running the MySQL server does not have much memory and MySQL is used only occasionally, it is ideal to use the my-small.cnf configuration file, which tells mysqld daemon to use the least system resources. If, on the other hand, the MySQL server is used to support a large online marketplace and the system has 2G memory, mysql-huge.cnf is best used. To use the sample configuration file above, we should first copy the one that best fits the requirements and name it my.cnf. This replicated configuration file can be used in three ways: global: copy the my.cnf file to the server's /etc directory, and the parameters defined in the file will be globally valid for all MySQL database servers running on that server. Partial: copying this my.cnf file to [MYSQL-INSTALL-DIR]/var/ will make it available only to the specified server, where [MYSQL-INSTALL-DIR] represents the directory where MySQL is installed. Users: finally, we can restrict the scope of this file to the specified user by copying the my.cnf file to the user's root directory. So, how do you set the parameters in the my.cnf file? Or step 1 and say, what are the parameters that we can set? All of these parameters have a global impact on the MySQL server, but at the same time each parameter is closely related to a specific part of MySQL. For example, the max_connections parameter belongs to the mysqld1 class. So, how do you know that? This is done by executing the following command:
% > / usr/local/mysql libexec/mysqld � help this command will show and mysqld about various options and parameters. It is very convenient to look for these parameters as they are at the end of the line "Possible variables for option, set-variable (-O) are". After finding these parameters, we can set all of them in the my.cnf file as follows:
set-variable = max_connections=100
The effect of this line of code is that the maximum number of simultaneous connections to the MySQL server is limited to 100. Don't forget to add an set-variable directive to section [mysqld] of the my.cnf file. See the example in the configuration file for details.