Next steps after XMover

[amotl]

Thoughts

After excellent tooling enhancements by @WalBeh per GH-523, we are thinking about the next steps how to improve operational tooling, in the spirit of an operators journey when fire-fighting a busy cluster.

Topics

How to re-use existing components in ctk, mostly the cfr/info/jobstats subsystem, how to glue them together, and what to expand.

Report about hot shards

Thoughts

@matriv will send SQL statements.
@juanpardo says:

From what I have seen playing around with a crfree cluster, the seq_no_stats.max_seq_no, global_checkpoint and local_checkpoint all increase with insert, update and delete operations, so it seems we can easily measure heat per shard.

Thanks!

Early explorations

• Admin/XMover: Shard heat monitoring, optionally watching #527

Report about table use

Q: We want to know which tables are being queried with which queries. We do NOT want to know how long those statements take, the focus should be on the frequency of table use. Expected output:

Options and modes:
- sort order: total descending, with options to focus on other
- limit: 5/10
- mode: report
- report mode: short-term (hot spot) vs. long-term (trend) looking glass

# last 5 seconds / 30 seconds / 5 minutes
# last 5 minutes / 30 minutes / 6 hours / 24 hours

doc.Kochtopf (insert: 400, update: 20, select: 342, total: 487)
doc.Deckel (insert: 200, update: 10, select: 142, total: 333)

A: Report about hot tables (in terms of traffic/queries). Inquire from sys.jobs_log and do some stats on it. Caveat: When just looking at sys.jobs_log, you cannot directly identify tables when a query hits a view. Let's make the tool even smarter by considering the SQL VIEW DDL for backtracking.

Bestandsaufnahme

• What did we do with cfr+marimo already?
• cfr info, cfr jobstats

[matriv]

@matriv will send SQL statements.
After looking carefullly into option what Juan has started with (seq numbers) is the way to go imho, ideally in combination with sys.jobs_log where we can filter out the write stmts like:

SELECT * FROM sys.jobs_log WHERE classification['type'] IN ('INSERT', 'UPDATE', 'DELETE') AND started BETWEEN ? AND ?;

and then (thx @surister) use something like:

from cratedb_sqlparse import sqlparse

t = sqlparse("""delete from table doc.test where v = 1""")

print(t[0].metadata.tables)
# [Table(name='test', schema='doc')]

to parse the stmt from the sys.jobs_log and get our hands on the table name.

[amotl]

Hi again. @juanpardo and me did something the other day with those patches.

• Admin/XMover: Shard heat monitoring, optionally watching #527
• ctk info table-traffic #528

They will need to be revisited and wrapped up, as they lost a bit of traction while the focus was on producing the XMover baseline first.