mySQL and mariaDB come stock with a few defaults, but sometimes it is better to modify them yourself to tune the system more to your usage.
Following is based on a stand alone server with 16G of memory. The system is a central repository for multiple machines, so it needs to be accessible to other machines in the network.
Note: Debian has a case of the cutes when it comes to building configuration files. The one you want is /etc/mysql/mariadb.conf.d/50-server.cnf. Be careful which group you put things in.
# comment out the bind-address if you should respond to external clients # bind-address = 127.0.0.1 # set to 64M * memory size (in Gig) tmp_table_size = 1024M # max_heap_size = 1024M # This fails on current versions # helps with repetitive queries query_cache_type = 1 query_cache_limit = 256K query_cache_min_res_unit = 2k query_cache_size = 200M # Records slow queries slow-query-log = 1 slow-query-log-file = /var/log/mysql/mysql-slow.log long_query_time = 1 # Set to 60-70% of memory available on dedicated db server # sets aside buffer for in memory storage of indicies and data cache innodb_buffer_pool_size = 10G # 62.5% # Skip reverse DNS lookup of clients skip-name-resolve # use one table per file on innodb. Allows admins to decrease # space on a per-table basis and perform other maintenance tasks innodb_file_per_table=1
Following are some suggestions I got from other places. Don't know what they were, and don't remember the source. Use with caution.
[mysqld] # force one file per table for innodb innodb_file_per_table # method to flush data. O_DIRECT available on most # linux and FreeBSD systems innodb_flush_method=O_DIRECT # maximum size of innodb log file # larger means smoother loads on file writes innodb_log_file_size=1G # size of memory set aside for caching. more is better, but # don't get so large as to interfere with system memory innodb_buffer_pool_size=1G show variables;