MySQL最优配置模板

my3306.cnf

I assume the MySQL Server as followings. You should tune the variables
according to your server.

[client]
port = 3306   #端口
socket = /data/mysql3306/mysql3306.sock  
#mysql以socket方式运营的sock文件地点

32 CPU core

[mysql]
auto-rehash      #电动补全

256G Memory

[mysqld_safe]
log-error = /data/mysql3306/my3306.err   #荒唐日志
pid-file = /data/mysql3306/my3306.pid   #process id文件

SSD storage with 20000 IOPS in 16K page size

[mysqld]
user = mysql   #mysql以怎么样顾客运营
port = 3306    #端口
default_storage_engine = InnoDB   #暗中同意存款和储蓄引擎
character-set-server = utf8   #默认utf8字符集
socket= /data/mysql3306/mysql3306.sock  
#mysql以socket方式运转的sock文件位置
pid-file = /data/mysql3306/mysql3306.pid  #process id文件
datadir = /data/mysql3306/data   #mysql的数量目录
basedir = /usr/local/mysql   #安装目录

skip_name_resolve = 1  #剥夺DNS主机名查找,启用后使用IP或localhost
#skip-external-locking   #用以多进度条件下为MyISAM数据表举行锁定
symbolic-links=0 
#是或不是帮助符号链接,数据库或表能够积存在my.cnf中内定datadir之外的分区或目录,为0不开启
max_connections = 3000    #mysql最达累斯萨Lamb接数
max_connect_errors = 100000   #当先其总是错误次数会被遮挡
transaction_isolation = READ-COMMITTED  #东南接离品级
interactive_timeout=180  #服务器关闭交互作用式连接前等候活动的秒数
wait_timeout=180  #服务器关闭非人机联作连接早先等待活动的秒数
back_log=600   
##在MYSQL停止响应新诉求以前,长时间内的多少个央浼可以被存在仓库中
#explicit_defaults_for_timestamp = 1  #
假诺大器晚成行数据中一些列被更新了,要是这意气风发行中有timestamp类型的列,这一个列的数据也会自动更新到不行时间点,设置为1关门自动更新时间点

[mysql]

# LOGGING #
log_bin=/data/mysql3306/logbin/mysql-bin   #binlog日志目录
binlog_format = row    #建议选拔row形式
expire_logs_days = 7  #binlog日志保存天数
# sync_binlog = 1    #垄断数据库的binlog刷到磁盘上去,默感到0
slow_query_log = 1   #慢查询开启
slow_query_log_file=/data/mysql3306/my3306slow.log  
#慢查询日志文件保留路线
#log_queries_not_using_indexes = 1  #是或不是记录未利用索引的说话
#log_slow_admin_statements = 1   #记录optimize table,analyze
table和alter table语句
#log_slow_slave_statements = 1  #记录由Slave所产生的慢查询
#log_throttle_queries_not_using_indexes = 10  
#设定每分钟记录到日志的未利用索引的言辞数目,超越这几个数目后只记录语句数量和开销的总时间
long_query_time = 1   #慢查询时间,单位s
#min_examined_row_limit = 100   
#查询检查重回少于该参数钦点行的SQL不被记录到慢查询日志
#master_info_repository = TABLE  #从机保存主节点消息方式,设成file时
会生成master.info 和
relay-log.info2个文本,设成table,信息就能存在mysql.master_slave_info表中
#relay_log_info_repository = TABLE  #封存从机读取relay
log的职责新闻,可选值为FILE,TABLE
gtid_mode = on  #启用gtid类型
#enforce_gtid_consistency = 1  #强制GTID的生机勃勃致性
#log_slave_updates   #从机更新是或不是记入日志,在做双主框架结构时十分主要
#relay_log =/data/slave/mysql-relay.log   #relay_log保存的任务
#relay_log_recovery = 1 
#当slave从库宕机后,假若relay-log损坏了,招致一些接入日志未有管理,则自动屏弃具备未推行的relay-log,並且重新从master上赢得日志,这样就保障了relay-log的完整性
#binlog_gtid_simple_recovery = 1  
#以此参数调节了当mysql运营或重启时,mysql在检索GTIDs时是怎么样迭代使用binlog文件的。
那么些选项设置为真,会晋级mysql实践恢复的习性。因为如此mysql-server运行和binlog日志清理越来越快
#slave_skip_errors = ddl_exist_errors  #跳过荒唐

default-character-set=utf8mb4

 

user = root

