Appendix B: Sample MySQL file
1. My.cnf

Appendix B: Sample MySQL file

This section contains a sample MySQL file for my.cnf.

My.cnf

# MySQL Server Instance Configuration File

# ---------------------------------------------------------------------

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

# for NIAS 1.2.x use utf8, for NIAS 1.4 use utf8mb4

#character-set-server = utf8

#collation-server = utf8_general_ci

character-set-server = utf8mb4

collation-server = utf8mb4_general_ci

# ---------------------------------------------------------------------

# Settings to change based on server configuration

# Server Id, random number for each server to support future replication setup server-id = 14239084

# TCP port for MySQL to listen on port = 3306

# The path to the MySQL server data directory. Ideally, a RAID array or bus attached SAN.

datadir = /var/lib/mysql

# The directory used for temporary files and temporary tables. Ideally, a RAID array or bus attached SAN.

tmpdir = /tmp

# Maximum number of simultaneous connections. Adjust based on the number of agents. Adjust the NIAS EndpointServer DatabaseConnectionPoolSize to be less than this number. Keep in mind that the RestAPI, internal web server, and background services also require database connections.

# Number of Endpoints, suggested max_connections, DatabaseConnectionPoolSize

# 1-500 150 100

# 500-2000 500 400

# 2000-5000 1000 800

# 5000+ 2000 1500

max_connections = 350

# NOTE: These suggested values may change depending on the NIAS version, server hardware,# and expected database workload event_scheduler=ON

# --------------------------------------------------------------------

# Settings to change based on server hardware

# InnoDB buffer pool memory that can be used. It should be set to around 80% of physical memory.

innodb_buffer_pool_size = 3G

# Number of instances the buffer pool should be divided into. This reduces lock contention on the buffer pool and should be set as high as possible. Max value is 64. If MySQL fails to start, you may need to reduce this value.

innodb_buffer_pool_instances = 16

# The size in bytes of each log file in a log group. Larger values are better so the server can handle peaks in workload without having to flush to disk. Total log file size is the result of this value

* innodb_log_files_in_group and cannot exceed 512GB. innodb_log_files_in_group is set in the do not change section below and should be set to 2 in almost all configurations.

innodb_log_file_size = 4G

# InnoDB Disk I/O settings adjust based on the type of storage device used for the datadir.

# single spinning disk.

#innodb_io_capacity = 100

#innodb_io_capacity_max = 200

#innodb_lru_scan_depth = 1024

#innodb_flush_neighbors = 1

#innodb_read_io_threads = 4

#innodb_write_io_threads = 4

# single low-end SSD.

#innodb_io_capacity = 200

#innodb_io_capacity_max = 400

#innodb_lru_scan_depth = 1024

#innodb_flush_neighbors = 0

#innodb_read_io_threads = 4

#innodb_write_io_threads = 4

# multiple spinning disks in RAID.

#innodb_io_capacity = 500

#innodb_io_capacity_max = 1000

#innodb_lru_scan_depth = 1024

#innodb_flush_neighbors = 1

#innodb_read_io_threads = 4

#innodb_write_io_threads = 8

# bus attached SSD or SAN.

innodb_io_capacity = 1000

innodb_io_capacity_max = 2500

innodb_lru_scan_depth = 1024

innodb_flush_neighbors = 1

innodb_read_io_threads = 4

innodb_write_io_threads = 8

# bus attached high-end SAN.

#innodb_io_capacity = 4000

#innodb_io_capacity_max = 6000

#innodb_lru_scan_depth = 2048

#innodb_flush_neighbors = 1

#innodb_read_io_threads = 8

#innodb_write_io_threads = 16

# ---------------------------------------------------------------------

# Settings that should generally not be changed

# Set time to GMT default_time_zone = '+00:00'

default-storage-engine = INNODB

sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# Connection settings

max_connect_errors = 1000000

max_allowed_packet = 64M

# Enable binary logging

log_bin = 1

expire_logs_days = 14

sync_binlog = 1

binlog_format = ROW

transaction-isolation = READ-COMMITTED

# log errors and queries that don't hit indexes

log_error = "NIAS.err"

log_queries_not_using_indexes = 1

# log queries that take longer than 10 seconds

slow-query-log = 1

slow_query_log_file = "NIASSLOW.log"

long_query_time = 10

# Query cache is a deprecated feature, set to zero to disable

query_cache_type = 0

query_cache_size = 0

# Various important Cache and limit settings

table_definition_cache = 4096

table_open_cache = 4096

open_files_limit = 65535

tmp_table_size = 32M

max_heap_table_size = 32M

thread_cache_size = 20

# These are the default values. These can be adjusted up if there are performance issues with joins/sorts that cannot be solved through changing the query or indexing do not increase these too much as they are allocated per-connection

join_buffer_size = 256K

sort_buffer_size = 256K

# MyISAM specific settings (NIAS does not use MyISAM tables)

myisam_max_sort_file_size = 1G

key_buffer_size = 16M

read_buffer_size = 32K

read_rnd_buffer_size = 32K

# InnoDB specific settings

innodb_purge_threads = 4

innodb_log_files_in_group = 2

innodb_flush_log_at_trx_commit = 1

innodb_file_per_table = 1

innodb_log_buffer_size = 32M

innodb_thread_concurrency = 0

innodb_concurrency_tickets =5000

innodb_sort_buffer_size =4M

innodb_open_files = 4096

innodb_stats_on_metadata = 0

innodb_checksum_algorithm = 0

innodb_autoinc_lock_mode = 2

innodb_log_compressed_pages = 0

innodb_adaptive_hash_index_parts = 32

# ---------------------------------------------------------------------

lower_case_table_names = 1

‎