Improve COPY and batch insert performance #91831

cucaroach · 2022-11-14T15:12:28Z

COPY/Batch insert performance master tracking bug

Batch inserts and COPY in CRDB are really slow (<1MB/s throughput). Below is a list of things we can do to speed up batch inserts and COPY in the front of the house. Once these are done further speedups will require work at the the KV/storage layers.

COPY:

Batch insert:

Make single statement batch inserts use COPY fast path (optimizer pass through for VALUES), are there any common multi statement batch patterns we should try to optimize as well?
sql: make prepared insert batches use bulk inserts #99319
Make sure secondary indexes are in separate ranges to get range parallelism, this is already done by default sometimes, see make new/empty table range splitting consistent #91087
Use separate goroutines to prepare and insert each index and column family, should we sort indexes too or let KV handle it?

IMPORT:

Make import use datum free vectorized code

INDEX BACKFILL:

Does it make sense for index backfill to user columnar encoding code? Don't see why not...

Speculative, not currently being persued ideas:

Can we just do an import if the table was created in the same transaction as the COPY? See sql: support COPY ... with FREEZE #85573
Can batch inserts cheat if table was created in same transaction? Can we detect an empty table and use AddSST? Empty table/same transaction DDL tricks feel like wasted energy, most inserts won’t be into empty table and won’t be in same txn as create table.
Can we take secondary indexes offline and update them lazily outside scope of transaction? Maybe just for non-unique indexes?

Jira issue: CRDB-21448

Epic CRDB-25320

cucaroach · 2022-12-12T16:32:23Z

Copy benchmark phase results: https://docs.google.com/spreadsheets/d/10mayXq3ICsRD__UuacT1UFT5I0I7anWzrNjNjqqFEvc/edit#gid=0

Explanation loom:

https://www.loom.com/share/8a319cc39df941e19bd57addebfed882

In order to build coldata.Vec's from string data a new version of ParseAndRequireString is provided which pulls out the special types supported by the vector engine and delegates to ParseAndRequireString for anything else. Informs: cockroachdb#91831 Release note: None

Epic: CRDB-18892 Informs: cockroachdb#91831 Release note: None

Implement new on by default vectorized insert for COPY FROM statements. Controlled by vectorize and copy_fast_path_enabled session variables which both default to true. If you set copy_fast_path_enabled to false you get the old unoptimized behavior (22.1). If you leave copy_fast_path_enabled enabled but turn off vectorize you get the 22.2 behavior. COPY FROM fast path row version and vectorized version both now respect memory limits on a per row basis, ie if huge rows are encountered COPY buffers will be flushed before we reach the configured copy row batch size. Also if lots of rows are sent in one CopyData statement we will now flush when we reach the copy row batch size limit instead of inserting all the data. This matters little with psql clients which typically do a row per CopyData segment but matters a lot with pgx which will do 64k CopyData segments. Keys are not inserted in the exact same order as they were with the row version of copy. Now they are sorted per batch so that all the PK Keys are inserted and then the first secondary index etc. The vectorized insert benefits from larger batch sizes so we are more generous with how big they can get. By default we start with 64 row batches and double up till a limit derived by KV raft command batch size parameterized by schema (ie wider bigger schema will get smaller batch size upper limit) not to exceed 32k which is roughly where performance gains from bigger batches start to trail off. Epic: CRDB-18892 Informs: cockroachdb#91831 Release note: (sql change): Bulk COPY FROM statements are now processed with a vectorized insert and can be anywhere from %50 to 5x faster. Typical hardware and schemas should see a 2x improvement. Vectorized inserts are only used for COPY statements and are not yet applied to regular inserts. Both the vectorize and copy_fast_path_enabled session variables can be used to disable this feature.

Epic: CRDB-18892 Informs: cockroachdb#91831 Release note: None

