Slow mysql import _ MySQL

Slow mysql import

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. A smaller application can be used.

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.