How to speed up SQL import in MySQL

How to speed up SQL import in MySQL

 

 

1. If MySQL has very little data and the memory is large enough, you can prevent data from being sent to the memory disk.

Linux:

Mount-T ramfs NONE/Ram

By default, half of the memory is used.

If the memory is not large enough and the system has multiple hard disks, the MySQL ApplicationProgramAnd the data directory are separated to different hard disks.

2. MySQL tables are set to myiasm, which is more than 20 times faster than InnoDB under the same conditions.

3. Create a database index after the import is complete.

4. After the import is complete, convert it to another engine as needed, such as InnoDB

5. Insert multiple data entries into a table. You can use the multi-record method:

Insert into tablename values ('xxx', 'xxx'), ('yyy', 'yyy ')...;

6. If multiple MySQL instances are imported, use delayed.

Insert delayed into tablename values ('ss', 'sss ');

7. Large file SQL files can be split into multiple copies for re-export

8. Under the same conditions, RedHat is much better than ubuntu (almost certainly)

9 Performance Tuning tips)

1. if the Unix top or Windows Task Manager shows that the CPU usage of the service is less than 70%, (shows that the CPU usage percentage with your workload is less than 70% ,) your System Bottleneck may be caused by disk read/write. You may have committed a large number of transactions, or the buffer pool is too small. Setting the buffer pool to a greater value is helpful, but it must not be greater than 80% of the physical memory.

2. A transaction contains several modifications. If the transaction modifies the database, InnoDB must refresh the log to the disk when the transaction is committed. Because the hard disk rotation speed is usually as high as 167 rpm, as long as the disk does not cheat the operating system, the number of committed transactions is also limited to 167 times/second.

3. If you lose the most recent transactions, you can set the innodb_flush_log_at_trx_commit parameter to 0 in the my. CNF file. InnoDB always tries to refresh the logs every second, even though the refresh is not guaranteed.

4. Set the log files to a larger value so that the total number of log files is exactly the same as that of the buffer pool. When InnoDB uses up the space of log files, it has to write the modified content in the buffer pool to the disk at a time point. A small log file may cause unnecessary disk write operations. However, the disadvantage of a large log file is that it takes a long time to recover data.

5. Set the size of the log buffer as large as possible, for example, 8 Mb.
6. If you want to store a variable string or field that may contain a large number of Nulls, replace char with a varchar field. A char (n) field always uses n Bytes to store data, even if the string is short or a null value. Small tables are more suitable for buffer pool while reducing disk I/O.

7. (suitable for versions 3.23.41 and later) in some versions of Linux and unixes, It is very slow to use Unix fsync or other similar methods to refresh files to the disk. The default InnoDB method is fsync. If you are not satisfied with the disk write performance of the database system, you can try to set innodb_flush_method to o_dsync in my. CNF, although the o_dsync option looks slower on most systems.

8. When importing data to InnoDB, make sure that autocommit = 1 is not enabled for MySQL. Otherwise, each insert statement must refresh the log to the disk. Add the first line of your SQL import File

Set autocommit = 0;

And add

Commit;

If you use the mysqldump option-opt, you will get a dump file that can be quickly imported into the InnoDB table. You can even stop using the set autocommit = 0 ;... Commit ;.

9. be careful about the full roolback of the insert set: InnoDB uses the insert buffer to reduce disk I/O during insertion, but this mechanism is not used in the corresponding rollback. A disk-bound rollback may take 30 times the corresponding insertion time. If an out-of-control rollback occurs, you can view the techniques in section 6.1 to stop it.

10. A large disk-bound operation is also required. Use drop table or truncate (from MySQL-4.0 or above) to clear a table, instead of using Delete from yourtable.

11. If you want to insert a large number of record rows, you can use multi-line insert to reduce the communication overhead between the client and the server:

Insert into yourtable values (1, 2), (5, 5 );

This technique is effective for inserting any table, not just InnoDB.

12. If you have unique constraints on the secondary keys, you can disable the unique key check in an import Session from 3.23.52 and 4.0.3 to improve the data import speed:

Set unique_checks = 0;

Importing a large table reduces disk I/O, because InnoDB may use its own insert buffer to record secondary indexes in batches.

13. If the table contains a foreign key constraint, you can disable the foreign key check in an import session to speed up data import from 3.23.52 and 4.0.3:

Set foreign_key_checks = 0;

Importing a large table reduces the disk I/O workload.

Reprinted statement:This article from http://hi.baidu.com/ssnh1379/blog/item/26733a1e12468103314e1579.html

========================================================== ======================================