====== 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.

<code bash>
# 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

</code>

Following are some suggestions I got from other places. Don't know what they were, and don't remember the source. Use with caution.

<code bash>
[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;
</code>

===== Links =====
  * [[https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/]]