MySQL Tuning some knowledge

Understand some common parameters

key_buffer_size = 32M			// Buffer size of index block 
read_buffer_size = 2M      			// Read in buffer size 
read_rnd_buffer_size = 4M			//sort Read in cache used after query 
table_open_cache = 1000			// Table open cache 
table_definition_cache = 1000  		// Table definition file cache 
table_open_cache_instances = 64		// Number of table open cache instances , The default is 16
tmp_table_size = 96M		// Temporary table cache 
max_heap_table_size = 96M	// And tmp_table_size Use it together , To limit the size of the internal memory table 
thread_stack = 512K			// The default is 64KB. If the thread stack is too small , This limits what the server can handle SQL The complexity of the statement 
max_allowed_packet = 1024M		// finger mysql The maximum allowable packet size of server and client 
sort_buffer_size = 4M			// Sort cache default 256K
join_buffer_size = 2M			// Multi table query cache , Default 128K,1 It takes about 12M size 

query_cache_size = 0		//# Query cache size 
query_cache_type = 0		//# The query cache , The default value is 0, close . High concurrency , A system with a large number of writes , It is suggested to disable this function .
query_cache_min_res_unit=		# The buffer size that a single query can use , Default is 1M

external-locking = FALSE    // External locking , When opening, the server often needs to wait for unlocking when accessing the data table , Will make MySQL Performance degradation .
max_connections = 500			// The number of customers allowed at the same time , The default value is 100
max_connect_errors = 1000000   // Maximum number of unsuccessful connection attempts 
thread_cache_size = 1200			//# Number of threads in thread cache 
back_log = 1024				// Before temporarily stopping answering new requests , The number of connections that can be stored in the stack in a short time 
interactive_timeout = 600     // The number of seconds waiting for action on the interactive connection . The default value is 28800
wait_timeout = 600			// Link waiting time 

slow_query_log = 1			// Whether to enable slow query log ,1 To enable ,0 To disable 
long_query_time = 0.5		// Slow query time    Range 0-10
log-bin = /data/mysql/mybinlog
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
sync_binlog = 1
binlog_cache_size = 4M			//binlog cache , Cache used to store binary logs during transactions 
max_binlog_cache_size = 2G		// When executing multi statement transactions   all session Maximum memory used 
max_binlog_size = 1G		// The default size is 1G
expire_logs_days = 7    // Beyond the retention time binlog The log will be deleted automatically 

Example 1

[mysqld]
user = mysql
server_id = 1
port = 3306
socket = /tmp/mysql.sock
datadir = /data/mysql/data1
lower_case_table_names = 1    # Surfaces are case sensitive 
character-set-server = utf8
default-storage-engine = INNODB
log-bin = bin.log
log-error = error.log
pid-file = /tmp/mysql.pid
# Turn on slow query 
long_query_time = 2
slow_query_log = 'on'
slow_query_log_file = /data/mysql/data1/slow.log
#binlog Cache memory for logs 、 Format 、 Maximum memory used 、 Log size 、 Retention time 
binlog_cache_size = 4M
binlog_format = mixed
max_binlog_cache_size = 16M
max_binlog_size = 1G
expire_logs_days = 30
ft_min_word_len = 4  
back_log = 512    # stay MySQL How many requests can be stored in the stack in a short period of time before you temporarily stop answering new requests 
# Maximum data transmission , maximum connection 
max_allowed_packet = 64M
max_connections = 4096
max_connect_errors = 100		# Database connection aborted due to network exception 
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
query_cache_size = 64M
table_open_cache = 10000
thread_cache_size = 256
max_heap_table_size = 64M
tmp_table_size = 64M
thread_stack = 192K
thread_concurrency = 24
local-infile = 0
skip-show-database
skip-name-resolve
skip-external-locking
connect_timeout = 600
interactive_timeout = 600
wait_timeout = 600
#*** MyISAM
key_buffer_size = 512M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 1
concurrent_insert = 2
myisam_recover
#*** INNODB
innodb_buffer_pool_size = 16G
innodb_additional_mem_pool_size = 32M
innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency = 16
innodb_open_files = 1024
read-only
relay-log = relay.log
log-slave-updates
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid
replicate-do-db =all

Example 2

[mysqld]
binlog-ignore-db=mysql
binlog-ignore-db=test
#report_host=
skip-slave-start
#skip-grant-tables
#log_slave_updates
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
user    = mysql
port    = 3306
basedir = /usr/local/mysql
datadir = /data/mysql/data
socket  = /data/mysql/mysql.sock
pid-file = mysqldb.pid
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit    = 65535
back_log = 1024				// Before temporarily stopping answering new requests , The number of connections that can be stored in the stack in a short time 
max_connections = 500			// The number of customers allowed at the same time , The default value is 100
max_connect_errors = 1000000   // Maximum number of unsuccessful connection attempts 
table_open_cache = 1000			// Table open cache 
table_definition_cache = 1000  		// Table definition file cache 
table_open_cache_instances = 64		// Number of table open cache instances , The default is 16
thread_stack = 512K			// The default is 64KB. If the thread stack is too small , This limits what the server can handle SQL The complexity of the statement 
external-locking = FALSE    // External locking , When opening, the server often needs to wait for unlocking when accessing the data table , Will make MySQL Performance degradation .
max_allowed_packet = 1024M		// finger mysql The maximum allowable packet size of server and client 
sort_buffer_size = 4M			// Sort cache default 256K
join_buffer_size = 2M			// Multi table query cache , Default 128K,1 It takes about 12M size 
thread_cache_size = 1200			//# Number of threads in thread cache 
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 600     // The number of seconds waiting for action on the interactive connection . The default value is 28800
wait_timeout = 600			// Link waiting time 
tmp_table_size = 96M		// Temporary table cache 
max_heap_table_size = 96M			// Read at the same time / Write the system variable of the attribute , And tmp_table_size Use it together , To limit the size of the internal memory table 
slow_query_log = 1			// Whether to enable slow query log ,1 To enable ,0 To disable 
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
long_query_time = 0.5		// Slow query time    Range 0-10
server-id = 3306
log-bin = /data/mysql/mybinlog
sync_binlog = 1
binlog_cache_size = 4M			//binlog cache , Cache used to store binary logs during transactions 
max_binlog_cache_size = 2G		// When executing multi statement transactions   all session Maximum memory used 
max_binlog_size = 1G		// The default size is 1G
expire_logs_days = 7    // Beyond the retention time binlog The log will be deleted automatically 
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
binlog_format = row
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M			// Buffer size of index block 
read_buffer_size = 2M      			// Read in buffer size 
read_rnd_buffer_size = 4M			//sort Read in cache used after query 
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 4G

#  According to your server IOPS Ability is adjusted appropriately 
#  Generally, it matches with ordinary SSD On the plate , It can be adjusted to  10000 - 20000
#  High end configuration PCIe SSD Card's words , It can be adjusted higher , such as  50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000

innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 35
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_status_file = 1
innodb_status_output = 1
innodb_status_output_locks = 1
innodb_stats_on_metadata = 0

#performance_schema
performance_schema = 1
performance_schema_instrument = '%=on'

#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"

# Half a synchronous 
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

[mysqldump]
quick
max_allowed_packet = 1024M