Today, we will mainly describe the MySQL Configuration Parameter my. inimy. cnf. The following article will describe the specific content of the actual operation.
Today, we will mainly describe the MySQL Configuration Parameter my. ini/my. cnf. The following article will describe the actual operation details.
The following articles mainly describe the MySQL Configuration Parameter my. ini/my. for detailed analysis of cnf, we mainly configure the MySQL parameter my. ini/my. the actual operation steps of cnf are described as follows.
1. Get the current configuration parameters
To optimize MySQL configuration parameters, you must first understand the current configuration parameters and running conditions. Use the following command to obtain the configuration parameters currently used by the server:
The Code is as follows:
Mysqld-verbose-help
Mysqladmin variables extended-status-u root-p
In the MySQL console, run the following command to obtain the value of the status variable:
The Code is as follows:
Mysql> show status; if you only need to check several STATUS variables, you can use the following command:
Mysql> show status like '[matching mode]'; (% ,? )
2. optimization parameters
Parameter Optimization is based on the premise that InnoDB tables are generally used in our databases, rather than MyISAM tables. When optimizing MySQL, two MySQL configuration parameters are the most important, table_cache and key_buffer_size.
Table_cache
Table_cache specifies 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 increase the value of table_cache. If you find that open_tables is equal to table_cache and opened_tables is growing, you need to increase the value of table_cache (the preceding state value can be obtained using show status like 'open % tables ). Note that you cannot blindly set table_cache to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure.
For machines with 1 GB memory, the recommended value is 128-256.
Case 1: a server that is not particularly busy
The Code is as follows:
Table_cache-512
Open_tables-103
Open ed_tables-1273
Uptime-4021421 (measured in seconds)
In this case, table_cache seems to be too high. During the peak time, the number of opened tables is much less than that of table_cache.
Case 2: A Development Server.
The Code is 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, opened_tables has a very low value compared to the server running time. Therefore, increasing the value of table_cache should be of little use.
Case 3: A upder‑ming Server
The Code is as follows:
Table_cache-64
Open_tables-64
Open ed_tables-22423
Uptime-1, 19538
In this case, table_cache is set too low. Although the running time is less than 6 hours, open_tables reaches the maximum value, and opened_tables has a very high value. In this way, you need to increase the value of table_cache.
Key_buffer_size
Key_buffer_size specifies the size of the index buffer, which determines the index processing speed, especially the index reading 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 _ read % ).
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.
For machines with 1 GB memory, if the MyISAM table is not used, the recommended value is 16 M (8-64 M ).
Case 1: Health Status
The Code is as follows:
Key_buffer_size-402649088 (384 M)
Key_read_requests-597579931
Key_read-56188
Case 2: alarm status
The Code is as follows:
Key_buffer_size-16777216 (16 M)
Key_read_requests-597579931
Key_read-53832731
In Case 1, the ratio is lower than, which is a healthy condition. In case 2, the ratio reaches, and the alarm has been triggered.
Query_cache_size Optimization
MySQL configuration parameters provide a query buffer mechanism starting from 4.0.1. Using the Query Buffer, MySQL stores the SELECT statement and query result in the buffer. In the future, the same SELECT statement (case sensitive) will be read directly from the buffer. According to the MySQL user manual, query buffering can achieve a maximum efficiency of 238%.
Check the STATUS value Qcache _ * to check whether the query_cache_size setting is reasonable (the preceding STATUS value can be obtained using show status like 'qcache % ). If the Qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient. If the Qcache_hits value is also very large, it indicates that the query buffer is frequently used. In this case, you need to increase the buffer size;
If the Qcache_hits value is small, it indicates that your query repetition rate is very low. In this case, using the Query Buffer will affect the efficiency, so you can consider not to use the query buffer. In addition, adding SQL _NO_CACHE to the SELECT statement explicitly indicates that no Query Buffer is used.
Parameters related to query buffering include query_cache_type, query_cache_limit, and query_cache_min_res_unit. Query_cache_type specifies whether to use the Query Buffer. It can be set to 0, 1, and 2. This variable is a SESSION-level variable.
Query_cache_limit specifies the buffer size that can be used by a single query. The default value is 1 MB. Query_cache_min_res_unit is introduced after version 4.1. It specifies the minimum unit for allocating the buffer space. The default value is 4 K. Check the status value Qcache_free_blocks. If the value is very large, it indicates that there are many fragments in the buffer. This indicates that the query results are relatively small. In this case, reduce query_cache_min_res_unit.
Enable Binary Log)
The binary log contains all the statements for updating data. It is used to restore the data to the final state as much as possible when restoring the database. In addition, if you perform Replication, you also need to use binary logs to transmit modifications.
To enable binary log, you must set the log-bin parameter. Log_bin specifies the log file. If no file name is provided, MySQL generates its own default file name. MySQL automatically adds a digital index after the file name. A new binary file is generated every time the service is started.
In addition, you can use log-bin-index to specify the index file, binlog-do-db to specify the database for the record, and binlog-ignore-db to specify a database without record. Note: binlog-do-db and binlog-ignore-db specify only one database at a time, and specify multiple statements for multiple databases. In addition, MySQL will change all database names to lowercase letters, and all database names must be in lower case when specifying the database, otherwise it will not work.
Run the show master status Command in MySQL to view the current binary log STATUS.
Enable slow query log)
Slow query logs are useful for queries with tracing problems. It records all long_query_time queries. If needed, you can also record records that do not use indexes. The following is an example of slow log query:
To enable slow query logs, you must set the log_slow_queries, long_query_times, and log-queries-not-using-indexes parameters. Log_slow_queries specifies the log file. If no file name is provided, MySQL generates its own default file name. Long_query_times specifies the threshold for slow queries. The default value is 10 seconds. Log-queries-not-using-indexes is a parameter introduced after 4.1.0. It indicates that the record does not use an index for queries.
Configure InnoDB
Correct MySQL configuration parameters are more important than MyISAM tables. The most important parameter is innodb_data_file_path. It specifies the storage space for table data and indexes. It can be one or more files. The last data file must be automatically expanded, and only the last file can be automatically expanded. In this way, when the space is used up, the data file is automatically expanded (in 8 Mb) to accommodate additional data. For example:
The Code is as follows:
Innodb_data_file_path =/disk1/ibdata1: 900 M;/disk2/ibdata2: 50 M: autoextend
The two data files are stored on different disks. Data is first placed in ibdata1. When the data reaches MB, it is placed in ibdata2. Once it reaches 50 MB, ibdata2 will automatically increase in 8 MB.
If the disk is full, you need to add a data file to another disk. Therefore, you need to view the size of the last file and calculate the nearest integer (MB ). Then manually modify the file size and add a new data file. For example, if ibdata2 already has mb data, you can modify it as follows:
The Code is as follows:
Innodb_data_file_path =/disk1/ibdata1: 900 M;/disk2/ibdata2: 109 M;/disk3/ibdata3: 500 M: autoextend
Flush_time
If the system is faulty and often locked or rebooted, set this variable to a non-zero value, which will cause the server to refresh the table's cache in flush_time seconds. Writing table modifications in this way reduces the performance, but reduces the chance of table loss or data loss.
