Example of how to optimize MySQL insert performance

MySQL Performance Optimization

MySQL performance optimization is to make MySQL run faster and save more resources by reasonably arranging resources and adjusting system parameters. MySQL performance optimization includes query speed optimization, update speed optimization, MySQL server optimization, etc. This blog will introduce three aspects: query optimization, database structure optimization, and MySQL server optimization.

MySQL database optimization, on the one hand, is to identify system bottlenecks and improve the overall performance of the MySQL database; on the other hand, it requires reasonable structural design and parameter adjustment to improve user operation response speed; at the same time, it is also necessary to save system resources as much as possible so that the system can provide services with greater loads. For example, by optimizing the file system, the read and write efficiency of disk I\O can be improved; by optimizing the operating system scheduling strategy, the load capacity of MySQL under high load can be improved; by optimizing the table structure, index, query statements, etc., the query response can be faster.

MySQL insert statement syntax, and an introduction to optimizing MySQL insert performance.

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

Methods to improve insert performance

1. Insert multiple records in one SQL statement

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);

Can be written as

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

2. Use 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;

Notice

1. There is a limit on the length of SQL statements, so be careful when merging SQL statements. The length limit can be modified through the max_allowed_packet configuration item, the default is 1M.

2. A transaction that is too large will affect execution efficiency. MySQL has an innodb_log_buffer_size configuration item. If this value is exceeded, disk data will be used, affecting execution efficiency.

Description of transaction configuration items:

innodb_buffer_pool_size

If you use Innodb, this is an important variable. Compared with MyISAM, Innodb is more sensitive to buffer size. MySIAM may be fine with the default key_buffer_size for large amounts of data, but Innodb feels like it is climbing with the default value for large amounts of data. Innodb's buffer pool caches data and indexes, so there is no need to leave space for the system cache. If you only use Innodb, you can set this value to 70%-80% of the memory. Similar to key_buffer, if the amount of data is small and does not increase much, then not setting this value too high can also improve memory usage.

innodb_additional_pool_size

The effect of this is not very noticeable, at least when the operating system is able to allocate memory properly. But you may still need to set it to 20M or more to see how much memory Innodb will allocate for other purposes.

innodb_log_file_size

This is very important when writing a lot of data, especially large amounts of data. Note that larger files provide better performance, but database restores will take longer. I usually use 64M-512M, depending on the space on the server.

innodb_log_buffer_size

The default value is fine for most uses with medium write operations and short transactions. If you make frequent updates or use a lot of blob data, you should increase this value. But if it is too large, it will waste memory, because there will be a flush (how do you say this in Chinese?) once per second, so there is no need to set it to more than 1 second. 8M-16M should generally be enough. Small applications can be set smaller.

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 instruction outside of a transaction requires the log to be flushed to the hard disk, which is very time-consuming. Especially when using a battery backed up cache. Setting it to 2 is fine for many applications, especially when transferring from MyISAM tables. It means that the data is not written to the hard disk but to the system cache. The log is still flushed to disk every second, so you generally won't lose more than 1-2 seconds of updates. Setting it to 0 will be faster, but less secure, and transaction data may be lost even if MySQL crashes. A value of 2 may only result in data loss if the entire operating system crashes.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.