MySQL database performance optimization-parameter optimization

Mysql Server Optimization

1. View MySQL server parameters

• Show variables;   /show variables like ' parameter name '; --mysql Server configuration information
• Show status; /show global status;  /Show status like ' field name '; --mysql the various states that the server is running
• Mysqladmin-uroot variables; View directly under--os
• Mysqladmin-uroot status;
• Mysqld--verbose--help|more--View detailed definitions of parameters
• LOG: Show variable like ' log_% ';  --whether the log show master status is enabled;  -Log status show Master log; --Log information

2. Important parameters that affect MySQL performance

1) Number of links

• Show variables like ' max_connections '; --View the maximum number of links allowed by MySQL (this is also the parameter configured in/ETC/MY.CNF)
• Show global status like ' Max_used_connections '; --View the maximum number of connections that MySQL has ever responded to

more Ideal setting: Max_used_connections/max_connections * 100% = 85%

2) The probability of an index miss cache

• Show global status like ' key_read% ';

key_cache_miss_rate = key_reads/key_read_requests * 100%(under 0.1% is good, if under 0.01%, and Key_buffer_size is assigned too much, can be reduced appropriately)

Understanding: Key_read_requests is the request to read the index from the cache, and Key_reads is the number of times the index is read from the physical hard disk. Each time an index is required, it is requested in the cache, and then read from the hard disk when the index is not in the cache.

3) Table Descriptor cache

• Show variables like ' Table_open_cache '; --Table Descriptor cache size, MySQL reads some data into the Table_open_cache cache every time a table is opened, and when MySQL does not find the appropriate information in this cache, it is read directly to the disk.
• Show global status like ' open%tables% '; --Open_tables: Number of open tables; Opened_table: The number of open tables, if that is too large, indicates that the Table_open_cache value may be too small.

Compare the appropriate values:

Open_tables/opened_tables * 100% >= 85%

Open_tables/table_open_cache * 100% <= 95%

4) Table Scan rate

• Show global status like ' Handler_read_rnd_next '; --Reads the number of requests in the data file for the next line. If you are doing a large number of table scans, this value is higher. It is usually indicated that your table index is incorrect or the query being written does not take advantage of the index.
• Show global status like ' Com_select '; The--com_select variable records the number of queries without caching + Error queries + permission check queries
• Show variables like ' read_buffer_size '; --buffer when table is scanned
• Show variables like ' read_rnd_buffer_size '; --random scan of the buffer, appropriate adjustment to the performance of the Orader by operation has a certain effect

Table Scan rate = Handler_read_rnd_next/com_select -More than 400 indicates that there are too many table scans, it is likely that the index is not built, and that increasing the read_buffer_size value will have some benefits, but it is best not to exceed 8M.

5)Innodb_flush_log_at_trx_commit ---control the timing of data written to the log file in the buffer and when the log file data is flushed to disk

• Show variables like ' innodb_flush_log_at_trx_commit ';

0:log thread writes data from log buffer to a file every 1s, and notifies the file system to flush the file synchronously, ensuring that the data is actually written to the physical file above the disk. ( best performance, lowest security)

1: Log thread is triggered at the end of each transaction to write data from the log cache to the file and notify the file system to synchronize the files. ( the safest, the performance is slightly poor )

The 2:log thread will write the data to the transaction log at the end of each transaction, but this write is simply a file write operation that invokes the filesystem, when the file system synchronizes the cached data to the physical file, and the log thread is completely unaware. ( used when data consistency and integrity requirements are not high )

General principles:

• If the transaction data is very important (such as a bank transaction), then this parameter must be set to 1.
• If the Internet should be general (such as community discussion), then you can set it to 2 to get better write performance.
• In a production environment, there is generally no reason for this parameter to be 0.

6) Innodb_log_file_size --the size of each log file in a log group, default 5M

Show variables like ' innodb_log_file_size ';

It is important to have high write loads, especially big data. The higher the value, the higher the performance, but the side effect is that when the system suffers, the recovery time is longer (the longer the scan recovers). Generally use 64m-512m, depending on the space of the server.

MySQL database performance optimization-parameter optimization