Differences

This shows you the differences between two versions of the page.

--- software:mariadb [2020/01/12 20:07] – rodolico
+++ software:mariadb [2022/11/09 23:08] (current) – removed rodolico
@@ Line 1: / Line 1: @@
-====== 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/]]