mysql5.7性能调优my.ini

服务器运行内存128G 下面贴的有的路径可能需要改一下 大家需要注意

[client]
port                                        = 3306
 
[mysql]

default-character-set=utf8

[mysqld]

skip-grant-tables

port                                        = 3306
#datadir                                     = "D:/Program Files/MySQL/MySQL Server 5.7/Data"
datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data

#################################################
#------------------GTID主从---------------------#
 
server-id=1
 
  
#################################################
#------------------binlog-----------------------#
log_bin                     = mysql3306-binlog
 
binlog_format                               = ROW
expire_logs_days                = 30
binlog_checksum                             = NONE
#二进制日志格式，OLTP为row，DSS系统为mixed，如不确定，使用row
#保留30天=内的binlog历史日志
#禁用二进制日志时间校验和
 
sync_binlog                                 = 1
#控制binlog_cache中的所有binlog什么时候刷新进磁盘，1最安全，每一次事务提交均写；0最不安全，由文件系统自行控制；N表示每N次事务后刷新进磁盘，如果IO压力大可以设置为500或1000
 
binlog_row_image                            = full
#full（默认），记录所有前镜像(修改前)、后镜像（修改后）的内容，最安全；
#MINIMAL前镜像只记录唯一识别列(唯一索引列、主键列)，后镜像只记录修改列。
 
#max_binlog_cache_size = 100M
#max_binlog_size = 500M
#binlog_cache_size = 8M
  
#################################################
#--------------------log------------------------#
innodb-print-all-deadlocks                  = true
log-error                   = "WIN-3IB0R160S9G.err"
#开启死锁日志计入到error_log中
#设置错误日志
  
slow_query_log                  = on
slow_query_log_file             = "WIN-9I446C6MO8P-slow.log"
#开启慢日志
 
long_query_time                 = 1
log_queries_not_using_indexes           = 1
log-output                                  = FILE
#记录超过1s的慢查询
#记录没有索引的查询
#指定慢查询日志的输出方式，日志文件(FILE),数据库表(TABLE，mysql.slow_log)
  
log-slow-admin-statements           = 1
 #记录执行缓慢的管理SQL，如alter table,analyze table, check table, create index, drop index, optimize table, repair table等
  
#log_slow_filter
#指定超过阈值的查询类型计入慢日志，如full_scan，filesort等
 
#log_slow_verbosity
#设置慢查询日志中，需要增加的额外信息，可选值query_plan
 
#################################################
#-------------------global variables------------#
 
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#5.7后的兼容配置
 
master_info_repository                      = TABLE
#复制元数据存储在系统表
 
#relay_log_info_repository                   = TABLE
#relay_log中继日志信息写入到表,避免因relay.info更新不及时导致主从复制出错
 
autocommit                                  = ON
#自动提交事物
 
##Skip Options
skip-name-resolve
skip_external_locking
skip-symbolic-links
#开启域名解析
#跳过外部锁定，当多台数据库使用同一数据库目录时需要注释掉该项                    
#不对表使用软连接
 
max_connections                             = 3000
max_connect_errors                          = 20000
#最大连接数
#MySQL同一IP网络连接错误最大值，到达该值时将禁止连接，可防止SYN泛滥攻击
 
 
wait_timeout                                = 172800
#非交互式连接（jdbc等）空闲连接最大等待时间
interactive_timeout                         = 172800
#交互式（mysql客户端）空闲时长，注意variable出来的是基于这个参数的，因此实际wait_timeout是需要看配置文件确定的
 
net_read_timeout                            = 3600
net_write_timeout                           = 3600
net_buffer_length                           = 16384
#数据读取超时时间
#数据库写超时时间
#mysqldump时提高insert效率，默认1M，最大16M，数据量大时可减少insert次数提高效率
 
table_open_cache                            = 1024
table_definition_cache                      = 1024
#指定表高速缓存的大小
#表定义文件.frm缓存大小，最大2000
 
 
thread_stack                                = 192K
thread_cache_size                           = 200
#每个连接线程被创建时，MySQL给它分配的内存大小
#线程池缓存大小（当客户端断开连接后 将当前线程缓存起来 当在接到新的连接请求时快速响应 无需创建新的线程）优化参照2G  —> 16；3G  —> 32；>3G  —> 64，一般不超过200
 
 
open_files_limit                                = 65535
#mysqld进程能使用的最大文件描述符数量
 
 
##Default Settings
default-time-zone                           = system
default-storage-engine                      = InnoDB
explicit_defaults_for_timestamp             = 1
lower_case_table_names                      = 1
 
