executor: make point updates go short path by cfzjywxk · Pull Request #12243 · pingcap/tidb

cfzjywxk · 2019-09-18T02:46:30Z

What problem does this PR solve?

make point update cached like point select decoupled with prepared plan cache

sysbench oltp_update_index and oltp_update_non_index
tpcc benchmarksql
will get benefit from this

What is changed and how it works?

Check List

Tests

Unit test

Code changes

Has exported function/method change

Side effects

Related changes

Release note

cfzjywxk · 2019-09-18T02:46:37Z

/bench

zz-jason · 2019-09-18T02:53:52Z

@cfzjywxk please add some proper labels for this PR.

sre-bot · 2019-09-18T05:49:17Z

@@                               Benchmark Diff                               @@
================================================================================
--- tidb: b6696405b19a7be8dc8fc1f5112ceac231d85b51
+++ tidb: 397490e27f7d32759e174d7b3e49243d8e209ef1
tikv: f718999e17b7fe6fa6438a5fd1e5efbcef5490c9
pd: 16f41793c02a10050dd19040548a03a9f0f1aca3
================================================================================
test-1: < oltp_read_write >
    * QPS : 37077.97 ± 0.1635% (std=37.08) delta: 0.02%
    * AvgMs : 138.60 ± 0.1587% (std=0.14) delta: -0.04%
    * PercentileMs99 : 257.95 ± 0.0000% (std=0.00) delta: 0.00%
            
test-2: < oltp_point_select >
    * QPS : 75602.29 ± 1.3070% (std=596.41) delta: -8.27%
    * AvgMs : 3.38 ± 1.2419% (std=0.03) delta: 9.03%
    * PercentileMs99 : 7.56 ± 0.0000% (std=0.00) delta: 25.83%
            
test-3: < oltp_insert >
    * QPS : 21281.10 ± 0.2136% (std=28.41) delta: -0.27%
    * AvgMs : 12.02 ± 0.1830% (std=0.01) delta: 0.27%
    * PercentileMs99 : 43.39 ± 0.0000% (std=0.00) delta: 0.72%
            
test-4: < oltp_update_index >
    * QPS : 17360.08 ± 0.3920% (std=50.56) delta: 2.47%
    * AvgMs : 14.75 ± 0.3662% (std=0.04) delta: -2.38%
    * PercentileMs99 : 47.47 ± 0.0000% (std=0.00) delta: -1.80%
            
test-5: < oltp_update_non_index >
    * QPS : 29974.28 ± 0.1160% (std=23.22) delta: 3.11%
    * AvgMs : 8.54 ± 0.1171% (std=0.01) delta: -2.98%
    * PercentileMs99 : 30.81 ± 0.0000% (std=0.00) delta: -0.72%

https://perf.pingcap.com

cfzjywxk · 2019-09-18T06:09:10Z

/bench

sre-bot · 2019-09-18T08:17:00Z

@@                               Benchmark Diff                               @@
================================================================================
--- tidb: b6696405b19a7be8dc8fc1f5112ceac231d85b51
+++ tidb: 1d737392b3c4267bb4824ba18b80346a2b496129
tikv: f718999e17b7fe6fa6438a5fd1e5efbcef5490c9
pd: 16f41793c02a10050dd19040548a03a9f0f1aca3
================================================================================
test-1: < oltp_read_write >
    * QPS : 37074.77 ± 0.3849% (std=97.45) delta: 0.01%
    * AvgMs : 138.62 ± 0.3838% (std=0.36) delta: -0.03%
    * PercentileMs99 : 257.95 ± 0.0000% (std=0.00) delta: 0.00%
            
test-2: < oltp_point_select >
    * QPS : 82726.25 ± 1.6852% (std=814.70) delta: 0.37%
    * AvgMs : 3.09 ± 1.8100% (std=0.03) delta: -0.26%
    * PercentileMs99 : 5.99 ± 0.0000% (std=0.00) delta: -0.30%
            
test-3: < oltp_insert >
    * QPS : 21294.15 ± 0.5875% (std=89.29) delta: -0.21%
    * AvgMs : 12.02 ± 0.5493% (std=0.05) delta: 0.22%
    * PercentileMs99 : 43.23 ± 2.1650% (std=0.58) delta: 0.36%
            
test-4: < oltp_update_index >
    * QPS : 17312.77 ± 1.2234% (std=125.25) delta: 2.19%
    * AvgMs : 14.79 ± 1.2037% (std=0.11) delta: -2.11%
    * PercentileMs99 : 47.31 ± 2.1814% (std=0.64) delta: -2.13%
            
test-5: < oltp_update_non_index >
    * QPS : 29952.02 ± 0.0446% (std=9.50) delta: 3.04%
    * AvgMs : 8.54 ± 0.0780% (std=0.00) delta: -2.94%
    * PercentileMs99 : 30.81 ± 0.0000% (std=0.00) delta: -0.72%

https://perf.pingcap.com

codecov · 2019-09-18T09:39:27Z

Codecov Report

Merging #12243 into master will not change coverage.
The diff coverage is n/a.

@@             Coverage Diff             @@
##             master     #12243   +/-   ##
===========================================
  Coverage   79.8496%   79.8496%           
===========================================
  Files           462        462           
  Lines        102564     102564           
===========================================
  Hits          81897      81897           
  Misses        14782      14782           
  Partials       5885       5885

cfzjywxk · 2019-09-18T09:43:11Z

/bench

cfzjywxk · 2019-09-18T09:48:45Z

/run-all-tests

sre-bot · 2019-09-18T11:44:52Z

@@                               Benchmark Diff                               @@
================================================================================
--- tidb: b6696405b19a7be8dc8fc1f5112ceac231d85b51
+++ tidb: 303a116abb006d5f56c7ec2bd3f746017636f21a
tikv: f718999e17b7fe6fa6438a5fd1e5efbcef5490c9
pd: 16f41793c02a10050dd19040548a03a9f0f1aca3
================================================================================
test-1: < oltp_read_write >
    * QPS : 37469.22 ± 0.4900% (std=135.38) delta: 1.08%
    * AvgMs : 137.16 ± 0.4739% (std=0.51) delta: -1.08%
    * PercentileMs99 : 262.64 ± 0.0000% (std=0.00) delta: 1.82%
            
test-2: < oltp_point_select >
    * QPS : 84454.76 ± 0.3584% (std=203.04) delta: 2.47%
    * AvgMs : 3.03 ± 0.4122% (std=0.01) delta: -2.24%
    * PercentileMs99 : 6.43 ± 0.0000% (std=0.00) delta: 7.02%
            
test-3: < oltp_insert >
    * QPS : 22243.07 ± 0.6793% (std=108.23) delta: 4.24%
    * AvgMs : 11.50 ± 0.6433% (std=0.06) delta: -4.05%
    * PercentileMs99 : 23.35 ± 1.0791% (std=0.21) delta: -45.79%
            
test-4: < oltp_update_index >
    * QPS : 17578.94 ± 0.6765% (std=83.28) delta: 3.76%
    * AvgMs : 14.52 ± 0.3582% (std=0.04) delta: -3.89%
    * PercentileMs99 : 31.37 ± 0.0000% (std=0.00) delta: -35.11%
            
test-5: < oltp_update_non_index >
    * QPS : 29949.91 ± 0.4331% (std=89.88) delta: 3.03%
    * AvgMs : 8.54 ± 0.4684% (std=0.03) delta: -2.98%
    * PercentileMs99 : 20.52 ± 1.0820% (std=0.18) delta: -33.89%

https://perf.pingcap.com

cfzjywxk · 2019-09-22T04:30:13Z

/bench

sre-bot · 2019-09-22T05:12:14Z

@@                               Benchmark Diff                               @@
================================================================================
--- tidb: f792e8d7879c13c5ee250c6242269527303369a2
+++ tidb: 79fe8bc64053553f0911dca3a85f02fcdb90a764
tikv: 15082b6cb7d1be3414badc0b74a21b67a505a29e
pd: 4ca2265d180531191dcfc531e468ab8380737a33
================================================================================
test-1: < oltp_read_write >
    * QPS : 37344.48 ± 0.2218% (std=68.33) delta: 0.09%
    * AvgMs : 137.66 ± 0.2310% (std=0.27) delta: -0.06%
    * PercentileMs99 : 262.64 ± 0.0000% (std=0.00) delta: 0.00%
            
test-2: < oltp_point_select >
    * QPS : 82091.69 ± 2.1347% (std=1284.69) delta: 1.01%
    * AvgMs : 3.12 ± 2.2436% (std=0.05) delta: -0.89%
    * PercentileMs99 : 6.60 ± 2.9100% (std=0.14) delta: -1.08%
            
test-3: < oltp_insert >
    * QPS : 21743.92 ± 0.7865% (std=109.20) delta: -0.23%
    * AvgMs : 11.77 ± 0.7478% (std=0.06) delta: 0.26%
    * PercentileMs99 : 24.21 ± 1.0658% (std=0.21) delta: 1.08%
            
test-4: < oltp_update_index >
    * QPS : 17486.40 ± 1.0320% (std=146.72) delta: 1.83%
    * AvgMs : 14.56 ± 0.8380% (std=0.08) delta: -2.07%
    * PercentileMs99 : 31.37 ± 0.0000% (std=0.00) delta: 2.91%
            
test-5: < oltp_update_non_index >
    * QPS : 29847.93 ± 0.0283% (std=6.09) delta: 3.19%
    * AvgMs : 8.57 ± 0.0000% (std=0.00) delta: -3.14%
    * PercentileMs99 : 20.74 ± 0.0000% (std=0.00) delta: 11.45%

https://perf.pingcap.com

cfzjywxk · 2019-09-22T08:44:49Z

/bench

sre-bot · 2019-09-22T09:31:36Z

@@                               Benchmark Diff                               @@
================================================================================
--- tidb: 599eaa3d33376e8ff927adff9b77a10c78486093
+++ tidb: 79fe8bc64053553f0911dca3a85f02fcdb90a764
tikv: 15082b6cb7d1be3414badc0b74a21b67a505a29e
pd: 4ca2265d180531191dcfc531e468ab8380737a33
================================================================================
test-1: < oltp_read_write >
    * QPS : 36874.67 ± 0.2860% (std=64.32) delta: -0.95%
    * AvgMs : 139.40 ± 0.2744% (std=0.24) delta: 1.01%
    * PercentileMs99 : 267.41 ± 0.0000% (std=0.00) delta: 1.08%
            
test-2: < oltp_point_select >
    * QPS : 81658.14 ± 1.6951% (std=1090.36) delta: 0.37%
    * AvgMs : 3.14 ± 1.7857% (std=0.04) delta: -0.25%
    * PercentileMs99 : 6.60 ± 1.0912% (std=0.06) delta: -1.08%
            
test-3: < oltp_insert >
    * QPS : 21715.22 ± 0.3027% (std=54.10) delta: -0.91%
    * AvgMs : 11.78 ± 0.3055% (std=0.03) delta: 0.91%
    * PercentileMs99 : 23.95 ± 0.0000% (std=0.00) delta: 0.72%
            
test-4: < oltp_update_index >
    * QPS : 17533.23 ± 0.1872% (std=24.74) delta: 2.43%
    * AvgMs : 14.50 ± 1.0898% (std=0.10) delta: -2.39%
    * PercentileMs99 : 31.49 ± 2.1470% (std=0.42) delta: 2.19%
            
test-5: < oltp_update_non_index >
    * QPS : 29774.05 ± 0.4589% (std=77.86) delta: 2.65%
    * AvgMs : 8.59 ± 0.1747% (std=0.01) delta: -2.69%
    * PercentileMs99 : 20.74 ± 0.0000% (std=0.00) delta: 11.45%

https://perf.pingcap.com

cfzjywxk · 2019-09-22T13:21:57Z

/run-all-tests

jackysp · 2019-09-24T03:45:07Z

session/session.go


 // ExecutePreparedStmt executes a prepared statement.
 func (s *session) ExecutePreparedStmt(ctx context.Context, stmtID uint32, args []types.Datum) (sqlexec.RecordSet, error) {
+	s.PrepareTxnCtx(ctx)


why change this?

both cached execution and compile execution path need this, put here to make it simple

planner/core/common_plans.go

sre-bot · 2019-09-24T14:38:56Z

@@                               Benchmark Diff                               @@
================================================================================
--- tidb: 73e762c032781b7c3990f82698047a4660355d0a
+++ tidb: 7de528662c0fd0d69f862673ea383dffdcf8febc
tikv: 064beb0ecbe77e744127eca47673f7f962437a2a
pd: c973515613157209832d943d698fc3e2133624a5
================================================================================
tidb_max_cpu: 19.63, delta: 0.38%
tikv_max_cpu: 12.64, delta: -3.72%
tidb_max_memory: 1679.27 MiB, delta: 1.23%
tikv_max_memory: 60176.51 MiB, delta: 0.13%

Measured tpmC (NewOrders): 21461.18, delta: 0.20%

https://perf.pingcap.com

session/session.go

cfzjywxk · 2019-09-26T09:04:18Z

/run-all-tests

cfzjywxk · 2019-09-26T09:57:16Z

@jackysp @imtbkcat PTAL

sre-bot · 2019-09-27T10:09:59Z

@jackysp, @imtbkcat, @lysu, @coocood, PTAL.

jackysp

LGTM

jackysp · 2019-09-27T10:19:09Z

PTAL @lysu

coocood

LGTM

sre-bot · 2019-09-27T10:32:03Z

/run-all-tests

cfzjywxk added the status/WIP label Sep 18, 2019

cfzjywxk force-pushed the pupd_short branch from cb4ec93 to 2ab0e93 Compare September 18, 2019 06:08

cfzjywxk force-pushed the pupd_short branch 2 times, most recently from 6622551 to 1d73739 Compare September 18, 2019 06:30

cfzjywxk closed this Sep 18, 2019

cfzjywxk reopened this Sep 18, 2019

cfzjywxk changed the title ~~executor: make point update short~~ executor: make point updates go short path Sep 18, 2019

cfzjywxk force-pushed the pupd_short branch from b60f81d to 303a116 Compare September 18, 2019 09:42

cfzjywxk force-pushed the pupd_short branch from 8a80211 to 79fe8bc Compare September 22, 2019 04:30

make point update go short path

7de5286

cfzjywxk force-pushed the pupd_short branch from 79fe8bc to 7de5286 Compare September 22, 2019 13:19

cfzjywxk added status/PTAL and removed status/WIP labels Sep 22, 2019

cfzjywxk requested review from coocood, imtbkcat and lysu September 22, 2019 14:03

jackysp reviewed Sep 24, 2019

View reviewed changes

planner/core/common_plans.go Outdated Show resolved Hide resolved

jackysp reviewed Sep 24, 2019

View reviewed changes

planner/core/common_plans.go Show resolved Hide resolved

jackysp reviewed Sep 24, 2019

View reviewed changes

planner/core/common_plans.go Outdated Show resolved Hide resolved

imtbkcat reviewed Sep 25, 2019

View reviewed changes

session/session.go Show resolved Hide resolved

cfzjywxk added 3 commits September 26, 2019 10:30

Merge branch 'master' into pupd_short

5bdcc74

Merge branch 'master' into pupd_short

eabb339

address comment

80cbad8

cfzjywxk requested review from imtbkcat and jackysp September 26, 2019 03:27

cfzjywxk added 3 commits September 26, 2019 14:04

Merge branch 'master' into pupd_short

ad8e469

Merge branch 'master' into pupd_short

917f80f

Merge branch 'master' into pupd_short

f20ed7c

cfzjywxk added the status/all tests passed label Sep 26, 2019

jackysp reviewed Sep 27, 2019

View reviewed changes

coocood approved these changes Sep 27, 2019

View reviewed changes

coocood added the status/can-merge Indicates a PR has been approved by a committer. label Sep 27, 2019

Merge branch 'master' into pupd_short

d3ec503

sre-bot merged commit a582640 into pingcap:master Sep 27, 2019

bb7133 added a commit to bb7133/tidb that referenced this pull request Dec 18, 2020

planner: revert part of pingcap#12243

91bbcb8

This was referenced Dec 18, 2020

planner: incorrect NOW() result when executing a prepared point-get update #21884

Closed

planner: the issue that incorrect result is returned for indeterministic function when executing a prepared point-get update. #21883

Merged

bb7133 mentioned this pull request Dec 29, 2020

planner: the issue that incorrect result is returned for nondeterministic function when executing a prepared point-get update. #22067

Merged

Conversation

cfzjywxk commented Sep 18, 2019 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

What problem does this PR solve?

What is changed and how it works?

Check List

Uh oh!

cfzjywxk commented Sep 18, 2019

Uh oh!

zz-jason commented Sep 18, 2019

Uh oh!

sre-bot commented Sep 18, 2019

Uh oh!

cfzjywxk commented Sep 18, 2019

Uh oh!

sre-bot commented Sep 18, 2019

Uh oh!

codecov bot commented Sep 18, 2019 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Codecov Report

Uh oh!

cfzjywxk commented Sep 18, 2019

Uh oh!

cfzjywxk commented Sep 18, 2019

Uh oh!

sre-bot commented Sep 18, 2019

Uh oh!

cfzjywxk commented Sep 22, 2019

Uh oh!

sre-bot commented Sep 22, 2019

Uh oh!

cfzjywxk commented Sep 22, 2019

Uh oh!

sre-bot commented Sep 22, 2019

Uh oh!

cfzjywxk commented Sep 22, 2019

Uh oh!

jackysp Sep 24, 2019

Choose a reason for hiding this comment

Uh oh!

cfzjywxk Sep 24, 2019

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

sre-bot commented Sep 24, 2019

Uh oh!

Uh oh!

cfzjywxk commented Sep 26, 2019

Uh oh!

cfzjywxk commented Sep 26, 2019

Uh oh!

sre-bot commented Sep 27, 2019

Uh oh!

jackysp left a comment

Choose a reason for hiding this comment

Uh oh!

jackysp commented Sep 27, 2019

Uh oh!

coocood left a comment

Choose a reason for hiding this comment

Uh oh!

sre-bot commented Sep 27, 2019

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

6 participants

cfzjywxk commented Sep 18, 2019 •

edited

Loading

codecov bot commented Sep 18, 2019 •

edited

Loading