[Draft] Yugabyte ASH notes and suggestions.

[DRAFT, not for wider publication]

TL;DR: Request for adding ID fields to yb_active_session_history. First, use the tablet_uuid instead of the first string-part of the uuid. Furthermore, add IDs to identify the descriptive texts (but keep the text for human readable). And finally consider defining the Root-request-ID as a separate entity (possibly another view: yb_root_request). 

Elaborate..

So far I have created a table to store yb_active_session_history records. The columns in the table are copies of the columns from the view, with two additions: id and host. The id comes from  sequence, and the host is the $HOSTNAME of the node where the tserver runs, e.g. where the record is collected (in future, I would use tsrv_uuid).

My Ash records are "under development", but this is my latest:

I also built a stored-function to capture the records, which was made to run on every node (every tserver) with 1 or 3 or 10min intervals (insert where not exist...). The resulting data can be queried from Grafana, sql-scripts or adhoc via query-tools like DBeaver.

After collecting about half-million of ASH records (several times now...), I came up with some ideas for my data-collection and interpretation.

The records are around 180 bytes on average. They could be leaner, easier to store and query, if I could normalize some data out to lookup-tables (e.g. the events), and to parent-records (e.g. a table with root-request-records to hold some of the information regarding query and client-process). 

Defining parent-records for Root_request would also add more clarity to the meaning of the data as some fields seem to depend solely on root_request, not on the individual ash-record.

Hence some suggestions.

1. Wait_event_aux => Tablet_UUID. 

Replace aux by tablet_uuid? Use uuid instead of text. The only function of the wait_event_aux seems to be the lookup of a tablet. In that case, there may as well be a field tblt_uuid (uuid) to link directly to (local)tablets.

2. IDs. Replace descriptive text fields with IDs (integer).

For this reasons above,  the following 4 fields could be "normalized out":

• wait_event  : add a field for wait_event_id (or identifying constant)
• wait_event_type: add a wait_event_type_id
• wait_event_class : idem
• wait_event_component: idem.

I am assuming the source-code already contains constants for these descriptors anyway, so maybe re-use those?

By just adding the fields (not replacing them), the view remains "human readable", but those who want to store the data for later use can gain in efficiency. Replacing those text fields by integer-IDs would save about 30 bytes,  (+/-15%) and would make searching and comparisons (integers instead of text) more efficient.

3. The Root-Request_id: 

This rr_id seems to represent a so-far unknown entity. I Think that  the query_id, and client_node_ip, and probably top_level_node_id belong at this level. The combination of top_level + pid always points to some client-process or to a t-server. And some root-requests have hundreds of ash-records attributed to them (mostly heavy queries, sometimes background-processing).

The main obstacle to extracting root_request are the requests + top_node with IDs of 0000s. I suspect that can be solved, especially since the PID in those records (always?) seems to correspond to the t-server-pid of one of the nodes. Hence, for root-req 000 there _is_ a way to attribute it to a t-server (or to the local tserver by default). I might experiment with this: lookup the tsever from the given pid.

Summarise...

Most Important (to me) is: a more transparant data-model, with root-request which also makes it easier to aggregate per entity when so desired.

From suggestions 2 and 3, especially if the query and client_node_ip can be kept at the level of root_request, that would save approx another 20 bytes. All in all almost 30% savings on record-size possible. But byte-savings are not my main aim.

Note: My idea is also specifically not to remove fields in existing views (yet) so as not to disturb others who already have use for ASH-views.

So far my thoughts. 

I need more time to digest + experiment. But wanted to write this out to see if any comments or suggestions...