MariaDB Performance Tuning on Linux Servers
- AuthorBen H.
- Date
- Time17 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 mariadbStep 3: Analyze Slow Queries
bash
sudo apt install -y percona-toolkit
sudo pt-query-digest /var/log/mysql/slow.log | lessStep 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
sudo systemctl restart mariadbStep 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 mariadbStep 7: Validate with mysqltuner
bash
sudo apt install -y mysqltuner
mysqltunerStep 8: Watch for Regression
bash
mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"
mysql -e "SHOW ENGINE INNODB STATUS\\G" | head -n 80"The fastest database tweak is often a better index, not a more aggressive global setting."
