Share some of the problems I encountered during three days of performance tuning

In the past few days, I have been looking at a performance-tuned stored procedure. The running time of this stored procedure in the customer's production environment (the database is mysql 5.1) is more than 30 to 40 minutes. The customer's request is to improve

Implementation of this stored procedure:

1. The queries of the two tables (due to business needs, also involve subqueries of the table) are inserted into the temporary table respectively; the data of the two tables are 310W+, 120W

2. Some processing needs to be done in the middle, and the data group by of the temporary table is inserted into another temporary table;

3. Finally, the data group by of the temporary table is inserted I arrived at a formal table and inserted 140W+

of data. Then I went further and further on the road to optimizing performance in the past few days, trying all kinds of methods and experiencing all kinds of hardships.

Pictures from App

Various attempts:

1. I feel that the logic of this implementation is a bit Complex, and then simplified the implementation according to my ideas. However performance is not improved. Because my implementation brings the large amount of data to the front, and subsequent operations have to operate on this large amount of data, such as group by. So although my implementation is logically simplified, the performance is not improved.

2. According to different logical identifiers, two sets of temporary tables are created, so that the amount of data in one table will not be so large, hoping to reduce some pressure on subsequent operations. Still ended in failure. The reason is that because of the setting of logical identifiers, everything is based on one set of logic. The second set of logic is just a formality and will not actually check the table with millions of data, so the pressure is still on the table with more than 3 million. .

3. Use prepared statements. In fact, I don’t know much about the mechanism of preprocessing statements. I just heard that preprocessing is more efficient. The performance is still not improved, probably because there are not many similar queries or inserts. Hmm, I don’t quite understand the preprocessing mechanism.

4. Remove the subquery and use a temporary table to save this part of the data. In this way, the table with more than 3 million data still has to be checked twice, and there is no performance improvement.

5. Change the engine of the temporary table from myisam to memory, and set the global variables max_heap_table_size and tmp_table_size of the database to 1000M, the same as the production environment. The result still reports

The table 'tmp_item_bu_parter_price' is full

, so the amount of data is too large, causing the memory to burst?

Sixth, I also followed different branches of logic, but found that they all followed the same logic. This is a bit of a disadvantage. I spent a long time thinking about this before and didn't find out the customer's settings first.

7. Multi-threading. Since the syntax of insert into ...select... is used in the stored procedure, and the where condition is not indexed, a full table lock is caused. The result of the multi-threaded test is undoubtedly the lock table, and some data execution fails.

8. Logically, it is not inserted in full, but in increments, but the existing data still needs to be updated. So the performance should be about the same.

Performance is mainly consumed by inserting millions of data. Now I am completely at a loss and don't know how to deal with it.

【Related recommendations】

1. Free mysql online video tutorial

2. MySQL latest manual tutorial

3. Those things about database design

The above is the detailed content of Share some of the problems I encountered during three days of performance tuning. For more information, please follow other related articles on the PHP Chinese website!