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

innodb bulk insert

    XMLWordPrintable

Details

    Description

      The original description of this task read as follows:

      as a solution for bulk-inserting huge amount of data into innodb, we consider an utility that creates exported innodb tablespaces.

      Normally one needs to create a table in InnoDB or XtraDB and then "export" it. But we could try to create an exported tablespace with a table not by actually exporting it but directly from the data, say, from XML or CSV.

      In the end, we decided to optimize the INSERT code path inside InnoDB. There are many different InnoDB ROW_FORMAT, and we do not have instant import until MDEV-11658 gets some thought. Besides, if we make INSERT faster, also some normal workloads will benefit.

      We implement an idea that was suggested by monty in October 2017: When InnoDB is inserting into an empty table or partition, we can write a single undo log record TRX_UNDO_EMPTY to cover the operation. ROLLBACK would clear the table when undoing this record.

      For this to work, the insert into an empty table or partition must be covered by an exclusive table lock that will be held until the transaction has been committed or rolled back, or until a partial rollback that affects the INSERT operation (so that the table is empty again).

      Concurrent non-locking reads must be adjusted: If the read view was created before the INSERT into an empty table was committed, then we must continue to imagine that the table is empty, and not try to read any records. If the read view was created after the COMMIT of the INSERT, then all records must be visible normally.

      This special handling only applies to the very first INSERT statement of a transaction for the empty table or partition. If a subsequent statement in the transaction is modifying the initially empty table again, we must enable row-level undo logging, so that we will be able to roll back to the start of the statement in case of an error (such as duplicate key).

      INSERT IGNORE will continue to use row-level logging and locking, because implementing it would require the ability to roll back the latest row, while our table-level logging only allows the entire statement to be rolled back.

      Future work:

      • MDEV-24621 will make such INSERT even faster, by sorting the records for each index separately and by building indexes one page at a time.
      • MDEV-24622 will enable this feature on replicas (slaves).
      • MDEV-24623 will enable this feature on Galera cluster.

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23484 Rollback unnecessarily acquires dict_operation_lock for every row

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

          Task - A task that needs to be done. MDEV-24621 In bulk insert, pre-sort and build indexes one page at a time

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24622 Replication does not support bulk insert into empty table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24623 Galera does not support MDEV-515 bulk insert

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24700 Assertion `"lock not found" == 0' failed in lock_table_x_unlock

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24715 Assertion `!node->table->skip_alter_undo' failed in row_upd_clust_rec on CREATE

          • 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-24781 Assertion `mode == 16 || mode == 12 || fix_block->page.status != buf_page_t::FREED' failed in buf_page_get_low

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24818 Concurrent use of InnoDB table is impossible until the first transaction is finished

          • 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-24832 Root page AHI Removal fails fails during bulk index rollback

          • 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-24905 Assertion `!m.first->second.is_bulk_insert()' failed in trx_undo_report_row_operation

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24993 pymysql - executemany test failing - lock timeout - insert wait on non-indexed table

          • 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-25010 Assertion `!lock_sys_t::get_first(receiver_cell, receiver_id, receiver_heap_no)' failed in lock_rec_move

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25297 InnoDB: Failing assertion: trx->roll_limit <= trx->undo_no in trx_rollback_start

          • 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-25315 Assertion `ptr' failed in ut_align_down | SIGSEGV in dict_table_t::not_redundant | Assertion `!lock_sys_t::get_first(receiver_cell, receiver_id, receiver_heap_no)' failed in lock_rec_move

          • 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-25401 Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed in Diagnostics_area::set_ok_status on SAVEPOINT

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25487 Assertion `!lock_sys_t::get_first(receiver_cell, receiver_id, receiver_heap_no)' failed in lock_rec_move

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25496 Assertion `trx->bulk_insert' failed in trx_undo_report_row_operation on INSERT

          • 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-25534 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock on INSERT

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26121 [Note] InnoDB: Resetting invalid page [page id: space=5, page number=3] type 17855 to 6.

          • Major - Major loss of function.
          • 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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28037 Assertion `trx->bulk_insert' failed in innodb_prepare_commit_versioned

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28122 OPTIMIZE TABLE crash

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31835 LOAD DATA into InnoDB w/partitions: huge performance loss, affected 10.6+

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31861 Empty INSERT crashes with innodb_force_recovery=6 or innodb_read_only=ON

          • Major - Major loss of function.
          • Closed
          duplicates

          Task - A task that needs to be done. MDEV-5835 ibulkload

          • Major - Major loss of function.
          • Closed
          includes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11415 Remove excessive undo logging during ALTER TABLE…ALGORITHM=COPY

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

          Task - A task that needs to be done. MDEV-12288 Reset DB_TRX_ID when the history is removed, to speed up MVCC

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-16329 Engine-independent online ALTER TABLE

          • 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-20804 Use the sequence engine in main.index_merge_innodb and main.index_merge_myisam

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-23805 Make Online DDL to Instant DDL when table is empty

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24859 Assertion `static_cast<ins_node_t*>(thr->run_node)->bulk_insert' failed in trx_undo_report_row_operation

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

          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-28327 InnoDB persistent statistics fail to update after bulk insert

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29545 InnoDB replace statement returns "can't find record" error during bulk insert operation.

          • Major - Major loss of function.
          • 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.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32648 InnoDB: Failing assertion: old_rec in storage/innobase/btr/btr0pcur.cc line 366

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-4299 Portable Tablespace

          • 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-9459 Truncate table causes innodb stalls

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

          Task - A task that needs to be done. MDEV-11634 Improve the InnoDB change buffer

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-14094 benchmark effects of innodb change buffer

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-14602 Reduce malloc()/free() usage in InnoDB

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23017 range query performance regression in 10.5.4

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24716 Assertion `thr->graph->trx->id == trx_read_trx_id( static_cast<const byte*>( trx_id->data)) || static_cast<ins_node_t*>( thr->run_node)->bulk_insert' failed in btr_cur_optimistic_insert

          • 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-25064 rpl.rpl_parallel_temptable failed in bb, ASAN heap-use-after-free in std::__atomic_base<long>::store

          • 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-29975 Assertion `m_status == DA_ERROR' failed in Diagnostics_area::sql_errno on SAVEPOINT, Got error 1, Got error 153, Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed.

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

          Task - A task that needs to be done. MDEV-32250 To benefit from MDEV-515 , please make option --no-autocommit default in mysqldump from 10.6 onwards

          • 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.
          • Stalled
          mentioned in

          Page Loading...

          Activity

            People

              marko Marko Mäkelä
              serg Sergei Golubchik
              Votes:
              10 Vote for this issue
              Watchers:
              22 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.