Example Method for MySQL to Optimize insert Performance

MySQL Performance Optimization

MySQL performance optimization is to make MySQL run faster and save resources by rationally arranging resources and adjusting system parameters. MySQL performance optimization includes query speed optimization, update speed optimization, MySQL server optimization and so on. This blog will be introduced from three aspects: query optimization, database structure optimization and MySQL server optimization.

MySQL database optimization, one aspect is to find out the bottleneck of the system, improve the overall performance of MySQL database; On the other hand, reasonable structural design and parameter adjustment are needed to improve the response speed of user operation; At the same time, we should save system resources as much as possible, so that the system can provide services with greater load. For example, by optimizing the file system, improve the read-write book reading of disk I\ O; By optimizing the scheduling strategy of the operating system, the load capacity of MySQL under high load is improved; Optimize table structure, index, query statement, etc. to make query response faster.

MySQL insert statement syntax, about mysql optimization insert performance related introduction.

insert into `table`(`field1`,`field2`) values('value1','value2'); 

Methods to improve the performance of insert

1.1 sql statement inserts multiple pieces of data

INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES ('userid_0', 'content_0', 0); 
INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES ('userid_1', 'content_1', 1); 

It can be written as

INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES ('userid_0', 'content_0', 0), ('userid_1', 'content_1', 1); 

2. Working with transactions

START TRANSACTION; 
INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES ('userid_0', 'content_0', 0); 
INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES ('userid_1', 'content_1', 1); 
... 
COMMIT; 

Attention

1. The length of sql statements is limited, so pay attention when merging sql statements. The length limit can be modified through the max_allowed_packet configuration item, which defaults to 1M.

2. Too much transaction will affect the execution efficiency. mysql has innodb_log_buffer_size configuration entry. If it exceeds this value, disk data will be used, which will affect the execution efficiency.

Description of configuration items for transactions:

innodb_buffer_pool_size

If Innodb is used, then this is an important variable. Compared with MyISAM, Innodb is more sensitive to buffer and size. MySIAM may be fine with the default key_buffer_size for large data volumes, but Innodb feels crawling with the default value for large data volumes. The buffer pool of Innodb caches data and indexes, so there is no need to leave room for the system cache. If you only use Innodb, you can set this value to 70%-80% of the memory. Same as key_buffer, if the amount of data is small and does not increase much, don't set this value too high to improve memory utilization.

innodb_additional_pool_size

The effect of this is not obvious, at least when the operating system can allocate memory reasonably. But you may still need to set it to 20M or more to see how much memory Innodb allocates for other purposes.

innodb_log_file_size

It is very important for writing a lot of data, especially a large amount of data. Note that large files provide higher performance, but it takes more time to recover the database. I generally use 64M-512M, depending on the server space.

innodb_log_buffer_size

The default value is OK for most medium writes and short transactions. If you update frequently or use a lot of blob data, you should increase this value. But too big is also a waste of memory, because 1 second will always be flush (how to say this word in Chinese?) 1 time, so there is no need to set it to exceed the demand of 1 second. 8M-16M1 should be enough. Small applications can be set to be smaller by 1 point.

innodb_flush_log_at_trx_commit

Complaining that Innodb is 100 times slower than MyISAM? Then you probably forgot to adjust this value. The default value of 1 means that every transaction commit or extra-transaction instruction requires a log to be written to the (flush) hard disk, which is time consuming. Particularly when using battery-powered buffers (Battery backed up cache). Setting it to 2 is OK for many applications, especially for converting from the MyISAM table, which means writing not to the hard disk but to the system cache. Logs will still be flush to the hard drive every second, so you will not lose updates for more than 1-2 seconds. Setting to 0 will be 1 point faster, but the security aspect is poor. Even if MySQL hangs up, the transaction data may be lost. A value of 2 will only lose data when the whole operating system hangs up.

Summarize