Mariadb

---
name: mariadb
description: "MariaDB technology expert covering ALL versions. Deep expertise in InnoDB, Aria, ColumnStore, Galera Cluster, MaxScale, replication, query optimization, and operational tuning. WHEN: \"MariaDB\", \"Galera\", \"MaxScale\", \"ColumnStore\", \"Aria engine\", \"mariadb-dump\", \"MariaDB replication\", \"mariadb-backup\", \"Spider engine\", \"WSREP\", \"Galera cluster\", \"mariadb.cnf\", \"mariadb-secure-installation\", \"system versioning\", \"MariaDB thread pool\"."
license: MIT
---

# MariaDB

This skill covers MariaDB across all supported versions (10.6 through 12.x). It covers MariaDB internals, storage engines, Galera Cluster, MaxScale, query optimization, and operational tuning. For version-specific detail, see the matching file under `references/versions/`.

## When to Use This Skill vs. Version-Specific Guidance

**Use this agent when the question spans versions or is version-agnostic:**
- "How does Galera Cluster replication work?"
- "Tune InnoDB buffer pool for a write-heavy workload"
- "Set up MariaDB replication"
- "Compare Aria vs InnoDB"
- "Best practices for mariadb server configuration"

**See the matching version reference when the question is version-specific:**
- "MariaDB 12.x optimizer hints" --> `references/versions/12.x.md`
- "MariaDB 11.8 VECTOR data type" --> `references/versions/11.8.md`
- "MariaDB 11.4 cost-based optimizer changes" --> `references/versions/11.4.md`
- "MariaDB 10.11 password_reuse_check" --> `references/versions/10.11.md`
- "MariaDB 10.6 Atomic DDL" --> `references/versions/10.6.md`

## How to Approach Tasks

When you receive a request:

1. **Classify** the request:
   - **Architecture/internals** -- Load `references/architecture.md`
   - **Performance diagnostics** -- Load `references/diagnostics.md`
   - **Configuration/operations** -- Load `references/best-practices.md`
   - **Version-specific feature** -- See the matching `references/versions/<v>.md` file
   - **Comparison with other databases** -- see the `overview` skill

2. **Determine version** -- Ask if unclear. Behavior differs significantly across versions (e.g., cost-based optimizer rewrite in 11.4+, VECTOR type only in 11.8+).

3. **Analyze** -- Apply MariaDB-specific reasoning. Reference storage engines, the query optimizer, Galera mechanics, and replication topology as relevant.

4. **Recommend** -- Provide actionable guidance with specific server variables, SQL, or configuration changes.

5. **Verify** -- Suggest validation steps (ANALYZE FORMAT=JSON, SHOW STATUS, Performance Schema, wsrep_% variables).

## Core Expertise

### Storage Engines

MariaDB supports multiple storage engines, each with distinct characteristics:

| Engine | Purpose | When to Use |
|---|---|---|
| **InnoDB** | ACID-compliant row-level locking transactional engine | Default for OLTP workloads; primary engine for most applications |
| **Aria** | Crash-safe replacement for MyISAM | System tables, temporary tables, read-heavy workloads not requiring transactions |
| **ColumnStore** | Columnar OLAP engine | Analytics, data warehousing, large-scale aggregations |
| **Spider** | Sharding engine with federated table support | Horizontal partitioning across multiple servers |
| **S3** | Archival engine storing data in S3-compatible object storage | Cost-effective archival of historical data |
| **CONNECT** | Access external data sources (CSV, JSON, XML, ODBC, etc.) | ETL, data integration, querying external files |

### InnoDB (Primary Engine)

InnoDB is the default and recommended engine for virtually all OLTP workloads:

- Row-level locking with MVCC for high concurrency
- Clustered index on the primary key (data stored in PK order)
- Buffer pool caches data and index pages (size with `innodb_buffer_pool_size`)
- Doublewrite buffer prevents partial page writes on crash
- Change buffer defers secondary index updates for non-unique indexes
- Redo log (ib_logfile0/1 or ib_redo in newer versions) for crash recovery
- Undo logs for MVCC snapshots and rollback

### Aria (Crash-Safe MyISAM Replacement)

Aria is MariaDB's improvement over MyISAM:

- Crash-safe: uses a write-ahead log for recovery
- Used internally for system tables and on-disk temporary tables
- Table-level locking (not suitable for concurrent write workloads)
- Faster full table scans and key reads than InnoDB for read-only workloads
- Supports both transactional and non-transactional modes

### Thread Pool

MariaDB includes a built-in thread pool (unlike MySQL where it is Enterprise-only):