97321: copy: enhance copyfrom tests with kvtrace feature and more tests r=cucaroach a=cucaroach Epic: CRDB-18892 Informs: #91831 Release note: None 98264: colfetcher: track KV CPU time in the direct columnar scan r=yuzefovich a=yuzefovich This commit addresses a minor TODO to track the KV CPU time when direct columnar scans are used. In the regular columnar scan this time is tracked by the cFetcher, but with the KV projection pushdown the cFetcher is used on the KV server side, so we need to augment the ColBatchDirectScan to track it. Notably, this means that the decoding done on the KV server side is included. Additionally, this commit clarifies how the KV CPU time is obtained from the cFetcher (we don't need to use a helper (unlike in the case of `bytesRead` and `batchRequestsIssued` fields which are written to on `cFetcher.Close`), and we don't need the mutex protection there either). Epic: None Release note: None 98546: multitenant: allow secondary tenants to split/scatter by default r=knz a=arulajmani AdminSplit and AdminScatter requests are subject to capability checks. Previously, these capabilities were codified in the "enabled" form. As such, by default, secondary tenants did not have the ability to perform these operations. This is in violation of what secondary tenants could do prior to 23.1, at a time before capabilities existed. Moreover, RESTORE/IMPORT rely on performing these operations for performance. This made disallowing these operations by default a performance regression. This patch flips the phrasing of how these capabilities are stored on the proto to use the "disable" verbiage. As such, secondary tenants are able to perform splits and scatters by default. However, no change is made to the public interface -- users above the `tenantcapabilitiespb` package continue to interact with these capabilities as they were before, oblivious to how these things are stored on disk. There's a few testing changes here: - As part of this change, we also clean up a testing knob that was used by various backup, CDC, and logictests to override capability checks in the authorizer. This isn't required with the new default behaviour. - We also add some missing E2E tests for the `CanAdminUnsplit` capability which were missing when it was introduced. Fixes #96736 Release note: None 98615: sql_test: re-skip TestShowTraceReplica r=msirek a=msirek TestShowTraceReplica wasn't failing under stress, but failed in TeamCity once enabled. This re-skips the test until it can be reliably reproduced and debugged. Informs #98598 Release note: None Co-authored-by: Tommy Reilly <[email protected]> Co-authored-by: Yahor Yuzefovich <[email protected]> Co-authored-by: Raphael 'kena' Poss <[email protected]> Co-authored-by: Arul Ajmani <[email protected]> Co-authored-by: Mark Sirek <[email protected]>

Previously this was hardcoded to coldata.BatchSize or 1024, now it can be increased or decreased. Epic: CRDB-18892 Informs: cockroachdb#91831 Release note: None

98544: colmem: allow Allocator max batch size to be customized r=cucaroach a=cucaroach Previously this was hardcoded to coldata.BatchSize or 1024, now it can be increased or decreased. Epic: CRDB-18892 Informs: #91831 Release note: None 98630: kv: don't perform async intent resolution on 1PC with point lock spans r=arulajmani a=nvanbenschoten Fixes #98571. This commit fixes the regression detected in #98571. In that issue, we saw that the bug fix in 86a5852 (#98044) caused a regression in kv0 (and other benchmarks). This was due to a bug in `kvserverbase.IntersectSpan`, which was considering local point spans to be external to the provided range span. This commit fixes the bug by not calling `kvserverbase.IntersectSpan` for point lock spans. The commit also makes the utility panic instead of silently returning incorrect results. There's an existing TODO on the utility to generalize it. For now, we just make it harder to misuse. Finally, we add a test that asserts against the presence of async intent resolution after one-phase commits when external intent resolution is not needed. ``` name old time/op new time/op delta KV/Insert/Native/rows=1-10 61.2µs ± 3% 48.9µs ± 3% -20.10% (p=0.000 n=8+9) KV/Insert/Native/rows=10-10 93.3µs ±15% 76.2µs ± 3% -18.34% (p=0.000 n=9+9) KV/Insert/Native/rows=1000-10 2.84ms ±12% 2.42ms ± 4% -14.97% (p=0.000 n=9+9) KV/Insert/Native/rows=100-10 365µs ± 5% 320µs ± 8% -12.40% (p=0.000 n=10+9) KV/Insert/Native/rows=10000-10 27.6ms ± 6% 24.4ms ± 3% -11.53% (p=0.000 n=9+9) name old alloc/op new alloc/op delta KV/Insert/Native/rows=1000-10 4.66MB ± 1% 2.76MB ± 1% -40.89% (p=0.000 n=9+9) KV/Insert/Native/rows=100-10 478kB ± 1% 287kB ± 1% -39.90% (p=0.000 n=10+10) KV/Insert/Native/rows=10000-10 54.2MB ± 2% 34.3MB ± 3% -36.73% (p=0.000 n=10+10) KV/Insert/Native/rows=10-10 64.2kB ± 1% 42.1kB ± 1% -34.39% (p=0.000 n=10+9) KV/Insert/Native/rows=1-10 22.1kB ± 1% 17.3kB ± 1% -21.56% (p=0.000 n=9+10) name old allocs/op new allocs/op delta KV/Insert/Native/rows=1000-10 21.5k ± 0% 14.7k ± 0% -31.70% (p=0.000 n=8+9) KV/Insert/Native/rows=10000-10 212k ± 0% 146k ± 0% -31.31% (p=0.000 n=9+10) KV/Insert/Native/rows=100-10 2.34k ± 1% 1.61k ± 0% -31.31% (p=0.000 n=10+10) KV/Insert/Native/rows=10-10 392 ± 1% 276 ± 0% -29.59% (p=0.000 n=8+8) KV/Insert/Native/rows=1-10 173 ± 1% 123 ± 0% -29.04% (p=0.000 n=9+8) ``` Release note: None Co-authored-by: Tommy Reilly <[email protected]> Co-authored-by: Nathan VanBenschoten <[email protected]>

