sql: 23.1 microbenchmark regression investigation and sign-off

[mgartner]

This issue tracks SQL Queries -related benchmark regressions. The main issue tracking all benchmark regressions is #96960.

The pkg/sql sheet is here: https://docs.google.com/spreadsheets/d/1uDYotBARIqdL5nSquMoF7Fy3qqoMfNigwHAcB8NPR7M/edit?pli=1#gid=7

Here's a list of the major regressions we need to investigate:

time/op regressions (up to 20%)

• Phases/many-columns-and-indexes-c/
  • 500+% slower
  • @mgartner: I've bisected this to 7f8a334. Prior to this commit join ordering was disabled in these benchmarks. I'm going to backport this change to release-22.2 and re-compare all optimizer benchmarks.
  • Fixed by release-22.2: opt: set default session settings and cache histograms in benchmarks #98309
• Chain/chain
  • Up to 490% slower
  • @mgartner: This is likely also due to 7f8a334.
  • Fixed by release-22.2: opt: set default session settings and cache histograms in benchmarks #98309
• Encodings/(1::varchar(4) COLLATED "en_US", null, Encodings/(1::char(1) COLLATED "en_US", null, Encodings/(1::text(4) COLLATED "en_US", null, Encodings/(1::char(2) COLLATED "en_US", null
  • Up to 182% slower
  • @mgartner: I've bisected to tree: fix panic when encoding tuple #95009.
  • This is likely a non-issue - it's regressed from 50ns to 120ns.
  • Will be fixed by tree: fix tuple encoding performance regression #98717.
• KV/Delete/SQL
  • Up to 496% slower
  • @DrewKimball: Expected due to benchmark change in kv: insert rows during preparation for BenchmarkKV/Delete #90563
• KV/Delete/Native
  • ~50% slower
  • @DrewKimball: Expected due to benchmark change in kv: insert rows during preparation for BenchmarkKV/Delete #90563
• KV/Insert/Native
  • ~40% slower
  • @DrewKimball: Likely due to kv: async resolve unreplicated locks on other ranges after one-phase commit #98044, fixed by kv: don't perform async intent resolution on 1PC with point lock spans #98630
• ProjOp/projGTInt64Int64/useSel=false/hasNulls=false-8
  • 132% slower
  • @yuzefovich unreliable benchmark, not worth looking into
• IsDatumEqual
  • 112% slower
  • @mgartner: I've bisected to colinfo: add missing type families into CanHaveCompositeKeyEncoding #95839.
  • This benchmark only changed with the introduction of TSQuery and TSVector encoding. I don't think we should be concerned about this regression.
• EncodeDatum-8
  • 67% slwoer
• SelLTInt64Int64Op/useSel=false,hasNulls=false-8
  • 60% slower
  • @yuzefovich unreliable benchmark, not worth looking into
• ConcurrentSelect1/concurrentConn
  • Up to 50% slower
• NumberedContainerIteratorCaching/lookup-join, NumberedContainerIteratorCaching/inverted-join
  • Up to 40% slower
• CrossJoiner/spillForced=false/type=INNER/rows=8192-8
  • 30% slower
• HashAggregatorPartialOrder/ANY_NOT_NULL/hash-partial-order
  • 30% slower
• Distinct/PartiallyOrdered/hasNulls=true/newTupleProbability=0.001/rows=4096/ordCols=1/type=bytes-8
  • 27% slower
  • @yuzefovich not used in production
• SortTopK/rows=16384/cols=2/matchLen=1/avgChunkSize=128-8
  • 22% slower
• ValuesProcessor/rows=16,cols=1-8
  • 20% slower
• Phases/tpcc-stock-level/Prepared
  • 20% slower
  • Fixed by release-22.2: opt: set default session settings and cache histograms in benchmarks #98309

alloc/op regressions

TODO

allocs/op regressions

TODO

[blathers-crl]

Hi @mgartner, please add branch-* labels to identify which branch(es) this release-blocker affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[blathers-crl]

Hi @mgartner, please add branch-* labels to identify which branch(es) this release-blocker affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[mgartner]

We might need to investigate some regressions in pkg/ccl too: https://docs.google.com/spreadsheets/d/1HxTZbtEMscgzt_4f-z1D2KB6HdvXw-qGaZF4zkXV1ik/edit#gid=6

[mgartner]

Here is a comparison of the optimizer benchmarks comparing release-22.2 to master after making the join reordering limit consistent: https://gist.github.com/mgartner/9e2bff7eaef825d0224363b41538c973

All the regressions seem acceptable. The only minor ones that we might want to look into are some of the bytes allocated regressions:

Phases/many-columns-and-indexes-b/Simple/ExecBuild-24                      26.7kB ± 0%    48.7kB ± 0%   +82.15%  (p=0.008 n=5+5)
Phases/many-columns-and-indexes-b/Simple/Explore-24                        25.7kB ± 0%    47.5kB ± 0%   +85.35%  (p=0.008 n=5+5)
Phases/many-columns-and-indexes-b/Simple/OptBuildNorm-24                   24.2kB ± 0%    46.1kB ± 0%   +90.67%  (p=0.008 n=5+5)
Phases/many-columns-and-indexes-b/Simple/OptBuildNoNorm-24                 20.4kB ± 0%    42.2kB ± 0%  +106.99%  (p=0.008 n=5+5)
Phases/many-columns-and-indexes-a/Simple/ExecBuild-24                      18.6kB ± 0%    40.0kB ± 0%  +115.37%  (p=0.016 n=4+5)
Phases/many-columns-and-indexes-a/Simple/Explore-24                        17.8kB ± 0%    39.2kB ± 0%  +119.77%  (p=0.008 n=5+5)
Phases/many-columns-and-indexes-a/Simple/OptBuildNorm-24                   16.4kB ± 0%    37.8kB ± 0%  +130.76%  (p=0.008 n=5+5)
Phases/many-columns-and-indexes-a/Simple/OptBuildNoNorm-24                 14.2kB ± 0%    35.6kB ± 0%  +149.72%  (p=0.016 n=4+5)

[mgartner]

We've accounted for the major regressions. I'm going to close this. Thanks for the help @yuzefovich and @DrewKimball!