快捷搜索:
来自 网络数据库 2019-06-20 08:33 的文章
当前位置: 67677新澳门手机版 > 网络数据库 > 正文

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最大连接数
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服务器设置的大小: 操作系统内存的70%-80%最佳
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  #ORDER 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标识来记录事务。如果使用MGR,那么这个hash值需要用于分布式冲突检测何处理#,在64位的系统,官网建议设置该参数使用 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或ALTER ROUTINE权限外还有SUPER权限,否则将禁止创建或修改存储函数,同时,还要求在创建函数时必需为之使用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,该变量可以动态调整,但是物理上的回滚段不会减少,只是会控制用到的回滚段的个数;默认为128个回滚段

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服务器花很长时间来恢复

本文由67677新澳门手机版发布于网络数据库,转载请注明出处:MySQL最优布局模板

关键词: