analyzing-mariadb

name	analyzing-mariadb
description	Use when working with Mariadb — mariaDB Galera cluster status, MaxScale routing, ColumnStore engine analysis, performance schema tuning, and replication health.
connection_type	mariadb
preload	false

MariaDB Analysis Skill

Analyze and optimize MariaDB clusters with safe, read-only operations.

MANDATORY: Two-Phase Execution

You MUST follow this two-phase pattern. Skipping Phase 1 causes hallucinated database/table names.

Phase 1: Discovery (ALWAYS run first)

#!/bin/bash

# 1. MariaDB version
mariadb -h "$MDB_HOST" -u "$MDB_USER" -p"$MDB_PASSWORD" -e "SELECT VERSION();"

# 2. List databases
mariadb -h "$MDB_HOST" -u "$MDB_USER" -p"$MDB_PASSWORD" -e "SHOW DATABASES;"

# 3. List tables
mariadb -h "$MDB_HOST" -u "$MDB_USER" -p"$MDB_PASSWORD" -e "SHOW TABLES FROM my_database;"

# 4. Describe table (never assume column names)
mariadb -h "$MDB_HOST" -u "$MDB_USER" -p"$MDB_PASSWORD" -e "DESCRIBE my_database.my_table;"

# 5. Check for Galera
mariadb -h "$MDB_HOST" -u "$MDB_USER" -p"$MDB_PASSWORD" -e "SHOW STATUS LIKE 'wsrep_%';" 2>/dev/null

# 6. Check for ColumnStore
mariadb -h "$MDB_HOST" -u "$MDB_USER" -p"$MDB_PASSWORD" -e "SELECT ENGINE FROM information_schema.ENGINES WHERE ENGINE = 'Columnstore';" 2>/dev/null

Phase 1 outputs:

• MariaDB version and engine capabilities
• Databases and tables
• Cluster type (standalone, Galera, replication)

Phase 2: Analysis (only after Phase 1)

Only reference databases, tables, and columns confirmed in Phase 1.

Shell Script Patterns

Helper Function

#!/bin/bash

# Core MariaDB query runner — always use this
mdb_query() {
    local query="$1"
    mariadb -h "${MDB_HOST:-localhost}" -P "${MDB_PORT:-3306}" \
        -u "${MDB_USER:-root}" -p"${MDB_PASSWORD}" \
        -N -B -e "$query"
}

# MaxScale API helper (if MaxScale is in use)
maxscale_api() {
    local endpoint="$1"
    curl -s -u "${MAXSCALE_USER:-admin}:${MAXSCALE_PASSWORD:-mariadb}" \
        "http://${MAXSCALE_HOST:-localhost}:8989/v1/$endpoint"
}

Anti-Hallucination Rules

• NEVER reference a database or table without confirming via SHOW commands
• NEVER reference column names without running DESCRIBE
• NEVER assume Galera cluster — always check wsrep status first
• NEVER assume MaxScale is present — check separately
• NEVER assume ColumnStore engine — verify via ENGINES table

Safety Rules

• READ-ONLY ONLY: Use only SELECT, SHOW, EXPLAIN, information_schema/performance_schema queries
• FORBIDDEN: DROP, ALTER, INSERT, UPDATE, DELETE, SET GLOBAL without explicit user request
• ALWAYS add LIMIT to user table queries
• Use EXPLAIN before running expensive queries
• Never modify Galera cluster state without explicit user request

Common Operations

Galera Cluster Status

#!/bin/bash
echo "=== Galera Cluster Overview ==="
mdb_query "SHOW STATUS LIKE 'wsrep_cluster_size';"
mdb_query "SHOW STATUS LIKE 'wsrep_cluster_status';"
mdb_query "SHOW STATUS LIKE 'wsrep_local_state_comment';"
mdb_query "SHOW STATUS LIKE 'wsrep_ready';"
mdb_query "SHOW STATUS LIKE 'wsrep_connected';"

echo ""
echo "=== Node Status ==="
mdb_query "SHOW STATUS WHERE Variable_name IN ('wsrep_local_recv_queue_avg', 'wsrep_local_send_queue_avg', 'wsrep_flow_control_paused', 'wsrep_cert_deps_distance', 'wsrep_last_committed');"

echo ""
echo "=== Replication Health ==="
mdb_query "SHOW STATUS WHERE Variable_name LIKE 'wsrep_local%' OR Variable_name LIKE 'wsrep_received%';"

MaxScale Routing Analysis

#!/bin/bash
echo "=== MaxScale Servers ==="
maxscale_api "servers" | jq '.data[] | {id, attributes: {state: .attributes.state, address: .attributes.parameters.address, port: .attributes.parameters.port}}'

echo ""
echo "=== MaxScale Services ==="
maxscale_api "services" | jq '.data[] | {id, attributes: {router: .attributes.router, state: .attributes.state, connections: .attributes.connections}}'

echo ""
echo "=== MaxScale Monitors ==="
maxscale_api "monitors" | jq '.data[] | {id, attributes: {module: .attributes.module, state: .attributes.state}}'

Performance Schema Analysis

#!/bin/bash
echo "=== Slow Queries ==="
mdb_query "SELECT DIGEST_TEXT, COUNT_STAR, ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_ms, SUM_ROWS_EXAMINED, SUM_ROWS_SENT FROM performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC LIMIT 15;"

echo ""
echo "=== Table I/O ==="
mdb_query "SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT_READ, COUNT_WRITE, SUM_TIMER_READ/1000000000 as read_ms, SUM_TIMER_WRITE/1000000000 as write_ms FROM performance_schema.table_io_waits_summary_by_table WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema') ORDER BY SUM_TIMER_WAIT DESC LIMIT 15;"

echo ""
echo "=== InnoDB Status ==="
mdb_query "SHOW ENGINE INNODB STATUS\G" | head -50

ColumnStore Analysis

#!/bin/bash
echo "=== ColumnStore Tables ==="
mdb_query "SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS, ROUND(DATA_LENGTH/1024/1024, 2) as data_mb FROM information_schema.tables WHERE ENGINE = 'Columnstore' ORDER BY DATA_LENGTH DESC;"

echo ""
echo "=== ColumnStore System Info ==="
mdb_query "SELECT * FROM columnstore_info.system_status;" 2>/dev/null || echo "ColumnStore not available"

echo ""
echo "=== ColumnStore Extents ==="
mdb_query "SELECT * FROM columnstore_info.extent_map LIMIT 20;" 2>/dev/null || echo "ColumnStore not available"

Output Format

Present results as a structured report:

Analyzing Mariadb Report
════════════════════════
Resources discovered: [count]

Resource       Status    Key Metric    Issues
──────────────────────────────────────────────
[name]         [ok/warn] [value]       [findings]

Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]

Target ≤50 lines of output. Use tables for multi-resource comparisons.

Counter-Rationalizations

Shortcut	Counter	Why
"I'll skip discovery and check known resources"	Always run Phase 1 discovery first	Resource names change, new resources appear — assumed names cause errors
"The user only asked for a quick check"	Follow the full discovery → analysis flow	Quick checks miss critical issues; structured analysis catches silent failures
"Default configuration is probably fine"	Audit configuration explicitly	Defaults often leave logging, security, and optimization features disabled
"Metrics aren't needed for this"	Always check relevant metrics when available	API/CLI responses show current state; metrics reveal trends and intermittent issues
"I don't have access to that"	Try the command and report the actual error	Assumed permission failures prevent useful investigation; actual errors are informative

Common Pitfalls

• Galera SST blocking: State Snapshot Transfer blocks the donor node — monitor wsrep_local_state_comment
• Flow control: Galera flow control pauses writes when a node falls behind — check wsrep_flow_control_paused
• Multi-master conflicts: Galera rejects conflicting writes — monitor certification failures
• MaxScale failover: MaxScale automatic failover can cause brief connection drops
• ColumnStore vs InnoDB: ColumnStore is for analytics, InnoDB for OLTP — do not mix workload types
• GTID replication: Galera uses its own GTID — do not confuse with MariaDB GTID replication