博主: haitianisgood

原博客网址:

http://blog.csdn.net/haitianisgood/article/details/73656862

Mysql配置参数详解调优

port= 3306

#默认为3306#Mysql使用的端口socket= /data/mysql/mysql.sock

user=mysql

#Mysql用户

atadir=/data/mysql

#默认为/var/lib/mysql#数据文档存储目录

skip-slave-start

#默认为未启用#禁止slave复制进程随Mysql数据库启动而启动#如果从数据库宕机，从数据库还原数据后，容易造成主从数据库数据不一致。

skip-name-resolve

#默认为未启用#禁止Mysql进行DNS反向解析，只能通过Mysql授权表中IP来访问。#默认安装的MySql开启了DNS的反向解析#禁止DNS反向解析后可以提高Mysql访问速度。

sort_buffer_size = 4M

#默认为256KB# Sort_Buffer_Size 是一个connection级参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。#Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。例如：500个连接将会消耗 500*sort_buffer_size(8M)=4G内存#Sort_Buffer_Size 超过2KB的时候，就会使用mmap() 而不是 malloc() 来进行内存分配，导致效率降低。

join_buffer_size = 2M

#默认为128KB#用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。#如果应用中，很少出现join语句，则可以不用太在乎join_buffer_size参数的设置大小。#如果join语句不是很少的话，个人建议可以适当增大join_buffer_size到1MB左右，如果内存充足可以设置为2MB

table_open_cache = 5000

#默认为64#打开表的缓存数量。也不是定义内存的大小的。而是定义可以缓存多少打开的表的文件句柄信息。#如果定义的太小，那么mysql在需要打开新表的时候就要不断的关闭已经打开的表和打开此次需要打开的表。性能会受到影响。

table_definition_cache = 5000

#默认值为256 (Myslq5.12后，之前128)#表定义信息缓存是从MySQL5.1.3 版本才开始引入的一个新的缓存区，用来存放表定义信息。#当我们的MySQL 中使用了较多的表的时候，此缓存无疑会提高对表定义信息的访问效率。MySQL 提供了table_definition_cache 参数给我们设置可以缓存的表的数量

thread_cache_size = 512

#定义缓存了的线程数量#当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)#物理内存设置规则：通过比较Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。(-->表示要调整的值)   根据物理内存设置规则如下：     1G  ---> 8     2G  ---> 16     3G  ---> 32     3G  ---> 64只要设置的大小除以8小于物理内存就可以的。

query_cache_size = 32M

#默认值为0#查询缓存是MySQL 比较独特的一个缓存区域，用来缓存特定Query 的结果集（Result Set）信息，且共享给所有客户端。通过对Query 语句进行特定的Hash 计算之后与结果集对应存放在Query Cache 中，以提高完全相同的Query 语句的相应速度。#在数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建议把该功能禁掉。

query_cache_limit = 2M

#默认值为1MB#指定单个查询能够使用的缓冲区大小

max_allowed_packet = 100M

#默认值为4M。#接受的数据包大小

max_connect_errors = 1000000

#默认值为10#一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。max_connect_errors的值与性能并无太大关系# mysqladmin flush-hosts命令来解锁已经被屏蔽的主机

max_connections = 1500

#默认值为100#MySQL允许最大的进程连接数，如果经常出现Too Many Connections的错误提示，则需要增大此值。

tmp_table_size = 256M

#默认值大小为32M#临时表使用内存#如果一张临时表超出该大小，MySQL产生一个 The table tbl_name is full 形式的错误，如果你做很多高级 GROUP BY 查询，增加 tmp_table_size 值。如果超过该值，则会将临时表写入磁盘。

max_heap_table_size = 256M

#定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变#其大小还小于 tmp_table_size 的时候，MySQL 将使用 max_heap_table_size 参数所设置大小作为最大的内存临时表大小，而忽略 tmp_table_size 所设置的值。

---

——–log———

