Mysql performance optimization tips
For Mysql performance optimization, refer to [1], [2], and [3]. Some of the articles that have been used and have relatively good results are as follows:
1. Practical optimization
(1) Disable autocommit to prevent submitting every insert and refresh the log.
SET autocommit=0;... SQL import statements ...COMMIT;
(2) create an index for fields that are frequently queried. Note that after adding an index, the insert operation will slow down.
(3) Use LIMIT 1 when only one row of data is required
SELECT 1 FROM tbl_name LIMIT 1
Note: SELECT 1 is used to check whether a record EXISTS and is generally used as a condition query (normally it will be used with WHERE and often EXISTS). The values of all returned rows are 1. For efficiency, 1> anycol> *, because dictionary tables are not needed. [4]
(4) always set an ID for each table
Set an ID for each table in the database as its primary key, and the best is an INT type (UNSIGNED is recommended), and set the AUTO_INCREMENT flag automatically added.
(5) Insert multiple rows at a time
INSERT INTO yourtable VALUES (1,2), (5,5), ...;
2. Configuration Optimization
Next we will make a summary of the optimization of Disk I/O and query_cache, and assume that the machine memory we use is 8 GB. The following parameters are all set in [mysqld] of the file my. cnf.
(1) innodb_buffer_pool_size and innodb_log_file_size
We recommend that you set the size from Article [5].
## Set buffer pool size to 50-80% of your computer's memoryinnodb_buffer_pool_size=4Ginnodb_additional_mem_pool_size=256M## Set the log file size to about 25% of the buffer pool sizeinnodb_log_file_size=1Ginnodb_log_buffer_size=256M
How to securely change this configuration comes from [6]
mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"service mysqld stoprm -f /var/lib/mysql/ib_logfile[01]service mysqld start
Here, service mysqld stop is a command under centos, but other Linux systems such as ubuntu may use service mysql stop
(2) query_cache_size
For parameter descriptions, see [7], but do not mention the recommended size. setting too large may also cause disadvantages. The size set by myself is as follows:
query_cache_type=1query_cache_limit=2Mquery_cache_size=128M
Query_chache_type = 1 indicates that the query cache is enabled, query_cache_size indicates the total query cache size, and query_cahce_limit indicates the Maximum Cache size for a single query.
After the settings are complete, perform the following operations:
service mysqld restart
(3) innodb_flush_method
Innodb_flush_method is set to O_DIRECT or O_DSYNC. In the article [2], setting it to O_DIRECT will increase performance, but in the article [8] and [2], these two parameters are similar in actual use, and related to the specific hardware used. Therefore, it is not very sure whether setting it to O_DIRECT will optimize the performance.
(4) max_allowed_packet
This parameter must be controlled when data is transmitted over the network. If the transmitted data is too large (especially when large BLOB columns or long strings data exists), it exceeds the upper limit of max_allowed_packet, an error may occur, so we need to increase this parameter. My settings are as follows:
max_allowed_packet = 16M
Note:
1. view some commands of System Variables
show variables like 'innodb_buffer%';SHOW GLOBAL STATUS LIKE '%innodb%';show global status like 'Qc%';
2. My Configuration