MySQL Write insert data optimization configuration

*innodb_buffer_pool_size

If you use InnoDB, then this is an important variable. InnoDB is more sensitive to buffer size than MyISAM. Mysiam may use the default key_buffer_size for large data volumes, but InnoDB is crawling with default values when it comes to large data volumes. The InnoDB buffer pool caches data and indexes, so you do not need to leave room for the system's cache, and if you use only InnoDB, you can set this value to 70%-80% of memory. As with Key_buffer, if the amount of data is smaller, do not set this value too high to increase the memory utilization

*innodb_additional_pool_size

The effect of this is not obvious, at least when the operating system can reasonably allocate memory. 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

It's very important to write a lot, especially big data volumes. It is important to note that large files provide higher performance, but the database is restored with more time. I usually use 64m-512m, depending on the server's space

*innodb_log_buffer_size

The default value is available for most medium write operations and short transactions. This value should be increased if updates are frequently made or if a lot of BLOB data is used. But too big is also a waste of memory, because 1 seconds will always flush (the word in Chinese how to say it?) ) Once, so it does not need to be set to more than 1 seconds of demand. 8m-16m generally should be enough. Small use can be set a little bit smaller

Innodb_flush_log_at_trx_commit (Www.111cn.net this works.

Complaining that InnoDB is 100 times times slower than MyISAM? Then you probably forgot to adjust the value. The default value of 1 means that every single transaction commit or out-of-transaction instruction requires the log to be written to (flush) the hard disk, which is time consuming. Especially when using the battery-powered cache (Battery backed up cache). Set to 2 for many applications, especially from the MyISAM table is possible, it means to write to the system cache instead of writing to the hard disk. The log will still flush to the hard drive every second, so you will generally not lose more than 1-2 seconds of updates. Set to 0 will be faster, but the security side is poor, even if MySQL hangs may also lose the transaction data. A value of 2 will only lose data if the entire operating system is hung.

Above is online to see, I found that slow query log has a lot of update and insert query, the innodb_flush_log_at_trx_commit changed to 2, the effect is obvious, changed to 0 will be more obvious, but the security is poor. Do the following to start mysqld effective

vim/etc/my.cn

innodb_flush_log_at_trx_commit=2

can also be executed at MYSQLD runtime

Set GLOBAL Innodb_flush_log_at_trx_commit = 2

Here's 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 will be written to log file once per second, and the flush (brush to disk) operation of log file is performed simultaneously, but in this mode, when the transaction commits, There will be no action. If Innodb_flush_log_at_trx_commit is set to 1 (the default), log buffer will be written to log file each time the transaction commits, and the flush is brushed to disk. If Innodb_flush_log_at_trx_commit is set to 2,log buffer is written to log file every time the transaction commits, the flush (brush to disk) operation does not take place at the same time. In this mode, MySQL will do the flush (brush to disk) operation once per second. Note: This "flush (brush to disk) per second operation" is not guaranteed to be 100% "per second" due to a process scheduling policy issue

The default value of 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, but in the event of a system crash, you may lose up to one second of transactional data. When you set Innodb_flush_log_at_trx_commit to the 0,MYSQLD process, a crash causes all transaction data to be lost in the last second. If you set the Innodb_flush_log_at_trx_commit to 2, all transaction data may be lost for the last second only if the operating system crashes or the system loses power. InnoDB's crash recovery crash Recovery mechanism is not affected by this value, regardless of how much this value is set, the crash recovery crash recovery mechanism will work.

In addition, the Innodb_flush_method parameter is worth paying attention to, which has influence on writing operation.

Innodb_flush_method: How to set InnoDB sync io

1) default– use Fsync ()

2) O_sync open files in SYNC mode, usually slow

3) O_direct, use DIRECT IO on Linux. Can significantly increase the speed, especially on RAID systems. Avoid additional data duplication and double buffering (MySQL buffering and OS buffering).

For more detailed information, please see: http://www.111cn.net/database/mysql/56376.htm

MySQL Write insert data optimization configuration