...
ID | IEP-22 | ||||||||
Author | Vladimir Ozerov Ozerov | ||||||||
Sponsor | Vladimir Ozerov Ozerov | ||||||||
Created | 15 Jun 2018 | ||||||||
Status |
|
Table of Contents |
---|
...
TODO
...
Most industrial vendors rely on the following assumptions:
Oracle offers "direct path load" [1] as the fastest method to populate data after manual file copying. Direct path load can be from both command line utility SQL*Loader to load existing files (resembling Ignite's COPY
command), and with conventional SQL, such as INSERT
and CREATE TABLE AS SELECT
(CTAS). Major highlights:
[1] https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_modes.htm#SUTIL009
[2] https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/managing-tables.html#GUID-134D6EB6-219E-4820-AB54-8C60067A8F0F
PostgreSQL offers a number of best practices for efficient data load. Dedicated COPY
command exists to bypass SQL parsing and minimize network overhead. Apart from that there are not dedicated features for data load. General recommendations are:
maintenance_work_mem
parameter (buffer for index rebuild routines)max_wal_size
parameterMySQL and it's forks are pretty similar to PostgreSQL in available techniques and best practices [1]. MySQL also has COPY
command [2], which is the fastest data load method, reducing a lot parsing and network overhead. MySQL also advises to disable indexes, unique and FK constraints. But as MySQL is index-organized database and has key-value MyISAM engine, there are two unique points:
[1] https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html
[2] https://dev.mysql.com/doc/refman/8.0/en/load-data.html
[3] https://dev.mysql.com/doc/internals/en/bulk-insert.html
Implement distributed cache (table) lock - when it is held no other operation on cache is possible, PME infrastructure will be used for that
Index update and integrity checks will be delayed during direct data load. In order to maintain data consistency during this time it is essential to prevent any concurrent updates. This could be done with new cache lock concept. We will initiate exchange which will wait for all cache operations to finish, and then put special flag on that cache. If flag is set, exception will be thrown in an attempt to access cache.
We will not update PK and secondary indexes during the data load, so it is necessary to rebuild them in the end. The most efficient way to build indexes is bottom-up approach, when the lowest level of BTree is built first, and the root is build last. We will need a buffer where indexed values and respective links will be sorted in index order. If the buffer is big enough and all the data fits into it, index will be created in one hop. Otherwise it is necessary to sort indexed values in several runs using an external sort. It is necessary to let users configure sort parameters - buffer size (ideally - in bytes), and the file system path where temp files will be stored. The latter is critical - typically users would like to keep temp files on a separate disk, so that WAL and checkpoint operations are not affected.
We will have a small in-memory buffer for several consecutive data blocks. Data being injected is put into these blocks, bypassing the page memory. When the buffer is full, we could issue a multi-buffer async disk write and continue filling the buffer with new data. As data loading typically affects several partitions, multiple buffers and/or some additional synchronization may be required.
Data will be inserted into the new blocks only. We will have to track the start and end positions of the inserted data blocks. These positions will be used to scan new data during index rebuilding.
Jira | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|