Percona MySQL 5.7.x my.cnf 2core / 8G

my.cnf

[client]
default-character-set              = utf8mb4
no-auto-rehash                               # faster cli interaction but no autocomplete
show-warnings                                # show warnings when it happens
prompt=\u@\h:\d\_\R:\m:\\s>                  # make cli prompt better ex) root@localhost:(profile) 17:08:55>
pager="less -n -i -F -X -E"                  # using less for long result rows

[mysqld]
# default
character-set-server               = utf8mb4
collation-server                   = utf8mb4_unicode_ci

validate_password_policy           = LOW    
event-scheduler                    = OFF     # no cron in mysql, nobody use it
sysdate-is-now                               # make sysdate() = now()
skip-name-resolve


# global
query_cache_type                   = OFF     # highly depends on db usage style
query_cache_size                   = 0       # should be zero if off the query cache

long_query_time                    = 5
max_connections                    = 512
performance_schema                 = ON
max_allowed_packet                 = 1000000000
net_buffer_length                  = 1000000

tmp_table_size                     = 32M
max_heap_table_size                = 32M

wait_timeout                       = 600


# per conn
join_buffer_size                   = 512K
sort_buffer_size                   = 2M
thread_pool_size                   = 32


# innodb
# possibly lost data for 2-5sec when db/os crash happened
innodb_buffer_pool_size            = 4G      
innodb_buffer_pool_instances       = 4       # +1 per innodb_buffer_pool_size 1G

innodb_io_capacity                 = 400     # 100 for HDD, 200(default) for low-end SSD, 1000 for high-end SSD

innodb_log_buffer_size             = 8M      # recommanded 1-8MB, default 1MB
innodb_log_file_size               = 2G      # innodb buffer pool > 10G = 2-4GB, otherwise <=2G
innodb_flush_log_at_timeout        = 2       # ext4fs + `sync_binlog=0` => commit disk every 5sec, so set this half of that.
innodb_flush_log_at_trx_commit     = 0       # written after each transaction commit and flushed to disk once per second, Transactions for which logs have not been flushed can be lost in a crash.  @See http://mblogthumb3.phinf.naver.net/20160905_22/parkjy76_1473004444126e1Vtk_PNG/trx.png?type=w2
innodb-flush-method                = ALL_O_DIRECT # Percona MySQL only, Note: When using this option on ext4 filesystem variable innodb_log_write_ahead_size should be set to 4096 (default log-block-size in ext4)
innodb_log_write_ahead_size        = 4K

innodb_write_io_threads            = 4
innodb_read_io_threads             = 4

innodb-file-per-table              = ON
innodb_temp_data_file_path         = ibtmp1:12M:autoextend:max:10G    # https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html

gtid_mode                          = ON
enforce_gtid_consistency

federated

# binlog
sync_binlog                        = 0       # matter on this machine's disk i/o, not to slave relay or delay, and ext4 fs will sync every 5s [needs a citation]

# slave only
super_read_only                    = 1

# replication only
# recovery replication temporary
# slave-skip-errors = 1062,1032,1007,1396
slave-skip-errors = 1286,1396,1677


# etc
# remove ONLY_FULL_GROUP_BY
# @See https://stackoverflow.com/questions/23921117/disable-only-full-group-by
sql_mode                           = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION