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.

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

[YSQL] Avoid redundant reads for non-unique index inserts #4363

Closed
m-iancu opened this issue May 1, 2020 · 1 comment
Closed

[YSQL] Avoid redundant reads for non-unique index inserts #4363

m-iancu opened this issue May 1, 2020 · 1 comment
Assignees
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature

Comments

@m-iancu
Copy link
Contributor

m-iancu commented May 1, 2020

For non-unique indexes, the primary key of the main table is implicitly added to the DocDB key, guaranteeing uniqueness of the full DocDB key (indexed columns plus encoded base table primary key).
Therefore, we can just execute such inserts as upserts and avoid the read and uniqueness check.
We already do this for YCQL (see cql_operation.cc).

@kmuthukk kmuthukk added area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature labels May 5, 2020
m-iancu added a commit that referenced this issue May 7, 2020
Summary:
For non-unique indexes, the primary key of the main table is implicitly added
to the DocDB key, guaranteeing uniqueness of the full DocDB key (indexed
columns plus encoded base table primary key).
Therefore, we can just execute such inserts as upserts and avoid the read and
uniqueness check.
We already do this for YCQL (in cql_operation.cc).

Test Plan:
Existing Jenkins tests.

Tested locally with SqlSecondaryIndex sample app:

```
java -jar target/yb-sample-apps.jar --workload SqlSecondaryIndex --nodes 127.0.0.1:5433 --num_threads_read 4 --num_threads_write 4
```
- Before:
```
120649 [Thread-1] INFO com.yugabyte.sample.common.metrics.MetricsTracker  -
  Read: 2576.59 ops/sec (1.55 ms/op), 335179 total ops  |
  Write: 1608.69 ops/sec (2.49 ms/op), 204802 total ops  |
  Uptime: 120097 ms |
```

- After:
```
120660 [Thread-1] INFO com.yugabyte.sample.common.metrics.MetricsTracker  -
  Read: 2796.42 ops/sec (1.43 ms/op), 372056 total ops  |
  Write: 1656.78 ops/sec (2.41 ms/op), 219060 total ops  |
  Uptime: 120102 ms |
```

- Summary:
Reads: 335179 -> 372056 (~11% increase)
Writes: 204802 -> 219060 (~7% increase)
Note: This uses RF-1 cluster and one index -- improvement should be less for RF-3 and better for more indexes.

Reviewers: neha

Reviewed By: neha

Subscribers: kannan, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D8385
@m-iancu
Copy link
Contributor Author

m-iancu commented May 7, 2020

Fixed by 02f5812.

@m-iancu m-iancu closed this as completed May 7, 2020
@m-iancu m-iancu added this to YQL-beta Nov 30, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature
Projects
Archived in project
Development

No branches or pull requests

2 participants