Description:

As a database administrator, I want the sampling mechanism to capture detailed state information for each active session so that I can diagnose what each session was doing during performance issues.

Acceptance Criteria:

For each sampled session, collect the metrics listed in the example table below.
Include a wait_event_group metric derived from wait_event, with predefined groups such as 'CPU', 'IO', 'Lock', 'Network', 'Commit', 'Idle', to categorize waits for easier troubleshooting (e.g., identifying if slow performance is due to CPU usage, I/O delays, or commit operations).

For sessions actively executing (on CPU), set wait_event_group to 'CPU' and wait_event to 'on CPU'.

For sessions waiting, assign wait_event_group based on the type of wait event (e.g., 'IO' for I/O waits, 'Lock' for lock waits).

Ensure data collection is optimized to avoid significant performance overhead (e.g., reuse existing Performance Schema hooks where possible).

Example Metrics Table:

The table below outlines the metrics to collect for each sampled session to support ASH-like monitoring in MariaDB. This comprehensive list ensures detailed performance analysis, incorporating feedback about grouping wait events.

Notes on Wait Event Grouping:

'CPU': Assigned when the session is actively executing (e.g., wait_event = 'on CPU').
'IO': For I/O-related waits (e.g., wait/io/file/sql/binlog → 'IO'). Maps to User I/O or System I/O based on context.
'Lock': For lock-related waits (e.g., wait/synch/mutex/sql/LOCK_global_system_variables → 'Lock').
*'Network': *For network-related delays.
'Commit': For waits related to transaction commits (e.g., redo log or I/O during commit).
'Idle': For sessions in a sleep state (though typically not sampled unless configured otherwise).

This grouping aligns with general user feedback and pgsentinel’s approach, making it easier to troubleshoot performance bottlenecks by category.

Server Enhancement Requirements:

Instrumentation: Extend the MariaDB server’s instrumentation to collect additional metrics such as cpu_time, network_usage, lock_wait_time, and detailed wait event information. This may require adding new hooks or extending existing Performance Schema instruments.
Wait Event Grouping Logic: Implement logic to derive wait_event_group from wait_event. Initially, use a fixed mapping based on the wait event name prefix (e.g., 'wait/io' → 'IO', 'wait/synch/mutex' → 'Lock'). Future enhancements could use a configurable mapping table.
Optimization: Ensure the sampling mechanism efficiently gathers metrics by leveraging existing Performance Schema data (e.g., events_waits_current) and only calculates additional metrics when necessary.