slow_query_log=1

slow quere log的开关，当值为1的时候说明开启慢查询。

slow_query_log_file=slow.log

#慢查询日志位置及其文件名字

long_query_time = 1

#慢查询时间 超过1秒则为慢查询

log-error = mysql-error.log

#Mysql错误日志开关#错误日志位置及错误日志文件名# must set for mha change master operation, with binlog diff

log_slave_updates = 1

#默认为0(关闭状态)#控制从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据时候是否写入从库自身的binlog日志

relay_log_purge = 0

#默认为1 (自动删除状态)#控制SQL 线程在执行完一个 relay log 后是否自动将其删除

read_only = 1

#默认为0 (读写状态)#设置数据库为只读模式，只对普通用户起作用。对root或拥有super权限的无效果。mysql> show global variables like "%read_only%";mysql> flush tables with read lock;mysql> set global read_only=1;mysql> show global variables like "%read_only%";

——–binlog———

expire_logs_days = 30

#默认为0 (不自动删除)#设置binlog自动删除过期时间

log-bin=mysql-bin

#默认为关闭#Mysql的binlog日志开关#设置binlog日志位置及日志文件名

binlog_format=mixed

#默认为statement# binlog日志格式，建议使用mixed

server-id = 1055

#Mysql server唯一标示，一般以服务器IP的后两位就可以#主从同步中使用# mysql的同步的数据中是包含server-id的，用于标识该语句最初是从哪个server写入的，所以server-id一定要有的#每一个同步中的slave在master上都对应一个master线程，该线程就是通过slave的server-id来标识的；每个slave在master端最多有一个master线程，如果两个slave的server-id 相同，则后一个连接成功时，前一个将被踢掉。 这里至少有这么一种考虑：slave主动连接master之后，如果slave上面执行了slave stop；则连接断开，但是master上对应的线程并没有退出；当slave start之后，master不能再创建一个线程而保留原来的线程，那样同步就可能有问题；#在mysql做主主同步时，多个主需要构成一个环状，但是同步的时候有要保证一条数据不会陷入死循环，这里就是靠server-id来实现的

——–innodb———

innodb_data_home_dir = /data/mysql/ibdata

#默认使用MySQL的 datadir 目录为缺省目录# InnoDB表的目录共用设置#如果设定一个空字串,可以在 innodb_data_file_path 中设定绝对路径

innodb_file_per_table = 1

#默认为关闭(值为0)#控制是否使用独立表空间模式

innodb_autoextend_increment = 256

#默认是8M#当自动扩展表空间被填满之时，为扩展而增加的尺寸（MB为单位）# mysql 5.6.5版本之前默认值是8Mb，从5.6.6版本之后默认为64Mb，最小值为1Mb最大值为1000Mb。#这个参数受到innodb_file_per_table参数的影响

innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend

#指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，也只有最后一个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长以容纳额外的数据#innodb_data_file_path的值应该为一个或多个 数据文件规格的列表。如果命名一个以上的数据文件，用 分号(‘;’)分隔它们#autoextend属性和后面跟着的属性只可被用来对innodb_data_file_path行里最后一个数据文件。#InnoDB不创建目录，所以在启动服务器之前请确认/ibdata目录的确存在#如果没有指定innodb_data_home_dir，则默认为mysql数据目录#如果你指定innodb_data_home_dir为一个空字符串，你可以为列在innodb_data_file_path值里的数据文件指定绝对路径。#例子：假设希望创建一个数据文件ibdata1，初始大小为100MB，并希望在每次达到当前大小限制时，自动增加8MB（8MB是指定autoextend时的默认扩展大小).但是，不希望此文件超过1GB，可以使用如下配置: innodb_data_home_dir = innodb_data_file_path = /data/ ibdata1:100M:autoextend:8M: max:1GB 如果此文件增加到预定的1G的限制，可以再增加另外一个数据文件,如下: innodb_data_file_path = /data/ibdata1:100M:autoextend:8M: max:1GB;innodb_data_file_path = /data2/ibdata2:100M:autoextend:8M: max:2GB