####cache#####
table_open_cache=128   
#点名表高速缓存的轻重,表的轻重相同是(max_connections*
查询同期采取的表数卡塔尔国
thread_cache_size=300   #线程池,线程缓存
query_cache_size=128M  #询问缓存大小
query_cache_min_res_unit=128k     #代表query cache
各种结果集贮存的小不点儿内部存款和储蓄器大小
sort_buffer_size=8M  #各类连接装置查询排序时所能使用的缓冲区大小
read_buffer_size=8M    #每一个连接读查询操作使用的缓存
read_rnd_buffer_size=4M  #各种连接随机读查询操作使用的缓存
join_buffer_size=2M   #各样连接联合查询所采纳的缓存大小
bulk_insert_buffer_size=16M   
#为三回插入多条新记录的INSERT命令分配的缓存村长度
max_heap_table_size=64M   
#这些变量定义了顾客能够创建的内部存款和储蓄器表(memory
table)的大小.那些值用来总括内部存款和储蓄器表的最大行数值
tmp_table_size=64M   #临时表的大小

password = 123456

query_cache_type=1  #开启Query Cache 功能,有0,1,2三个值

port = 3306

 

socket = /tmp/mysqld.sock

###MyISAM###
#key_buffer_size=64M   #目录缓存大小
#key_cache_block_size=4k   钦命每种索引缓存的区块大小
#myisam_sort_buffer_size=2M 

prompt=”u@h d>”

########innodb settings########
#innodb_page_size = 4k  #innodb各类数据页大小
innodb_buffer_pool_size = 4G 
#缓存innodb表的目录,数据,插入数据时的缓冲,专用mysql服务器设置的分寸:
操作系统内部存款和储蓄器的五分之四-七成一流
innodb_buffer_pool_instances = 1  
#能够打开五个内部存款和储蓄器缓冲池,把供给缓冲的数码hash到分裂的缓冲池中,那样能够相互的内部存储器读写
#innodb_buffer_pool_load_at_startup = 1 
#默以为关闭OFF。要是翻开该参数,运转MySQL服务时,MySQL将地点热数据加载到InnoDB缓冲池中
#innodb_buffer_pool_dump_at_shutdown = 1 
#暗中同意为关闭OFF。要是展开该参数,甘休MySQL服务时,InnoDB将InnoDB缓冲池中的热数据保存到地方硬盘
#innodb_lru_scan_depth = 2000  #会影响page
cleaner线程每趟刷脏页的数额, 那是二个每1秒 loop一遍的线程
innodb_lock_wait_timeout = 50  
事务等待获取财富等待的最长日子,抢先那几个时刻还没分配到财富则会回来应用败北,单位s
#innodb_io_capacity = 4000 
#innodb_io_capacity_max = 8000  
#这两个设置会耳濡目染InnoDB每秒在后台试行微微操作,知道完美可进行稍稍次IO操作最棒
#innodb_flush_method = O_DIRECT  #暗中认可值为 fdatasync. 要是使用
硬件RAID磁盘调整器, 可能要求安装为
O_DIRECT,不接受硬件RAID调控器,也许使用SAN存款和储蓄时, O_DIRECT
大概会引致质量减弱

#innodb_log_group_home_dir = /data/mysql3306/redolog/    
#redolog重做日志存放目录
#innodb_undo_directory = /data/mysql3306/undolog/  
#undolog回滚日志寄存目录
innodb_undo_logs = 128  #undo回滚段的数额, 至少超越等于35,暗中认可128
#innodb_undo_tablespaces = 3  
#用来设定创设的undo表空间的个数,在mysql_install_db时初叶化后,就再也不能够被改造了;私下认可值为0,表示不独立设置undo的tablespace,暗许记录到ibdata中;
innodb_flush_neighbors = 1  
#InnoDB存款和储蓄引擎在刷新二个脏页时,会检查评定该页所在区(extent)的有着页,尽管是脏页,那么协作刷新。那样做的补益是因此AIO能够将五个IO写操作合併为三个IO操作。对于金钱观固态硬盘提议使用,而对此固态硬盘可以关闭。
innodb_log_file_size = 1G 
#概念了日记文件的大小,innodb日志文件的意义是用来保存redo日志
innodb_log_buffer_size = 8M  #事务在内部存款和储蓄器中的缓冲。 分配原
则:调控在2-8M.这么些值不用太多的。他里面包车型大巴内部存款和储蓄器经常风华正茂分钟写到磁盘壹次
innodb_purge_threads = 4  
#操纵是还是不是接纳,使用几个独立purge线程(消亡二进制日志卡塔 尔(英语:State of Qatar)
innodb_large_prefix = 1  #当改为ON时,允许列索引最大概达3072
innodb_thread_concurrency = 64  #InnoDB kernel并发最大的线程数
innodb_print_all_deadlocks = 1  #是不是将死锁相关音讯保存到MySQL
错误日志中
innodb_strict_mode = 1 
#开启InnoDB严酷检查形式,尤其选拔了页数据压缩功效后,最佳是敞开该意义,假使写法有错误,不会有警报音讯,而是径直抛出荒诞
innodb_sort_buffer_size = 8M  #O瑞鹰DEPAJERO BY 大概GROUP BY
操作的buffer缓存大小

