Mysql database insertion speed and read speed adjustment record _ MySQL

Mysql database insertion speed and read speed adjustment record bitsCN.com

(1) thoughts on improving database insertion performance: Try to write Data to the Data File at a time and reduce the checkpoint operation of the database. The following four configuration items are modified:
1) set the innodb_flush_log_at_trx_commit configuration to 0, and set it to 0 based on past experience, which greatly improves the insertion speed.

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 written out to the log file at each transaction commit and the flush to disk operation is saved Med on the log file
2: the log buffer is written out to the file at each commit, but the flush to disk operation is not supported Med on it
2) adjust the innodb_autoextend_increment configuration to 128 M by default.

This configuration item is mainly used to determine how much space the MySQL system needs to automatically expand when the tablespace space is full. every time the tablespace is extended, each SQL statement remains in the waiting state. Increasing the automatic scaling Size can reduce the number of automatic scaling of tablespace.

3) set innodb_log_buffer_size to 16 MB by default.

This configuration item sets the innodb database engine's log writing cache. increasing the cache segment can reduce the number of times the database writes data files.

4) adjust the innodb_log_file_size configuration to 128 MB by default.

This configuration item sets the size of the UNDO log of the innodb database engine to reduce the database checkpoint operation.

After the above adjustment, the system insertion speed is increased from tens of thousands of entries in 10 minutes to about in 1 second. note: The above parameter adjustment needs to be adjusted based on different machines. In particular, innodb_flush_log_at_trx_commit, innodb_log_buffer_size, and innodb_log_file_size must be adjusted with caution because it involves the disaster tolerance of MySQL itself.

(2) improve database read speedReading speed increases at the database-heavy layer mainly due to the following reasons: simplifying SQL statements, adding indexes and partitioning; checking program SQL is already the simplest, and adding indexes to query conditions. We can only use weapons: table partitioning.

Prepare for MySQL database partitioning: In MySQL, the tablespace is the data file that stores data and indexes.
Modify the S11 database to support multiple tablespaces because of the same tablespace;

Modify the tables wb_user_info_sina and wb_user_info_tx to their independent tablespaces (Sina: 1700W data, 2.6 GB big data file, Tencent 1400 W, 2.3G big data file );
Partition operation:
Delete existing primary keys and indexes first
Reproduce the joint primary key with the id and uid
Then partition with uid as the key value. When you view the data file in/var/data/mysql, you can see that the independent tablespace of the two tables has been split into several less independent partitions. (At this time, if the query condition is uid, it does not increase the speed; because the key value is only the partition for data storage and does not create a partition index. I am very depressed. this is worse than Oracle at half past one .)
Then create an index on the uid field. Check the data file in the/var/data/mysql folder again and find that the Size of each partition is huge. MySQL still stores indexes and data in the same tablespace. It can be better managed if index and data separation are available.

After the above adjustments, the system reading speed has not been improved for the time being; the speed is generally between 2 and 2 ~ 5 K data is updated in 3 seconds.

Additional information on MySQL database insertion speed adjustment:

MySQL increased the insert speed from 1000 at the beginning to 10000 per minute. I believe everyone is waiting for a brief introduction. the whole process of optimization is as follows. Thoughts on improving database insertion performance:
1. try to make the database write Data File at one time
2. reduce database checkpoint operations
3. buffer data as much as possible in the program for batch insertion and submission
4. reduce system IO conflicts

As an amateur DBA, the MySQL service is adjusted as follows:
Modify and record the MySQL server configurations to improve the overall MySQL write speed. Specifically, the following three database variable values are: innodb_autoextend_increment, innodb_log_buffer_size, and innodb_log_file_size; the default values of these three variables are 5 M, 8 M, and 8 M respectively. modify these three variables: 128 M, 16 M, 128 M. At the same time, the original two Log files are also changed to eight Log files. This modification mainly satisfies the first and second steps. for example, innodb_autoextend_increment is added to avoid MySQL checkpoint operations due to frequent automatic expansion of Data files;
Convert a large table into an independent table empty and partition it, and then attach different partitions to multiple hard disk arrays.

After completing the above modification, I can see the following happy results:

Obtain 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 in the last table:
+ ------------ +
| Count (*) |
+ ------------ +
| 1, 10000000 |
+ ------------ +
From the above results, the increased data volume will have a certain impact on the insert performance. However, the overall speed is still very negotiable. In less than one day, 0.4 billion pieces of data can be processed normally. It is expected that the database bottleneck has been cleverly solved, and the result turns into a program "", complaining to me hard, big brother don't have to be so embarrassed.

BitsCN.com