#character-set-server                            = utf8
#服务器安装时指定的默认编码格式，这个变量建议由系统自己管理，不要人为定义
#collation-server                    = utf8_general_ci
#指定默认字符集
 
 
performance_schema                              = 1
#收集数据库服务器性能参数
 
memlock                         = true
#锁定内存，不使用swap空间
 
#transaction-isolation                          = READ-COMMITTED
#设置事务隔离级别，READ-COMMITTED：读已提交 REPEATABLE-READ：可重复读
 
#large-pages
#支持大页内存模式，内存压力大时考虑开启，否则不建议设置
 
event_scheduler                 = OFF
#关闭定时调度器
 
query_cache_type                            = 0
query_cache_size                            = 0
query_cache_limit                           = 0
query_cache_min_res_unit                    = 0
#关闭查询缓存
 
 
#########################################
#----------------MGR--------------------#
 
#transaction_write_set_extraction            = XXHASH64
#在server收集写集合的同时将其记录到二进制日志。写集合基于每行的主键，并且是行更改后的唯一标识此标识将用于分布式冲突检测和处理。
 
#loose-group_replication_group_name          = 'ce9be252-2b71-11e6-b8f4-00212844f856'
#组的名字必须是有效的UUID,但不能用主机的GTID!所有节点的这个组名必须保持一致！在二进制日志中为组复制事件设置GTID时，将在内部使用此UUID。可使用SELECT UUID()生成一个UUID。
 
#loose-group_replication_start_on_boot       = off
#表示组复制是否随实例启动而启动
 
#loose-group_replication_local_address       = '192.168.x.x:33061'
#不同节点配置本身的IP地址和端口，负责组内通信，需区分MYSQL自身的3306端口
 
#loose-group_replication_group_seeds         ='192.168.157.128:33061,192.168.157.129:33061,192.168.157.130:33061'
#设置组成员的主机名和端口
 
#loose-group_replication_bootstrap_group     = off
#只用于集群初始化的时候设置ON开启，主节点必须执行，其他节点不需要执行
 
#loose-group_replication_single_primary_mode = on
#单主模式为ON,多主模式为OFF
 
#loose-group_replication_enforce_update_everywhere_checks = off
#是否在所有节点启用多主数据更新的严格一致性检查
#loose-group_replication_ip_whitelist        ='192.168.x.0/24'
#设置IP白名单
 
 
########################################
#--------------------innoDB------------#
 
innodb_status_file                           = 1
#启用InnoDB的status_file
innodb_autoinc_lock_mode             = 2
#不设auto_inc锁，在binlog_format是raw | mixed时，2在性能最好的同时主从复制也是安全的
 
innodb_strict_mode                           = true
#InnoDB严格检查模式，当语句写法错误时，无警告信息，直接报错
 
innodb_flush_log_at_trx_commit               = 1
#1：每次事务提交时都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认（最安全）。
#2：每次事务提交时都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作，（速度快些，较安全）。只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。IO无法满足时考虑设置为2。
#0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作，（不安全，但速度最快）。
 
innodb_flush_neighbors                       = 1
#默认值为1，SSD设置为0，Neighbors指的是属于一个extent的所有页面，一个extent共64个页面，出于硬盘IO顺序写的考虑，InnoDB通常会刷新一个extent中的所有页面，因为SSD在随机IO上没有额外负载，所以不需要启用该特性
 
innodb_page_size                 = 16K
#虽然SSD建议设置为4KB，确实会提高性能，但会出现索引不能超过768的错误，建议保持默认16KB
 
innodb_read_io_threads                       = 12
innodb_write_io_threads                      = 12
#io读写线程的个数，可根据cpu*核心数调整，默认是4
 
innodb_io_capacity                           = 5000
innodb_io_capacity_max                       = 200000
#innodb_io_capacity                           = 200
#innodb_io_capacity_max                       = 2000
#InnoDB后台进程最大的IO性能指标，根据服务器IOPS性能调整（建议使用Oracle ORION进行测试），200代表每次可以执行200次IOPS
#sas/sata:200    sas*12 raid10:2000    普通ssd:5000-200000    fusion-io高性能ssd：50000-80000
 
