sql: implement mechanisms to limit resource consumption

[knz]

This is a meta-issue to track progress on the issue of resource usage.

Memory usage:

• track dynamic memory usage (where the amount is dependent on user queries and/or current db state) sql: preliminary mechanism to track and limit SQL memory usage. #8691
• sql+kv: monitor memory usage by KV batches and write intents kv: monitor memory usage by KV batches and write intents #10320
• bound memory usage to prevent large queries from crashing server docs: RFC on resource usage boundaries. #7657
  • e.g sql: implicit cross joins can cause OOM errors #7572
• stream results from executor through pgwire instead of collecting them first in RAM sql: stream planNode.Next() results through pgwire without collecting all intermediate results in RAM #7775
• automatically drop idle client connections
• whatever mechanism is introduced for throttling the start of new queries could also be a good place to prevent starting new queries in a server that is being drained
• restrict REPEAT sql: REPEAT built-in can crash the server #10545

Time usage:

• sql: actively cancel queries when client connection drops sql: actively cancel queries when client connection drops #16262
• prevent queries from consuming 100% cpu time
  • e.g. sql: decimal round can DOS the server #8633
• prevent queries from running forever unless there are actually results communicated to the client cc docs: RFC on resource usage boundaries. #7657
• allow clients to set a custom transaction timeout sql: implement idle_in_transaction_session_timeout #5924

File descriptors:

• ensure the db doesn't run out of file descriptors all: Limit file descriptor usage #6210
  • [ ] limit the number of concurrent connections tracked in separate issue
• ensure that KV txns are cleaned up when clients go away without closing their SQL connection

[a6802739]

@knz, I wonder if I could offer some help here? :)

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[jordanlewis]

@knz I feel that this issue has been sufficiently tackled and broken down into other issues, so I'll close it. Thanks for starting this initiative back in the day! :) If you feel like there are missing pieces still, let me know and we can create / link to new issues.

[knz]

All the check list at the top is crossed off so clearly the work is completed in my book