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

innodb bulk insert

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.
          • Closed

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

          Page Loading...

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Description as a solution for bulk-inserting huge amount of data into innodb, we consider an utility that creates exported innodb tablespaces.

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

            InnoDB and Percona Server/XtraDB support exportable tablespaces. Wee 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.
            serg Sergei Golubchik made changes -
            Description as a solution for bulk-inserting huge amount of data into innodb, we consider an utility that creates exported innodb tablespaces.

            InnoDB and Percona Server/XtraDB support exportable tablespaces. Wee 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.             		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.
            serg Sergei Golubchik made changes -
            Fix Version/s 10.1.0 [ 12200 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Assignee Jan Lindström [ jplindst ]
            serg Sergei Golubchik made changes -
            Assignee Jan Lindström [ jplindst ]
            serg Sergei Golubchik made changes -
            Labels innodb
            dbart Daniel Bartholomew made changes -
            Priority Major [ 3 ] Minor [ 4 ]
            serg Sergei Golubchik made changes -
            Workflow defaullt [ 14115 ] MariaDB v2 [ 42608 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 10.1.0 [ 12200 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 42608 ] MariaDB v3 [ 61584 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.1 [ 16100 ]
            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 -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Priority Minor [ 4 ] Major [ 3 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s N/A [ 14700 ]
            marko Marko Mäkelä made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s N/A [ 14700 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            marko Marko Mäkelä made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            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 Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            julien.fritsch Julien Fritsch made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Component/s Storage Engine - InnoDB [ 10129 ]
            Description 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.             		The original description of this task read as follows:
            {quote}
            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.
            {quote}
            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.
            Labels innodb performance
            marko Marko Mäkelä made changes -
            issue.field.resolutiondate 2021-01-25 16:44:17.0 2021-01-25 16:44:17.987
            marko Marko Mäkelä made changes -
            Fix Version/s 10.6.0 [ 24431 ]
            Fix Version/s 10.6 [ 24028 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            elenst Elena Stepanova 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 -
            danblack Daniel Black made changes -
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Roel Roel Van de Paar made changes -
            Roel Roel Van de Paar made changes -
            Roel Roel Van de Paar made changes -
            Roel Roel Van de Paar made changes -
            Roel Roel Van de Paar made changes -
            Roel Roel Van de Paar made changes -
            Roel Roel Van de Paar made changes -
            marko Marko Mäkelä made changes -
            Roel Roel Van de Paar made changes -
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 61584 ] MariaDB v4 [ 131975 ]
            Roel Roel Van de Paar made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            bar Alexander Barkov made changes -
            marko Marko Mäkelä made changes -
            Roel Roel Van de Paar made changes -
            ycp Yuchen Pei 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 -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 104989
            marko Marko Mäkelä made changes -

            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.