[mysqld]

######mysqld-5.7########
innodb_buffer_pool_dump_pct = 40    #代表转储每种bp instance
LRU上最热的page的比例。通过安装该参数能够减小转储的page数
innodb_page_cleaners = 4   #引进了多个page
cleaner线程。进而实现并行刷脏的效率
innodb_undo_log_truncate = 1  #是否张开在线回笼(收缩卡塔 尔(阿拉伯语:قطر‎undo
log日志文件,扶植动态设置
innodb_max_undo_log_size = 2G 
#当凌驾那些阀值(暗许是1G卡塔 尔(英语:State of Qatar),会触发truncate回笼(降低卡塔尔国动作,truncate后空中降低到10M
innodb_purge_rseg_truncate_frequency = 128  #决定回笼(减少卡塔 尔(英语:State of Qatar)undo
log的频率。undo log空间在它的回滚段还未得到释放此前不会裁减,
想要扩展释放回滚区间的频率,就得下跌设定值
binlog_gtid_simple_recovery=1 
#其风华正茂参数调节了当mysql运营或重启时,mysql在查究GTIDs时是什么样迭代使用binlog文件的。
这一个选项设置为真,会进级mysql施行恢复生机的性质。因为这么mysql-server运维和binlog日志清理更加快
log_timestamps=system   #5.7.2 之后改参数为私下认可 UTC
那样会促成日志中记录的时日比中华夏族民共和国这边的慢,诱致查看日志不便于。改良为
SYSTEM 就会解决难题
#transaction_write_set_extraction=MURMUR32 
#用来定义七个笔录事务的算法,那些算法使用hash标记来记录事务。若是使用名爵凯雷德,那么那一个hash值供给用于布满式冲突检查测量检验何地理#,在65个人的种类,官方网站提议设置该参数使用
XXHASH64 算法。假诺线上并未有运用该意义,应该设为off
#show_compatibility_56=on 
#从mysql5.7.6开始information_schema.global_status已经早先被扬弃,为了包容性,那个时候供给张开show_compatibility_56
[mysqldump]
quick 
max_allowed_packet = 32M     #mysql最大采用的数据包大小

# basic settings #

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

user = mysql

#skip-grant-tables   #跳过权力

bind-address = 0.0.0.0

socket = /tmp/mysqld.sock

character_set_server = utf8mb4

transaction_isolation = READ-COMMITTED

explicit_defaults_for_timestamp = 1

max_allowed_packet = 67108864  
 //限定Server接纳的多寡包大小。偶然候大的插入和立异会受此参数约束,导致大数据写入可能更新失败

max_long_data_size = 67108864  
 
//设定能够由mysql_stmt_send_long_data()那些C
API函数所传递的参数值的最大尺寸,若无在mysqld运营时设定,其暗中认可为max_allowed_packet变量的值

event_scheduler = 1    //事件调解器的总开关

default_password_lifetime = 0  
 
//设置密码自动失效的时刻,0为永不失效

autocommit = 1

server-id = 1

sql_mode =
“STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER”

# connection #

interactive_timeout = 1800  
 //MySQL服务器关闭人机联作式连接前等待的秒数

wait_timeout = 1800    //MySQL服务器关闭非人机联作连接从前等待的秒数

lock_wait_timeout = 1800

skip_name_resolve = 1

max_connections = 1024    //针对全部客商连接节制

max_user_connections = 256    //针对同后生可畏顾客的连续几日限定

max_connect_errors = 1000000  
 
//当错误连接数抢先设定的值后,将不可能平常连接

# table cache performance settings #

table_open_cache = 4096  
 
//钦定表高速缓存的轻重。每当MySQL访谈多少个表时,假诺在表缓冲区中还只怕有空间,该表就被张开并放入当中,那样能够更加快地拜见表内容

table_definition_cache = 4096    //表定义音信缓存

table_open_cache_instances = 64    //指的是 MySQL 缓存 table
句柄的分区的个数,而每一个 cache_instance 能够包罗不超越table_open_cache/table_open_cache_instances
的table_cache_element

