Mysql Data insertion Optimization Method

In general, read/write operations in MyISAM are serial, but when querying and inserting the same table, to reduce the frequency of lock competition, according to the settings of concurrent_insert, myISAM can process queries and inserts in parallel:

When concurrent_insert = 0, concurrent insertion is not allowed.
When concurrent_insert = 1, concurrent insertion is allowed for tables without holes. The new data is at the end of the data file (default ).
When concurrent_insert = 2, concurrent insertion at the end of the data file is allowed regardless of whether the table has holes.

In this case, it is very cost-effective to set concurrent_insert to 2. As for the resulting file fragments, You can regularly use the optimize table syntax to OPTIMIZE them.

Max_write_lock_count:

By default, the write operation has a higher priority than the read operation. Even if the read request is sent first and then the write request is sent, the write request is processed first, then process the Read Request. This creates a problem: Once I send several write requests, all read requests will be blocked until all write requests are processed. Max_write_lock_count:

Max_write_lock_count = 1

With this setting, when the system processes a write operation, the write operation will be suspended to give the read operation a chance.

Low-priority-updates:

We can also simply reduce the write operation Priority and give the read operation a higher priority.

Low-priority-updates = 1

In summary, concurrent_insert = 2 is absolutely recommended. As for max_write_lock_count = 1 and low-priority-updates = 1, it depends on the situation. If you can reduce the priority of write operations, use low-priority-updates = 1; otherwise, use max_write_lock_count = 1.



Set-variable = max_allowed_packet = 1 M
Set-variable = net_buffer_length = 2 K



Under myisam engine

1. try to use insert into table_name values (...), (.....), (.....) insert data in this way to avoid using inset into table_name values ();

2. Add bulk_insert_buffer_size (8 MB by default)

3. If a non-empty table is created, use alter table table_name disable keys, load data infile, and run the following command after the data is imported:

Alter table table_name enable keys. If it is an empty table, this operation is not required because the myisam table imports data in an empty table first and then establishes indexs.

4. When inserting data, consider using insert delayed... in this way, mysql puts the insert operation into the queue for concentrated insertion, which is faster.

5. Using load data infile is nearly 20 times faster than using insert. Use this operation whenever possible.


Under innodb engine

1. Run set unique_checks = 0 before importing data to prohibit the check of unique indexes. After the data is imported, run set unique_checks = 1.

2. Run set foreign_key_checks = 0 to disable foreign key check before importing data. After the data is imported, run set foreign_key_checks = 1.

3. Before importing data, execute set autocommit = 0 to disable automatic commit of Automatic transactions. After the data is imported, execute set autocommit = 1 to restore automatic commit.

For tables using the innodb engine, physical storage is stored in PK order. Disable keys similar to myisam cannot be used.

Improving the disk I/0 on the hardware is very helpful for the insertion speed (so if you want to import and export large amounts of data, try to compare NB hardware to reduce the completion time, prevents problems ).



When a thread executes a DELAYED statement on a table, if such a handler does not exist, a processor thread is created to process all the DELAYED statements on the table.
The thread checks whether the Handler has obtained a DELAYED lock; if not, it tells the handler to get the lock. The DELAYED lock can be obtained even if other threads have a READ or WRITE lock on the table. However, the handler will wait for all alter table locks or flush tables to ensure that the TABLE structure is up-to-date.
The thread executes the INSERT statement, but does not write rows into the table. It puts copies of the last row into a queue managed by the processor thread. Any syntax error can be detected by the thread and reported to the client program.
The customer cannot report the number of repeat or AUTO_INCREMENT values of the result rows; it cannot obtain them from the server because the INSERT statement returns the result before the INSERT operation is complete. If you use c api, for the same reason, the mysql_info () function does not return anything meaningful.
When a row is inserted into a table, the update log is updated by a processor thread. When multiple rows are inserted, the update log is updated when the first row is inserted.
After each write to the delayed_insert_limit row, the processor checks whether any SELECT statement is still unfinished. If so, these statements can be executed before continuing.
When the processor does not have more rows in its queue, the table is unlocked. If the new insert delayed command is not received within seconds of delayed_insert_timeout, the processor is terminated.
If more than delayed_queue_size rows have not been resolved in a specific processor queue, the thread waits until the queue has space. This helps ensure that the mysqld server does not use all memory for the delayed memory queue.
The processor thread will display delayed_insert In the MySQL table in the Command column. If you execute a flush tables command or KILL thread_id to KILL it, it will be killed. However, before exiting, it will first store all queued rows in the table. During this period, this time it will not accept any new INSERT commands from other threads. If you execute insert delayed after it, a new processor thread will be created.
NOTE: If an insert delayed processor is running, the insert delayed command has a higher priority than normal INSERT! Other update commands must wait until the insert delay queue becomes empty, KILL the processor thread (using KILL thread_id), or execute flush tables.
The following status variables provide information about the insert delayed command: Number of Delayed_insert_threads processor threads
Number of rows written by Delayed_writes Using INSERT DELAYED
Not_flushed_delayed_rows row number to be written