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

Modify Session temporary tablespace truncation on connection disconnect to reduce high CPU usage

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 8.0.20-11
    • Fix Version/s: 8.0.22-13
    • Component/s: None
    • Labels:

      Description

      Upstream:  https://bugs.mysql.com/bug.php?id=98869

      With a large buffer pool, when thread-local intrinsic InnoDB tablespace is truncated at disconnection, the full LRU list scan leads to InnoDB choke. As a result, you will notice stall for transactions and High CPU usage spikes. 

      Tested with PS 8.0.20

      Reproduciable test case:

      To repeat this, you need a large BP loaded with data init. In our test  following is the configuration,

       

      innodb_flush_log_at_trx_commit=2
      innodb_log_file_size=4G
      innodb_buffer_pool_size=180G
      innodb_flush_method=O_DIRECT

       

      Loaded data using Sysbench  45 tables with 25M rows in each table.

      To load data in innodb buffer run the following,
      for i in $(seq 1 45); do ./use -e "SELECT * FROM sbtest.sbtest${i}"> /dev/null & if (( $i % 10 == 0 )); then wait; fi; done
      As a result ,  innodb buffer size reached ~150GB

       

      In the new session run,

      1. select count(*) from information_schema.processlist; // Intrinsic Temporary Table
       or
       2. create a temporary innodb table explicitly. // External Temporary Table

      disconnection and you will notice this issue.

      The cpu usage is the result of `pidstat -u -p <mysql pid> 1`.
      The stack is from `perf record -ag -p <pid>` and `perf report`.

      Our perf result shows that mysql spent most of the time on at "buf_LRU_flush_or_remove_pages"  which same what we found in the user case.

       

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              satya.bodapati Satya Bodapati
              Reporter:
              lalit.choudhary Lalit Choudhary
              Votes:
              1 Vote for this issue
              Watchers:
              5 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 - 1 week, 15 minutes
                  1w 15m

                    Smart Checklist