MariaDB configuration can make a huge difference in ERPNext performance. The default settings are conservative. This guide shows you how to tune MariaDB for a production ERPNext instance.

STEP 1 Find Your Config File

# Ubuntu/Debian
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# macOS (Homebrew)
sudo nano /opt/homebrew/etc/my.cnf

STEP 2 Required ERPNext Settings

These are mandatory for ERPNext to function correctly:

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb-file-format = barracuda
innodb-file-per-table = 1
innodb-large-prefix = 1

[mysql]
default-character-set = utf8mb4

STEP 3 Performance Tuning

Add these settings based on your server RAM. Below is for a server with 4GB RAM:

[mysqld]
# InnoDB Buffer Pool - set to 50-70% of available RAM
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2

# Log file size - larger means better write performance
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M

# Flush method
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

# Connection settings
max_connections = 200
wait_timeout = 600
interactive_timeout = 600

# Query cache (disable for MariaDB 10.6+)
query_cache_type = 0

# Temp table size
tmp_table_size = 64M
max_heap_table_size = 64M

# Sort and join buffers
sort_buffer_size = 4M
join_buffer_size = 4M
read_rnd_buffer_size = 4M

STEP 4 Apply Changes

sudo systemctl restart mariadb

STEP 5 Verify Settings

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'character_set_server';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'collation_server';"

After tuning, you should notice faster page loads, quicker report generation, and better handling of concurrent users in ERPNext.