Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23855

InnoDB log checkpointing causes regression for write-heavy OLTP

Details

    Description

      Workload: sysbench OLTP write-only, i.e. as it is used by the regression benchmark suite in t_writes_innodb_multi.

      Setup: 16G buffer pool. 4G redo log. 4G or 8G data set. innodb_flush_neighbors = 0, innodb_io_capacity = 1000 (or 5000, 10000)

      Observation: after starting high, performance drops after ~ 1 minute. If waiting long enough, one can see oscillations in throughput. This seems to be related to Innodb_checkpoint_age reaching Innodb_checkpoint_max_age. There seems to be no LRU flushing at all, only flush_list flushing.

      Attachments

        1. Innodb_io_capacity.png
          Innodb_io_capacity.png
          66 kB
        2. Innodb-max-dirty-pages-pct-lwm.png
          Innodb-max-dirty-pages-pct-lwm.png
          60 kB
        3. oltp_ts_128.png
          oltp_ts_128.png
          30 kB
        4. oltp_ts_256.png
          oltp_ts_256.png
          39 kB
        5. oltp_ts_64.png
          oltp_ts_64.png
          27 kB
        6. oltp_ts_64.png
          oltp_ts_64.png
          41 kB
        7. oltp_ts_8.png
          oltp_ts_8.png
          26 kB
        8. Screen Shot 2020-10-20 at 5.59.20 PM.png
          Screen Shot 2020-10-20 at 5.59.20 PM.png
          690 kB

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-11659 Move the InnoDB doublewrite buffer to flat files

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-23756 Implement event-driven innodb_adaptive_flushing=OFF that ignores innodb_io_capacity

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24000 Reduce fil_system.mutex contention

          • Major - Major loss of function.
          • Closed
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24049 InnoDB: Failing assertion: node->is_open() in fil_space_t::flush_low

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24053 MSAN use-of-uninitialized-value in tpool::simulated_aio::simulated_aio_callback()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24101 innodb_random_read_ahead=ON causes hang on DDL or shutdown

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24109 InnoDB hangs with innodb_flush_sync=OFF

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24190 Accessing INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION may break innodb_open_files logic

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24313 Hang with innodb_use_native_aio=0 and innodb_write_io_threads=1

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24348 InnoDB shutdown hang with innodb_flush_sync=0

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24391 heap-use-after-free in fil_space_t::flush_low()

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24442 Assertion `space->referenced()' failed in fil_crypt_space_needs_rotation

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24537 innodb_max_dirty_pages_pct_lwm=0 lost its special meaning

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25093 Adaptive flushing fails to kick in even if innodb_adaptive_flushing_lwm is hit. (possible regression)

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25110 [ERROR] [FATAL] InnoDB: Trying to write 16384 bytes at 1032192 outside the bounds

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25214 Server crash in fil_space_t::try_to_close or Assertion `!node->is_open()' failed in fil_node_open_file_low

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25215 Excessive logging "Cannot close file ./test/FTS_xxx.ibd because of n pending operations and pending fsync

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25613 mariabackup.absolute_ibdata_paths caused signal 6 (assertion failure)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25948 Remove log_flush_task

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26293 InnoDB: Failing assertion: space->is_ready_to_close() || space->purpose == FIL_TYPE_TEMPORARY || srv_fast_shutdown == 2 || !srv_was_started

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30227 [ERROR] [FATAL] InnoDB: fdatasync() returned 9

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30860 Race condition between buffer pool flush and log file deletion in mariadb-backup --prepare

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31124 Innodb_data_written miscounts doublewrites

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31347 fil_ibd_create() may hijack the file handle of an old file

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32150 InnoDB reports corruption on 32-bit platforms with ibd files sizes > 4GB

          • Major - Major loss of function.
          • Closed
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23986 [ERROR] [FATAL] InnoDB: Page ... name ... page_type ... key_version 1 lsn ... compressed_len ...

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24012 Assertion `cursor->is_system() || srv_operation == SRV_OPERATION_RESTORE_DELTA || xb_close_files' failed.

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-11384 AliSQL: [Feature] Issue#19 BUFFER POOL LIST SCAN OPTIMIZATION

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15949 InnoDB: Failing assertion: space->n_pending_ops == 0 in fil_delete_tablespace upon DROP TABLE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20648 InnoDB: Failing assertion: !(*node)->being_extended, innodb.log_data_file_size failed in buildbot, assertion `!space->is_stopping()'

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24350 buf_dblwr unnecessarily uses memory-intensive srv_stats counters

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24661 The test innodb.innodb_wl6326_big often times out

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Task - A task that needs to be done. MDEV-24854 Change innodb_flush_method=O_DIRECT by default

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24949 Enabling idle flushing (possible regression from MDEV-23855)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25425 Useless message "If the mysqld execution user is authorized page cleaner thread priority can be changed."

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26855 Enable spinning for log_sys_mutex and log_flush_order_mutex

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33770 Alter operation hangs when encryption thread works on the same tablespace

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14462 Confusing error message: ib_logfiles are too small for innodb_thread_concurrency=0

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15752 Possible race between DDL and accessing I_S.INNODB_TABLESPACES_ENCRYPTION

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23399 10.5 performance regression with IO-bound tpcc

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23929 innodb_flush_neighbors is not being ignored for system tablespace on SSD

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25003 mtflush_service_io - workitem status is always SUCCESS even if pool flush fails

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27295 MariaDB 10.5 does not do idle checkpoint (regression). Marked as fixed on 10.5.13 but it is not

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            axel Axel Schwenke created issue -
            axel Axel Schwenke made changes -
            Field Original Value New Value
            axel Axel Schwenke made changes -
            Attachment oltp_ts_64.png [ 54065 ]
            Attachment oltp_ts_128.png [ 54066 ]
            Attachment oltp_ts_256.png [ 54067 ]
            marko Marko Mäkelä made changes -
            axel Axel Schwenke made changes -
            axel Axel Schwenke made changes -
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Vladislav Vaintroub [ wlad ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            abdelmawla Abdelmawla Gharieb made changes -
            Attachment Screen Shot 2020-10-20 at 5.02.56 PM.png [ 54439 ]
            abdelmawla Abdelmawla Gharieb made changes -
            Attachment Screen Shot 2020-10-20 at 5.02.56 PM.png [ 54439 ]
            abdelmawla Abdelmawla Gharieb made changes -
            Attachment Screen Shot 2020-10-20 at 5.59.20 PM.png [ 54440 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Assignee Vladislav Vaintroub [ wlad ] Marko Mäkelä [ marko ]
            marko Marko Mäkelä made changes -
            issue.field.resolutiondate 2020-10-26 16:06:16.0 2020-10-26 16:06:16.493
            marko Marko Mäkelä made changes -
            Fix Version/s 10.5.7 [ 25019 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Resolution Fixed [ 1 ]
            Status In Review [ 10002 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            axel Axel Schwenke made changes -
            Attachment Innodb-max-dirty-pages-pct-lwm.png [ 54636 ]
            axel Axel Schwenke made changes -
            Attachment Innodb_io_capacity.png [ 54637 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            axel Axel Schwenke made changes -
            Epic Link MDEV-26620 [ 102791 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 114047 ] MariaDB v4 [ 158419 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -

            People

              marko Marko Mäkelä
              axel Axel Schwenke
              Votes:
              0 Vote for this issue
              Watchers:
              14 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.