데이터베이스 튜닝

mariadb.cnf

[client]
port                            = 3306
socket                          = /data/mysql/mysql.sock

[mysqld]
port                            = 3306
socket                          = /data/mysql/mysql.sock

## config server and data path
datadir                         = /data/mysql/mysql-data
tmpdir                          = /home/mysql/tmpdir
innodb_data_home_dir           = /data/mysql/mysql-data
innodb_log_group_home_dir       = /home/mysql/log


# Log Config
binlog_format                   = mixed
expire_logs_days                = 7
long_query_time                 = 10
max_binlog_size                 = 1G
sync_binlog                     = 1
slow_query_log                  = 1
log-bin                         = /home/mysql/log/binary/mysql-bin
log-error                       = /home/mysql/log/error/mysql.err
relay-log                       = /home/mysql/log/relay/relay-log
slow_query_log_file             = /home/mysql/log/mysql-slow-query.log
general_log_file                = /home/mysql/log/general/mysql_general.log
log-warnings                    = 2

# Character set Config (utf8mb4)
character_set-client-handshake  = FALSE
character-set-server            = utf8mb4
collation_server                = utf8mb4_general_ci
init_connect                    = set collation_connection=utf8mb4_general_ci
init_connect                    = set names utf8mb4

# Common Config
back_log                        = 1024
binlog_cache_size               = 1M
ft_min_word_len                 = 4
interactive_timeout             = 600
join_buffer_size                = 2M
max_allowed_packet              = 1G
max_connections                 = 8196
max_heap_table_size             = 4096M
max_length_for_sort_data        = 1024
open_files_limit                = 8192
performance_schema
read_buffer_size                = 1M
read_rnd_buffer_size            = 8M
skip_external_locking
skip-name-resolve               = 1
sort_buffer_size                = 1M
key_buffer_size                 = 8388608
table_open_cache                = 10240
tmp_table_size                  = 4096M
transaction_isolation           = READ-COMMITTED
slave_skip_errors               = all

# Query Cache Disable
query_cache_type                = 0
query_cache_size                = 0

# Innodb config
innodb_additional_mem_pool_size = 32M
innodb_autoinc_lock_mode        = 1
innodb_buffer_pool_size         = 3G # 현재 서버 사양의 70~80%를 설정한다.
innodb_fast_shutdown            = 1
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 2
innodb_flush_method=ALL_O_DIRECT
innodb_lock_wait_timeout        = 72000
innodb_log_buffer_size          = 64M
innodb_log_file_size            = 512M
innodb_log_files_in_group       = 8
innodb_mirrored_log_groups      = 1
innodb_open_files               = 8192
innodb_read_io_threads          = 8
innodb_thread_concurrency       = 0
innodb_thread_sleep_delay       = 0
innodb_write_io_threads         = 8

#Thread Pool
thread_handling=pool-of-threads
thread_pool_idle_timeout        = 120
thread_pool_stall_limit         = 60

# Replication related settings
log_bin_trust_function_creators = 1

server-id                       = 1 

[mysqldump]
quick
max_allowed_packet              = 512M

my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

###########################################

# generic configuration options
#max_connections = 500

# The maximum amount of concurrent sessions
thread_cache_size = 128

# 16K is the maximum for this item.
table_open_cache = 640

innodb_buffer_pool_size = 2G
# innodb_additional_mem_pool_size = 2M
# innodb_log_file_size = 256M
# innodb_log_buffer_size = 32M
# key_buffer_size = 256M
# query_cache_size = 1024M
# thread_concurrency = 16
# sort_buffer_size = 4M
# read_buffer_size = 8M
# read_rnd_buffer_size = 16M
# myisam_sort_buffer_size = 64M
# join_buffer_size = 128M
# tmp_table_size = 80M
# max_heap_table_size = 80M
max_allowed_packet = 512M
group_concat_max_len = 50240
innodb_lock_wait_timeout = 30
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

###########################################

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid