User Tools

Site Tools


software:mysql:tuning

mariaDB/mySQL Tuning

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;
software/mysql/tuning.txt · Last modified: 2022/11/09 23:08 by rodolico