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 Michael Widenius 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

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              serg Sergei Golubchik
              Votes:
              10 Vote for this issue
              Watchers:
              21 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: