Shulou(Shulou.com)06/01 Report--

1. Purpose

Through the comparison test, the performance comparison before and after the tabC split scheme of a data table is analyzed, and the feasibility of the split scheme is determined.

two。 Method

The two types before and after the split scheme are compared and tested.

At the same time, two update methods are used in each test:

1. Similar to the original, all fields of the data table are updated multiple times.

two。 Updates to all fields of the data table are completed at once

3. Environment

This test uses online actual data import. There are 1.32 million rows in the tabC table, with a total of 100G.

Reassemble all the fields to ensure that the actual row length of each sub-table is no longer than 8KB, and split into 3 child tables with the following sizes:

TabC_1.ibd 796M

TabC_2.ibd 10.2G

TabC_3.ibd 8.4G

After that, the full table is updated randomly, and each round of tests is completed by calling stored procedures concurrently in the script, up to a maximum of 10 concurrent processes.

Restart mysqld before each test without additional pressure to ensure a fair environment.

4. Result

Full table, multiple update02:56:4900:16:06, one update00:54:0200:08:56

5. Conclusion

It is clear from the test results that the efficiency of concurrent updates after the sub-table is much higher than that before the score table, and it can be greatly improved if multiple updates to the tblC table can be merged together.

Therefore, it is recommended that:

1. Implement the same split scheme for other tables similar to the tblC table

two。 It is best to update the data table at one time, rather than dividing multiple fields into multiple transactions.

Sub-table principle:

1. Separate the text field from the original table

two。 Reassemble the separated text fields to ensure that the actual row length of each subtable is not higher than 8KB

3. If possible, do not use text. If the actual length is less than 255, convert it directly to varchar.

-- split line--

Http://zhishuedu.com training is a professional high-quality training brand jointly launched by senior MySQL experts Ye Jinrong and Wu Bingxi. It mainly includes MySQL DBA actual combat optimization and Python operation and maintenance development courses. It is the most conscientious and quality training course in the industry.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.