MySQL data insertion optimization method concurrent_insert

When a thread executes a DELAYED statement for a table, if no such handler exists, a handler thread is created to handle all DELAYED statements for the table.

Generally speaking, read and write operations in MyISAM are serial, but when querying and inserting the same table, in order to reduce the frequency of lock competition, MyISAM can process queries and inserts in parallel according to the setting of concurrent_insert:

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

It seems that setting concurrent_insert to 2 is very cost-effective. As for the resulting file fragmentation, you can use the OPTIMIZE TABLE syntax to optimize it regularly.

max_write_lock_count:

By default, the priority of write operations is higher than that of read operations. Even if a read request is sent first and a write request is sent later, the write request will be processed first and then the read request. This creates a problem: once I issue several write requests, all read requests will be blocked until all write requests are processed, and then there is a chance to process read requests. At this time, you can consider using max_write_lock_count:

max_write_lock_count=1

With this setting, when the system processes a write operation, it will pause the write operation to give the read operation a chance to execute.

low-priority-updates:

We can also be more straightforward and directly lower the priority of write operations and give read operations 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 the priority of write operations can be lowered, use low-priority-updates=1, otherwise use max_write_lock_count=1.

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

Under the MyISAM engine

1. Try to use insert into table_name values ​​(…), (…..), (…..) to insert data, and avoid using inset into table_name values ​​(); inset into table_name values ​​(); inset into table_name values ​​();

2 Increase bulk_insert_buffer_size (default 8M)

3 If the table is not empty, use alter table table_name disable keys, then load data infile, and execute after importing the data:

alter table table_name enable keys. If it is an empty table, this operation is not required, because when a MyISAM table imports data into an empty table, it imports the data first and then creates indexes.

4 When inserting data, consider using: insert delayed… This operation actually puts the insert operation into a queue for relatively concentrated insertion, which is faster.

5. Using load data infile is nearly 20 times faster than using insert operation. Try to use this operation.

Under InnoDB engine

1. Before importing data, execute set unique_checks=0 to disable the check of unique indexes. After the data is imported, run set unique_checks=1.

2. Before importing data, execute set foreign_key_checks=0 to disable foreign key checks. After the data is imported, execute set foreign_key_checks=1.

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

For tables using the innodb engine, physical storage is stored in PK order. You cannot use disable keys like MyISAM.

Improving the disk I/0 on the hardware is very beneficial to the insertion speed (so if you are importing or exporting large amounts of data, try to do it on relatively good hardware to reduce the completion time and prevent problems).

When a thread executes a DELAYED statement for a table, if no such handler exists, a handler thread is created to handle all DELAYED statements for the table.

The thread checks whether the handler has already acquired a DELAYED lock; if not, it tells the handler to acquire one. A DELAYED lock can be acquired even if another thread has a READ or WRITE lock on the table. However, the handler will wait for any ALTER TABLE locks or FLUSH TABLES to ensure that the table structure is up to date.

The thread executes the INSERT statement, but instead of writing the row to the table, it places a copy of the last row into a queue managed by the processor thread. Any syntax errors can be detected by the thread and reported to the client program.

The client cannot report the number of repetitions or AUTO_INCREMENT values ​​for the resulting rows; it cannot obtain them from the server because INSERT returns before the insert operation is complete. If you use the C API, the mysql_info() function does not return anything meaningful for the same reason.

As rows are inserted into the table, the update log is updated by the processor thread. In case of multiple row inserts, the update log is updated when the first row is inserted.
After writing each delayed_insert_limit rows, the processor checks whether any SELECT statements are still outstanding and, if so, allows them to execute before continuing.

When the processor has no more rows in its queue, the table is unlocked. If no new INSERT DELAYED command is received within delayed_insert_timeout seconds, the handler terminates.

If there are more than delayed_queue_size rows already pending in a particular processor's queue, the thread waits until there is room in the queue. This helps ensure that the mysqld server does not use all of its memory for delayed memory queues.

The processor thread will show delayed_insert in the MySQL process table in the Command column. If you execute a FLUSH TABLES command or kill it with KILL thread_id, it will be killed, however, it will first store all queued rows in the table before exiting. During this time it will not accept any new INSERT commands from other threads. If you execute an INSERT DELAYED after it, a new processor thread will be created.

Note that the above means that if there is an INSERT DELAYED handler already running, the INSERT DELAYED command has higher priority than a normal INSERT! Other update commands will have to wait until the INSERT DELAY queue becomes empty, kill the processor thread (with KILL thread_id), or execute FLUSH TABLES.

The following status variables provide information about the INSERT DELAYED command: Delayed_insert_threads The number of processor threads.

Delayed_writes The number of rows written with INSERT DELAYED
Not_flushed_delayed_rows Number of rows waiting to be written

Solution for high concurrent insert statements

Preface

1. Prevent multiple modifications of data

1.1 、Insert solution

1. Add uniqpue to solve the problem (update if repeated)

Insert is generally no problem, just control the unique, so that no two inserts will be made (if duplicates are made, an update operation will be performed)

2. Update plan

1. redis distributed lock, message queue (only one is inserted at a time)

2. MySQL lock (optimistic lock can be used for updates)

2. Security under high concurrency

1. Execute a large DELETE or INSERT query on an online website to avoid your operation causing your entire website to stop responding. Because these two operations will lock the table (update also locks the table if no unique primary key or index is specified), once the table is locked, no other operations can be performed. So be very careful

2. If you lock your table for a period of time, such as 30 seconds, then for a site with a high number of visits, the number of access processes/threads, database links, and open files accumulated in these 30 seconds may not only cause your web service to crash, but may also cause your entire server to hang up immediately. >

2.1 Solution

2.1.1. Table Adjustment

The method of dividing a table into several tables by columns can reduce the complexity of the table and the number of fields, thereby achieving the purpose of optimization. (It would be scary if there were more than a hundred fields)

Example 1:

In the Users table, there is a field called home address. This field is an optional field. Besides personal information, you don't need to read or rewrite this field frequently when operating the database. So why not put it in another table? This will give your table better performance. Think about it, most of the time, for the user table, only user ID, user name, password, user role, etc. will be frequently used. Smaller tables always have better performance.

Example 2:

You have a field called "last_login" that is updated every time a user logs in. However, each update will cause the query cache for the table to be cleared. Therefore, you can put this field in another table, so that it will not affect your constant reading of user ID, user name, and user role, because query cache will help you increase a lot of performance. HP Programmer's Home

In addition, it should be noted that we do not think that the table formed by these separated fields will be joined frequently. Otherwise, the performance will be worse than when the table is not split, and it will be an extreme drop.