MySQL TENS database insert speed and read speed adjustment record

(1) To improve the database insert performance Central idea: as far as possible to write data to the database file and reduce the checkpoint operations. The following four configuration entries were modified this time:
1) Set the Innodb_flush_log_at_trx_commit configuration to 0, according to past experience set to 0, the insertion speed will be greatly improved.

0:write the log buffer to the log file and flush the log file every second, but does nothing at transaction commit.
1:the log buffer is written out to the log file in each transaction commit and the flush to disk operation was performed on The log file
2:the log buffer is written out to the file in each commit, but the flush to disk operation isn't performed on it
2 The Innodb_autoextend_increment configuration due to the default 8M adjustment to 128M

This configuration key is mainly when the tablespace space is full, need the MySQL system need to automatically expand how much space, each tablespace expansion will make each SQL in wait state. Increasing the size of automatic expansion reduces the number of tablespace automatic extensions.

3 The Innodb_log_buffer_size configuration due to the default 1M adjustment to 16M

This configuration item action sets the INNODB database engine write log buffer; Increasing this cache segment can reduce the number of database write data files.

4 The Innodb_log_file_size configuration due to the default 8M adjustment to 128M

This configuration item action sets the size of the INNODB database engine undo log, thereby reducing database checkpoint operations.

After the above adjustment, the system insertion speed due to the original 10 minutes tens of thousands of to 1 seconds to 1W; Note: The above parameter adjustment needs to be adjusted according to different machines. In particular, Innodb_flush_log_at_trx_commit, innodb_log_buffer_size and innodb_log_file_size need to be carefully adjusted because of the disaster-tolerant handling involved in MySQL itself.

(2) To improve the speed of database reading, heavy database level to improve reading speed mainly due to a few: simplifying SQL, indexing and partitioning; The checker SQL is already the simplest and the query condition has been indexed. We can only use weapons: Table partitioning.

Pre-database MySQL partitioning: in MySQL, table spaces are data files that store data and indexes.
The S11 database is modified to support multiple tablespace because of the shared tablespace;

The Wb_user_info_sina and WB_USER_INFO_TX two tables are modified to separate table spaces (sina:1700w data, 2.6G large data files, Tencent 1400w,2.3g large data files);
Partition operation:
Delete the existing primary key and index first
Recreate the joint primary key of the Id,uid
The key value is then partitioned with UID. At this time to/var/data/mysql view the data file, you can see that two large tables separate table space has been divided into several less independent partition space. (This time, if the query is based on UID, it does not raise the speed; Because the key value is only the partition that arranges the data store, the partition index is not established.) I'm very depressed this is not 1:30 than Oracle. ）
The index is then indexed on the UID field. Again to the/var/data/mysql folder to view data files, very depressed to find that each partition size unexpectedly big. MySQL is still the same way to store indexes and data in the same tablespace. If can index and data separation can better management.

After the above adjustment, temporarily failed to reflect the system read speed upgrade, basically in 2-3 seconds to complete 5K data updates.

MySQL Database insertion Speed adjustment supplementary information:

MySQL from the beginning of the 1000/minute insertion speed up to 10,000/sec. I believe we have been waiting for the relevant introduction, the following I do the whole process of tuning. To improve database insertion performance central idea:
1, try to make the database write data File
2, reduce the checkpoint operation of the database
3, the program as far as possible buffer data, batch type insert and submit
4, reduce the system IO conflict

Based on the above four points, the MySQL service is adjusted as an amateur DBA as follows:
The revision is responsible for recording MySQL server configuration, improve MySQL overall write speed; The following three database variable values: innodb_autoextend_increment, Innodb_log_buffer_size, Innodb_log_ File_size; The default values for these three variables are 5M, 8M, 8M, respectively, depending on the size of the server memory and the specific use of the three, respectively, modified to: 128M, 16M, 128M. At the same time, the original 2 log file was changed to 8 log file. The main changes to meet the first and 2nd, such as: To increase the innodb_autoextend_increment is to avoid the frequent automatic expansion of the data file led to MySQL checkpoint operation;
Turn a large table into a stand-alone table space and partition it, and then hang the different partitions in multiple different hard disk arrays.

After completing the above modification operation; I see the following happiness 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
The amount of data in the last table:
+------------+
| COUNT (*) |
+------------+
| 10000000|
+------------+
From the above results, the increase in data volume will have a certain impact on the insertion performance. However, the overall speed is very negotiable. In less than a day, you can complete 400 million data processing normally. The database bottleneck is expected to be cleverly resolved, the result becomes the program "ape" bitter forced to complain to me, Big Brother need not so ruthless ah.