1. Get the current configuration parameters
To optimize configuration parameters, you first know the current configuration parameters and operations. Use the following command to get the configuration parameters used by the current server:
mysqld -verbose -help
mysqladmin Variables Extended-Status -u Root -p
In the MySQL console, run the following command to get the value of the status variable:
mysql> show status;
If you only check a few status variables, you can use the following command:
MySQL> Show Status Like '[Matching Mode]'; (can usage% ,?, etc.)
2. Optimized parameters
Parameter optimization is based on one premise, in our database usually uses the InnoDB table without using the Myisam table. When optimizing MySQL, two configuration parameters are the most important, namely Table_Cache and Key_Buffer_Size.
Table_cache
Table_Cache Specifies the size of the cache. Whenever the MySQL accesses a table, if there is space in the table buffer, the table is opened and placed in which the table content can be accessed faster. By checking the status value Open_TABLES and OPENED_TABLES of the peak time, you can decide whether you need to increase the value of Table_Cache. If you find Open_Tables equal to Table_Cache, and OPENED_TABLES is growing, then you need to increase the value of Table_Cache (the above status value can be obtained using Show Status Like 'Open% Tables'). Note that Table_Cache cannot be blindly set to a large value. If it is set too high, it may cause insufficient file descriptors to cause unstable performance or connection failure.
For machines with 1G memory, the recommended value is 128-256.
Case 1: This case comes from one server that is not a special busy
TABLE_CACHE - 512
Open_TABLES - 103
OPENED_TABLES - 1273
Uptime - 4021421 (Measured In Seconds)
Table_cache seems to be too high in this case. In peak time, the number of open tables is much less than Table_Cache.
Case 2: This case comes from a development server.
Table_Cache - 64
Open_TABLES - 64
Opened-Tables - 431
Uptime - 1662790 (Measured in Seconds)
Although Open_Tables has been equal to Table_Cache, the value of OPENED_TABLES is also very low relative to server running time. Therefore, the value of increasing Table_Cache should be not used.
Case 3: This case comes from a UPDERPERFORMING server
Table_Cache - 64
Open_TABLES - 64
Opened_Tables - 22423
Uptime - 19538
Table_cache is set too low in this case. Although the run time is less than 6 hours, Open_TABLES reaches the maximum, the value of Opened_Tables is also very high. This needs to increase the value of Table_Cache.
Key_Buffer_Size
Key_Buffer_Size Specifies the size of the index buffer, which determines the speed of the index processing, especially the index read. By checking the status value key_read_requests and key_reads, you can know if the key_buffer_size setting is reasonable. Proportional key_reads / key_read_requests should be as low as possible, at least 1: 100, 1: 1000 is better (the state value can be obtained using show status like 'key_read%'). Key_buffer_size only features MyISAM. Even if you don't use myisam tables, the internal temporary disk table is the Myisam table, but also uses this value. You can use the check status value created_tmp_disk_tables to know the details.
For 1G memory machine, if you do not use the Myisam table, the recommended value is 16m (8-64m).
Case 1: Health
Key_Buffer_Size - 402649088 (384M)
Key_Read_Requests - 597579931
Key_Reads - 56188
Case 2: Alarm Status
Key_Buffer_Size - 16777216 (16M)
Key_Read_Requests - 597579931
Key_Reads - 53832731
The proportion of cases 1 is less than 1: 10000, is a healthy situation; case 2 has reached 1:11, the alarm has already touched.
Optimize Query_Cache_size
From 4.0.1, MySQL provides a query buffer mechanism. Using query buffer, MySQL stores the SELECT statement and query results in the buffer. In the future, for the same SELECT statement (case sensitive), read the results directly from the buffer. According to the MySQL user manual, you can reach up to 238% efficiency using the query buffer.
By checking the status value QCache_ *, you can know whether query_cache_size setting is reasonable (the above status value can be obtained using Show Status Like 'QCache%'). If the value of QCache_lowmem_prune is very large, it indicates that there is not enough buffer, and if the value of QCache_hits is very large, the query buffer is very frequent, and it needs to increase the buffer size; if the value of QCache_hits is not large, it indicates you. The query repetition rate is very low. In this case, the query buffer will affect the efficiency, so it can be considered without the query buffer. In addition, add SQL_NO_CACHE in the SELECT statement can clearly indicate that the query buffer is not used.
The parameters related to the query buffer also have query_cache_type, query_cache_limit, query_cache_min_res_unit. Query_Cache_Type Specifies whether to use query buffers, can be set to 0, 1, 2, which is the variable of the session level. Query_cache_limit Specifies the buffer size that a single query can use, default is 1m. Query_Cache_MIN_RES_UNIT is introduced after version 4.1, which specifies the minimum unit of allocating buffer space, default is 4K. Check status qcache_free_blocks, if the value is very large, it indicates a lot of fragments in the buffer, which indicates that the results of the query are smaller, and query_cache_min_res_unit needs to be reduced.
Open binary logs (binary log)
The binary log contains all the statements of all updated data, which uses it to restore data to the final state when restoring the database. In addition, if you synchronize replication, you will also need to use binary logs to transfer modification. Turn on the binary log, you need to set the parameter log-bin. Log_bin Specifies the log file. If the file name is not provided, MySQL produces the default file name. MySQL automatically adds a digital index after the file name, and each time you start the service, you will regenerate a new binary file.
In addition, using log-bin-index can specify an index file; use binLog-DO-DB to specify a recorded database; use binLog-Ignore-DB to specify a not recorded database. Note that binlog-do-db and binlog-ignore-db only specify a database, specifying multiple databases to need multiple statements. Moreover, MySQL changes all database names to lowercase, and must use lowercase names when specifying the database, otherwise it will not work.
Use the show master status command in MySQL to see the current binary log status.
Turn on the slow query log (Slow Query Log)
Slow query logs are very useful for tracking problems. It records all queries that check long_query_time, and records that do not use indexes can be recorded if needed. Below is an example of a slow query log:
Turn on the slow query log, you need to set the parameters log_slow_queries, long_query_times, log-queries-not-using-indexes. Log_slow_queries Specifies the log file. If the file name is not provided, MySQL will generate the default file name. Long_Query_Times Specifies the threshold of the slow query, the default is 10 seconds. Log-queries-not-using-indexes is the parameters introduced after 4.1.0, which indicates that the record does not use the index.
Configuring InnoDB
For the Myisam table, the correct configuration parameters are more critical to the InnoDB table. Where the most important parameter is INNODB_DATA_FILE_PATH. It specifies a space for table data and index storage, which can be one or more files. The last data file must be automatically expanded, and only the last file allows automatic expansion. Thus, when the space is used, the automatic expansion of the data file will automatically grow (in 8MB) to accommodate additional data. E.g:
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 IBDATA1, and after 900M, the data is placed in IBDATA2. Once 50MB, IBDATA2 will automatically grow at 8MB.
If the disk is full, you need to add a data file on another disk. To do this, you need to view the size of the last file and then calculate the closest integer (MB). Then manually modify the size of the file and add a new data file. For example: Suppose IBDATA2 has 109MB of data, then you can modify as follows:
InnoDB_Data_file_path = / disk1 / ibdata1: 900m; / disk2 / ibdata2: 109m; / disk3 / ibdata3: 500m: autoextend
FLUSH_TIME
If there is a problem and often lock or reboot, the variable should be set to a non-zero value, which will cause the server to refresh the table's cache. Use this method to write a modification of the table will reduce performance, but can reduce the chances of indicia or data loss. Generally use the default value.
BINLOG_CACHE_SIZE
The size of the cache to hold the SQL statements for the binary log during a transaction A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has binary log enabled (-. Log-bin option ). If you often use big, multiple-statement transactions, you can increase this to get more performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable.
3. Storage engine
In the MySQL 3.23.0, the MyISAM storage engine is introduced. It is a non-transactional storage engine that has become the default storage engine of MySQL. However, if you use the Setup Wizard to set the parameters, it will use InnoDB as the default storage engine. InnoDB is a transactional storage engine.
When you create a table, you can specify the storage engine for the table, the syntax is as follows:
Create Table T (I int) engine = myisam
Create Table T (I int) Type = MyISAM
If not specified, use the default storage engine. You can also use ALTER TABLE to replace the table engine, the syntax is as follows:
Alter Table T Engine = MyISAM
A table that can contain different storage engines in the same database.
The transaction table has the following features:
Ø Safer. Even if mysql crashes or you get hardware problems, you can get your data back, Either by Automatic Recovery or from a Backup Plus the Transaction log.
Ø You CAN Combine Many Statements and Accept The all at the Same Time with the commit sitement.
Ø You Can Execute Rollback to ignore your changed.
Ø IF An Update Fails, All Your Changes Will Be Restore, All Changes That Have Taken Place Are Permanent.)
Ø Transaction-Safe Storage Engines CAN Provide Better Concurency for Tables That Get Many Updates Concurrently with reads.
The non-transaction table has the following advantages:
Ø MUCH FASTER
Ø Lower Disk Space Requirements
Ø Less Memory Required to Perform Updates
4. Myisam storage engine
The following Myisam's parameters are the recommended parameters recommended by the MySQL manual, which is said to adapt to most of the situation. There is still no shortprint for how to monitor the parameter settings.
MAX_CONNECTIONS = 200
READ_BUFFER_SIZE = 1M
READ_RND_BUFFER_SIZE = 8M
sort_buffer_size = 1m
Read_buffer_size
Each Thread That Does a Sequential Scan Allocates A Buffer of this Size for Each Table It Scans. You Might Want To Increse This value.
R_rnd_buffer_size
When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so You Should Not Set The Global Variable To A Large Value. Instead, Change The Session Variable Only from With Clients That Need To Run Large Queeries.
Bulk_insert_buffer_size
This parameter is introduced in 4.0.3. Myisam uses a tree-type buffer to speed up a large number of inserts, such as insert ... select, insert ... Values (...), values (...), ..., load data infile, etc. This parameter specifies the size of the buffer. The default is 8m, set to 0, indicates that this optimization is not used.
If you do not use the Myisam table, you can set it to 0.
5. InnoDB storage engine
Referring with a lot of information, it did not clearly show how to optimize the InnoDB parameters, and how to monitor these parameter settings is reasonable, and only according to the introduction above the MYSQL user manual.
InnoDB_Buffer_Pool_Size
For InnoDB tables, the role of InnoDB_Buffer_Pool_Size is equivalent to Key_Buffer_Size for the role of Myisam tables. InnoDB uses this parameter to specify the size of the memory to buffer data and indexes. For a separate MySQL database server, you can set this value to 80% of physical memory.
According to the MySQL manual, the recommended value for 2G memory is 1G (50%).
InnoDB_Flush_Log_at_trx_commit
This value specifies the way the InnoDB record log. If set to 1, when each transaction is submitted, MySQL will write the transaction log into the disk. If set to 0 or 2, the log is written to the disk once a second. (Not clear from 0 and 2)
The actual test found that the value of this value is very affected by the speed of the inserted data, and then inserts 10,000 records when set to 2. It takes only 2 seconds. It takes only 1 second when set to 0, and it takes 229 seconds when set to 1. Therefore, the MySQL manual also suggested that the insertion operation is combined into a transaction, which can greatly increase the speed. According to the MySQL manual, this value can be set to 0 under the presence of a risk of losing recent partial transactions.
InnoDB_log_file_size
The size of each log file in a log group. The default is 5MB. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I / O. But large log files also mean that recovery will be slower In Case of a Crash.
According to the MySQL manual, the recommended value is 25% of INNODB_BUFFER_POOL_SIZE.
Note: When the value is reset, it seems to be deleted with the original file.
InnoDB_log_buffer_size
The size of the buffer that InnoDB uses to write to the log files on disk. Sensible values range from 1MB to 8MB. The default is 1MB. A large log buffer allows large transactions to run without a need to write the log to disk before the THUS COMMIT. Thus, IF you have big transportions, Making The Log Buffer Larger Will Save I / O.
According to the MySQL manual, the recommended value is 8m.
InnoDB_additional_mem_pool_size
This parameter specifies the internal memory pool size of InnoDB to store data dictionary and other internal data structures. The default is 1M. Usually it is not too big, as long as it is enough, it should be related to the complexity of the table structure. If not enough, MySQL will write a warning message in the error log.
According to the mysql manual, the recommended value is 20m for the 2G memory machine.
Show InnoDB Status
Show the status of the InnoDB storage engine
