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

In bulk insert, pre-sort and build indexes one page at a time

Details

    Description

      In MDEV-515, the original intention was to do 2 things to speed up INSERT to an empty table or partition:

      1. Stop writing undo log records for each inserted record, and instead write just one saying that the table was empty.
      2. When the table was empty, pre-sort the records for each index, and build the indexes one page at a time, instead of the current one unsorted record at a time.

      The undo logging change turned out to be rather challenging on its own, because it affects MVCC and locking as well. Preventing the useless undo logging should speed up ROLLBACK and recovery, and also the purge of history, compensating for MDEV-12288. Hence, it makes sense to complete MDEV-515 in the limited form.

      The purpose of this task is to make INSERT into an empty table or partition even more efficient by making use of sorting. We expect data loads into huge tables with many secondary indexes to become faster. The change buffer (MDEV-11634) should no longer come into play in this case. (Huge loads into nonempty tables will be unaffected by this enhancement.)

      After implementation comments:
      The following queries will use the new optimization:

      • CREATE ... SELECT
      • Inserting into an empty table together with SET STATEMENT unique_checks=0,foreign_key_checks=0 FOR ...
        • INSERT statement
        • INSERT ... SELECT
        • LOAD DATA INFILE

      Insert into an empty table without unique_checks=0,foreign_key_checks=0 will NOT be optimized.

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26155 Mangled data after IMPORT tablespace up till assert for DDL

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33087 ALTER TABLE...ALGORITHM=COPY should build indexes more efficiently

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27220 Assertion `!(index)->is_spatial()' failed in BtrBulk::BtrBulk(dict_index_t*, const trx_t*)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27312 LeakSanitizer error in trx_mod_table_time_t::start_bulk_insert

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27316 Assertion `!(index)->is_spatial()' failed.

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27318 SIGSEGV in row_merge_tuple_sort and Assertion `data_size < srv_sort_buf_size' failed in row_merge_bulk_buf_add on INSERT with unique_checks and foreign_key_checks disabled

          • 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-27858 Assertion `page_dir_get_n_heap(new_page) == 2U' failed in PageBulk::init

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27890 SIGSEGV in innobase_get_mysql_key_number_for_index and Assertion `table->magic_n == 76333786' failed in dict_table_get_first_index and InnoDB: Failing assertion: i > 0 and Assertion `!trx->check_foreigns' failed on INSERT

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27953 Assertion `!check_unique_secondary' failed in trx_t::check_bulk_buffer

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28138 MariaDB Assertion Failed in mtr_buf_t::has_space

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28242 Assertion `!check_foreigns' failed in trx_t::check_bulk_buffer

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28865 Assertion `!check_foreigns' failed in trx_t::bulk_insert_apply

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29570 LeakSanitizer error in trx_mod_table_time_t::start_bulk_insert #4

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30426 Assertion !rec_offs_nth_extern(offsets2, n) during bulk insert

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30796 Auto_increment values changed after restart

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31025 Redundant table alter fails when fixed column stored externally

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

          Task - A task that needs to be done. MDEV-515 innodb bulk insert

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26623 Possible race condition between statistics and bulk insert

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

          Task - A task that needs to be done. MDEV-16281 Implement parallel CREATE INDEX, ALTER TABLE, or bulk load

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-25036 use bulk insert optimization for multiple insert statements

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26947 UNIQUE column checks fail in InnoDB resulting in table corruption

          • 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-27751 InnoDB: Failing assertion: !cursor->index->is_committed() in row0ins.cc line 221 (10.7+)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27971 SIGSEGV in trx_undo_build_roll_ptr on INSERT with binary logging enabled and using XA

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28237 Assertion `0' failed in row_upd_sec_index_entry on DELETE

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28400 LeakSanitizer error in trx_mod_table_time_t::start_bulk_insert #3

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29761 Bulk insert fails to rollback during insert..select

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29989 binlog_do_db option breaks importing sql dumps

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30063 InnoDB: Failing assertion: ib_table->stat_initialized in ha_innobase::info_low()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30489 Assertion `trx->bulk_insert' failed in innodb_prepare_commit_versioned on SET autocommit=ON

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31027 Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed in Diagnostics_area::set_ok_status on EXECUTE of prepared statement with savepoint

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31298 Assertion `!check_foreigns' failed in trx_mod_table_time_t* trx_t::check_bulk_buffer(dict_table_t*), Assertion `table->skip_alter_undo || !check_unique_secondary' failed in trx_t::check_bulk_buffer

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31537 Bulk insert operation aborts the server for redundant table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32453 Bulk insert fails to apply when trigger does insert operation

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33243 Assertion `!check_foreigns' failed in dberr_t trx_t::bulk_insert_apply_low()

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-5171 Add support for --innodb-optimize-keys to mysqldump.

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-16226 TRX_ID-based System Versioning refactoring

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26740 Inplace alter rebuild increases file size

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27214 Import with disabled keys corrupts meta-data like rows, indexes, ...

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28679 After upgrade to 10.7.3-1 with enabled data-at-rest encryption unable to restore dump file.

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31667 SEGV in trx_undo_report_row_operation during some INSERT

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31686 InnoDB: Failing assertion: heap_no < n_heap in page0zip.cc

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34703 Innodb bulk load : high IO, crashes on Linux, OOM on Linux, as tested with LOAD DATA INFILE

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34719 Disable purge for LOAD DATA INFILE into empty table

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

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä created issue -
            marko Marko Mäkelä made changes -
            Field Original Value New Value
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            serg Sergei Golubchik made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            marko Marko Mäkelä made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Axel Schwenke [ axel ]
            Priority Major [ 3 ] Critical [ 2 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            marko Marko Mäkelä made changes -
            Assignee Axel Schwenke [ axel ] Thirunarayanan Balathandayuthapani [ thiru ]
            marko Marko Mäkelä made changes -
            issue.field.resolutiondate 2021-10-26 12:18:11.0 2021-10-26 12:18:11.414
            marko Marko Mäkelä made changes -
            Fix Version/s 10.7.1 [ 26120 ]
            Fix Version/s 10.7 [ 24805 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 118240 ] MariaDB v4 [ 134379 ]
            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 -
            Roel Roel Van de Paar made changes -
            Roel Roel Van de Paar made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Roel Roel Van de Paar made changes -
            marko Marko Mäkelä made changes -
            cmaehler Christopher Mähler made changes -
            Roel Roel Van de Paar made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            bar Alexander Barkov made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            marko Marko Mäkelä made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels performance Preview_10.7 performance
            Roel Roel Van de Paar made changes -
            Roel Roel Van de Paar made changes -
            marko Marko Mäkelä made changes -
            alice Alice Sherepa 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 -
            midenok Aleksey Midenkov made changes -
            marko Marko Mäkelä made changes -
            monty Michael Widenius made changes -
            Description In MDEV-515, the original intention was to do 2 things to speed up {{INSERT}} to an empty table or partition:
            # Stop writing undo log records for each inserted record, and instead write just one saying that the table was empty.
            # When the table was empty, pre-sort the records for each index, and build the indexes one page at a time, instead of the current one unsorted record at a time.

            The undo logging change turned out to be rather challenging on its own, because it affects MVCC and locking as well. Preventing the useless undo logging should speed up {{ROLLBACK}} and recovery, and also the purge of history, compensating for MDEV-12288. Hence, it makes sense to complete MDEV-515 in the limited form.

            The purpose of this task is to make {{INSERT}} into an empty table or partition even more efficient by making use of sorting. We expect data loads into huge tables with many secondary indexes to become faster. The change buffer (MDEV-11634) should no longer come into play in this case. (Huge loads into nonempty tables will be unaffected by this enhancement.)             		In MDEV-515, the original intention was to do 2 things to speed up {{INSERT}} to an empty table or partition:
            # Stop writing undo log records for each inserted record, and instead write just one saying that the table was empty.
            # When the table was empty, pre-sort the records for each index, and build the indexes one page at a time, instead of the current one unsorted record at a time.

            The undo logging change turned out to be rather challenging on its own, because it affects MVCC and locking as well. Preventing the useless undo logging should speed up {{ROLLBACK}} and recovery, and also the purge of history, compensating for MDEV-12288. Hence, it makes sense to complete MDEV-515 in the limited form.

            The purpose of this task is to make {{INSERT}} into an empty table or partition even more efficient by making use of sorting. We expect data loads into huge tables with many secondary indexes to become faster. The change buffer (MDEV-11634) should no longer come into play in this case. (Huge loads into nonempty tables will be unaffected by this enhancement.)

            After implementation comments:
            The following queries will use the new optimization:
            - CREATE ... SELECT
            - Inserting into an empty table together with SET STATEMENT unique_checks=0,foreign_key_checks=0 FOR ...
              - INSERT statement
              - INSERT ... SELECT
              - LOAD DATA INFILE

            Insert into an empty table without unique_checks=0,foreign_key_checks=0 will NOT be optimized.
             
            monty Michael Widenius made changes -
            Description In MDEV-515, the original intention was to do 2 things to speed up {{INSERT}} to an empty table or partition:
            # Stop writing undo log records for each inserted record, and instead write just one saying that the table was empty.
            # When the table was empty, pre-sort the records for each index, and build the indexes one page at a time, instead of the current one unsorted record at a time.

            The undo logging change turned out to be rather challenging on its own, because it affects MVCC and locking as well. Preventing the useless undo logging should speed up {{ROLLBACK}} and recovery, and also the purge of history, compensating for MDEV-12288. Hence, it makes sense to complete MDEV-515 in the limited form.

            The purpose of this task is to make {{INSERT}} into an empty table or partition even more efficient by making use of sorting. We expect data loads into huge tables with many secondary indexes to become faster. The change buffer (MDEV-11634) should no longer come into play in this case. (Huge loads into nonempty tables will be unaffected by this enhancement.)

            After implementation comments:
            The following queries will use the new optimization:
            - CREATE ... SELECT
            - Inserting into an empty table together with SET STATEMENT unique_checks=0,foreign_key_checks=0 FOR ...
              - INSERT statement
              - INSERT ... SELECT
              - LOAD DATA INFILE

            Insert into an empty table without unique_checks=0,foreign_key_checks=0 will NOT be optimized.
                          		In MDEV-515, the original intention was to do 2 things to speed up {{INSERT}} to an empty table or partition:
            # Stop writing undo log records for each inserted record, and instead write just one saying that the table was empty.
            # When the table was empty, pre-sort the records for each index, and build the indexes one page at a time, instead of the current one unsorted record at a time.

            The undo logging change turned out to be rather challenging on its own, because it affects MVCC and locking as well. Preventing the useless undo logging should speed up {{ROLLBACK}} and recovery, and also the purge of history, compensating for MDEV-12288. Hence, it makes sense to complete MDEV-515 in the limited form.

            The purpose of this task is to make {{INSERT}} into an empty table or partition even more efficient by making use of sorting. We expect data loads into huge tables with many secondary indexes to become faster. The change buffer (MDEV-11634) should no longer come into play in this case. (Huge loads into nonempty tables will be unaffected by this enhancement.)

            After implementation comments:
            The following queries will use the new optimization:
            - CREATE ... SELECT
            - Inserting into an empty table together with SET STATEMENT unique_checks=0,foreign_key_checks=0 FOR ...
              -- INSERT statement
              -- INSERT ... SELECT
              -- LOAD DATA INFILE

            Insert into an empty table without unique_checks=0,foreign_key_checks=0 will NOT be optimized.
             
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -

            People

              thiru Thirunarayanan Balathandayuthapani
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              12 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.