Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[YSQL] Tuning parameters for faster copy performance. #12293

Closed
lnguyen-yugabyte opened this issue Apr 27, 2022 · 0 comments
Closed

[YSQL] Tuning parameters for faster copy performance. #12293

lnguyen-yugabyte opened this issue Apr 27, 2022 · 0 comments
Assignees
@lnguyen-yugabyte
Labels
area/ysql Yugabyte SQL (YSQL) kind/bug This issue is a bug priority/medium Medium priority issue
Projects
Bulk Load

Comments

@lnguyen-yugabyte
Copy link
Contributor

lnguyen-yugabyte commented Apr 27, 2022 •
edited by yugabyte-ci

Jira Link: DB-1977

Description

Adjust the default among the following parameters for optimal copy performance:
`

  • ROWS_PER_TRANSACTION
  • ysql_session_max_batch_size
  • ysql_max_in_flight_ops
    `

Make sure to report the result of the copy performance via microbenchmarking through the cloud cluster.
@lnguyen-yugabyte lnguyen-yugabyte added area/ysql Yugabyte SQL (YSQL) status/awaiting-triage Issue awaiting triage labels Apr 27, 2022
@lnguyen-yugabyte lnguyen-yugabyte self-assigned this Apr 27, 2022
@lnguyen-yugabyte lnguyen-yugabyte mentioned this issue Apr 27, 2022
Open
lnguyen-yugabyte added a commit that referenced this issue Apr 29, 2022
@lnguyen-yugabyte
[#12293] YSQL: Use better defaults for copy command
d95a159 
Summary:
With AsyncFlush landed in master (D16005), we adjust the default values to achieve a better copy performance. Note that we cannot set ROWS PER TRANSACTION too high as it will OOM the tserver; once packed column is enabled, we will set it at higher value (see D16717).

Note that if one sets ROWS PER TRANSACTION too high, we get the error:

```
ysqlsh:/home/centos/gen_table/insert_script.sql:6: ERROR:  Remote error: Service unavailable (yb/rpc/yb_rpc.cc:165): Call rejected due to memory pressure: Call yb.tserver.PgClientService.Perform 172.151.28.7:54138 => 172.151.28.7:9100 (request call id 2301)
```

Test Plan:
jenkins

Run and test on portal (make sure to disable packed column):
On copying 1M rows with 2 CPUs:
```
512 ysql_session_max_batch_size, 20K rows per transaction:
01:50.504
3072 ysql_session_max_batch_size, 20K rows per transaction:
01:45.250
```

Reviewers: smishra, rthallam

Reviewed By: rthallam

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D16752
lnguyen-yugabyte added a commit that referenced this issue Apr 30, 2022
@lnguyen-yugabyte
[#12293] YSQL: Adjust default value in copy test
3cb638e 
Summary:
In D16752 we changed the default value of ROWS_PER_TRANSACTION.
Here we fix the test to adjust the value of rows per transaction accordingly.

Test Plan: `ybd --java-test org.yb.pgsql.TestBatchCopyFrom`

Reviewers: smishra, rthallam

Reviewed By: rthallam

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D16755
lnguyen-yugabyte added a commit that referenced this issue May 11, 2022
@lnguyen-yugabyte
Revert "[#12293] YSQL: Use better defaults for copy command"
9c72d68 
Summary:
The previous commits broke the xcluster replication (specifically when setting `ysql_session_max_batch_size` at too high value). The issue was not included in a unit test or integration test. Reverting to the old value fixes the problem.

This reverts commit d95a159.

Revert "[#12293] YSQL: Adjust default value in copy test"

This reverts commit 3cb638e.

Test Plan:
jenkins

Test with xcluster replication to ensure it resumes working as normal.

Reviewers: jhe, slingam, rthallam

Reviewed By: slingam, rthallam

Subscribers: smishra, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D16914
@yugabyte-ci yugabyte-ci added kind/bug This issue is a bug priority/medium Medium priority issue labels Jun 9, 2022
@rthallamko3 rthallamko3 removed the status/awaiting-triage Issue awaiting triage label Jun 10, 2022
@rthallamko3 rthallamko3 added this to Items in Bulk Load via automation Jun 10, 2022
lnguyen-yugabyte added a commit that referenced this issue Jun 11, 2022
@lnguyen-yugabyte
[#12293] YSQL: Reenable new copy command default
e9ce925 
Summary: After the fix on D17055 is landed, we can update ysql_session_max_batch_size and DEFAULT_BATCH_ROWS_PER_TRANSACTION to have better defaults, so as to get better concurrency, effectively speeding up copy.

Test Plan: jenkins

Reviewers: rthallam, smishra

Reviewed By: rthallam, smishra

Subscribers: rahuldesirazu, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D17577
Bulk Load automation moved this from Items to Completed Jun 13, 2022
lnguyen-yugabyte added a commit that referenced this issue Jun 13, 2022
@lnguyen-yugabyte
[BACKPORT 2.15.0][#12293] YSQL: Reenable new copy command default
1e7f2ec 
Summary: After the fix on D17055 is landed, we can update ysql_session_max_batch_size and DEFAULT_BATCH_ROWS_PER_TRANSACTION to have better defaults, so as to get better concurrency, effectively speeding up copy.

Test Plan: jenkins

Reviewers: smishra, rthallam

Reviewed By: smishra, rthallam

Subscribers: yql, rahuldesirazu

Differential Revision: https://phabricator.dev.yugabyte.com/D17650
lnguyen-yugabyte added a commit that referenced this issue Jun 13, 2022
@lnguyen-yugabyte
[BACKPORT 2.14][#12293] YSQL: Reenable new copy command default
93f8723 
Summary: After the fix on D17055 is landed, we can update ysql_session_max_batch_size and DEFAULT_BATCH_ROWS_PER_TRANSACTION to have better defaults, so as to get better concurrency, effectively speeding up copy.

Test Plan: jenkins

Reviewers: smishra, rthallam

Reviewed By: smishra, rthallam

Subscribers: yql, rahuldesirazu

Differential Revision: https://phabricator.dev.yugabyte.com/D17651
@sushantrmishra sushantrmishra mentioned this issue Sep 5, 2022
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@lnguyen-yugabyte lnguyen-yugabyte

Labels
area/ysql Yugabyte SQL (YSQL) kind/bug This issue is a bug priority/medium Medium priority issue
Projects
Bulk Load
Completed
Milestone
No milestone
Development

No branches or pull requests
3 participants
@rthallamko3 @yugabyte-ci @lnguyen-yugabyte