# session memory settings #

read_buffer_size = 16M  
 
//MySQL读入缓冲区的深浅,将对表举行逐项扫描的倡议将分配三个读入缓冲区,MySQL会为它分配生机勃勃段内部存储器缓冲区,read_buffer_size变量支配这生机勃勃缓冲区的尺寸,要是对表的次第扫描特别频繁,并你以为频仍扫描举行的太慢,能够由此扩展该变量值以致内部存款和储蓄器缓冲区大小进步其性质,read_buffer_size变量支配这风流罗曼蒂克进步表的依次扫描的效能数据文件顺序

read_rnd_buffer_size = 32M    //

sort_buffer_size = 32M   
//是MySQL的自便读缓冲区大小,当按专擅顺序读取行时(列如根据相排版序依次卡塔尔将分配八个即兴读取缓冲区,实行排序查询时,MySQL会首先扫描一次该缓冲,防止止磁盘搜索,升高查询速度,假若必要大量多少可方便的调解该值,但MySQL会为每一种客商连接分配该缓冲区所以尽量方便设置该值,避防内部存款和储蓄器用迈过大。表的人身自由的逐个缓冲
升高读取的频率

tmp_table_size = 64M   
//它规定了中间内部存款和储蓄器偶尔表的最大值,种种线程都要分配。(实际起节制效能的是tmp_table_size和max_heap_table_size的矮小值。卡塔 尔(英语:State of Qatar)要是内存有的时候表超过了约束,MySQL就能活动地把它转载为依附磁盘的MyISAM表,存储在钦赐的tmpdir目录下。优化查询语句的时候,要防止使用不经常表,假诺实在防止不了的话,要保险这么些不常表是存在内部存款和储蓄器中的。假如供给的话况兼你有那个group
by语句,何况你有看不完内部存储器,增大tmp_table_size(和max_heap_table_size)的值。这一个变量不适用与客户制造的内部存储器表(memory
table).

