User Tools

Site Tools


software:mariadb

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
software:mariadb [2020/01/12 20:07] rodolicosoftware: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/]] 
software/mariadb.1578881238.txt.gz · Last modified: 2020/01/12 20:07 by rodolico