Improve write speed by configuring MySQL parameters (grooming)

1) 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 it does not increase, so do not set this value too high to increase memory usage.

2) 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.

3) 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 space of the server.

4) 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 to a smaller point.

5) Innodb_flush_log_at_trx_commit (This works well)

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.

6) Innodb_flush_method : How to set innodb sync io

1) default– use Fsync ().

2) O_sync open files in SYNC mode, usually slower.

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)

7) Keep Thread_cache in a relatively high number, about 16 – to prevent slow opening of the connection.

8) assign a minimum quantity to the Max_connections parameter – too many connections will run out of RAM and lock the MySQL service.

9) Adjust the innodb_autoextend_increment configuration to 128M due to the default 8M

The main purpose of this configuration item is that when the tablespace space is full, there is a need for the MySQL system to automatically expand how much space, each time the tablespace extension will let each SQL wait state. Increasing the auto-scaling size reduces the number of tablespace auto-expansions.

Improve write speed by configuring MySQL parameters (grooming)