innodb_table_locks = 0

#默认值为1#使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。#如果autocommit=1,Innodb的内部表锁可能会导致deadlock,可以通过设置innodb_table_locks=0来解决这个问题#设置innodb_table_locks=0,如果由于lock tables语句或者其它存储引擎设置了lock导致dead lock,Innodb将不能自动侦测到. 这时,必须设置innodb_lock_wait_timeout变量,使deadlock超时后rollback

innodb_lock_wait_timeout = 10

#指示 MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)#InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

innodb_buffer_pool_size = 16G

#默认为8M#InnoDB 用来高速缓冲数据和索引内存缓冲大小#InnoDB最重要的设置，对InnoDB性能有决定性的影响#可以设置60-80%的内存

innodb_additional_mem_pool_size = 20M

#默认值为1MB#InnoDB 用来存储数据字典(data dictionary)信息和其它内部数据结构(internal data structures)的存储器组合(memory pool)大小#参数对系统整体性能并无太大的影响，只要能存放需要的数据就好，否则只会浪费内存#几百个Innodb 表的，推荐20M以内就足以了。#此参数在5.7中已经移除https://dev.mysql.com/worklog/task/?id=7628

innodb_read_io_threads = 6

#后台读线程, 主要处理INNODB 数据文件异步读请求#每个read线程最多可以pend 256个任务(Linux平台中)

innodb_write_io_threads = 6

#后台写线程数, 主要处理INNODB 数据文件异步写请求#每个write线程最多可以pend 256个任务(Linux平台中)

innodb_flush_method=O_DIRECT

#默认为fdatasyncfdatasync：调用fsync()去刷数据文件与redo log的bufferO_DSYNC：innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件O_DIRECT：innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log

innodb_log_files_in_group = 2

#默认为2#控制日志文件数

innodb_log_file_size = 1G

#默认为 5M#用来在mysql crash后的恢复.所以设置合理的大小对于mysql的性能非常重要#通过show engine innodb status\G;可以查看mysql checkpoint情况，可以算出上次checkpoint和最后一次checkpoint的中间值，官方文档建议最好不要超过innodb_log_files_in_group*innodb_log_file_size的0.75由此可以推算出innodb_log_file_size比较合适的值。# #在mysql 5.5和5.5以前innodb的logfile最大设置为4GB,在5.6以后的版本中logfile最大的可以设为512GB#当mysql crash后，在重启之前需要将老的innodb logfile删除。https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb_log_file_size/https://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html

innodb_log_buffer_size = 48M

#默认为8M#当事务提交时，保存脏数据到内存中，后续再刷新保存到磁盘#适当调整此参数大小，可以减少磁盘I/O

innodb_flush_log_at_trx_commit=2

#默认为1#控制log buffer写入log file和控制flush操作#innodb_flush_log_at_trx_commit=0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。#innodb_flush_log_at_trx_commit=1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.#innodb_flush_log_at_trx_commit=2，每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。注意：  由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。

sync_binlog = 15

#默认值为0#像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。当sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。注:   如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。#一般与innodb_flush_log_at_trx_commit同时设置

innodb_support_xa = 0

#默认为自动开启#0表示关闭#分布式事务中此参数确保事务日志写入bin-log 的顺序与是事务的time-line 是一致的

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#默认为空#此参数在mysql数据库版本升级时候用比较合适

binlog_cache_size=2M

#默认为32K#一个事务，在没有提交（uncommitted）的时候，产生的日志，记录到Cache中；等到事务提交（committed）需要提交的时候，则把日志持久化到磁盘#在事务中存储二进制日志sql语句的缓存大小

transaction_isolation = READ-COMMITTED

#默认为 REPEATABLE READ#关于MySQL的事务处理及隔离级别#READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE