mysql 5.6 常规优化配置

2019独角兽企业重金招聘Python工程师标准>>>

这是继续上一篇的my.cnf配置文件，只是常规的参数，里面加入了部分优化参数。硬件配置10核32G内存。优化是个大工程，后面会陆续补充。比如mysql的sync-binlog=1等mysql的配置双一会专门抽出来写。以后会从DML语句开始写，sql语句毕竟是数据库的灵魂。

client]
port    = 42116
socket  = /database/mysql/3306/mysql.sock
default-character-set=utf8

[mysql]
pager="more"
no-auto-rehash

[mysqld]
core-file
#### Baes dir ####
user    = mysql
port    = 42116
socket  = /database/mysql/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /database/mysql/3306/data
pid-file = /database/mysql/3306/mysql.pid

#### Base configure info ####

skip-name-resolve
old_passwords = 0
lower_case_table_names = 1
open_files_limit = 65535
max_allowed_packet = 24M
max_connect_errors = 50000
max_connections = 2100
max_user_connections=2000
thread_cache_size=256
table_definition_cache = 2048
thread_stack = 524288
transaction_isolation = READ-COMMITTED

#### Log info ####
slow_query_log = 1
long_query_time  = 0.1

slow_query_log_file = /database/mysql/3306/logs/slow.log
log-error = /database/mysql/3306/logs/error.log

 

#### Binary log && Relay log ####
server-id = 204
log-bin = /data/mysql/3306/logs/binlog/mysql-bin
relay-log=/data/mysql/3306/logs/relaylog/relay-bin
relay-log-index=/data/mysql/3306/logs/relaylog/relay-log.index
relay-log-info-file=/data/mysql/3306/logs/relaylog/relay-log.info
max_binlog_size = 500M
max_binlog_cache_size = 2G
binlog_format = ROW
binlog_cache_size = 2M
log_bin_trust_function_creators =1
replicate_wild_ignore_table= mysql.%
replicate_wild_ignore_table= test.%
slave-skip-errors     = all

concurrent_insert = 2
key_buffer = 256
sort_buffer_size = 100k
join_buffer_size = 100K
read_buffer_size = 1M
read_rnd_buffer_size=5M
myisam_sort_buffer_size = 100M

#innodb plugin
innodb_file_format=Barracuda
innodb_strict_mode=1
innodb_thread_concurrency = 36
#innodb
default-storage-engine = INNODB
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size= 200M
innodb_log_file_size = 256M
innodb_buffer_pool_size = 28G
innodb_max_dirty_pages_pct = 60
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_lock_wait_timeout = 100
innodb_log_files_in_group = 2
innodb_open_files=60000
innodb_support_xa=1
###InnoDB IO
innodb_write_io_threads=8
innodb_read_io_threads=8
# set 0 just for test
innodb_flush_log_at_trx_commit = 2
innodb_data_file_path = ibdata1:1G:autoextend
innodb_io_capacity = 1000

[mysqldump]
quick
max_allowed_packet = 32M

转载于:https://my.oschina.net/luoyedao/blog/506774