mysql 1032 优化_Mysql配置优化

MySQL5.7, 48核128G

# Percona 5.7

# mkdir /soft/mysql/data/{tmp,log,binlog,innodb,redo}

# chown -R mysql.mysql /soft/mysql/data

# The MySQL Client

[client]

port = 3306

socket = /soft/mysql/data/tmp/mysql.sock

# The MySQL Server

########################## Basic Setting #######################################

[mysqld]

bind_address = 172.19.1.3

server-id = 133306

port = 3306

user = mysql

basedir = /soft/mysql/

datadir = /soft/mysql/data

tmpdir = /soft/mysql/data/tmp

socket = /soft/mysql/data/tmp/mysql.sock

pid-file = /soft/mysql/data/tmp/mysql.pid

secure_file_priv = '/tmp'

########################## Control Engines #######################################

#skip-myisam

#skip-innodb

########################## Feather Setting #######################################

#explicit_defaults_for_timestamp

skip-external-locking

skip-name-resolve

auto_increment_increment = 1

auto_increment_offset = 1

lower_case_table_names = 1

log_bin_trust_function_creators = 1

#log_warnings = 0

log_error_verbosity = 2

default-storage-engine = INNODB

wait_timeout = 28800

connect_timeout = 20

interactive_timeout = 28800

net_read_timeout = 100

back_log = 512

sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

log_timestamps = SYSTEM

#default_password_lifetime = 360

#max_execution_time = 1

#event_scheduler = ON

########################## Query Log Setting #######################################

general_log = 0

general_log_file = /soft/mysql/data/log/query.boss.log

########################## Binlog Setting #######################################

log-bin = /soft/mysql/data/binlog

binlog_format = ROW

max_binlog_size = 1G

binlog_cache_size = 128M

expire-logs-days = 7

sync_binlog = 1

#binlog_row_image = minimal

binlog_rows_query_log_events = ON

########################## Relaylog Setting #######################################

relay-log = /soft/mysql/data/relaylog

relay_log_purge = 1

sync_master_info = 0

sync_relay_log = 0

sync_relay_log_info = 0

relay_log_recovery = 1

########################## Replication #######################################

# master-host =

# master-user =

# master-password =

# master-port =

# skip-slave-start

read_only = 1

log_slave_updates = 1

slave-net-timeout = 10

slave_exec_mode = IDEMPOTENT

slave_compressed_protocol = ON

master_info_repository = TABLE

relay_log_info_repository = TABLE

#rpl_semi_sync_master_enabled = 1

#rpl_semi_sync_master_wait_no_slave = 1

#rpl_semi_sync_master_timeout = 1000

#rpl_semi_sync_slave_enabled = 1

#rpl_semi_sync_master_wait_point = AFTER_SYNC

#slave_parallel_type = LOGICAL_CLOCK

#slave_parallel_workers = 4

#slave_preserve_commit_order = 1

# skip-slave-start

# slave-skip-errors = 1032,1062,126,1114,1146,1048,1396,1050,1205

# replicate_do_db = boss

# replicate_wild_do_table = boss.%

# replicate_rewrite_db = boss->boss_alias

# replicate_wild_do_table = boss_alias.%

########################## Slow Log #######################################

slow_query_log = 1

#log_queries_not_using_indexes = 1

#log-short-format = 1

#log_slow_admin_statements = 1

slow-query_log_file = /soft/mysql/data/log/slowsql.boss.log

long_query_time = 1

########################## Error Log #######################################

log_error = /soft/mysql/data/log/error.boss.log

########################## Per_thread_buffers #######################################

max_connections = 3000

max_user_connections = 1200

max_connect_errors = 10000

query_cache_type = 0

query_cache_size = 256M

query_cache_limit = 512K

table_open_cache = 2048

table_definition_cache = 4096

sort_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

join_buffer_size = 8M

max_heap_table_size = 1024M

max_allowed_packet = 512M

max_join_size = 100M

tmp_table_size = 512M

bulk_insert_buffer_size = 64M

thread_cache_size = 256

thread_stack = 256K

########################## MyISAM Setting #######################################

low_priority_updates = 1

delay_key_write = ON

concurrent_insert = 1

key_buffer_size = 32M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 512M

myisam_repair_threads = 1

myisam-recover-options = backup,force

########################## Innodb Setting #######################################

#innodb_read_only = 1

innodb_fast_shutdown = 1

#innodb_force_recovery = 1

innodb_data_home_dir = /soft/mysql/data/innodb

innodb_log_group_home_dir = /soft/mysql/data/redo

innodb_data_file_path = ibdata_boss:512M:autoextend

#innodb_undo_directory = /soft/mysql/undo/boss

#innodb_undo_logs = 128

#innodb_undo_tablespaces = 4

innodb_undo_log_truncate = 1

innodb_max_undo_log_size = 1Gsoft/my

innodb_purge_rseg_truncate_frequency = 128

innodb_buffer_pool_size = 40G

innodb_buffer_pool_instances = 8

innodb_log_file_size = 256M

innodb_log_buffer_size = 64M

innodb_log_files_in_group = 3

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

innodb_sync_spin_loops = 40

innodb_max_dirty_pages_pct = 90

innodb_support_xa = 1

innodb_thread_concurrency = 0

innodb_thread_sleep_delay = 500

innodb_concurrency_tickets = 1000

innodb_flush_method = ALL_O_DIRECT

innodb_file_per_table = 1

innodb-read-io-threads = 8

innodb-write-io-threads = 8

innodb_io_capacity = 4000

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_purge_threads = 1

innodb_purge_batch_size = 32

innodb_old_blocks_pct = 75

innodb_change_buffering = ALL

innodb_open_files = 40960

innodb_doublewrite = OFF

performance_schema = 0

transaction_isolation = REPEATABLE-READ

innodb_flush_neighbors = 1

# thread pool

thread_handling = pool-of-threads

thread_pool_max_threads = 300

thread_pool_size = 64

# Strict_Mode

innodb_strict_mode = 1

# Default On

innodb_stats_on_metadata = OFF

# Dump Hot Data

innodb_buffer_pool_dump_at_shutdown = 1

innodb_buffer_pool_load_at_startup = 1

# Hand Dump Hot Data

innodb_buffer_pool_dump_now = 1

innodb_buffer_pool_load_now = 1

# Hash Index Default On

#innodb_adaptive_hash_index = ON

skip-innodb-adaptive-hash-index

# NUMA

#innodb_buffer_pool_populate = 1 5.7

#innodb_numa_interleave = OFF

# Kill Idle Transaction

innodb_kill_idle_transaction = 30

########################## Charset Setting #######################################

#character_set_client = utf8mb4

#character-set-client-handshake = utf8mb4

character_set_server = utf8mb4

########################## Otter Setting #######################################

[mysqldump]

quick

max_allowed_packet = 128M

myisam_max_sort_file_size = 10G

[mysql]

auto-rehash

prompt = (\\u@\\h) [\\d]>\\_

secure-auth = off

[myisamchk]

key_buffer_size = 64M

sort_buffer_size = 64M

read_buffer = 256M

write_buffer = 256M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 40960

log-error = /soft/mysql/data/log/error.safe.boss.log

#malloc-lib = /usr/lib64/libtcmalloc_minimal.so