Analyzing Mariadb

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

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