Database

MariaDB Performance Tuning on Linux Servers

Author :Liam K.
Date :March 08, 2026
Time :17 minutes

Step 1: Gather Baseline Metrics

bash

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Queries';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Step 2: Enable Slow Query Log

bash

sudo tee /etc/mysql/mariadb.conf.d/60-slow-query.cnf >/dev/null <<'EOF'
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
EOF
sudo systemctl restart mariadb

Step 3: Analyze Slow Queries

bash

sudo apt install -y percona-toolkit
sudo pt-query-digest /var/log/mysql/slow.log | less

Step 4: Add Missing Indexes

Review top slow statements and add indexes where full scans are avoidable.

sql

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;
ALTER TABLE orders ADD INDEX idx_orders_customer_created (customer_id, created_at);

Step 5: Tune InnoDB Memory

For dedicated DB servers, set buffer pool to roughly 60-70% of RAM.

bash

sudo tee /etc/mysql/mariadb.conf.d/61-innodb-tuning.cnf >/dev/null <<'EOF'
[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
EOF
[...]

Command truncated. Copy to view full command.

Step 6: Set Connection Limits Safely

bash

sudo tee /etc/mysql/mariadb.conf.d/62-connections.cnf >/dev/null <<'EOF'
[mysqld]
max_connections = 200
thread_cache_size = 100
table_open_cache = 4000
EOF
sudo systemctl restart mariadb

Step 7: Validate with mysqltuner

bash

sudo apt install -y mysqltuner
mysqltuner

Step 8: Watch for Regression

bash

mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"
mysql -e "SHOW ENGINE INNODB STATUSG" | head -n 80

"The fastest database tweak is often a better index, not a more aggressive global setting."

Technical Author

Liam K.

System administrator and technical writer specializing in server infrastructure, security and deployment. Creating comprehensive guides to help you master server administration.