MySQL write and insert Data Optimization Configuration

This article focuses on mysql write optimization. Here we will explain mysql's Innodb and MyISAM storage engines. I hope this article will help you.

* Innodb_buffer_pool_size

If Innodb is used, this is an important variable. Compared with MyISAM, Innodb is more sensitive to buffer size. MySIAM may also use the default key_buffer_size for large data volumes, but Innodb will feel crawling when using the default value for large data volumes. The Innodb buffer pool caches data and indexes, so you do not 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 data volume is small and does not increase much, do not set this value too high or increase the memory usage.

* Innodb_additional_pool_size

This effect is not very obvious, at least when the operating system can reasonably allocate memory. However, you may still need to set it to 20 MB or more to see how much memory Innodb will allocate for other purposes.

* Innodb_log_file_size

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

* Innodb_log_buffer_size

The default value can be used for most moderate write operations and short transactions. If you often update or use a lot of blob data, you should increase this value. But it is a waste of memory because it will always flush in one second ?) Once, so you do not need to set it to more than 1 second. 8-16 m should be enough. Small applications can be set to a little smaller

Innodb_flush_log_at_trx_commit (this is useful

Why is Innodb 100 times slower than MyISAM? You probably forgot to adjust this value. The default value 1 indicates that logs need to be written to the hard disk (flush) for each transaction commit or non-transactional command. This is time-consuming. Especially when Battery backed up cache is used. Set to 2 is applicable to many applications, especially the conversion from the MyISAM table. It means writing data to the system cache instead of writing data to the hard disk. Logs are flushed to hard disks per second, so you will not lose updates that exceed 1-2 seconds. Setting 0 is faster, but the security is poor. Even if MySQL fails, the transaction data may be lost. Value 2 can only lose data when the entire operating system is down.

I have read it online. I found that there are many update and insert queries in the slow query log. I changed innodb_flush_log_at_trx_commit to 2. The effect is obvious. It is more obvious to change it to 0, however, the security is poor. Start mysqld to take effect.

Vim/etc/my.cn

Innodb_flush_log_at_trx_commit = 2

It can also be executed at mysqld runtime.

Set GLOBAL innodb_flush_log_at_trx_commit = 2

The following is an explanation of innodb_flush_log_at_trx_commit In the mysql manual.

If innodb_flush_log_at_trx_commit is set to 0, log buffer writes data to log file once a second, and flush (flush to disk) operations of log file are performed simultaneously. However, in this mode, when a transaction is committed, there will be no action. If innodb_flush_log_at_trx_commit is set to 1 (default), log buffer writes the log file to the disk for each transaction commit and flush the data to the disk. If innodb_flush_log_at_trx_commit is set to 2, log buffer writes the log file every time a transaction is committed. However, flush (flush to disk) operations are not performed at the same time. In this mode, MySQL flush (flush to disk) operations every second. Note: Due to the process scheduling policy, this "flush (flush to disk) operation once per second" does not guarantee 100% "per second"

The default value 1 is for ACID (atomicity, consistency, isolation, durability) atomicity, consistency, isolation and persistence considerations. If you do not set innodb_flush_log_at_trx_commit to 1, you will get better performance. However, when the system crashes, you may lose up to one second of transaction data. When you set innodb_flush_log_at_trx_commit to 0, the crash of the mysqld process will lead to the loss of all transaction data in the last second. If you set innodb_flush_log_at_trx_commit to 2, all transaction data may be lost in the last second only when the operating system crashes or the system powers down. The crash recovery mechanism of InnoDB is not affected by this value. No matter how much this value is set, the crash recovery mechanism will work.

In addition, the innodb_flush_method parameter is also worth noting and has an impact on write operations.

Innodb_flush_method: sets the InnoDB synchronous IO method.

1) Default-use fsync ()

2) O_SYNC open files in sync mode, which is usually slow

3) O_DIRECT: Use Direct IO on Linux. This can significantly increase the speed, especially on RAID systems. Avoid extra data replication and double buffering (mysql buffering and OS buffering ).