MariaDB10.x系の設定サンプル

MariaDB10.x系での設定サンプルです。
いくつかの数字はサーバー環境に応じて編集してください。
16コアCPU、128GBメモリ、io-Memory搭載のサーバーを想定して作っています。(現実的でないですねw)

[mysqld]
#Charset
character-set-server=utf8

#Storage
default_storage_engine=InnoDB
innodb_file_format=Barracuda
innodb_data_file_path=ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:10M:autoextend
innodb_data_home_dir=/var/lib/mysql
innodb_file_per_table
datadir=/var/lib/mysql
port=3306
socket=/var/lib/mysql/mysql.sock
skip_external_locking
max_allowed_packet=16M
table_open_cache=256

#Connection&Timeout
max_connections=1024
max_connect_errors=100000
wait_timeout=120
interactive_timeout=120
innodb_lock_wait_timeout=120

#Buffers-Thread
key_buffer=32M
sort_buffer_size=3M
read_buffer_size=1M
read_rnd_buffer_size=1M
join_buffer_size=1M

#Buffers-Global
innodb_buffer_pool_instances=16
innodb_buffer_pool_size=96G
innodb_log_buffer_size=16M
innodb_additional_mem_pool_size=64M

#Thread-Pool
thread_handling=pool-of-threads
thread_pool_size=16
thread_pool_max_threads=500
thread_pool_idle_timeout=300
innodb_thread_concurrency=0
thread_stack=240K
thread_cache_size=1024

#Flushing
innodb_adaptive_flushing=1
innodb_max_dirty_pages_pct=90
innodb_flush_neighbors=0
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
innodb_doublewrite=0

#Query-Cache
query_cache_type=1
query_cache_size=512M
query_cache_limit=16M
query_cache_min_res_unit=1M

#IO
innodb_write_io_threads=16
innodb_read_io_threads=16
innodb_io_capacity=40000
innodb_io_capacity_max=50000

#Log
innodb_log_file_size=1G
innodb_log_files_in_group=2
log_bin=mysql-bin
binlog_cache_size=2M
max_binlog_size=256M
expire_logs_days=4
sync_binlog=0
binlog_format=ROW

#Temporary-Table
tmp_table_size=64M
max_heap_table_size=64M

#Replication
server-id=101

skip-name-resolve

transaction-isolation=READ-COMMITTED

plugin-load=handlersocket.so

slow_query_log=0
log-output=FILE
slow_query_log_file=/var/log/mysqld_slow.log
long_query_time=0.05

[mysqldump]
quick
max_allowed_packet=16M

関連記事

コメントは利用できません。

ページ上部へ戻る