Mysql insert data performance test

What is the performance of Mysql insert when executing data? It is estimated that most of the data cannot be answered. Today I am looking for some test articles, but I will test it myself to talk about data.

I tested Mysql some time ago and got some test data. I can see some Mysql summary on the Internet, but I still need to read the data.

The following test cases insert tens of millions of data records to test three types of data tables (MYISAM, INNODB, and NDB). The test cases are as follows:

NDB is the official cluster deployment version, and the test data is based on 6.x. Mysql proxy is also official. It seems to be in version 0.8.x. It has been used for load balancing, read/write splitting testing, and load balancing is relatively stable, but read/write splitting is very unstable under multiple threads.
1. Single thread of MYISAM data table
2. Enable innodb_flush_log_at_trx_commit for a single thread of an INNODB data table
3. Disable innodb_flush_log_at_trx_commit for a single thread of an INNODB data table
4. MYISAM 100 thread and 100 connection pool
5. INNODB 100 thread, 100 connection pool, NO LOG
6. MYISAM 1000 thread and 1000 Thread Pool
7. INNODB 1000 threads and 1000 thread pools
8. Data Tables of the NDB type (3 NODE and 3 NODE redundancy), single-thread, use Mysql proxy Server Load balancer
9. NDB (3 NODE, 3 redundant), 100 thread, 100 connection pool, using Mysql proxy
10. NDB (3 NODE, 3 redundant), 1000 thread, 1000 connection pool, using Mysql proxy

Test result summary:

• The X-axis is the data volume, and the X-axis is the time, in milliseconds. The performance of the machine used to bubble test cases is compared. After 2000 threads are enabled, the machine cannot run.
• Transactions are not used in the test case. All test inserts contain 10 varchar (255) fields and 10 32-bit uuid fields. No indexes are created except for the primary key.
• All tests are run on virtual machines. The NDB type is considered as a VM sharing Nic and CPU, and is composed of three independent host machines.
• MYISAM has obvious advantages in stress testing. The bottom line is 100 threads, second only to 1000 threads, and its performance is basically linear.

• The INNODB test should first look at the innodb_flush_log_at_trx_commit condition, and the performance of enabling log is almost at the bottom of a single thread.
• After INNODB closes the Log, the performance is acceptable, but the performance is significantly reduced under high pressure and cannot be called linear.
• NDB data is stored in the memory. With the addition of a 3-point redundancy, the default memory configuration will soon be cleaned up. You need to manually set the memory size. The establishment of visual data redundancy is synchronous.
• Since NDB data is stored in the memory, it is assumed that the test performance will not be too bad. NDB is underwritten under a single thread, and may have a certain impact on the performance of the Proxy. However, the good news is that the performance of the 100 thread and the 1000 thread basically overlap on the diagram.

Mysql-related technologies are rich and varied. I can find related solutions on the Internet. However, in the cloud computing era, downward compatibility may be required, so there is no special solution to the problems of data consistency, high load, redundant disaster recovery, and massive data. From the perspective of insert, MongoDB is used to test the data of tens of millions in a single thread, which is three times faster than Mysql. Based on the storage principle (MongoDB stores indexes in the memory) it is estimated that the performance gap of the select statement will be larger. More and more non-relational databases are becoming more mature in these aspects, and Mysql has only the advantage of transaction support.