- Limits the number of concurrently executing threads to reduce context switching
- Groups connections into thread groups (`thread_pool_size`, default = CPU count)
- Each group has a listener thread and worker threads
- Prevents performance degradation under high connection counts
- Key parameters: `thread_handling=pool-of-threads`, `thread_pool_size`, `thread_pool_max_threads`, `thread_pool_stall_limit`

### Galera Cluster (Synchronous Multi-Master)

Galera provides synchronous multi-master replication via the WSREP API:

**How It Works:**
1. A transaction executes locally on the originating node
2. At COMMIT, the node creates a writeset containing all row changes
3. The writeset is broadcast to all nodes via group communication (GComm)
4. Each node runs **certification-based conflict detection** -- checks for write-write conflicts against pending writesets
5. If certification passes, all nodes apply the writeset; if it fails, the originating node rolls back

**Key Concepts:**
- **Quorum**: Cluster requires a majority of nodes to operate (3 nodes tolerate 1 failure)
- **SST (State Snapshot Transfer)**: Full data copy to a joining node (mariabackup, rsync, mysqldump)
- **IST (Incremental State Transfer)**: Partial transfer of missed writesets from GCache
- **GCache**: Ring buffer on each node storing recent writesets for IST
- **Flow Control**: Throttles the cluster when a node falls behind (monitored via `wsrep_flow_control_paused`)
- **Certification**: Deterministic conflict detection; all nodes independently reach the same commit/abort decision

**Galera Limitations:**
- All tables MUST have a primary key (implicit row IDs cause issues)
- Only InnoDB/XtraDB storage engine is supported for replication
- Large transactions (>128K rows) cause cluster-wide performance issues
- DDL is executed via Total Order Isolation (TOI) -- blocks entire cluster
- No support for LOCK TABLES or GET_LOCK in multi-master mode
- XA transactions not supported

### MaxScale (Proxy / Load Balancer)

MaxScale is MariaDB's intelligent database proxy:

- **Query routing**: Read/write splitting, connection-based or statement-based
- **Load balancing**: Distributes reads across replicas
- **High availability**: Automatic failover with MariaDB Monitor
- **Query filtering**: Masking, firewall, tee (duplicate queries)
- **Monitoring**: Health checks for Galera, replication, and server states
- Key routers: ReadWriteSplit, ReadConnRoute, SchemaRouter

## Key Differences from MySQL

Understanding these differences is critical when migrating from MySQL or working with documentation:

| Area | MariaDB | MySQL |
|---|---|---|
| **JSON storage** | Stored as LONGTEXT with JSON validation; no binary format | Binary JSON (BSON-like) format |
| **GTID** | Domain-based GTIDs (`domain-server_id-sequence`); incompatible with MySQL GTIDs | UUID-based GTIDs (`server_uuid:transaction_id`) |
| **Thread pool** | Built-in, available in all editions | Enterprise Edition only |
| **System versioning** | Native temporal tables (`WITH SYSTEM VERSIONING`) | Not available natively |
| **Oracle compatibility** | SQL_MODE=ORACLE for PL/SQL syntax, ROWNUM, sequences | Limited Oracle compatibility |
| **Binary log encryption** | Uses its own encryption format; not compatible with MySQL | Different encryption format |
| **Authentication** | Default: unix_socket + ed25519/mysql_native_password | Default: caching_sha2_password (8.0+) |
| **Optimizer** | Truly cost-based (11.4+), rule-based elements in older versions | Cost-based with heuristics |
| **CHECK constraints** | Enforced (10.2+) | Enforced (8.0.16+), ignored in earlier versions |

### System Versioning (Temporal Tables)

A MariaDB-exclusive feature for tracking historical row data:

```sql
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
) WITH SYSTEM VERSIONING;

-- Query historical data
SELECT * FROM products FOR SYSTEM_TIME AS OF '2025-01-01 00:00:00';
SELECT * FROM products FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2025-06-01';
SELECT * FROM products FOR SYSTEM_TIME ALL;
```

### Binary Naming Transition

MariaDB has been transitioning command-line tool names from `mysql*` to `mariadb*`:

| Old Name | New Name | Status |
|---|---|---|
| `mysql` | `mariadb` | Symlinked; prefer `mariadb` |
| `mysqldump` | `mariadb-dump` | Symlinked; prefer `mariadb-dump` |
| `mysqladmin` | `mariadb-admin` | Symlinked; prefer `mariadb-admin` |
| `mysqlbackup` | `mariadb-backup` | Different tool (Percona XtraBackup fork) |
| `mysql_upgrade` | `mariadb-upgrade` | Symlinked |
| `mysqld` | `mariadbd` | Symlinked |

The old names remain as symlinks but may be removed in future versions. Always use the `mariadb*` names in new scripts and documentation.

## Query Optimization

### EXPLAIN and ANALYZE FORMAT=JSON

Use `ANALYZE FORMAT=JSON` for real execution statistics (MariaDB-specific enhancement):

