# /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

-- 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

-- 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;

-- 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;

-- 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
);

-- 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();

-- 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');

-- 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

-- 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%';

-- 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;

-- 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;

-- Optimize Aria table
OPTIMIZE TABLE logs;

-- Atau dengan mysqlcheck command
mysqlcheck -o database_name table_name

# Backup Aria table
mysqldump database_name table_name > backup.sql

# Restore
mysql database_name < backup.sql

-- 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;

-- 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

-- 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