server.cnfのsample

MariaDB Galera Cluster の server.cnf のサンプル

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

##----------------- Daemon -----------------##

pid-file                = /var/lib/mysql/mysql.pid
socket                  = /var/lib/mysql/mysql.sock
character-set-server    = utf8
collation-server        = utf8_general_ci
datadir                 = /var/lib/mysql/

##----------------- Log -----------------##

# binlog
expire_logs_days        = 5
log_slave_updates

# slowlog
slow_query_log
slow_query_log_file     = /var/log/mysql/slow-query.log
long_query_time         = 0.5

#
general_log_file        = /var/log/mysql/mysql.log
log_error               = /var/log/mysql/mysql.log
log_queries_not_using_indexes

##----------------- Tuning -----------------##
#
# TotalMemory > TotalGlobal + (Thread * max_connections) + (500M for OS)
#
#       Global:
#         key_buffer, query_cache_size, tmp_table_size (& max_heap_table_size),
#         innodb_buffer_pool_size, innodb_additional_mem_pool_size, innodb_log_buffer_size,
#
#       Thread:
#         sort_buffer_size, read_buffer_size, read_rnd_buffer_size, join_buffer_size
#         myisam_sort_buffer_size, thread_stack, binlog_cache_size
#
#       Example:
#         Total 126G > (16M + 1G + 1G + 108G + 32M + 32M) + (4M * 1000) + 500M
#
#       See also:
#         $ mymemcheck /etc/mysql/my.cnf
#             or
#         $ mysqltuner
#

max_connections         = 900

#-- Global Memory --#
key_buffer              = 16M                    # for MyISAM only
query_cache_size        = 16M
max_heap_table_size     = 256M                   # with tmp_table_size
tmp_table_size          = 256M                   # with max_heap_table_size


#-- Thread Memory --#
sort_buffer_size        = 2M                    # high weight
read_rnd_buffer_size    = 4M                    # high weight
read_buffer_size        = 1M
join_buffer_size        = 512K
thread_stack            = 192K


#-- Limit --#
max_allowed_packet      = 1M
query_cache_limit       = 1M

#-- Other --#
thread_cache_size       = 300                  # max_connections * number of tables
net_buffer_length       = 2K
wait_timeout            = 180
##----------------- InnoDB -----------------##

# Basic
default-storage-engine          = InnoDB
#innodb_file_per_table
#innodb_flush_method             = O_DIRECT
#innodb_data_home_dir            = ibdata
#innodb_log_file_size            = 1G       # innodb_buffer_pool_size > innodb_log_file_size * innodb_log_files_in_group

# Global Memory
innodb_buffer_pool_size         = 700M
innodb_log_buffer_size          = 16M

# I/O
#innodb_io_capacity              = 400
#innodb_read_io_threads          = 8
#innodb_write_io_threads         = 8

# Other
innodb_lock_wait_timeout        = 5
innodb_file_format              = Barracuda
table_open_cache                = 900
sync_binlog                     = 1
innodb_support_xa               = 1
innodb_rollback_on_timeout
skip-innodb-doublewrite

#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_cluster_name              = DBCLUSTER
wsrep_provider                  = '/usr/lib64/galera/libgalera_smm.so'
wsrep_cluster_address           = gcomm://192.168.56.211,192.168.56.212,192.168.56.213
wsrep_node_address              = 192.168.56.211
binlog_format                   = row
default_storage_engine          = InnoDB
innodb_autoinc_lock_mode        = 2
bind-address                    = 0.0.0.0
#
# Optional setting
#wsrep_slave_threads             = 1
#innodb_flush_log_at_trx_commit  = 0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.0 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.0]