Skip to content

cloudthinker-ai/CloudSkills / skills/connections/analyzing-mariadb

analyzing-mariadb

Verified

Use when working with Mariadb — mariaDB Galera cluster status, MaxScale routing, ColumnStore engine analysis, performance schema tuning, and replication health.

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

ShortcutCounterWhy
"I'll skip discovery and check known resources"Always run Phase 1 discovery firstResource names change, new resources appear — assumed names cause errors
"The user only asked for a quick check"Follow the full discovery → analysis flowQuick checks miss critical issues; structured analysis catches silent failures
"Default configuration is probably fine"Audit configuration explicitlyDefaults often leave logging, security, and optimization features disabled
"Metrics aren't needed for this"Always check relevant metrics when availableAPI/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 errorAssumed 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
slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack."Stars: 196kUpdated Claude CodeCodexVerifiedInstall
ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read.Stars: 192kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 192kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt.Stars: 192kUpdated Claude CodeCodexVerifiedInstall