innodb_buffer_pool_size                      = 107374182400
innodb-buffer-pool-instances                 = 1
#60%-75%物理内存
#开启内存缓冲池的个数，当buffer_pool大时，该值小表现更好
 
innodb_change_buffer_max_size            = 25
#InnoDB使用Change buffering以最小化随机IO，默认为25，最大值是50，代表buffer pool大小的25%，写多读少的业务，才需要调大这个值。
 
innodb_lock_wait_timeout                     = 300
#innodb事务锁超时时间
 
innodb_log_buffer_size                       = 16M
 
innodb_log_file_size                         = 512M
#ib_logfile的大小 调优：https://blog.csdn.net/liyanqiang19/article/details/93737632
 
innodb_log_files_in_group            = 3
#ib_logfile的个数，3-5（默认2），视生成的日志量而定
 
innodb-file-per-table                       = true
#开启独立表空间，每个数据库的每个表都会生成一个独立的数据空间，.frm文件。
 
innodb_use_native_aio                       = 1
#开启后利用linux异步I/O在一定程度上提高系统的并发能力
 
innodb_adaptive_flushing                    = 1
innodb_purge_threads                        = 1
innodb_change_buffering             = ALL
innodb_max_dirty_pages_pct                  = 85
innodb_support_xa               = 1
#innodb_rollback_on_timeout
 
innodb_fast_shutdown                = 0
#0，保障数据的完整性，会做清除脏页和插入缓冲区的合并操作，也会将脏页全部刷新到磁盘上面去，但是这个时候关闭的速度是最慢的
#1，关闭MySQL的时候不会做清除脏页和插入缓冲区的合并操作，也不会将脏页刷新到磁盘
#2，不会做清除脏页和插入缓冲区的合并操作，也不会将脏页刷新到磁盘，但是会刷新到redo log里面，再下次启动mysql的时候恢复
 
 
#innodb_rollback_on_timeout                 = true
#事务中超时报错回滚开启，需结合事务隔离级别，注意RR+TRUE的时候事务超时前的sql也会回滚，其他组合不会。具体参照https://www.cnblogs.com/gjc592/p/12084729.html,
 
#innodb-status-file             = TRUE
#InnoDB标准监控输出文件
 
innodb_thread_concurrency                  = 64
#INNODB存储引擎中允许的最大的线程并发数，优化建议http://blog.itpub.net/15498/viewspace-2141352/
 
#innodb_data_home_dir                       = /data/mysql/data
#innodb_data_file_path                      = ibdata1:2000M;ibdata2:10M:autoextend
#(专用data磁盘文件系统路径，存储系统表空间，建议不要明确设置，如需设置，ibdata1的大小需要和实际大小相同)
 
#innodb_flush_method                = O_DIRECT
#（如果不使用硬件RAID控制器或者使用SAN，则不用设置）
 
#innodb_log_group_home_dir                  = /usr/mysql/5.7/redo
#专用redo磁盘文件系统路径
 
#innodb-sort-buffer-size                    = 1M
#此选项为创建索引时使用，创建完释放，一般不用设置，默认1M
 
######################################
#------------session variables-------#
 
max_heap_table_size                     = 512M
#MEMORY内存引擎的表大小
tmp_table_size                  = 512M
#sql执行时生成临时数据表的内存缓存大小，受max_heap_table_size影响限制，需同时调整
 
sort_buffer_size                = 8M
read_buffer_size                = 8M
read_rnd_buffer_size                = 8M
join_buffer_size                = 16M
bulk_insert_buffer_size             = 120M
#sort缓存设置
#read缓存设置
#在使用行指针排序之后，随机读用的缓存
#使用join时的缓存设置
#数据量大时优化插入提速
 
 
 
########################################
#---------------replicate--------------#
relay-log                   = "mysql-relay-bin.log"
relay-log-index                 = "relay3306.index"
log-slave-updates
 
#MyISAM
key_buffer_size                          = 8M
#key_buffer_size                          = 2G
myisam_sort_buffer_size                  = 8M
#myisam_sort_buffer_size                  = 1G
#myisam_max_sort_file_size                = 2G
myisam_max_sort_file_size                = 100G
myisam_repair_threads                    = 1
myisam-recover-options                   = 1
 
[mysqlhotcopy]
#interactive-timeout
 
[mysqld_safe]
open-files-limit             = 32767
#文件描述符
innodb_open_files                = 32767
#限制Innodb能打开的表的数据