MySQL5.7配置参数
MySQL5.7的参数调整配置，参考例子，某些具体参数的设置还需要根据实际来调整。port = 3306
socket = /tmp/mysql3306.sock
default_character_set = utf8mb4
lower_case_table_names= 1
port = 3306
basedir = /usr/local/mysql
datadir = /home/mysql3306/mysql3306
socket = /tmp/mysq3306.sock
pid_file = /home/mysql3306/mysql5711.pid
tmpdir = /tmp/
slave_load_tmpdir = /tmp/
# *** dns resolve *** #
skip_name_resolve = 1
external_locking
explicit_defaults_for_timestamp= true
character_set_client_handshake = FALSE
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8m2b4'
# *** connection *** #
max_connections = 512
max_connect_errors = 100000
back_log = 102
interactive_timeout = 600
wait_timeout = 600
table_definition_cache = 5000
table_open_cache = 2048
#query_cache_size = 64M
#query_cache_limit = 2M
thread_cache_size = 51
ft_min_word_len = 4
#performance_schema = OFF
##If large data transfer problems,enlarge max_allowed_packet
thread_stack = 192K
# *** global memory parameter *** #
max_allowed_packet = 16M
tmp_table_size = 256M
max_tmp_tables = 128
max_heap_table_size = 96M
#close query cache
#query_cache_type = 0
# *** session memory parameter *** #
sort_buffer_size = 4M
read_buffer_size = 4M
join_buffer_size = 4M
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 64M
# *** engine *** #
default_storage_engine = InnoDB
#ignore_builtin_innodb
#plugin_load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
# *** log *** #
log_timestamps = system
log_bin = /home/mysql3306/logs/mysql-bin
expire_logs_days = 10
binlog_format = mixed
#binlog_gtid_simple_recovery = 1
#binlog_do_db
#binlog_ignore_db
log_slave_updates=1
relay_log = /home/mysql3306/logs/mysql5711_relay_bin
relay_log_index = /home/mysql3306/logs/mysql5711_relay_bin.index
relay_log_info_file = /home/mysql3306/logs/mysql5711_relay_bin.info
relay_log_recovery = 0
log_error = /home/mysql3306/logs/mysql5711.err
slow_query_log = 1
slow_query_log_file = /home/mysql3306/logs/mysql5711_slow.log
long_query_time = 1
log_output = FILE
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
min_examined_row_limit = 100
#general_log = off
general_log_file = /home/mysql3306/logs/mysql5711_general.log
#log_queries_not_using_indexes= /mysqllog/mysql5711/mysql5711_not_useing_index.log
max_binlog_cache_size = 8M
max_binlog_size = 1024M
binlog_cache_size = 4M
sync_binlog = 1
server_id = 28703306
#read_only = 0
#slave_skip_errors = all
# *** MyISAM Specific options *** #
#key_buffer_size = 128M
#bulk_insert_buffer_size = 64M
#myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover_options = BACKUP,FORCE
# *** INNODB Specific options *** #
innodb_data_home_dir = /home/mysql3306/mysql3306/
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_log_group_home_dir = /home/mysql3306/mysql3306/
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 1024MB
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 32M#default 16M
innodb_flush_log_at_trx_commit= 1
innodb_lock_wait_timeout = 30
innodb_undo_directory = /home/mysql3306/mysql3306/
innodb_undo_logs = 128#must >=35.default 128
innodb_undo_tablespaces = 3 #must >=2
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 256M
innodb_purge_rseg_truncate_frequency = 128
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 64M
disabled_storage_engines=MyISAM
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_file_format_max=Antelope
innodb_file_format=Antelope
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
innodb_open_files = 4000
innodb_file_per_table = 1
innodb_status_file = 1
innodb_lock_wait_timeout = 30
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 200
innodb_thread_concurrency = 32
innodb_commit_concurrency = 0
#innodb_flush_neighbors = 0#for ssd
#innodb_flush_neighbors = 1#for other
innodb_purge_batch_size = 1000
innodb_flush_log_at_trx_commit= 0
innodb_flush_method = O_DIRECT
innodb_purge_threads = 4
#innodb_file_format = barracuda
innodb_strict_mode = 1
innodb_support_xa = 1
skip_innodb_adaptive_hash_index
innodb_force_recovery =0
#innodb_fast_shutdown
innodb_change_buffering = all
#innodb_use_native_aio = 1
innodb_max_dirty_pages_pct =50
##replication
#replicate_do_db
#replicate_ignore_db = mysql
#replicate_ignore_db = test
#replicate_do_table
#replicate_ignore_table
#replicate_wild_do_table
#replicate_wild_ignore_table
#gtid_mode = on
#enforce_gtid_consistency = true
#open mts
master_info_repository = TABLE
relay_log_info_repository = TABLE
sync_master_info = 1
slave-parallel-type=LOGICAL_CLOCK #default databases,group rep
slave_parallel_workers = 16
relay_log_recovery=ON
binlog_checksum = CRC32 #default crc32
master_verify_checksum = 1 #default off
slave_sql_verify_checksum = 1
#binlog_rows_query_log_events = 1 #
##semi sync replication settings
#plugin_dir=/usr/local/mysql57/lib/plugin
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#loose_rpl_semi_sync_master_enabled = 1
#loose_rpl_semi_sync_slave_enabled= 1
#loose_rpl_semi_sync_master_timeout = 10000 # 10 second
##transaction
transaction_isolation = REPEATABLE-READ
#transaction_write_set_extraction= MURMUR32 #open --single-tra no use
quick
max_allowed_packet = 16M
no_auto_rehash
default_character_set = utf8mb4
#key_buffer_size = 20M
#sort_buffer_size = 4M
#read_buffer = 4M
#write_buffer = 2M
interactive_timeout
open_files_limit = 8192
总结：参数的调整，能对RW的性能有帮助，调整不好也是个坑......http://blog.51cto.com/e/u261/themes/default/images/spacer.gif
页:
[1]