您能够比较之中基于磁盘的有时表的总量和开创在内部存款和储蓄器中的有的时候表的总额(Created_tmp_disk_tables和Created_tmp_tables卡塔尔,通常的比例关系是:

Created_tmp_disk_tables/Created_tmp_tables<5%。max_heap_table_size本条变量定义了客商可以创制的内部存款和储蓄器表(memory
table)的大小.这么些值用来总括内部存储器表的最大行数值。那几个变量辅助动态纠正,即set
@max_heap_table_size=#

,可是对于已经存在的内存表就从未什么用了,除非那么些表被再度创建(create
table)恐怕涂改(alter table)只怕truncate
table。服务重启也会安装已经存在的内部存款和储蓄器表为全局max_heap_table_size的值。

其意气风发变量和tmp_table_size一同限定了此中内部存款和储蓄器表的高低。

join_buffer_size = 128M    //用于表间关联缓存的大大小小

thread_cache_size = 64   
//服务器线程缓存那几个值表示能够重新利用保存在缓存中线程的数据,当断开连接时后生可畏旦缓存中还也许有空间,那么顾客端的线程将被放置缓存中,假诺线程重新被倡议,那么要求将从缓存中读取,借使缓存中是空的要么是新的号令,那么这么些线程将被另行创造,假如有广大新的线程,扩展这么些值能够改良系统品质.通过相比较Connections 和 Threads_created 状态的变量,能够看来那个变量的功效.

# log settings #

log_error = error.log

log-bin = mysql-bin

slow_query_log = 1

slow_query_log_file = slow.log

log_queries_not_using_indexes = 1

log_slow_admin_statements = 1    //记录实行缓慢的保管SQL

log_slow_slave_statements = 1    //记录从库上实行的慢查询语句 

log_throttle_queries_not_using_indexes = 10   
//每分钟允许记录到slow log的且未利用索引的SQL语句次数

expire_logs_days = 30

long_query_time = 2

min_examined_row_limit = 100   
//查询语句的实施行数检查重临少于该参数钦命行的SQL不被记录到慢查询日志

binlog-rows-query-log-events = 1   
//当binlog_fromat=row的时候记录的是event,假设想要在row方式的意况下也记录SQL语句

log-bin-trust-function-creators = 1   
//此参数仅在启用二进制日志时有效,用于调整创立存款和储蓄函数时只要会引致不安全的事件记录二进制日志条件下是或不是禁绝创制存储函数。默许值为0,表示除非客商除了CREATE
ROUTING或ALTEEnclaveROUTINE权限外还只怕有SUPELX570权限,不然将抑制创造或改良存储函数,同期,还供给在开创函数时必须为之使用DETERMINISTIC属性,再否则正是附带READS
SQL DATA或NO
SQL属性。设置其值为1时则不启用那么些节制。效能范围为大局等级,可用来配置文件,属动态变量。

log-slave-updates = 1   
//日常景况下slave不会把从master接受到的binlog记录写入自身的binlog,这些参数会使slave通过SQL线程把从master接纳到的binlog写进自个儿的binlog,不过前提是slave必须要敞开自个儿的binlog,此参数平时用来级联复制,举例供给A复制到B,B复制到C,那么B就要翻开此参数。

# innodb settings #

innodb_page_size = 16384   
//参数innodb_page_size能够安装Innodb数据页为8K,4K,默认为16K。这几个参数在一同来早先化时就要参预my.cnf里,若是已经创办了表,再改革,运营MySQL会报错。

innodb_buffer_pool_size = 160G   
//参数表示缓冲池字节大小,InnoDB缓存表和目录数据的内部存储器区域

innodb_buffer_pool_instances = 16   
//暗中认可值是1,表示InnoDB缓存池被划分到几个区域。适本地追加该参数(举个例子将该参数值设置为2卡塔尔国,那个时候InnoDB被细分成为多少个区域,能够晋级InnoDB的现身品质。如若InnoDB缓存池被剪切成多个区域,提议每个地区相当的大于1GB的空中

innodb_buffer_pool_load_at_startup = 1   
//在运营时把热数据加载到内部存款和储蓄器

innodb_buffer_pool_dump_at_shutdown = 1   
//在闭馆时把热数据dump到地点磁盘

innodb_lru_scan_depth = 4096   
//调节LRU列表中可用页的数据,默许值为1024

innodb_lock_wait_timeout = 5    //锁等待超时时间

innodb_io_capacity = 10000   
//参数可以动态调解刷新脏页的多寡,那在洗颈就戮程度上解决了那大器晚成主题素材。innodb_io_capacity参数默许是200,单位是页。该参数设置的高低决意于硬盘的IOPS,即每秒的输入输出量

innodb_io_capacity_max = 20000   
//该参数约束了每秒刷新的脏页上限,调大该值能够追加Page
cleaner线程每秒的专门的学业量

innodb_flush_method = O_DIRECT   
//参照他事他说加以考察链接:

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda    //Innodb
Plugin引擎最先引入四种格式的行存款和储蓄机制,近日支撑:Antelope、Barracuda三种。此中Barracuda包容Antelope格式。

此外,Innodb
plugin还扶植行数据压缩天性,可是前提是行使Barracuda行存款和储蓄格式。

表空间启用压缩的前提是innodb表空间文件存款和储蓄格式改进成:Barracuda,必要改过2个接收:

innodb_file_format =
“Barracuda”

innodb_file_format_max = “Barracuda”

innodb_undo_logs = 128   
//定义在叁个政工中innodb使用的连串表空间中回滚段的个数。若是观望到同回滚日志有关的排挤争用,可以调解那些参数以优化品质。初期版本的命名称为 innodb_rollback_segments,该变量能够动态调节,可是物理上的回滚段不会削减,只是会决定用到的回滚段的个数;默认为126个回滚段

innodb_undo_tablespaces = 3   
//用于设定创立的undo表空间的个数,在mysql_install_db时开端化后,就再也无法被改动了;暗许值为0,表示不独立设置undo的tablespace,默许记录到ibdata中;不然,则在undo目录下创立那样五个undo文件,举个例子假定设置该值为4,那么就能成立命名叫undo001~undo004的undo
tablespace文件,每一个文件的暗许大小为10M。修正该值会招致Innodb不可能完毕早先化,数据库不可能运转,不过另八个参数能够校正

innodb_flush_neighbors = 0    //暗中同意值为 1.
在SSD存款和储蓄上应设置为0(禁止使用) ,因为使用各样IO没有别的性质收益.
在应用RAID的一点硬件上也相应剥夺此设置,因为逻辑上一连的块在物理磁盘上并不能够确定保证也是接连的

innodb_log_file_size = 200M    //日志组的大大小小,默感觉5M;若是对
Innodb
数据表有多量的写入操作,那么选取拾叁分的 innodb_log_file_size值对进级MySQL质量很主要。不过设置太大了,就能够追加苏醒的年华,由此在MySQL崩溃大概忽然断电等情状会令MySQL服务器花非常短日子来平复

发表评论

电子邮件地址不会被公开。 必填项已用*标注