[Proposal]: Add CSV file bulk load functionality to the community mysql version

[NSkeledzija]

Pre-flight Checklist

• I have searched existing GitHub issues and did not find a duplicate proposal.
• I have removed or redacted sensitive information.

Primary Contact Name

Nikša Skeledžija

Primary Contact Email

niksa.skeledzija@oracle.com

Company / Organization

Oracle

Role

Software Engineer

Additional Authors / Contributors

Annamalai Gurusami annamalai.gurusami@oracle.com

Component

InnoDB

Target Release (Optional)

No response

Roadmap Section

Performance & Observability

Related Issues / Pull Requests / References (Optional)

No response

Executive Summary

Ingesting a lot of raw data from CSV files becomes very slow in certain cases. Regular LOAD DATA statement in mysql boils down to a bunch of INSERTs in the background, which means we do a lot of tree traversals, page allocation just in time, and various other side effects of managing the B+tree inside InnoDB. In some cases, such as when ingesting unsorted data in the range of TBs where the data size exceeds the buffer pool size, we basically grind to a halt and it is not feasible to load such data into the DB. To allow such a use case, we will implement a bulk load algorithm which will deal with this exact scenario where we know up-front that a lot of data is coming in. We can then pre-allocate the extents involved, we can take table level locks up-front, we can sort the data before loading in case the input is unsorted to avoid random page access. We can also leverage the parallel index build infrastructure to build multiple separate sub-trees concurrently and merge them at a later phase by adding a root page.

User / Developer Stories

• As a developer using mysql, I would like to be able to ingest data coming from various non-mysql sources in CSV files quickly into my database.
• As a developer using a multi-core machine, I would like mysql to be able to consume as much of my system's resources (cpu cores, RAM) as I specify so it can maximize the ingestion throughput.

Proposed Scope

• Implement the bulk load functionality which is available by using LOAD DATA ... ALGORITHM=BULK.

Out of Scope / Future Work

• Non-empty tables require a merge with old data phase, which in some worst case scenarios reduces the performance of bulk load.
• Partitioned tables require the CSV file distribution per partition to be known up-front and given to bulk loader via the PARTITIONS clause in the LOAD DATA statement.

References

No response

Functional Requirements

No response

Non-functional Requirements

No response

Impact Areas

• SQL syntax or statements
• Configuration options or system variables
• Command-line options or utilities
• User-visible behavior
• Observability
• Security or privilege model
• Protocol or replication behavior
• Upgrade / downgrade compatibility
• Performance or resource usage
• Files, persistence, or metadata formats
• APIs or internal interfaces
• Testing or QA coverage needs

Summary of the Approach

No response

User Interface

No response

Configuration / Knobs

No response

Observability

No response

User Procedure

No response

Security Considerations

No response

Compatibility and Behavior Changes

No response

Block Diagram

No response

Interface Specification

No response

Proposed Implementation Plan

No response

QA Notes

No response

[jfg956]

I scratched my head on a similar feature in the past, below are some thoughts:

• making things fast on a primary but not on replicas is not such a win: this feature should also work on replication architectures;
• above requirement on replication means that if the loaded file is "pre-process" on the primary (sorted), the pre-processing should be avoided on replicas, and the pre-processed result should be sent to replicas via binlogs;
• above requirement on replication means index building / sorting should only be done once, not on every replicas;

And because of above, I arrived at the conclusion that the MySQL Server should provide an interface to "bulk loading" in the Storage Engine (via the Storage Engine interface and with a replication extension). This interface should be able to support different architecture of storage engine (InnoDB, MyRocks, ..., heap oriented storage, sharded storage, ...) and the initial scope could be only InnoDB.

The pre-processing could be done in the MySQL Server, but it could also be implemented in an external utility. I think external is better to avoid overloading the primary and doing the pre-processing "out of MySQL".

Obviously, such approach breaks when using different table formats on primary and replicas.

[NSkeledzija]

Thanks for your thoughts! We spent quite a bit of time thinking about the replication support here, but we don't have a conclusion yet. I believe we have 2 options here, either we do the "pre-process" on the primary and send the raw data through the binlogs (which means binlog gets flooded with all the TBs) or we do the "pre-processing" on all of the replicas, as in we binlog just the metainformation about the bulk load and let replicas do their thing and replicate it. I'm leaning more towards the latter option as I think we don't want to put all this data in the binlogs. However, right now we're not focusing on replication, it will initially be a limitation of this feature.