Implement new on by default vectorized insert for COPY FROM statements. Controlled by vectorize and copy_fast_path_enabled session variables which both default to true. If you set copy_fast_path_enabled to false you get the old unoptimized behavior (22.1). If you leave copy_fast_path_enabled enabled but turn off vectorize you get the 22.2 behavior. COPY FROM fast path row version and vectorized version both now respect memory limits on a per row basis, ie if huge rows are encountered COPY buffers will be flushed before we reach the configured copy row batch size. Also if lots of rows are sent in one CopyData statement we will now flush when we reach the copy row batch size limit instead of inserting all the data. This matters little with psql clients which typically do a row per CopyData segment but matters a lot with pgx which will do 64k CopyData segments. Keys are not inserted in the exact same order as they were with the row version of copy. Now they are sorted per batch so that all the PK Keys are inserted and then the first secondary index etc. The vectorized insert benefits from larger batch sizes so we are more generous with how big they can get. By default we start with 64 row batches and double up till a limit derived by KV raft command batch size parameterized by schema (ie wider bigger schema will get smaller batch size upper limit) not to exceed 32k which is roughly where performance gains from bigger batches start to trail off. Epic: CRDB-18892 Informs: cockroachdb#91831 Release note (sql change): Bulk COPY FROM statements are now processed with a vectorized insert and can be anywhere from %50 to 5x faster. Typical hardware and schemas should see a 2x improvement. Vectorized inserts are only used for COPY statements and are not yet applied to regular inserts. Both the vectorize and copy_fast_path_enabled session variables can be used to disable this feature.

Epic: CRDB-18892 Informs: cockroachdb#91831 Release note: None

Implement new on by default vectorized insert for COPY FROM statements. Controlled by vectorize and copy_fast_path_enabled session variables which both default to true. If you set copy_fast_path_enabled to false you get the old unoptimized behavior (22.1). If you leave copy_fast_path_enabled enabled but turn off vectorize you get the 22.2 behavior. COPY FROM fast path row version and vectorized version both now respect memory limits on a per row basis, ie if huge rows are encountered COPY buffers will be flushed before we reach the configured copy row batch size. Also if lots of rows are sent in one CopyData statement we will now flush when we reach the copy row batch size limit instead of inserting all the data. This matters little with psql clients which typically do a row per CopyData segment but matters a lot with pgx which will do 64k CopyData segments. Keys are not inserted in the exact same order as they were with the row version of copy. Now they are sorted per batch so that all the PK Keys are inserted and then the first secondary index etc. The vectorized insert benefits from larger batch sizes so we are more generous with how big they can get. By default we start with 64 row batches and double up till a limit derived by KV raft command batch size parameterized by schema (ie wider bigger schema will get smaller batch size upper limit) not to exceed 32k which is roughly where performance gains from bigger batches start to trail off. Epic: CRDB-18892 Informs: cockroachdb#91831 Release note (sql change): Bulk COPY FROM statements are now processed with a vectorized insert and can be anywhere from %50 to 5x faster. Typical hardware and schemas should see a 2x improvement. Vectorized inserts are only used for COPY statements and are not yet applied to regular inserts. Both the vectorize and copy_fast_path_enabled session variables can be used to disable this feature.

