共计 4784 个字符，预计需要花费 12 分钟才能阅读完成。
日常的 MySQL 运维中说起调优，MySQL 的配置文件 my.cnf 是不可忽略的。MySQL 的默认参数并不能满足我们日常线上业务的需求，因此对参数进行优化也是不可缺少的环节。这里不想列出 my.cnf 配置中有多少项和每一项的意思，这些都可以在官方文档上查到。以下仅对日常工作用应该注意的一些参数进行说明。
下面针对一些参数进行说明。当然还有其它的设置可以起作用，取决于你的负载或硬件：在慢内存和快磁盘、高并发和写密集型负载情况下，你将需要特殊的调整。然而这里的目标是让你可以快速地获得一个稳健的 MySQL 配置，而不用花费太多时间在调整一些无关紧要的 MySQL 设置或读文档，找出哪些设置对你来说是重要的。
从 MySQL 5.5 版本开始，InnoDB 就是默认的存储引擎并且它比任何其它存储引擎的使用要多得多。那也是为什么它需要小心配置的原因。
表的数据和索引存放在共享表空间里或者单独表空间里。我们的工作场景安装是默认设置了 innodb_file_per_table = ON，这样也有助于工作中进行单独表空间的迁移工作。MySQL 5.6 中，这个属性默认值是 ON。
默认值为 1，表示 InnoDB 完全支持 ACID 特性。当你的主要关注点是数据安全的时候这个值是最合适的，比如在一个主节点上。但是对于磁盘（读写）速度较慢的系统，它会带来很巨大的开销，因为每次将改变 flush 到 redo 日志都需要额外的 fsyncs。
如果将它的值设置为 2 会导致不太可靠（unreliable）。因为提交的事务仅仅每秒才 flush 一次到 redo 日志，但对于一些场景是可以接受的，比如对于主节点的备份节点这个值是可以接受的。如果值为 0 速度就更快了，但在系统崩溃时可能丢失一些数据：只适用于备份节点。说到这个参数就一定会想到另一个 sync_binlog。
这项配置决定了数据和日志写入硬盘的方式。一共有三种方式，我们默认使用 O_DIRECT。O_DIRECT 模式：数据文件的写入操作是直接从 mysql innodb buffer 到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在 flush 这步，日志还是要经过 OS 缓冲。
这项配置决定了为尚未执行的事务分配的缓存。其默认值（1MB）一般来说已经够用了，但是如果你的事务中包含有二进制大对象或者大文本字段的话，这点缓存很快就会被填满并触发额外的 I / O 操作。看看 Innodb_log_waits 状态变量，如果它不是 0，增加 innodb_log_buffer_size。
这个参数应该是运维中必须关注的了。缓冲池是数据和索引缓存的地方，它属于 MySQL 的核心参数，默认为 128MB，正常的情况下这个参数设置为物理内存的 60%~70%。（不过我们的实例基本上都是多实例混部的，所以这个值还要根据业务规模来具体分析。）
这是 redo 日志的大小。redo 日志被用于确保写操作快速而可靠并且在崩溃时恢复。如果你知道你的应用程序需要频繁地写入数据并且你使用的是 MySQL 5.6，那么你可以一开始就把它这是成 4G。（具体大小还要根据自身业务进行适当调整）
innodb_support_xa 可以开关 InnoDB 的 XA 两段式事务提交。默认情况下，innodb_support_xa=true，支持 XA 两段式事务提交。由于 XA 两段式事务提交导致多余 flush 等操作，性能影响会达到 10%，所有为了提高性能，有些 DBA 会设置 innodb_support_xa=false。这样的话，redolog 和 binlog 将无法同步，可能存在事务在主库提交，但是没有记录到 binlog 的情况。这样也有可能造成事务数据的丢失。
该参数用来存储数据字段信息和其他内部数据结构。表越多，需要在这里分配的内存越多。如果 InnoDB 用光了这个池内的内存，InnoDB 开始从操作系统分配内存，并且往 MySQL 错误日志写警告信息，默认 8MB。一般设置 16MB。
MySQL 服务器默认连接数比较小，一般也就 100 来个最好把最大值设大一些。一般设置 500~1000 即可每一个链接都会占用一定的内存，所以这个参数也不是越大越好。有的人遇到 too many connections 会去增加这个参数的大小，但其实如果是业务量或者程序逻辑有问题或者 sql 写的不好，即使增大这个参数也无济于事，再次报错只是时间问题。在应用程序里使用连接池或者在 MySQL 里使用进程池有助于解决这一问题。
- Seesion 级的内存分配
max_threads(当前活跃连接数)* (
read_buffer_size– 顺序读缓冲，提高顺序读效率
+read_rnd_buffer_size– 随机读缓冲，提高随机读效率
+sort_buffer_size– 排序缓冲，提高排序效率
+join_buffer_size– 表连接缓冲，提高表连接效率
+binlog_cache_size– 二进制日志缓冲，提高二进制日志写入效率ß
+tmp_table_size– 内存临时表，提高临时表存储效率
+thread_stack– 线程堆栈，暂时寄存 SQL 语句 / 存储过程
+thread_cache_size– 线程缓存，降低多次反复打开线程开销
+net_buffer_length– 线程持连接缓冲以及读取结果缓冲
+bulk_insert_buffer_size– MyISAM 表批量写入数据缓冲
)- global 级的内存分配
global buffer(全局内存分配总和) =
innodb_buffer_pool_size
— InnoDB 高速缓冲，行数据、索引缓冲，以及事务锁、自适应哈希等
+ innodb_additional_mem_pool_size
— InnoDB 数据字典额外内存，缓存所有表数据字典
+innodb_log_buffer_size
— InnoDB REDO 日志缓冲，提高 REDO 日志写入效率
+key_buffer_size
— MyISAM 表索引高速缓冲，提高 MyISAM 表索引读写效率
+query_cache_size
–查询高速缓存，缓存查询结果，提高反复查询返回效率 +table_cahce — 表空间文件描述符缓存，提高数据表打开效率
+table_definition_cache
–表定义文件描述符缓存，提高数据表打开效率 参数的优化最终目的是让 MySQL 更好地利用资源通过合理地控制内存的分配，合理的 CPU 使用建议降低 Session 的内存分配。
复制架构时确保 server-id 要不同，通常主 ID 要小于从 ID。
如果你想让数据库服务器充当主节点的备份节点，那么开启二进制日志是必须的。如果这么做了之后，还别忘了设置 server_id 为一个唯一的值。就算只有一个服务器，如果你想做基于时间点的数据恢复，这（开启二进制日志）也是很有用的：从你最近的备份中恢复（全量备份），并应用二进制日志中的修改（增量备份）。
二进制日志一旦创建就将永久保存。所以如果你不想让磁盘空间耗尽，你可以用 PURGE BINARY LOGS 来清除旧文件，或者设置 expire_logs_days 来指定过多少天日志将被自动清除。记录二进制日志不是没有开销的，所以如果你在一个非主节点的复制节点上不需要它的话，那么建议关闭这个选项。
当客户端连接数据库服务器时，服务器会进行主机名解析，并且当 DNS 很慢时，建立连接也会很慢。因此建议在启动服务器时关闭 skip_name_resolve 选项而不进行 DNS 查找。唯一的局限是之后 GRANT 语句中只能使用 IP 地址了，因此在添加这项设置到一个已有系统中必须格外小心。
sync_binlog 的默认值是 0，像操作系统刷其他文件的机制一样，MySQL 不会同步到磁盘中去而是依赖操作系统来刷新 binary log。
当 sync_binlog =N (N>0)，MySQL 在每写 N 次二进制日志 binary log 时，会使用 fdatasync() 函数将它的写二进制日志 binary log 同步到磁盘中去。当 innodb_flush_log_at_trx_commit 和 sync_binlog 都为 1 时是最安全的，在 mysqld 服务崩溃或者服务器主机 crash 的情况下，binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双 1 会导致频繁的 IO 操作，因此该模式也是最慢的一种方式。出于我们的业务考虑在业务压力允许的情况下默认的都是双 1 配置。
当业务中需要使用级联架构的时候 log_slave_update = 1 这个参数必须打开，否者第三级可能无法接收到第一级产生的 binlog，从而无法进行数据同步。
如果内存临时表超出了限制，MySQL 就会自动地把它转化为基于磁盘的 MyISAM 表，存储在指定的 tmpdir 目录下. 因此尽可能将 tmpdir 配置到性能好速度快的存储设备上。
slow_query_log = 1 #打开慢日志
slow_query_log_file = /mysql/log/mysql.slow
long_query_time = 0.5 #设置超过多少秒的查询会入慢日志
随着科学技术的发展，越来越多的存储设备开始由传统的机械组件转向由电子元件组成的永久存储，且价钱越来越能让企业接受。存储组件速度提升后，再用传统机械组件的 DB 配置就显得浪费了，所以就需要针对不同的存储技术对 MySQL 配置作出调整，比如 innodb_io_capacity 需要调大，日志文件和 redo 放到机械硬盘，undo 放到 SSD，atomic write 不需要 Double Write Buffer，InnoDB 压缩，单机多实例 +cgroup 等等。分析 I/O 情况，动态调整 innodb_io_capacity 和 innodb_max_dirty_pages_pct；试图调整 innodb_adaptive_flushing，查看效果。
针对 innodb_write_io_threads 和 innodb_read_io_threads 的调优我们目前没有做，但我相信调整为 8 或者 16，系统 I/O 性能会更好。还有，需要注意以下几点：任何一个调整，都要建立在数据的支撑和严谨的分析基础上，否则都是空谈；这类调优是非常有意义的，是真正能带来价值的，所以需要多下功夫，并且尽可能地搞明白为什么要这么调整。
- Innodb_thread_concurrency=0
- Innodb_sync_spin_loops=288
- table_definition_cache=2000
- Innodb_flush_method 建议用 O_DIRECT
- Innodb_io_capacity 设置成磁盘支持最大 IOPS
- Innodb_wirte_io_threads=8
- Innodb_read_io_threads=8
- Innodb_purge_threads=1
- Innodb 的预读方面，如果基于主建或是唯一索引的系统，建议禁用预读
- Innodb_random_read_ahead = off
