澳门在线威尼斯官方 > 电脑数据库 > MySQL最优配置模板

原标题:MySQL最优配置模板

浏览次数:132 时间:2019-09-15

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最达累斯萨拉姆接数
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    #垄断(monopoly)数据库的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线程(清除二进制日志)
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奥迪Q3DE奥迪Q5 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),会触发truncate回收(收缩)动作,truncate后空中压缩到10M
innodb_purge_rseg_truncate_frequency = 128  #垄断回收(缩短)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标志来记录事务。假如利用MGQX56,那么那几个hash值须要用于分布式争持检查评定何管理#,在六拾六人的类别,官方网址提出设置该参数使用 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的小小值。)如若内部存款和储蓄器有时表超越了限定,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或ALTETiguanROUTINE权限外还会有SUPE奥迪Q3权限,不然将禁止创设或修改存款和储蓄函数,同一时候,还须要在开立函数时必备为之使用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,该变量能够动态调节,可是物理上的回滚段不会收缩,只是会垄断用到的回滚段的个数;默感觉1贰19个回滚段

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服务器花非常短日子来过来

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:MySQL最优配置模板

关键词:

上一篇:0新特性之支持原子DDL语句,支持原子DDL语句

下一篇:没有了