Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-5581

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

    Details

    • Type: Bug
    • Status: Pending Release
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: Not 5.6, Not 8.0, 5.7.23-25
    • Fix Version/s: 8.0.16-7, 5.7.NEXT
    • Component/s: None
    • Environment:

      Percona Server 5.7.25, Percona Server 5.7.26
      Ubuntu 18.X

      Description

      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.

       

        Smart Checklist

          Attachments

          1. my.cnf
            1 kB
          2. SP_TEST.sql
            14 kB
          3. TEST.sql
            5 kB

            Issue Links

              Activity

                People

                • Assignee:
                  zsolt.parragi Zsolt Parragi
                  Reporter:
                  uday.varagani Uday Varagani
                • Votes:
                  1 Vote for this issue
                  Watchers:
                  6 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - Not Specified
                    Not Specified
                    Logged:
                    Time Spent - 2 days, 2 hours, 47 minutes
                    2d 2h 47m