How to execute 200,000 mysql insert requests per day?

It depends on how exactly the loading process takes place, however usually the problem of slow insertion is connected with indexes, constraints and foreign key on tables. The base needs to do additional checks to comply with all these restrictions. It can also help to disable auto-commit (in the case of InnoDB), which will write to the binlog after each insert to ensure data integrity.

General recommendations can be found here (InnoDB): https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

and here (MyISAM): https://dev.mysql.com/doc/refman/5.5/en/optimizing-myisam-bulk-data-loading.html

I suspect that you insert records one by one. A faster way is to insert multiple entries in one insert.

INSERT INTO table (col1,col2) VALUES (row11, row21), (row21, row22), ... (row1n, row2n); 

It is also good to create a separate sql file, which is then executed using souce .

Honestly, your question can only be answered by pointing your finger at the sky, because you basically have not written anything about the structure of your base and the hardware (resources) used.

With the help of simple mathematics, you can understand that the speed of execution of a single INSERT request is 90ms, or about 11 INSERT requests per second. Generally INSERT is a very fast operation and can be performed on average 0.2-1.0 ms, but not in your case ...

Let's look at what to spend time with INSERT?

• Connect (or use an existing connection)
• Sending a request to the server
• Parse query
• Check keys (if any)
• Insert record
• Insert Indexes
• Closing

So what are your problems?

1. Weak hardware or faults

If you do not delve into the structure of your database, used engine, etc. you can say that you have weak hardware, and if you want to speed up the insertion operation, you need a more powerful processor, a larger amount of RAM and an SSD drive that has several times more IOPS. And perhaps, since this is a VPS, the disk on which the recording is being made is just overloaded, hold the benchmark of your hardware at the time of INSERT . There may also be a strong fragmentation on the disk.

2. Incorrectly configured database configuration

Further, if there is no problem with this, it means that you have a poorly configured MySQL configuration. Usually, the following settings become stumbling blocks in innoDB

• innodb_log_file_size
• innodb_buffer_pool_size
• innodb_flush_log_at_trx_commit
• innodb_flush_method
• log_bin and sync_binlog
• innodb_buffer_pool_instances
• innodb_write_io_threads

For example, innodb_log_file_size, has a good effect on performance, but by default its size is very small.

And if the data can be re-requested, it is not necessary to strain the disk by permanently resetting the data, so you can use a different value innodb_flush_log_at_trx_commit (1 is safer, 2 is faster)

innodb_flush_log_at_trx_commit - complain that InnoDB is 100 times slower than MyISAM? You probably forgot about the innodb_flush_log_at_trx_commit setting. The default value “1” means that each UPDATE transaction (or a similar command outside the transaction) must flush the buffer to the disk, which is quite resource intensive. Most applications, especially previously used MyISAM tables, will work well with a value of “2” (i.e., “do not flush the buffer to disk, only to the OS cache”). The log, however, will still be flushed to the disk every 1-2 seconds, so in case of an accident you will lose a maximum of 1-2 seconds of updates. A value of “0” will improve performance, but you risk losing data even if mySQL server crashes, while setting the value of innodb_flush_log_at_trx_commit to “2” you lose data only when the entire operating system crashes.

3. Complicated database architecture or large table

Perhaps you have a huge table and a large number of indexes or foreign keys, due to which a large number of conditions are checked during INSERT and the index files are updated. Delete all unnecessary indexes. Perhaps you have indexes that simply do not fit in memory and this means that InnoDB can pull in data from page to page, depending on the recording, and this is very slow.

Perhaps with large amounts of data, partitioning will help you.

Complex and large architecture requires large resources for quick work.

4. Improper use of constructs to insert data

Do you insert one INSERT one at a time, and without transactions, and it runs slowly? What did you want? Thought it would be fast? No, a large amount of data needs to be inserted through a large INSERT query or LOAD DATA INFILE. When discarding each operation to disk, sequential INSERTs will be executed very slowly. INSERT can also be accelerated through some Handlersocket, it will not give a tangible increase, but it will remove additional costs for analyzing the request.

Listed the main reasons. Without detailed information about your server, configuration and architecture, I no longer know what else to say.

In any case, you need to test changes in the configuration and architecture, you can increase the insertion speed, but slow down the search speed.