サバフェスで使った my.cnf

my.cnf

[mysqld]
server_id              = 101
user                   = mysql
port                   = 3306
bind_address           = 0.0.0.0
character_set_server   = utf8mb4
datadir                = /fioa/mysql
tmpdir                 = /fioa/tmp
socket                 = /fioa/mysql/mysql.sock
default_storage_engine = InnoDB
lower_case_table_names = 1
transaction_isolation  = READ-COMMITTED
skip_name_resolve
skip_external_locking
skip_character_set_client_handshake
explicit_defaults_for_timestamp
sort_buffer_size       = 1M
read_buffer_size       = 1M
 
## log
log_output             = TABLE
log_error              = mysql-err.log
slow_query_log         = 0
slow_query_log_file    = mysql-slow.log
general_log            = 0
general_log_file       = mysql-general.log
#long_query_time        = 1
#log_slow_admin_statements
#log_queries_not_using_indexes
performance_schema     = 0
 
## network
back_log                       = 128
max_allowed_packet             = 16M
max_connections                = 100
thread_cache_size              = 100
max_connect_errors             = 999999999
wait_timeout                   = 60
 
## innodb
innodb_data_file_path          = ibdata1:10M:autoextend
innodb_flush_method            = O_DIRECT
innodb_thread_concurrency      = 32
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout       = 5
innodb_log_file_size           = 50G
innodb_log_files_in_group      = 2
innodb_log_buffer_size         = 256M
innodb_log_compressed_pages    = 0
innodb_doublewrite             = 1
innodb_read_io_threads         = 32
innodb_write_io_threads        = 32
innodb_flush_neighbors         = 0
innodb_random_read_ahead       = 0
innodb_read_ahead_threshold    = 0
# https://blog.mariadb.org/tag/fusion-io/
innodb_io_capacity             = 75000
innodb_io_capacity_max         = 100000
innodb_lru_scan_depth          = 2500
innodb_concurrency_tickets     = 100
innodb_adaptive_flushing       = 1
innodb_support_xa              = 0
innodb_autoinc_lock_mode       = 2
innodb_file_format             = Barracuda
innodb_file_per_table          = 1
innodb_print_all_deadlocks     = 1
innodb_large_prefix            = 1

## cache
query_cache_type               = 0
query_cache_size               = 0
table_open_cache               = 8192
max_heap_table_size            = 128M
tmp_table_size                 = 64M
table_open_cache_instances     = 32
metadata_locks_hash_instances  = 256
innodb_purge_threads           = 4
innodb_purge_batch_size        = 300
 
## memory
open_files_limit                = 65535
innodb_buffer_pool_size         = 28G
innodb_buffer_pool_instances    = 8
innodb_adaptive_hash_index      = 0
innodb_buffer_pool_filename     = ../../var/lib/mysql/ib_buffer_pool
innodb_buffer_pool_load_at_startup = 1