Description:
When upgrading from a lower version of 8.0 to a higher version, if there are many views in the instance, the memory usage will continue to increase during the upgrade process.
The following is an analysis based on the code:
In commit#196248, the data dictionary elements are processed one by one instead of collecting all and processing them, which alleviates the large amount of memory usage during the data dictionary upgrade process.
But in fact, we can find that in addition to the memory usage of the data dictionary elements themselves (the optimization of commit#196248), there are other memory issues that may have spikes or delayed release. Take the function check_views as an example. If we have a large number of views, the upgrade process will have a large amount of memory usage. The main paths of its memory usage are as follows:
check_views
-->dd::upgrade::invalid_sql
---->LEX::make_sql_cmd
And then, a large number of memory elements such as `class Item` will be built in the current THD, they will not be freed until call THD::free_items.
By the way, the title and the examples here are all based on view. In fact, I think a large number of triggers, events, and routines may also have memory problems. The reason is the same, that is, the memory of THD::mem_root is not released in time.
How to repeat:
We can build a large number of views in the lower version 80 and upgrade the version to reproduce this problem.
--Step 1. Startup lower version MySQL server 8.0 (such as 8.0.40)
--Step 2. Use shell script to create 50000 views.
```shell
for i in {1..50000}; do
echo "CREATE VIEW view_$i AS SELECT 1 AS view_column_name0, 1 AS view_column_name1, 1 AS view_column_name2, 1 AS view_column_name3, 1 AS view_column_name4, 1 AS view_column_name5, 1 AS view_column_name6, 1 AS view_column_name7, 1 AS view_column_name8, 1 AS view_column_name9,
1 AS view_column_name10, 1 AS view_column_name11, 1 AS view_column_name12, 1 AS view_column_name13, 1 AS view_column_name14, 1 AS view_column_name15, 1 AS view_column_name16, 1 AS view_column_name17, 1 AS view_column_name18, 1 AS view_column_name19,
1 AS view_column_name20, 1 AS view_column_name21, 1 AS view_column_name22, 1 AS view_column_name23, 1 AS view_column_name24, 1 AS view_column_name25, 1 AS view_column_name26, 1 AS view_column_name27, 1 AS view_column_name28, 1 AS view_column_name29,
1 AS view_column_name30, 1 AS view_column_name31, 1 AS view_column_name32, 1 AS view_column_name33, 1 AS view_column_name34, 1 AS view_column_name35, 1 AS view_column_name36, 1 AS view_column_name37, 1 AS view_column_name38, 1 AS view_column_name39,
1 AS view_column_name40, 1 AS view_column_name41, 1 AS view_column_name42, 1 AS view_column_name43, 1 AS view_column_name44, 1 AS view_column_name45, 1 AS view_column_name46, 1 AS view_column_name47, 1 AS view_column_name48, 1 AS view_column_name49,
1 AS view_column_name50, 1 AS view_column_name51, 1 AS view_column_name52, 1 AS view_column_name53, 1 AS view_column_name54, 1 AS view_column_name55, 1 AS view_column_name56, 1 AS view_column_name57, 1 AS view_column_name58, 1 AS view_column_name59,
1 AS view_column_name60, 1 AS view_column_name61, 1 AS view_column_name62, 1 AS view_column_name63, 1 AS view_column_name64, 1 AS view_column_name65, 1 AS view_column_name66, 1 AS view_column_name67, 1 AS view_column_name68, 1 AS view_column_name69,
1 AS view_column_name70, 1 AS view_column_name71, 1 AS view_column_name72, 1 AS view_column_name73, 1 AS view_column_name74, 1 AS view_column_name75, 1 AS view_column_name76, 1 AS view_column_name77, 1 AS view_column_name78, 1 AS view_column_name79,
1 AS view_column_name80, 1 AS view_column_name81, 1 AS view_column_name82, 1 AS view_column_name83, 1 AS view_column_name84, 1 AS view_column_name85, 1 AS view_column_name86, 1 AS view_column_name87, 1 AS view_column_name88, 1 AS view_column_name89,
1 AS view_column_name90, 1 AS view_column_name91, 1 AS view_column_name92, 1 AS view_column_name93, 1 AS view_column_name94, 1 AS view_column_name95, 1 AS view_column_name96, 1 AS view_column_name97, 1 AS view_column_name98, 1 AS view_column_name99;"
done > create_views.sql
```
Execute the script and `source create_views.sql` in mysql-server to create 50000 views.
--Step 3: Shutdown lower version 8.0 and startup with higher version 8.0(such as 8.0.41).
During the upgrade process, we can observe that there will be memory spikes, and after the upgrade check is completed, this part of the memory will be released
Suggested fix:
Release mem_root memory in time
Description: When upgrading from a lower version of 8.0 to a higher version, if there are many views in the instance, the memory usage will continue to increase during the upgrade process. The following is an analysis based on the code: In commit#196248, the data dictionary elements are processed one by one instead of collecting all and processing them, which alleviates the large amount of memory usage during the data dictionary upgrade process. But in fact, we can find that in addition to the memory usage of the data dictionary elements themselves (the optimization of commit#196248), there are other memory issues that may have spikes or delayed release. Take the function check_views as an example. If we have a large number of views, the upgrade process will have a large amount of memory usage. The main paths of its memory usage are as follows: check_views -->dd::upgrade::invalid_sql ---->LEX::make_sql_cmd And then, a large number of memory elements such as `class Item` will be built in the current THD, they will not be freed until call THD::free_items. By the way, the title and the examples here are all based on view. In fact, I think a large number of triggers, events, and routines may also have memory problems. The reason is the same, that is, the memory of THD::mem_root is not released in time. How to repeat: We can build a large number of views in the lower version 80 and upgrade the version to reproduce this problem. --Step 1. Startup lower version MySQL server 8.0 (such as 8.0.40) --Step 2. Use shell script to create 50000 views. ```shell for i in {1..50000}; do echo "CREATE VIEW view_$i AS SELECT 1 AS view_column_name0, 1 AS view_column_name1, 1 AS view_column_name2, 1 AS view_column_name3, 1 AS view_column_name4, 1 AS view_column_name5, 1 AS view_column_name6, 1 AS view_column_name7, 1 AS view_column_name8, 1 AS view_column_name9, 1 AS view_column_name10, 1 AS view_column_name11, 1 AS view_column_name12, 1 AS view_column_name13, 1 AS view_column_name14, 1 AS view_column_name15, 1 AS view_column_name16, 1 AS view_column_name17, 1 AS view_column_name18, 1 AS view_column_name19, 1 AS view_column_name20, 1 AS view_column_name21, 1 AS view_column_name22, 1 AS view_column_name23, 1 AS view_column_name24, 1 AS view_column_name25, 1 AS view_column_name26, 1 AS view_column_name27, 1 AS view_column_name28, 1 AS view_column_name29, 1 AS view_column_name30, 1 AS view_column_name31, 1 AS view_column_name32, 1 AS view_column_name33, 1 AS view_column_name34, 1 AS view_column_name35, 1 AS view_column_name36, 1 AS view_column_name37, 1 AS view_column_name38, 1 AS view_column_name39, 1 AS view_column_name40, 1 AS view_column_name41, 1 AS view_column_name42, 1 AS view_column_name43, 1 AS view_column_name44, 1 AS view_column_name45, 1 AS view_column_name46, 1 AS view_column_name47, 1 AS view_column_name48, 1 AS view_column_name49, 1 AS view_column_name50, 1 AS view_column_name51, 1 AS view_column_name52, 1 AS view_column_name53, 1 AS view_column_name54, 1 AS view_column_name55, 1 AS view_column_name56, 1 AS view_column_name57, 1 AS view_column_name58, 1 AS view_column_name59, 1 AS view_column_name60, 1 AS view_column_name61, 1 AS view_column_name62, 1 AS view_column_name63, 1 AS view_column_name64, 1 AS view_column_name65, 1 AS view_column_name66, 1 AS view_column_name67, 1 AS view_column_name68, 1 AS view_column_name69, 1 AS view_column_name70, 1 AS view_column_name71, 1 AS view_column_name72, 1 AS view_column_name73, 1 AS view_column_name74, 1 AS view_column_name75, 1 AS view_column_name76, 1 AS view_column_name77, 1 AS view_column_name78, 1 AS view_column_name79, 1 AS view_column_name80, 1 AS view_column_name81, 1 AS view_column_name82, 1 AS view_column_name83, 1 AS view_column_name84, 1 AS view_column_name85, 1 AS view_column_name86, 1 AS view_column_name87, 1 AS view_column_name88, 1 AS view_column_name89, 1 AS view_column_name90, 1 AS view_column_name91, 1 AS view_column_name92, 1 AS view_column_name93, 1 AS view_column_name94, 1 AS view_column_name95, 1 AS view_column_name96, 1 AS view_column_name97, 1 AS view_column_name98, 1 AS view_column_name99;" done > create_views.sql ``` Execute the script and `source create_views.sql` in mysql-server to create 50000 views. --Step 3: Shutdown lower version 8.0 and startup with higher version 8.0(such as 8.0.41). During the upgrade process, we can observe that there will be memory spikes, and after the upgrade check is completed, this part of the memory will be released Suggested fix: Release mem_root memory in time