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

Performance degradation during sustained full table scan

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: New
    • Priority: High
    • Resolution: Unresolved
    • Affects Version/s: 8.0.15-5
    • Fix Version/s: None
    • Component/s: None
    • Environment:

      Ubuntu 18.4, 2x12 Core Xeon class CPU, 512GB RAM, RAID storage

      Description

      On a large database 40+TB with mixed tables, both TokuDB and InnoDB, when doing full table scans (plain select count in range, the read performance degrades over time. The hardware setup scales up to 2000-2500 IOPS when doing concurrent reads. When doing a simple count of all elements concurrently (16 threads), the database reads a full IO capacity then in a matter of hours, it slowly starts degrading. After about 18 hours and about 3000 out of 80000 tables read, it stabilizes at about ~300 IOPS,  well below RAID array capacity, CPU usage 50-60% of one core. The behavior suggests some kind of internal locking or some kind of lock leak since neither the CPU or disk IO capacity are used. 

      When running same scenario over Percona MySQL 5.6 or Percona MySQL 5.7, the read performance stays at same levels for the entire duration and both appear to be limited by disk IO capacity (disk is always in overloaded state). Configuration parameters used were the same between versions 5.6, 5.7 and 5.8.

      The degradation can be observed also when executing sequential range indexed queries.  Also, I cannot fully confirm yet, but I suspect the behavior may affect only InnoDB engine, since most of the times when it read from TokuDB tables, it passed through them very fast (although due to the layout, TokuDB tables are read at beginning so may be pure coincidence).

      Node is part of a replication setup. Below the settings can be observed (all the settings that are not MySQL 8 specific were the same during all tests)

      table_open_cache = 200000
      table_open_cache_instances = 2
      table_definition_cache = 200000
      thread_stack = 512K
      thread_cache_size = 1000
      max_allowed_packet = 128M
      character-set-server = UTF8MB4
      collation-server = utf8mb4_unicode_ci
      open_files_limit = 500000
      lower_case_table_names = 0
      tmp-table-size = 512M
      sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

      1. InnoDB engine configuration
        innodb_file_per_table = true
        innodb_buffer_pool_size = 262144M
        innodb_buffer_pool_instances = 16
        innodb_flush_log_at_trx_commit = 1
        innodb_read_io_threads = 20
        innodb_write_io_threads = 64
        innodb_io_capacity = 2000
        innodb_log_file_size = 256M
        innodb_numa_interleave = 1
        innodb_online_alter_log_max_size = 1024M
        innodb-sort-buffer-size = 64M
        innodb-buffer-pool-dump-at-shutdown = 0
        innodb-buffer-pool-load-at-startup = 0
      1. TokuDB engine configuration
        tokudb_row_format = TOKUDB_ZLIB
        tokudb_cache_size = 32768M
        tokudb_lock_timeout = 30000
        tokudb_checkpointing_period = 86400
        tokudb_fs_reserve_percent = 1

       

       

       

       

       

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                sergiuhlihor Sergiu Hlihor
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: