yugabyte / tpcc

Repo to run TPCC benchmarks against YugabyteDB
Other
24 stars 21 forks source link

Remove `for update` from select queries in NewOrder transaction. #60

Closed psudheer21 closed 4 years ago

psudheer21 commented 4 years ago

Summary: The NewOrder transaction performs a select on the District and the Stock table with a row level lock. This row level lock is unnecessary as we are following this up with a write to the same rows.

Reviewers: Mihnea

psudheer21 commented 4 years ago

With select for update Results:

21:42:16,783 (DBWorkload.java:1017) INFO  - TPM-C: 12,672.63
21:42:16,783 (DBWorkload.java:1018) INFO  - Efficiency : 98.54%
21:42:16,964 (DBWorkload.java:1051) INFO  - NewOrder, Avg Latency: 54.06568269139874 msecs, p99 Latency: 675.424 msecs
21:42:17,059 (DBWorkload.java:1051) INFO  - Payment, Avg Latency: 25.02660878788458 msecs, p99 Latency: 118.827 msecs
21:42:17,069 (DBWorkload.java:1051) INFO  - OrderStatus, Avg Latency: 19.06597274380941 msecs, p99 Latency: 127.436 msecs
21:42:17,081 (DBWorkload.java:1051) INFO  - Delivery, Avg Latency: 124.70621622728736 msecs, p99 Latency: 837.939 msecs
21:42:17,092 (DBWorkload.java:1051) INFO  - StockLevel, Avg Latency: 285.446564279726 msecs, p99 Latency: 1251.21 msecs

After removing the ForUpdate:

19:40:34,487 (DBWorkload.java:1017) INFO  - TPM-C: 12,667.83
19:40:34,487 (DBWorkload.java:1018) INFO  - Efficiency : 98.51%
19:40:34,667 (DBWorkload.java:1051) INFO  - NewOrder, Avg Latency: 52.11471102960509 msecs, p99 Latency: 594.095 msecs
19:40:34,761 (DBWorkload.java:1051) INFO  - Payment, Avg Latency: 24.409624931473225 msecs, p99 Latency: 87.928 msecs
19:40:34,769 (DBWorkload.java:1051) INFO  - OrderStatus, Avg Latency: 17.26361703812317 msecs, p99 Latency: 70.034 msecs
19:40:34,779 (DBWorkload.java:1051) INFO  - Delivery, Avg Latency: 119.67307073739434 msecs, p99 Latency: 652.964 msecs
19:40:34,789 (DBWorkload.java:1051) INFO  - StockLevel, Avg Latency: 275.69005671955813 msecs, p99 Latency: 1284.804 msecs
Screen Shot 2020-10-20 at 3 01 42 PM Screen Shot 2020-10-20 at 3 01 53 PM