Mysql performance optimization suggestions, Mysql Performance Optimization

Source: Internet
Author: User
Tags mysql insert

Mysql performance optimization suggestions, Mysql Performance Optimization

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

Reference

[1] InnoDB Performance Tuning Tips

[2] How to improve MySQL INSERT and UPDATE performance?

[3] over 20 best MySQL performance optimizations

[4] What does 1 in the select 1 from... SQL statement mean?

[5] InnoDB Configuration

[6] How to safely change MySQL innodb variable 'innodb _ log_file_size '?

[7] Query Cache Configuration

[8] mysql setting variable innodb_flush_method to O_DSYNC or O_DIRECT

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.