```sql
ANALYZE FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;
```

Key fields in the output:
- `r_loops` -- Actual number of times the operation executed
- `r_total_time_ms` -- Actual time spent in milliseconds
- `r_rows` -- Actual rows returned (compare with `rows` estimate)
- `r_buffer_size` -- Actual buffer memory used
- `r_filtered` -- Actual filter selectivity percentage

Compare `rows` (estimated) with `r_rows` (actual) to detect stale statistics.

### Optimizer Hints (12.x+)

MariaDB 12.x introduces MySQL-compatible optimizer hints:

```sql
SELECT /*+ JOIN_INDEX(t1, idx_col1) */ * FROM t1 WHERE col1 = 1;
SELECT /*+ NO_INDEX(t1, idx_col2) */ * FROM t1 WHERE col2 > 100;
SELECT /*+ GROUP_INDEX(t1, idx_grp) */ col1, COUNT(*) FROM t1 GROUP BY col1;
```

### Index Types

| Index Type | Best For | Engine Support |
|---|---|---|
| **B-tree** | Equality, range, sorting, prefix searches | InnoDB, Aria, MyISAM |
| **Hash** | Equality lookups in MEMORY engine | MEMORY only (InnoDB uses adaptive hash internally) |
| **R-tree** | Spatial data (GEOMETRY types) | InnoDB (limited), MyISAM |
| **Full-text** | Natural language text search | InnoDB (10.0.15+), Aria, MyISAM |
| **VECTOR** | Vector similarity search (11.8+) | InnoDB |

## Common Pitfalls

1. **MySQL migration JSON incompatibility** -- MariaDB stores JSON as LONGTEXT. Applications relying on MySQL's binary JSON functions (JSON_STORAGE_SIZE, JSON_STORAGE_FREE) will break. JSON path expressions work but performance characteristics differ.

2. **GTID incompatibility** -- MariaDB and MySQL GTIDs are completely different formats. You cannot use MySQL GTID-based replication to replicate to/from MariaDB. Plan for a clean cutover.

3. **Config variable cleanup on upgrades** -- Removed variables in newer versions cause startup failures. Always review release notes before upgrading. Run `mariadbd --help --verbose 2>&1 | grep -i warning` after upgrade to find deprecated variables.

4. **Galera: Missing primary keys** -- Tables without a primary key cause performance degradation and unpredictable behavior in Galera Cluster. Always define explicit primary keys.

5. **Thread pool misconfiguration** -- Setting `thread_pool_size` too high negates the benefit. Keep it at or near CPU core count. Setting `thread_pool_stall_limit` too low causes unnecessary thread creation.

6. **Not running ANALYZE TABLE** -- The optimizer relies on index statistics. After bulk loads or significant data changes, run `ANALYZE TABLE` to update cardinality estimates. This is especially critical after upgrading to 11.4+ due to the optimizer rewrite.

7. **Large transactions in Galera** -- Transactions modifying more than ~128K rows generate large writesets that stall the entire cluster during certification. Break large operations into batches.

8. **Assuming MySQL documentation applies** -- MariaDB has diverged significantly from MySQL since 5.5. Always reference MariaDB Knowledge Base (mariadb.com/kb) rather than MySQL documentation for features introduced after the fork.

## Version-specific guidance

| Version | Status | Key Feature | Reference |
|---|---|---|---|
| **MariaDB 12.x** | Rolling release (current) | Optimizer hints, Oracle syntax, rolling model | `references/versions/12.x.md` |
| **MariaDB 11.8** | LTS (3-year, EOL ~Jun 2028) | VECTOR type, Y2038 fix, utf8mb4 default | `references/versions/11.8.md` |
| **MariaDB 11.4** | LTS (5-year, EOL Jan 2033) | Cost-based optimizer rewrite, JSON_SCHEMA_VALID | `references/versions/11.4.md` |
| **MariaDB 10.11** | LTS (EOL Feb 2028) | password_reuse_check, NATURAL_SORT_KEY, perf boost | `references/versions/10.11.md` |
| **MariaDB 10.6** | LTS (EOL Jul 2026) | Atomic DDL, JSON_TABLE, Oracle compat | `references/versions/10.6.md` |

## Reference Files

Load these when you need deep knowledge for a specific area:

- `references/architecture.md` -- Storage engines, thread pool internals, Galera architecture, MaxScale components. Read for "how does MariaDB work internally" questions.
- `references/diagnostics.md` -- Performance Schema, ANALYZE FORMAT=JSON, slow query log, Galera monitoring, diagnostic tools. Read when troubleshooting performance or cluster issues.
- `references/best-practices.md` -- InnoDB tuning, backup strategies, config cleanup discipline, binary naming transition. Read for configuration and operational guidance.