On Percona Server 5.7.25, memory usage is continuously increasing when we run a stored procedure. This stored procedure basically copies records from one table to an other similar table in batches.
It looks some data is being cached/stored in memory when running the stored procedure. We tested further and could figure out that the memory usage is increasing only when we enable below configurations.
We have attached the below details required.
- Stored procedure schema
- Required Tables schema
Steps to reproduce:
1) Set up a Percona Server 5.7.25 server instance.
2) Copy the my.cnf attached or enable the configuration items mentioned above.
3) Enabled memory instrument.
4)Create the stored procedure as in the attachemnt.
5) Create TEST table as in the attachment TEST.sql.
6) Create similar table TEST1 from TEST.
7) Load 100000 (more rows if testing on SSD drive) records by mysql_random_data_load(latest) tool.
8) run the stored procedure.
9) Check memory information using below query.
NOTE: Stored procedure initiallTEy fetches records and then starts INSERTING into the new table. Memory raise by the above query will appear only after the INSERTs start. This takes a 1 - 3 minutes.
- With more number of records in the TEST table, memory usage by the event "memory/sql/thd::main_mem_root" will be increasing and this would lead to OOM issues.