The following article mainly describes the MySQL configuration parameter my.ini/my.cnf detailed analysis, we mainly in the way of example demo to MySQL configuration parameters my.ini/my.cnf practical steps to explain, the following is the specific description of the relevant content.
1. Get Current configuration Parameters
To optimize the MySQL configuration parameters, first understand the current configuration parameters and the operation. Use the following commands to obtain the configuration parameters currently used by the server:
Copy Code code as follows:
Mysqld–verbose–help
Mysqladmin variables Extended-status–u root–p
In the MySQL console, run the following command to get the value of the state variable:
Copy Code code as follows:
Mysql> show STATUS; If you just check a few state variables, you can use the following command:
Mysql> show STATUS like ' [Match mode] '; (can use%,?, etc.)
2. Tuning parameters
Parameter optimization is based on the premise that the InnoDB table is usually used in our database instead of the MyISAM table. When optimizing MySQL, there are two MySQL configuration parameters that are most important, namely Table_cache and Key_buffer_size.
Table_cache
Table_cache Specifies the size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and placed in it, so that the table content can be accessed more quickly. By checking the status values of peak time open_tables and Opened_tables, you can decide whether to increase the Table_cache value. If you find that open_tables equals Table_cache, and Opened_tables is growing, then you need to add Table_cache values (the above status values can be used show status like ' open% Tables ' Get). Note that Table_cache can not be blindly set to a large value. If set too high, the file descriptor may be insufficient, causing performance instability or connection failure.
For machines with 1G of memory, the recommended value is 128-256.
Case 1: This case comes from a server that is not particularly busy
Copy Code code as follows:
table_cache–512
open_tables–103
opened_tables–1273
uptime–4021421 (measured in seconds)
Table_cache seems to be setting too high in this case. At peak times, the number of open tables is much less than Table_cache.
Case 2: The case comes from a development server.
Copy Code code as follows:
Table_cache–64
Open_tables–64
opened-tables–431
uptime–1662790 (measured in seconds)
Although Open_tables is already equal to Table_cache, the Opened_tables value is very low relative to the server run time. Therefore, increasing the value of Table_cache should be of little use.
Case 3: This case is from a upderperforming server
Copy Code code as follows:
Table_cache–64
Open_tables–64
opened_tables–22423
uptime–19538
The Table_cache is set too low in this case. Although the running time is less than 6 hours, the open_tables reaches the maximum value and the Opened_tables value is very high. This requires increasing the value of the Table_cache.
Key_buffer_size
KEY_BUFFER_SIZE Specifies the size of the index buffer, which determines the speed at which indexing is processed, especially the speed at which index reads. By checking the status values key_read_requests and Key_reads, you can see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above state values can be obtained using show status like ' key_read% ').
Key_buffer_size only works on the MyISAM table. Even if you don't use the MyISAM table, the internal temporary disk table is the MyISAM table, and you also use this value. You can use the Check status value created_tmp_disk_tables to know the details.
For machines with 1G memory, the recommended value is 16M (8-64m) If the MyISAM table is not used.
Case 1: Health status
Copy Code code as follows:
key_buffer_size–402649088 (384M)
key_read_requests–597579931
key_reads–56188
Case 2: Alert status
Copy Code code as follows:
key_buffer_size–16777216 (16M)
key_read_requests–597579931
key_reads–53832731
Case 1 is less than 1:10,000, which is a healthy situation; In case 2 the ratio reaches 1:11, the alarm is already ringing.
Optimize query_cache_size
Starting with 4.0.1, the MySQL configuration parameter provides a query buffering mechanism. With query buffering, MySQL stores the SELECT statement and query results in a buffer, and in future, for the same SELECT statement (case-sensitive), the result is read directly from the buffer. According to the MySQL user manual, you can use query buffering up to 238% efficiency.
By checking the status value qcache_*, you can see if the Query_cache_size setting is reasonable (the above status value can be obtained using 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 the qcache_hits is very large, it indicates that the query buffer use very frequently, at this time need to increase the buffer size;
If the value of qcache_hits is not large, it indicates that your query is very low repetition rate, in this case, the use of query buffering will affect efficiency, then you can consider not to query buffer. In addition, adding sql_no_cache to a SELECT statement makes it clear that query buffering is not used.
Parameters related to query buffering are Query_cache_type, Query_cache_limit, Query_cache_min_res_unit. QUERY_CACHE_TYPE Specifies whether to use query buffering, which can be set to 0, 1, 2, which is a variable at the session level.
QUERY_CACHE_LIMIT Specifies the size of the buffer that a single query can use, and the default is 1M. Query_cache_min_res_unit was introduced after version 4.1, which specifies the smallest unit of allocated buffer space, which defaults to 4K. Checking the status value qcache_free_blocks, if the value is very large, indicates that there are a lot of fragments in the buffer, which indicates that the query results are relatively small, and you need to reduce query_cache_min_res_unit.
Open binary log (Binary log)
The binary log contains all the statements that update the data, and is used to restore the data to its final state as much as possible when restoring the database. In addition, if you do synchronous replication (Replication), you also need to use binary log shipping modification.
Open binary log, you need to set parameters Log-bin. LOG_BIN Specifies the log file, and if the file name is not provided, MySQL produces the default file name itself. MySQL automatically adds a digital index after the filename, and every time the service is started, a new binary is regenerated.
In addition, you can use Log-bin-index to specify index files, use BINLOG-DO-DB to specify a database of records, and use BINLOG-IGNORE-DB to specify a database that is not logged. Note that binlog-do-db and binlog-ignore-db specify only one database at a time, and multiple statements are required to specify multiple databases. Also, MySQL will change all the database names to lowercase, you must use all lowercase names when specifying the database, otherwise it will not work.
Use the show MASTER status command in MySQL to view the current binary log status.
Turn on slow query log (slow query logs)
Slow query logs are useful for tracking queries that have problems. It records all queries that have been checked for Long_query_time, and records that do not use the index if necessary. The following is an example of a slow query log:
Open the slow query log, you need to set parameters log_slow_queries, Long_query_times, log-queries-not-using-indexes. log_slow_queries Specifies the log file, and if the file name is not provided, MySQL produces the default file name itself. LONG_QUERY_TIMES Specifies the threshold value for a slow query, which defaults to 10 seconds. Log-queries-not-using-indexes is a parameter introduced later in 4.1.0, which indicates that a query that does not use an index is logged.
Configure InnoDB
The correct MySQL configuration parameters are more critical to the InnoDB table than the MyISAM table. Among them, the most important parameter is Innodb_data_file_path. It specifies the space for table data and index storage, and can be one or more files. The last data file must be automatically expanded, and only the last file will allow automatic expansion. This way, when the space is exhausted, the automatic expansion data file automatically grows (in 8MB) to accommodate additional data. For example:
Copy Code code as follows:
Innodb_data_file_path=/disk1/ibdata1:900m;/disk2/ibdata2:50m:autoextend
Two data files are placed on different disks. The data is first placed in the Ibdata1, and when 900M is reached, the data is placed in the IBDATA2. Once the 50MB,IBDATA2 is reached, it will grow automatically in 8MB units.
If the disk is full, you need to add a data file to the other disk. To do this, you need to look at the size of the last file and then compute the nearest integer (MB). Then manually modify the size of the file and add a new data file. For example, suppose that the ibdata2 already has 109MB of data, you can modify the following:
Copy Code code as follows:
Innodb_data_file_path=/disk1/ibdata1:900m;/disk2/ibdata2:109m;/disk3/ibdata3:500m:autoextend
Flush_time
If the system has problems and is often locked or rebooted, the variable should be set to a value other than 0, which causes the server to flush the table cache by flush_time seconds. Writing changes to a table in this way reduces performance, but reduces the chance of table corruption or data loss.
