High memory usage with log_slow_verbosity=innodb while running a stored procedure.

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.

long_query_time = 1
slow_query_log

log_slow_verbosity=full

We have attached the below details required.

1. Stored procedure schema
2. Required Tables schema
3. my.cnf

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.

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%memory%';

4)Create the stored procedure as in the attachemnt.

# mysql-uroot -pxxxxxx TESTDB < SP_TEST.sql

5) Create TEST table as in the attachment TEST.sql.

# mysql -uroot -pxxxxxx TESTDB < TEST.sql

6) Create similar table TEST1 from TEST.

mysql> CREATE TABLE TEST1 LIKE TEST;

7) Load 100000 (more rows if testing on SSD drive) records by mysql_random_data_load(latest) tool.

# ./mysql_random_data_load -uroot -pxxxxxx -h127.0.0.1 --port=3306 --bulk-size=100 TESTDB1 TEST 100000

8) run the stored procedure.

mysql> USE TESTDB ;
mysql> CALL SP_TEST("test",1000);

9) Check memory information using below query.

mysql> select event_name, CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_global_by_event_name WHERE event_name like "%main_mem_root%"; 

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.