98605: copy: add vectorize insert support used solely by copy for now r=yuzefovich a=cucaroach Implement new on by default vectorized insert for COPY FROM statements. Controlled by vectorize and copy_fast_path_enabled session variables which both default to true. If you set copy_fast_path_enabled to false you get the old unoptimized behavior (22.1). If you leave copy_fast_path_enabled enabled but turn off vectorize you get the 22.2 behavior. COPY FROM fast path row version and vectorized version both now respect memory limits on a per row basis, ie if huge rows are encountered COPY buffers will be flushed before we reach the configured copy row batch size. Also if lots of rows are sent in one CopyData statement we will now flush when we reach the copy row batch size limit instead of inserting all the data. This matters little with psql clients which typically do a row per CopyData segment but matters a lot with pgx which will do 64k CopyData segments. Keys are not inserted in the exact same order as they were with the row version of copy. Now they are sorted per batch so that all the PK Keys are inserted and then the first secondary index etc. The vectorized insert benefits from larger batch sizes so we are more generous with how big they can get. By default we start with 64 row batches and double up till a limit derived by KV raft command batch size parameterized by schema (ie wider bigger schema will get smaller batch size upper limit) not to exceed 32k which is roughly where performance gains from bigger batches start to trail off. Epic: CRDB-18892 Informs: #91831 Release note (sql change): Bulk COPY FROM statements are now processed with a vectorized insert and can be anywhere from %50 to 5x faster. Typical hardware and schemas should see a 2x improvement. Vectorized inserts are only used for COPY statements and are not yet applied to regular inserts. Both the vectorize and copy_fast_path_enabled session variables can be used to disable this feature. 98828: licenses: update licenses prior to release-23.1 branch cut r=kpatron-cockroachlabs a=kpatron-cockroachlabs This commit updates the licenses. Release note: None fixes: REL-251 Co-authored-by: Tommy Reilly <[email protected]> Co-authored-by: Kyle Patron <[email protected]>

cucaroach added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Nov 14, 2022

cucaroach self-assigned this Nov 14, 2022

blathers-crl bot added the T-sql-queries SQL Queries Team label Nov 16, 2022

cucaroach mentioned this issue Dec 8, 2022

sql: support COPY ... with FREEZE #85573

Open

cucaroach mentioned this issue Jan 30, 2023

sql: move RandString to util to break coldata->rangden->sem/tree dep #96229

Merged

cucaroach mentioned this issue Jan 30, 2023

sql: move copy_in_test to pkg/sql/copy to have all copy tests together #96232

Merged

cucaroach mentioned this issue Jan 30, 2023

sem/tree: add support for producing vectorized data from strings #96235

Merged

cucaroach mentioned this issue Jan 30, 2023

kv: add simple bulk put routines for vectorized insert #96237

Merged

cucaroach added a commit to cucaroach/cockroach that referenced this issue Jan 31, 2023

sql: move RandString to util to break coldata->rangden->sem/tree dep

0b3a717

Epic: CRDB-18892 Informs: cockroachdb#91831 Release note: None

cucaroach mentioned this issue Mar 14, 2023

copy: add vectorize insert support used solely by copy for now #98605

Merged

cucaroach added a commit to cucaroach/cockroach that referenced this issue Mar 14, 2023

copy: add vectorization as a copyfrom test variation

a0edaef

Epic: CRDB-18892 Informs: cockroachdb#91831 Release note: None

cucaroach added a commit to cucaroach/cockroach that referenced this issue Mar 14, 2023

copy: enhance copyfrom tests with kvtrace feature and more tests

89ae338

Epic: CRDB-18892 Informs: cockroachdb#91831 Release note: None

cucaroach added a commit to cucaroach/cockroach that referenced this issue Mar 15, 2023

copy: add vectorization as a copyfrom test variation

5c674a8

Epic: CRDB-18892 Informs: cockroachdb#91831 Release note: None

cockroach-teamcity mentioned this issue Mar 19, 2023

PR #98605 - copy: add vectorize insert support used solely by copy for now cockroachdb/docs#16530

Open

mgartner added this to SQL Queries Jul 24, 2023

mgartner moved this to 23.2 Release in SQL Queries Jul 24, 2023

michae2 removed the status in SQL Queries Aug 3, 2023

michae2 added the meta-issue Contains a list of several other issues. label Aug 3, 2023

michae2 moved this to Active in SQL Queries Aug 3, 2023

cucaroach moved this from Active to 24.1 Release in SQL Queries Sep 12, 2023

yuzefovich unassigned cucaroach Oct 12, 2023

mgartner moved this from 24.1 Release to 24.2 Release in SQL Queries Nov 22, 2023

mgartner removed the status in SQL Queries Mar 28, 2024

mgartner moved this to New Backlog in SQL Queries Mar 28, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Improve COPY and batch insert performance #91831

Improve COPY and batch insert performance #91831

cucaroach commented Nov 14, 2022 •

edited

Loading

cucaroach commented Dec 12, 2022 •

edited

Loading

Improve COPY and batch insert performance #91831

Improve COPY and batch insert performance #91831

Comments

cucaroach commented Nov 14, 2022 • edited Loading

cucaroach commented Dec 12, 2022 • edited Loading

cucaroach commented Nov 14, 2022 •

edited

Loading

cucaroach commented Dec 12, 2022 •

edited

Loading