MySQL配置文件详细分析

2016-07-24
MySQL配置文件讲解

为了更加透彻的理解MySQL的配置文件，从同事那里要来了一份他 私人的配置文件总结(前爱奇艺/百度数据库工程师张xx)
# my.cnf
# 所有对参数的注解都在该参数的上方。未明确列出的参数请使用默认值。
# 该文件只是部分优化的参数模板。具体优化值可以根据自身的业务场景进行优化。
# 版本：MySQL/MariaDB 5.5
# Author: Perry.Zhang

[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
prompt="\\u@\\h [\\d]>"
#tee=/data/mysql/mysql_3306/data/query.log
no-auto-rehash

[mysqld]
# ---------- BASE CONFIGURE ----------
# 启动MYSQL数据库的用户
user = mysql
# MYSQL守护进程启动时监听的端口
port = 3306
# MYSQL安装路径：源码安装时在CMAKE_INSTALL_PREFIX注明
basedir =
# MYSQL数据文件存放路径：存放独立表空间文件、索引文件（MYI）、表结构文件（.frm）会按照模式（Schema）进行区分。
datadir =
# MYSQL插件存放路径。默认为BASEDIR/lib/plugin。
# 注：如果启动服务时出现找不到ha_xxxx.so的文件时，可能的原因是系统本身自带的MYSQL服务没有卸载干净。导致服务无法正确加载插件。
#     处理的方法是手动指定插件存放路径。
plugin_dir =
# MYSQL sock文件存放路径：该目录可以改动，但必需可以让mysql有写权限
socket = /tmp/mysql.sock
# MYSQL pid文件存放路径。
pid-file = mysql.pid
# 关闭计划任务--事件调度器（默认关闭，可以不加该条）
# event_scheduler = 0
# 初始化客户端连接：对super用户无效。
# 注：HUPU场景里设置可以保证客户端字符集未设置是utf8时的客户端连接保持Latin1字符集
# init_connect = 'set names latin1; SET CHARACTER SET latin1;'
# MYSQL SERVER字符集
character-set-server = utf8
# 事务隔离级别：MYSQL默认为REPEATABLE-READ。事务隔离级别从低到高依次为：READ UNCOMMITTED、READ COMMITTED、REPEATABLE-READ、SERIALIZABLE。
transaction-isolation = REPEATABLE-READ
# 表名不区分大小写：0，否；1，是
lower_case_table_names = 1
# 不进行DNS域名解析：0，否；1，是。
# 注：  1）不管使用IP还是HOST，MYSQL都会做域名反向解析。因此该参数最好开启，否则可能出现反查解析过慢引起的远程连接MYSQL变慢。
#       2）开启该参数后，授权表中请使用IP替换域名做权限设置。
skip-name-resolve
# MYSQL实例在大量发时，而MYSQL处理不过来，就会产生最大连接错误，当达到设定值后会锁死实例，新的连接将无法进来，而老的连接正常。
max_connect_errors = 100000
# 是否关闭授权表。一般只用在忘记服务器权限需要重置时，生产环境中不能开启。
# skip-grant-tables

# ---------- GLOBAL CACHES、BUFFER AND LIMITS ----------
# 为顺序扫描（顺序读）线程分配的缓冲池大小。必须为4KB的倍数。如果设置不为4KB的倍数时会向下取整。通常顺序读多的场景需要加大该值。最大2GB。
# 还有如下场景会用到该值：
# 1.使用ORDER BY做排序时，需要在临时文件（不是临时表）中缓存索引
# 2.大块INSERT插入分区
# 3.嵌套查询缓存结果集
# 注：该值针对每个线程分配，设置过大会影响并发性能。
# 优化建议：顺序读较多的场景可以适当增大该值。
read_buffer_size = 2M
# 为随机扫描（随机读）线程分配的缓冲池大小。查询中有大量使用ORDER BY子句做排序操作的，可以增大该值。最大2GB。
# 注：该值针对每个线程分配，设置过大会影响并发性能。
# 优化建议：最好只在较大结果集的ORDER BY子句的session里增大该值。
read_rnd_buffer_size = 8M
# 为排序操作的线程分配的缓冲池大小。全引擎通用。无法被查询优化器和索引优化的查询会使用该缓冲区。
# SHOW GLOBAL STATUS LIKE 'SORT_MERGE_PASSES' 的值过大时，可以考虑调高该值。
# 注：该值要符合业务场景需要，不要设定太大（因其即使未使用也会全部分配）且针对每个线程分配。最大可分配大小为4G-1（32 bit）。64 bit系统可以分配更大（除了64位windows）。
# 优化建议：LINUX系统下该值的阈值在256K-2M之间。官方文档称超过2M会出现明显的性能下降。建议GLOBAL值设为4M（每4G内存）。增大该参数以提高ORDER BY 或GROUP BY场景可以动态修改SESSION值。
sort_buffer_size = 4M
# 为普通索引扫描、范围索引扫描、没有使用索引而导致全表扫描的JOIN分配的缓冲池大小。
# 注：该值要符合业务场景需要，不要设定太大（因其即使未使用也会全部分配）且针对每个线程分配。最大可分配大小为4G-1（32 bit）。64 bit系统可以分配更大（除了64位windows）。
# 优化建议：GLOBAL设小，SESSION值可以调大（在优化时）.因为分配超过需要的过大内存会导致严重的性能下降。
join_buffer_size = 8M
# MYSQL允许并发会话数上限。默认151.阈值1-100000。
max_connections = 4096
# 最大打开文件数（mysqld文件描述符限制）。最大值为系统允许最大值，linux使用ulimit -n命令查看。
# 优化建议：应用出现Too many open files错误时需要增大该参数.
open_files_limit = 65535
# 表打开缓存。该参数控制开表缓存的大小，用于加快表访问速度。
# 优化建议：SHOW GLOBAL STATUS LIKE 'OPENED_TABLE'的值过大且不使用FLUSH TABLES手工关闭打开表时，可以增大该参数。
# 注：MYISAM引擎主要需要优化的是该参数与KEY_BUFFER_SIZE
table_open_cache = 1024


# ---------- TEMPORARY TABLE ----------
# 创建在内存中的临时表大小（内部heap表大小）。查询语句中有大量GROUP BY子句或机器有较大内存时，可以考虑加大该值。
# 注：其阈值为与max_heap_table_size两个值中最小的一个。
#     当内部heap表超过tmp_table_size设定值后，MYSQL会将基于内存heap表转换为基于磁盘的MYISAM表。
# 优化建议：1）SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'/'Created_tmp_tables'
#               Created_tmp_disk_tables：执行SQL语句时在磁盘中创建的临时表数量；
#               Created_tmp_tables：执行SQL语句时创建的临时表的总数，该值比Created_tmp_disk_tables大。
#               二者差值为创建在内存中的临时表。
#           2）如果Created_tmp_disk_tables过大或Created_tmp_tables-Created_tmp_disk_tables差值过小，
#              表明tmp_table_size或max_heap_table_size的值过小。可以考虑增大该值。
tmp_table_size = 256M
# 定义用户可以创建的内存表(memory table、heap引擎)最大值，用来计算内存表的最大行值。
# 注：MYSQL的内存表为堆表。
max_heap_table_size = 256M

# ---------- QUERY CACHE ----------
# 是否开启QC。
# 注：SHOW GLOBAL VARIABLES LIKE 'HAVE_QUERY_CACHE'; //查看MYSQL是否支持QC。
#      SHOW GLOBAL STATUS LIKE '%QC%'; //查看与QC相关的
#        1）Qcache_hits：QC命中查询数
#        2）Qcache_inserts：QC写入查询数
#        3）Qcache_not_cached：QC未缓存查询数（QC关闭时也会记录）
# 优化建议：
#       1）大并发场景（类似双十一）可以关闭。但是要求应用中的SQL语句必须通过索引查询且分配给MYSQL的内存也要大，否则会造成严重的性能问题。
#       2）场景中有表频繁更新可以考虑让应用在针对频繁更新的表的查询时增加SQL_NO_CACHE标签。
#       3）误区：加空格、TAB、注释等会被MYSQL当成一个新查询存入QC。
#          纠正：前后空格、TAB等将被过滤，都算作一个查询。
# 取值范围：
# 0：关闭。不分配QC所需的内存空间。
# 1：缓存所有查询。加SQL_NO_CACHE标签的除外。
# 2：只有带SQL_CACHE标签的查询才会使用QC。
query_cache_type = 0
# QC大小。最小为40K。小于40K会触发WARNING.
# 注：SHOW GLOBAL VARIABLES LIKE 'Qcache_lowmem_prunes'值可以帮助调整query_cache_size的大小。
#     Qcache_lowmem_prunes：由于query_cache_size太小而导致查询从QC中删除的数量。
# 优化建议：当发现Qcache_lowmem_prunes过大时，可以考虑调整query_cache_size的大小。
query_cache_size = 128M
# 单个查询能够使用的QC大小，默认1M
query_cache_limit = 2M
# 分配给QC的最小块大小。默认值4096
query_cache_min_res_unit = 512

# ---------- THREAD CACHE ----------
# 线程缓存大小。控制被缓存的线程数。
# 注：SHOW GLOBAL STATUS LIKE 'Threads%'; //查看与线程相关的状态值
#     1）Threads_cached：线程缓存中的线程数。
#     2）Threads_connected：当前打开的连接数。
#     3）Threads_created：创建用来处理连接的线程数（没有使用线程缓存中的线程时，该状态会增加）。
# 优化建议：当Threads_created过大时可以考虑增大thread_cache_size的值。
thread_cache_size = 1024
# 每个线程创建时分配的堆栈大小。默认192K（32 bits），256K（64 bits）。
# 注：线程使用的堆栈设置太小，会影响线程可处理的SQL语句的复杂程度、存储过程的递归深度及其他消耗内存的行为。
# 优化建议：在大多数场景中，使用默认值即可。在需要大量使用存储过程且循环较深的场景中，可以适当加大该值，但是不推荐。
thread_stack = 256K
# MYSQL线程处理模型。共no-threads、one-thread-per-connection（设为该值时将使用线程缓存）、dynamically-loaded
# dynamically-loaded：开启线程池时使用。（注：MariaDB中该值为pool-of-threads）
thread_handling = one-thread-per-connection

# ---------- THREAD POOL ----------
# 1. 线程池中worker线程处理单位为1个statement。而不是one-thread-per-connection一个线程只处理一个连接。
# 2. 线程池被划分为多个组（N个GROUP）。连接发送的SQL根据线程ID不同分配到不同的GROUP中。因此，一个连接发送的多个SQL是由同一个WORKER线程进行处理的。（SQL对应的线程组可以通过 线程ID % thread_pool_size 取模的算法得出）
# 3. 每个group都有2个任务队列，即优先队列和普通队列如果一个sql所在的事务已经开启，则将任务放到优先队列中，否则放到普通队列中，worker线程优先从优先队列中取任务执行，当优先队列为空则从普通队列取任务执行，这个可以保证已经开启的事务优先得到执行，从而尽早释放其占用的资源（主要是锁），可以有效减小响应时间，别且避免调度上的死锁（A和B被分到不同的group中，A事务已经开启，并且获得了锁，可能无法立即得到调度执行，B事务依赖A事务释放锁资源，但是先于A得到调度）
# 注：下面的参数主要使用在MariaDB中。
# 线程池中线程组的大小。默认值16。
# thread_pool_size = 16
# 该值的作用是防止线程池中的线程出现假死，设定值为timer线程检测间隔时长。
# 若超过该值线程无响应，则线程池会创建新的线程。
# MYSQL5.5：单位10毫秒，默认值6，60ms。阈值4-600，40ms-6s（奇葩的设置）.                
# MariaDB：单位毫秒，默认值500.
# thread_pool_stall_limit = 500
# 线程池中最大线程数量。默认值MDB5.5-10.0为500，10.1后为10000。
# thread_pool_max_threads = 500
# 空闲的线程退出时间间隔。默认值60s。       
# thread_pool_idle_timeout = 60  
# 控制每个Group里同时可以运行多少个任务。5.5一般在128个任务比较好。如16核的机器该值可以设定为8-10.
# thread_pool_oversubscribe = 8       

# ---------- TIMEOUT ----------
# 关闭一个交互连接时等待的秒数。
# 注：该值设置过小会造成一些查询无法返回结果，应用端出现server has gone away的2006错误。
interactive_timeout = 3600
# 关闭空闲连接时等待的秒数。
# 注：该值设置过大会造成并发场景中连接数飙高，或连接线程不释放引起的SLEEP状态线程过多的问题。
wait_timeout = 3600

# ---------- ERROR LOG & SLOW LOG ----------
# 指定GENERAL QUERY LOG和SLOW LOG的存储形式。值为：TABLE（日志记录在表里）、FILE、NONE。默认为FILE。
# 注：如果设置为NONE那该参数要放在所有日志参数的最前面。
log-output = file
# SLOW LOG开关及存放路径。
slow_query_log = 1
slow_query_log_file = $YOUR_SLOWLOG_PATH/mysql-slow.log
# 设置超过n秒的查询记录到SLOW LOG中。默认10S。
long_query_time = 1
# 是否将管理语句记录到慢查询中，如ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX,OPTIMIZE TABLE, REPAIR TABLE。默认0，关闭。
# 优化建议：不建议开启该参数。会加大磁盘IO。
# log-slow-admin-statements = 0
# 是否允许慢日志记录slave sql线程执行的sql语句。
# log-slow-slave-statements = 1

# 错误日志存放路径
log-error = $YOUR_ERRORLOG_PATHmysql-error-log.err
# 是否将警告信息记录到错误日志。默认设定为1，表示启用；设置为0为禁用；而其值为大于1时表示新发起连接时产生的ABORTED CONNECTIONS和ACCESS-DENIED也会被记录到错误日志。
log_warnings = 2

# GENERAL LOG
# general_log = 1
# general_log_file = general.log

# ---------- MYISAM ----------
# MYISAM键缓冲区大小。可以加快索引读及多重写入的速度。默认为8M。
# 优化建议：可以根据自身物理内存的大小增加该值。但是不要超过物理内存的50%。理论上物理内存的25%都可以用做该值。
key_buffer_size = 128M
# MYISAM引擎批量插入暂存使用内存。默认为8M。
# 注：会加速形如 INSERT ... SELECT,INSERT ... VALUES (...), (...) and LOAD DATA INFILE语句。
bulk_insert_buffer_size = 32M
# MYISAM引擎排序缓冲大小。
# 注：实际是指REPAIR TABLE、或使用CREATE INDEX/ALTER TABLE创建索引时，重新排序索引所需的缓冲区大小。
myisam_sort_buffer_size = 128M
# MYISAM重建索引使用的临时文件的最大大小。
# 注：  1）REPAIR TABLE, ALTER TABLE, LOAD DATA INFILE会重建索引。
#       2）该值设置过小时，重建索引操作会使用KEY BUFFER，从而引起读性能下降。
myisam_max_sort_file_size = 10G
# MYSQL修复线程数
myisam_repair_threads = 1
# 允许在实行CREATE TABLE语句时使用DELAY_KEY_WRITE选项，如果表已存在可以使用ALTER TABLE语句修改。控制MYISAM索引延迟写入磁盘。共3个值：OFF/ON/ALL，默认值ON。
# 注：开启后，修改数据时数据文件更改会先刷入磁盘，索引文件更改会暂存于内存中（KEY BUFFER），在表关闭时刷入磁盘。
# 优化建议：1）MYISAM频繁更新的大表（千万级别）可以开启该功能。有效提高写性能。
#           2）由于该参数会导致服务器突然断电后重启服务时表损坏，因此需要配合myisam_recover_options参数使用，防止出现数据不一致的情况。
delay_key_write = ON
# MYISAM引擎恢复模式。默认值OFF。
#   1）OFF 关闭。
#   2）DEFAULT 不使用BACKUP/FORCE/QUICK进行恢复。
#   3）BACKUP 若在恢复过程中数据文件发生改变，则将table_name.MYD文件备份为table_name-datetime.BAK。
#   4）FORCE 强制恢复。可能丢失数据。
#   5）QUICK 如果没有删除块时，不检查表中的行。只有在参数external locking（系统锁，默认4.0版本之后为关闭）关闭的时候起作用。
myisam_recover_options = DEFAULT

# ---------- INNODB ----------
# INNODB缓冲池大小。
# 注：理论值为机器物理内存的75%-85%。该值过小会导致频繁的磁盘IO。
innodb_buffer_pool_size =
# 设置innodb buffer pool被分割成的个数，用来提高并发性，减少锁争用，默认值为1。5.6后改为8.
# 注：设置该参数时innodb_buffer_pool_size必须要大于1G。
innodb_buffer_pool_instances = 1
# INNODB共享表空间文件。
# 注：  1）可以指定多个共享表空间文件，使用；分隔。但是只有最后一个文件支持autoextend或max选项。
#       2）不指定路径时默认存放在MYSQL的data文件夹下。
innodb_data_file_path = $YOUR_INNODBDATAFILE_PATH/ibdata1:1G:autoextend
# 控制innodb调用系统fsync刷盘策略。默认值为1.
# 灾难情况：1）MYSQL CRASH
#           2）OS CRASH
# 取值范围：
#   0：每秒Log Thread将log buffer中的数据写入log file并通知文件系统同步文件。
#      注：由于事务提交时不进行刷盘所以会丢失1S内提交的事务。     
#   1：每个事务提交时Log Thread将log buffer中的数据写入log file并通知文件系统同步文件。
#      注：数据安全性最高。灾难情况不会丢失已提交的数据。
#   2：每个事务提交时Log Thread将log buffer中的数据写入log file但不刷盘，每秒通知文件系统同步文件。
#      注：a）不过由于CPU的进程调度问题，不能保证100%是每秒执行一次flush disk。
#          b）MYSQL CRASH不会丢失事务。但是由于文件系统有cache所以实际是写入cache中，OS CRASH时会丢失没有刷盘的事务。
# 优化建议：1）对数据安全性要求不高的场景不要使用1.设置为2会提高写入性能。
#           2）对数据安全性要求高的场景建议使用1.但是要配合sync_binlog = 1来提高安全性。
innodb_flush_log_at_trx_commit = 2
# INNODB日志缓冲大小。默认值为8M。
# 注：该参数实际是指日志刷盘前在日志缓冲区中存储脏页大小。
# 优化建议：理论最大值4G-1但是建议不要超过64M。因为该缓冲区中的内容会由innodb_flush_log_at_trx_commit参数控制刷新到磁盘上，该值过大会导致灾难情况时的数据丢失。
innodb_log_buffer_size = 32M
# FLUSH LOG BUFFER中脏页的百分比。
# 优化建议：推荐值区间为75%-95%。如果写入数据为冷数据的场景可以将该值调低，为热数据是可以将该值调高。
innodb_max_dirty_pages_pct = 75
# INNODB LOG FILE（ib_logfileN）大小。
# 注：LOG FILE中存放的是REDO LOG信息。
# 优化建议：1）取值范围可以在1M-1/N BUFFER_POOL_SIZE之间（N为每个日志组中的日志数）推荐256M以上，但不要超过1G，过大的LOG FILE会使恢复速度变慢。
#           2）过小的LOG FILE会使CHECKPOINT比较频繁，导致刷新脏页到磁盘的次数增加，在刷新时整个系统变慢。
#           3）合理值计算方法：
#               a）通过SHOW ENGINE INNODB STATUS\G命令获得Log sequence number和Last checkpoint at值，求差记为RESULTS。
#               b）由公式(innodb_log_file_size * innodb_log_files_in_group(default 2)) * 0.75 = RESULTS，求得innodb_log_file_size。
innodb_log_file_size = 512M
# INNODB日志组。默认值为2.
innodb_log_files_in_group = 3
# INNODB日志存放路径。
innodb_log_group_home_dir = $YOUR_LOGFILE_PATH
# 控制Innodb checkpoint时的IO能力，默认值是200。
# 优化建议：一般可以按一块SAS 15K转的磁盘按200计算，6块SAS做Raid10则可以设为600。如果普通的SATA一块盘只能按100算。ssd可以为2000或更高。
innodb_io_capacity = 2000
# 控制INNODB DATAFILE 及 REDO LOG的打开、刷新模式。
# 取值范围：
#   1）fsync(默认)：调用系统级fsync()刷写数据文件与redo log的innodb log buffer。
#   2）O_DSYNC：innodb使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件。在UNIX系统中问题较多，不推荐使用。
#   3）O_DIRECT：innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log。
# 注：
#   1）fsync(int fd)函数：该函数作用是执行flush操作时，将与fd文件描述符所指文件有关的buffer刷写到磁盘中。并且刷写完元数据的信息（比如修改日期、创建日期等）后执行完毕。
#   2）见图。
innodb_flush_method = O_DIRECT
# 开启独立表空间。5.6后默认开启。
innodb_file_per_table = 1
# 事务超时会导致InnoDB中止并回滚整个事务.默认关闭。
innodb_rollback_on_timeout = 1
# 用于灾难恢复的选项。默认值为0.
# 注：没有特殊情况时一定设置为0.大于0的值会导致无法写入。
innodb_force_recovery = 0

# ---------- REPLICATION & BINLOG ----------
# 服务器id。最好使用某种固定格式。复制标识。
server-id =
# binlog格式 [STATEMENT(基于语句)|ROW(基于行)|MIXED(混合模式)]
# 注：基于行的复制有一些缺点
binlog_format = row
# binlog位置及binlog index位置.
log-bin = mysql-bin
log_bin_index = mysql-bin.index
# 指定binlog文件最大容量。超过设定值后MYSQL会自动切换binlog。
max_binlog_size = 1G
# binlog缓存大小。
# 注：该值针对每个线程分配，设置过大会影响并发性能。
binlog_cache_size = 4M
# binlog 能够使用的最大cache内存大小。
max_binlog_cache_size = 2G
# 控制binlog同步到磁盘的策略。默认值为0.
# 取值范围：
#   0：当事务提交之后，MySQL不调用fdatasync()函数刷新binlog_cache中的信息到磁盘，而让文件系统决定什么时候来做同步，或者在binlog_cache满了后才同步到磁盘。
#   N：每向binlog中写入N次后，才调用fdatasync()函数。
# 注：如果启用了autocommit，那么每条语句都算作一次写入binlog。未开启时每个事务算作一次写入binlog。
# 优化建议：1）对数据安全性要求不高时建议使用默认值。
#           2）innodb_flush_log_at_trx_commit = 1 且 sync_binlog = 1时写性能最差但安全性最高。
#           3）N > 1时的写性能比N = 0时高。
sync_binlog = 0
# 开启后在binlog中记录annotate event，用来在ROW模式下展示SQL语句。
binlog_annotate_row_events = 1
# 过滤选项
# binlog-do-db = $YOUR_DB_NAME
# binlog-ignore-db = $YOUR_DB_NAME
# binlog-wild-do/ignore-db = $YOUR_DB_NAME
# binlog自动删除时间。
expire_logs_days = 7

# ---------- REPLICATION & RELAYLOG（JUST FOR SLAVE）----------
# 设置MYSQL服务启动时不自动开启复制。
# 注：多数场景不要开启。
# skip_slave_start = 1
# 过滤选项。
# replicate_do_db = $YOUR_DB_NAME
# replicate_ignore_db = $YOUR_DB_NAME
# replicate-wild-do/ignore-db = $YOUR_DB_NAME
# 最大中继日志大小。
# max_relay_log_size = 1G
# relay_log_purge = 1
# relay_log_recovery = 1
# 开启从库上的更新记录到从库自己的binlog中。默认为0或OFF。
# 注：1）M-M架构：建议都开启该参数。防止自动切换时无法记录binlog导致数据丢失。
#     2）M-S1-S2架构：作为中继层的从库S1必须要开启。这样S2才会从S1中得到binlog更新。
#     3）由于MYSQL的binlog会记录执行SQL的源server_id。所以在1这种情况下，两台M都开启也不会形成循环复制。
# log_slave_updates = 1
# 开启后在relay-log中记录annotate event，用来在ROW模式下展示SQL语句。
# 注：只能在开启log_slave_update参数的从库上使用。
replicate_annotate_row_events = 1
# 从库SQL线程跳过的错误号。
# slave-skip-errors = 1032,1053,1062

[mysqldump]
quick
# 导出时指定单表最大文件
max_allowed_packet = 1G