MariaDB – A Mature Open Source Database

MariaDB emerged in 2009 as a fork of MySQL to maintain an independent, fully open-source database system. Over the years, it has diverged from MySQL, offering unique storage engines and flexible configuration options, making it suitable for everything from transactional systems to analytical workloads.

Storage Engines

MariaDB offers a variety of storage engines, each optimized for different use cases:

• InnoDB – Default transactional engine with ACID compliance, row-level locking, and foreign keys.
• Aria – Crash-safe alternative to MyISAM, good for temporary or non-critical transactional tables.
• MyISAM – Legacy engine focused on fast read operations, without transactional support.
• ColumnStore – Column-oriented engine ideal for data warehouses and analytics.
• Spider – Enables distributed tables across multiple servers.
• CONNECT – Access external data sources like CSV, JSON, or ODBC.
• RocksDB / TokuDB – Optimized for high-volume write workloads.
• Memory – Stores data in RAM for extremely fast temporary access.

Configuration on Ubuntu

MariaDB configuration on Ubuntu is typically stored in /etc/mysql/mariadb.conf.d/50-server.cnf or /etc/mysql/my.cnf. Key settings can influence storage engine behavior, performance, and memory usage.

Example Configuration File (my.cnf)

[mysqld]
# General settings
user            = mysql
bind-address    = 127.0.0.1
port            = 3306
character-set-server = utf8mb4
collation-server     = utf8mb4_general_ci

# InnoDB settings
innodb_buffer_pool_size = 1G
innodb_log_file_size    = 256M
innodb_file_per_table   = 1
innodb_flush_method     = O_DIRECT

# Aria settings
aria_pagecache_buffer_size = 128M
aria_sync_log             = 1

# MyISAM settings
key_buffer_size           = 64M
myisam_recover_options    = FORCE,BACKUP

# Performance tuning
max_connections           = 200
thread_cache_size         = 50
query_cache_type          = 0
query_cache_size          = 0

# Enable specific engines
plugin-load-add=ha_columnstore.so

[mysqld]
# General settings
user            = mysql
bind-address    = 127.0.0.1
port            = 3306
character-set-server = utf8mb4
collation-server     = utf8mb4_general_ci

# InnoDB settings
innodb_buffer_pool_size = 1G
innodb_log_file_size    = 256M
innodb_file_per_table   = 1
innodb_flush_method     = O_DIRECT

# Aria settings
aria_pagecache_buffer_size = 128M
aria_sync_log             = 1

# MyISAM settings
key_buffer_size           = 64M
myisam_recover_options    = FORCE,BACKUP

# Performance tuning
max_connections           = 200
thread_cache_size         = 50
query_cache_type          = 0
query_cache_size          = 0

# Enable specific engines
plugin-load-add=ha_columnstore.so

Notes on Configuration

• InnoDB: innodb_buffer_pool_size should typically be 60–70% of available RAM for transactional workloads.
• Aria: aria_pagecache_buffer_size defines how much memory is used for Aria tables, improving performance.
• MyISAM: key_buffer_size tunes index caching for fast reads.
• Engines: Some engines like ColumnStore or Spider require loading plugins via plugin-load-add.

On Ubuntu, after editing the configuration, you can restart MariaDB to apply changes:

sudo systemctl restart mariadb
sudo systemctl status mariadb

sudo systemctl restart mariadb
sudo systemctl status mariadb

This approach allows you to optimize MariaDB for your workload, whether you’re running a transactional application, analytics queries, or a hybrid environment.