sql,*: tracking issue for sql overheads

[ajwerner]

Describe the problem

Here is a profile
captured over a single-node run of kv95. The profile captures over 15 minutes of samples. This profile contains no networking for distributed KV execution,
and, thus is not a great representation of the overheads our network stack adds. Nevertheless, it provides
an interesting lens to look examine the cockroach process.

Note also that KV95 represents just about the worst case in terms of amortizing overheads. Many of the things which show up here would pale in comparison to planning and execution overheads of more complex queries. Nevertheless, it's valuable to understand the sources of overheads in simple workloads.

Note that the syscalls represent something like 5% of the CPU time.

category	percent
syscall: write	4.55%
syscall: read	0.86%
VFS.sync	0.1%

kvserver grabs about 22.17% of the CPU time.

Below find a list of interesting SQL stack traces that add up to around 35% of all of the CPU time.

• 7.97 (*sql.connExecutor).transitionsStateApplyWrapper
  • This is varied: almost 2% in stats things, 0.8% releasing leases, 1.45% making a new KV transaction

• 7.7% (*memo.Memo).IsStale

  • Most of this is in catalog code -- the use of btrees as opposed to hash maps seems to hurt.
  • 

• 5.43% (*sql.DistSQLPhysicalPlanner).setupFlows
  

• 4.34% (*sql.planner).makeOptimizerPlan
  

• 2.32% (*sql.connExecutor).recordStatementSummary

• 1.83% (*sql.connExecutor).addActiveQuery
  • @jordanlewis is doing something about this (I think) in sql: SHOW QUERIES lazily interpolates placeholders #86968

• 1.72% (*colfetcher.cFetcher).resetBatch

• 1.68% (*sql.connExecutor).updateTxnRewindPosMaybe

• 1.27% (*sql.DistSQLPhysicalPlanner).createPhysPlan
  

• 1.25% return from (*sql.DistSQLPlanner).PlanAndRunAll
  

• 1.01% (*sql.instrumentationHelper).SetIndexRecommendations
  

Additional context

Note that this does not explore other overheads like grpc, because we don't send any requests in this single-node workload.

Jira issue: CRDB-19988

[rytaft]

Might also be useful: https://cockroachlabs.slack.com/archives/C03NNC21L31/p1660193368559309