Aria adalah storage engine default di MariaDB yang dirancang sebagai pengganti MyISAM yang lebih baik. Setelah mengalami crash recovery issues dengan MyISAM, Aria menjadi pilihan yang lebih reliable untuk read-heavy workloads.

Apa itu Aria Storage Engine?

Perbandingan Aria vs MyISAM

Kelebihan Aria

• Crash-safe: Automatic recovery dari crash
• Page format: More efficient storage
• Better caching: LRU-based page cache
• Concurrent inserts: Improved concurrency

Konfigurasi Aria

1. Enable dan Konfigurasi Aria

# /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
Aria configuration
aria_pagecache_buffer_size = 512M
aria_sort_buffer_size = 256M
aria_block_size = 8192
Recovery settings
aria_checkpoint_interval = 30
aria_group_commit = hard
aria_group_commit_interval = 1000000
Page format default
aria_default_page_format = PAGE

2. Aria System Variables

-- Cek Aria variables
SHOW VARIABLES LIKE 'aria%';
-- Key variables:
-- aria_pagecache_buffer_size: Cache untuk Aria pages
-- aria_sort_buffer_size: Buffer untuk sorting
-- aria_block_size: Block size untuk PAGE format

Membuat Tabel Aria

1. Create Table dengan Aria

-- Create table dengan Aria engine
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp DATETIME,
    level VARCHAR(10),
    message TEXT,
    INDEX idx_timestamp (timestamp)
) ENGINE=Aria
DEFAULT CHARSET=utf8mb4;
-- Create dengan PAGE row format
CREATE TABLE cache_data (
cache_key VARCHAR(255) PRIMARY KEY,
cache_value MEDIUMTEXT,
expires_at TIMESTAMP,
INDEX idx_expires (expires_at)
) ENGINE=Aria
ROW_FORMAT=PAGE
DEFAULT CHARSET=utf8mb4;

2. Row Format Options

-- PAGE format (default, recommended)
CREATE TABLE page_table (...) ENGINE=Aria ROW_FORMAT=PAGE;
-- FIXED format (untuk data dengan fixed length)
CREATE TABLE fixed_table (...) ENGINE=Aria ROW_FORMAT=FIXED;
-- DYNAMIC format (untuk variable length data)
CREATE TABLE dynamic_table (...) ENGINE=Aria ROW_FORMAT=DYNAMIC;

Use Cases untuk Aria

1. Logging Tables

-- Application logs dengan high insert rate
CREATE TABLE application_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    log_level ENUM('DEBUG', 'INFO', 'WARN', 'ERROR'),
    component VARCHAR(50),
    message TEXT,
    INDEX idx_time_level (log_time, log_level),
    INDEX idx_component (component)
) ENGINE=Aria
ROW_FORMAT=PAGE
PARTITION BY RANGE (YEAR(log_time)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

2. Temporary atau Cache Tables

-- Session cache
CREATE TABLE session_cache (
    session_id VARCHAR(64) PRIMARY KEY,
    user_id INT,
    data BLOB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    INDEX idx_expires (expires_at)
) ENGINE=Aria;
-- Regular cleanup
CREATE EVENT cleanup_expired_sessions
ON SCHEDULE EVERY 1 HOUR
DO
DELETE FROM session_cache WHERE expires_at < NOW();

3. Read-Only Reference Data

-- Lookup tables yang jarang berubah
CREATE TABLE country_codes (
    code CHAR(2) PRIMARY KEY,
    name VARCHAR(100),
    region VARCHAR(50),
    currency_code CHAR(3)
) ENGINE=Aria;
-- Insert reference data
INSERT INTO country_codes VALUES
('ID', 'Indonesia', 'Asia', 'IDR'),
('US', 'United States', 'Americas', 'USD'),
('SG', 'Singapore', 'Asia', 'SGD');

Monitoring Aria

1. Aria Status Variables

-- Check Aria status
SHOW STATUS LIKE 'Aria%';
-- Key metrics:
-- Aria_pagecache_reads: Number of page reads dari cache
-- Aria_pagecache_writes: Number of page writes ke cache
-- Aria_pagecache_read_requests: Total read requests
-- Aria_transaction_log_syncs: Number of log syncs

2. Hit Ratio Calculation

-- Calculate page cache hit ratio
SELECT 
    ROUND(
        (SUM(CASE WHEN VARIABLE_NAME = 'Aria_pagecache_read_requests' THEN VARIABLE_VALUE END) -
         SUM(CASE WHEN VARIABLE_NAME = 'Aria_pagecache_reads' THEN VARIABLE_VALUE END)) /
        SUM(CASE WHEN VARIABLE_NAME = 'Aria_pagecache_read_requests' THEN VARIABLE_VALUE END)
        * 100, 
        2
    ) AS aria_cache_hit_ratio
FROM information_schema.global_status
WHERE VARIABLE_NAME LIKE 'Aria%';

3. Table Statistics

-- Check Aria table sizes
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
    ROW_FORMAT
FROM information_schema.TABLES
WHERE ENGINE = 'Aria'
ORDER BY DATA_LENGTH DESC;

Maintenance Aria Tables

1. Check dan Repair

-- Check table
CHECK TABLE application_logs;
-- Repair jika corrupt
REPAIR TABLE application_logs;
-- Quick repair
REPAIR TABLE application_logs QUICK;
-- Extended repair
REPAIR TABLE application_logs EXTENDED;

2. Optimize Tables

-- Optimize Aria table
OPTIMIZE TABLE logs;
-- Atau dengan mysqlcheck command
mysqlcheck -o database_name table_name

3. Backup dan Restore

# Backup Aria table
mysqldump database_name table_name > backup.sql
Restore
mysql database_name < backup.sql

Migrasi dari MyISAM ke Aria

1. Convert Single Table

-- Convert MyISAM ke Aria
ALTER TABLE old_myisam_table ENGINE=Aria;
-- Convert dengan specific row format
ALTER TABLE old_myisam_table 
ENGINE=Aria 
ROW_FORMAT=PAGE;

2. Batch Convert Script

-- Convert semua MyISAM tables ke Aria
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=Aria;')
FROM information_schema.TABLES
WHERE ENGINE = 'MyISAM'
  AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema');
-- Jalankan output script

3. Verify Conversion

-- Verify semua tables sudah Aria
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    ENGINE
FROM information_schema.TABLES
WHERE ENGINE = 'MyISAM'
  AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema');
-- Should return empty set

Best Practices

1. Gunakan PAGE row format untuk kebanyakan use cases
2. Set adequate page cache size untuk performa
3. Regular maintenance dengan CHECK dan OPTIMIZE
4. Monitor hit ratio untuk tuning
5. Enable automatic recovery settings

Kesimpulan

Aria adalah improvement yang signifikan dari MyISAM:
– Crash recovery capability
– Better data integrity
– Page-based storage lebih efficient
– Fully compatible dengan MyISAM tools

Untuk read-heavy workloads dan temporary tables, Aria adalah pilihan yang lebih baik daripada MyISAM.