# General Server Settings
[mysqld]
max_allowed_packet=256M # Reduce maximum packet size to save memory
innodb_large_prefix=ON # Support for large index key prefixes
innodb_file_format=barracuda # Use the Barracuda file format for InnoDB tables
innodb_file_per_table=ON # Store each InnoDB table in its own file
character-set-server=utf8 # Set the default character set to utf8
collation-server=utf8_unicode_ci # Use utf8 Unicode collation
# InnoDB Settings for Limited Memory
innodb_buffer_pool_size = 4G # Set buffer pool to 50% of RAM to balance cache size and memory usage
innodb_buffer_pool_instances = 4 # Reduce number of instances due to lower RAM
innodb_log_file_size = 512M # Reduce log file size to balance memory and disk usage
innodb_flush_log_at_trx_commit = 2 # Less strict ACID compliance to improve write performance
innodb_flush_method = O_DIRECT # Use O_DIRECT to avoid double buffering in InnoDB and OS cache
innodb_io_capacity = 500 # Lower capacity due to reduced I/O throughput on smaller systems
# Query Cache Configuration (optional but recommended for smaller servers)
query_cache_size = 64M # Small cache to speed up repeated queries
query_cache_type = 1 # Enable Query Cache for read-heavy workloads
# Connection and Thread Settings
max_connections = 200 # Set maximum connections for limited RAM capacity
thread_cache_size = 50 # Reduce thread cache size for smaller RAM
table_open_cache = 1000 # Reduce table cache to save memory
# Log and Timeout Settings
slow_query_log = 1 # Enable slow query log
slow_query_log_file = /var/log/mysql/mariadb-slow.log # Log file for slow queries
long_query_time = 1 # Time threshold for slow queries in seconds
log_queries_not_using_indexes = 0 # Disable logging of queries that do not use indexes
innodb_lock_wait_timeout = 60 # Reduce lock wait timeout to prevent long waits
# Other Performance Settings
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # Set SQL mode for strict error handling
default_storage_engine = InnoDB # Use InnoDB as the default storage engine
thread_stack = 256K # Set the stack size for each thread to save memory
# Service-Specific Settings (for systemd managed MariaDB)
[Service]
LimitMEMLOCK=infinity # Allow MariaDB to lock all memory, preventing paging
LimitNOFILE=524288 # Increase the file descriptor limit for handling large tables and connections
TimeoutStartSec=600 # Increase startup timeout for MariaDB under high load
ExecStartPre=/bin/sync # Ensure filesystem cache is flushed before starting MariaDB
ExecStartPre=/sbin/sysctl -q -w vm.drop_caches=3 # Drop filesystem caches to reduce initial I/O load |
