Surface SQL metrics in the CC console

[kevin-v-ngo]

We have surfaced statements, transactions, and sessions in the CC console. We should surface SQL time-series metrics as well for identifying performance trends and anomaly detection. This would go under the SQL activity page.

Top level trends and signals:

1. Open SQL Sessions - "The total number of open SQL sessions. This includes both active and idle sessions."

2. Open SQL Transactions - "The total number of SQL transactions that are in progress."

3. SQL Statements (same on the overview page) - "The average number of SELECT, INSERT, UPDATE, and DELETE SQL statements successfully executed per second."

4. SQL Statement Latency - "Service latency is calculated as the time in nanoseconds between when the cluster receives a SQL statement and finishes executing the statement. This time does not include returning results to the client. This represents the 99th, 90th, and 50th percentile of service latency."

Pinpointing specific issues:

5. Transaction Contention - "The total number of transactions that experienced contention."

6. Transaction Restarts - ""The total number of transaction restarts broken down by retry error."

7. SQL Statement Errors - "The total number of statements that experienced an error."

8. SQL Statement Contention - "The total number of statements that experienced contention."

9. SQL Statement Full Scans - "The total number of statements that had a full scan in their plan."

We can potentially remove "SQL" if this is under the "SQL" activity page in the chart titles and descriptions.

cc @Annebirzin and @ianjevans

Link to figma: https://www.figma.com/file/df6CWLRRWdRd9LJ93PR0rHWD/cc-obsrv-serverless-observability?node-id=4739%3A52650

Epic CC-5060

[Annebirzin]

@kevin-v-ngo a couple of questions:

• (3) and (4) use the word 'Open' while (5) uses the word 'Active' - is there a difference?
• What is the difference between (4) and (9)?
• For (7), you mentioned we'd likely be only starting with Transaction Contention, should this one be updated to 'transaction'?

[kevin-v-ngo]

Good questions! This was ported over from the existing SQL dashboard today but it would be good to revisit and see if we need to make any adjustments for clarity.

(3) A SQL Session can either be 'Idle' or 'Active depending on if there is an 'Open' transaction. Not sure why we are calling a session 'Open' - perhaps we aimed to be more specific. Note we have an issue where we received feedback to also surface an 'Active' SQL session metric. I'd be okay with removing 'Open' since it's already implied if there is a non-zero value representing both idle and active but for now, let's keep it consistent with other areas of the console where we qualify 'Open' session (serverless overview page)

(4) An 'Open' SQL Transaction is a transaction that is in progress. It has not yet been committed or aborted/rolled back.

(5) Not sure why we qualify with 'Active'. If you look at the existing charts today, we have 'Active SQL Statements' and 'SQL Statements'. The former is a 'Total' while the later is a 10-second moving average broken down by statement type. I'd suggest changing the former to 'Total SQL Statements' and the later to something along the lines of 'Average SQL Statements by type'?

(4) This is # Transactions that are in progress. (9) breaks it down further by Transaction state (BEGIN, COMMITTED, ROLLED BACK, ABORTED.

(7) Agree. The contention UX (showing contending information) will start at the Transaction-level so I'll create an issue so that we capture # Transactions which experienced contention for consistency. Note the SQL statement contention time-series metric is already available today in the SQL dashboard.

[kevin-v-ngo]

@Annebirzin, @ianjevans, and @stbof , I updated the chart titles and descriptions. Let me know if you have any feedback.

[Annebirzin]

Nice! I like the groupings too 'top-level insights' and 'specific issues'. Maybe we can find a way to work that into the UI.

[ghost]

A nit about spelling: in labels use all initial caps (aka heading case). In running text, only the first letter is capitalized (aka sentence case). In general, the words session and transaction should have an initial cap only in a label.

Therefore

Open SQL Sessions - "The total number of open SQL Sessions. This includes both active and idle sessions." should be

Open SQL Sessions - "The total number of open SQL sessions. This includes both active and idle sessions."

In this sentence, AND is a conjunction, and should not be all caps: SQL Statements (same on the overview page) - "The average number of SELECT, INSERT, UPDATE, AND DELETE SQL statements successfully executed per second."

Therefore it should be

SQL Statements (same on the overview page) - "The average number of SELECT, INSERT, UPDATE, and DELETE SQL statements successfully executed per second."

[kevin-v-ngo]

Nice! I like the groupings too 'top-level insights' and 'specific issues'. Maybe we can find a way to work that into the UI.

The groupings i had was to rationalize the order of the charts in terms of our session -> transaction -> statement hierarchy. We'll probably need to brainstorm better labels if we include them.

And thanks @stbof! Making the adjustments in the issue.

[kevin-v-ngo]

Hi @stbof, I think you already reviewed the tool-tips/descriptions of each of these metrics. Can you confirm? But if not, can you take a look to see if there should be any adjustments? These will go into the CC console as tool tips for each chart.

[ghost]

Hi @stbof, I think you already reviewed the tool-tips/descriptions of each of these metrics. Can you confirm? But if not, can you take a look to see if there should be any adjustments? These will go into the CC console as tool tips for each chart.

Yes, I reviewed already.