Adjustment records for the insert and read speeds of the mysql database

(1) improve the performance of database insert central idea: try to write the data to Data File once and reduce the checkpoint operation of the database. The following four configuration items were modified this time:
1) set innodb_flush_log_at_trx_commit to 0; Set it to 0 based on past experience, and the insertion speed will be greatly improved.

0: Write the log buffer to the log file and flush the log file every second, but do nothing at transaction commit.
1: the log buffer is out to the file transaction commit and flush to disk operation performed performed the log file
2: the log buffer is out to the file at each commit, but the flush operation is not on it
2) adjust the configuration of innodb_autoextend_increment to 128M by default of 8M

The main function of this configuration item is how much space the MySQL system needs to automatically extend when the tablespace space is full. Each tablespace extension leaves each SQL in a waiting state. Adding automatic expansion Size reduces the number of automatic expansion tablespace.

3) adjust the innodb_log_buffer_size configuration to 16M due to the default of 1M

This configuration item sets the write log cache of innodb database engine. Increasing this cache segment can reduce the number of database writes to data files.

4) adjust the innodb_log_file_size configuration to 128M by default of 8M

This configuration item sets the size of the innodb database engine UNDO log. This reduces database checkpoint operations.

After the above adjustment, the insertion speed of the system is increased to about 1W per second from tens of thousands of pieces per 10 minutes. Note: the above parameters need to be adjusted according to different machines. In particular, innodb_flush_log_at_trx_commit, innodb_log_buffer_size and innodb_log_file_size should be adjusted carefully. Because it involves the disaster recovery treatment of MySQL itself.

(2) to improve the reading speed of the database, the reading speed at the database level is improved mainly due to the following points: simplifying SQL, adding indexes and partitioning; After checking the program SQL is already the simplest, the index has been added on the query condition. We can only use weapons: table partitions.

Database MySQL preparation before partitioning: in MySQL, a table space is a data file that stores data and indexes.
Modify S11 database to support multiple tablespace due to Shared tablespace;

Modify wb_user_info_sina and wb_user_info_tx to separate table Spaces; (Sina: 1700W data, 2.6G big data files, Tencent 1400W, 2.3G big data files);
Partition operation:
Delete the existing primary key and index first
Recreate the federated primary key that creates id,uid
Then partition with uid as the key value. When you look at the data file at /var/data/mysql, you can see that the two large tables, each with its own separate tablespaces, have been split into several less independent partitioned Spaces. (in this case, if uid is used as the retrieval condition for the query, the speed will not be improved; Because the key values only arrange the partition of the data store and do not create a partition index. I'm so upset that it's not even 1 point worse than Oracle.)
Then index it on the uid field. Go back to the /var/data/mysql folder and check the data files again. MySQL stores the index and data in the same tablespace as usual. If index can be separated from the data, it will be easier to manage.

After the above adjustment, temporarily failed to reflect the system read speed increase; The data update of 5K is basically completed in 2~3 seconds.

MySQL database insert speed adjustment supplementary information:

MySQL started at 1000 bars/min and increased to 10000 bars/SEC. I believe that you have been waiting for the introduction, I will do the whole process of tuning. Central idea of improving database insert performance:
1. Try to write Data File to the database once
2. Reduce the checkpoint operation of the database
3. Buffer the data as much as possible in the program, and insert and submit the data in batches
4. Reduce system IO conflicts

According to the above four points, as an amateur DBA, the MySQL service has been adjusted as follows:
Modify the server configuration of recording MySQL to improve the overall writing speed of MySQL; Specifically, the following three database variable values are: innodb_autoextend_increment, innodb_log_buffer_size, innodb_log_file_size; The default values of these three variables are 5M, 8M and 8M, respectively. According to the memory size and specific usage of the server, these three variables are modified to 128M, 16M and 128M, respectively. At the same time, the original two Log File were changed to eight Log File. This modification mainly satisfies points 1 and 2, for example, adding innodb_autoextend_increment is to avoid checkpoint operation of MySQL due to frequent automatic extension of Data File;
Convert the large table to a separate table empty and partition, and then hang the different partitions in multiple different hard disk arrays.

After completing the above modification operation; I see the following happy results:

Get test results:
Query OK, 2500000 rows affected (4 min 4.85 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (4 min 58.89 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (5 min 25.91 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (5 min 22.32 sec)

Records: 2500000 Duplicates: 0 Warnings: 0
Data volume of the final table:
+------------+
| count(*) |
+------------+
| 10000000|
+------------+
From the above results, an increase in data volume can have a definite impact on insertion performance. Still, the overall pace is highly negotiable. In less than one day, 400 million data can be processed normally. Database bottleneck is expected to have been skillfully solved, the results into the procedure "ape" helplessly and painfully complain to me, big brother need not so hard ah.