前言说起日常MySQL运维中的调优，不能忽略MySQL配置文件my.cnf。MySQL默认的参数不能满足我们日常线上业务的需求，所以优化参数也是必不可少的环节。这里我不想列出my.cnf配置中有多少项以及每一项的含义，这些都可以在官方文档中找到。下面仅介绍一些日常工作中需要注意的参数。下面描述了一些参数。当然还有其他设置可以发挥作用，这取决于您的工作负载或硬件：在内存慢和磁盘快、高并发和写入密集型工作负载的情况下，您将需要进行特殊调整。然而，这里的目标是让您可以快速获得强大的MySQL配置，而无需花费太多时间调整无关紧要的MySQL设置或阅读文档来确定哪些设置对您很重要。InnoDB配置从MySQL5.5开始，InnoDB是默认的存储引擎，它的使用比任何其他存储引擎都多。这就是它需要仔细配置的原因。1innodb_file_per_table表的数据和索引存放在共享表空间或单独的表空间中。我们的工作场景安装是默认设置innodb_file_per_table=ON，这样也有助于工作中个别表空间的迁移。在MySQL5.6中，该属性的默认值为ON。2innodb_flush_log_at_trx_commit默认值为1，表示InnoDB完全支持ACID特性。当您主要关注数据安全时（例如在主节点上），此值最合适。但是对于磁盘（读写）速度较慢的系统来说，会带来巨大的开销，因为每次将变化刷新到redolog时，都需要额外的fsync。将其值设置为2将导致不太可靠（不可靠）。因为提交的事务每秒只刷到重做日志一次，但是对于一些场景是可以接受的，比如主节点的备份节点，这个值是可以接受的。如果值为0，则速度较快，但在系统崩溃时可能会丢失部分数据：仅适用于备份节点。说到这个参数，肯定会想到另一个sync_binlog。3innodb_flush_method该配置决定了数据和日志如何写入硬盘。共有三种方式，我们默认使用O_DIRECT。O_DIRECT模式：数据文件的写操作是直接从mysqlinnodbbuffer到磁盘，不经过操作系统的buffer，真正完成也是在flush这一步，日志还是需要缓冲的由操作系统。4innodb_log_buffer_size该配置决定了为尚未执行的事务分配的缓存。它的默认值(1MB)通常就足够了，但如果您的事务包含二进制大型对象或大型文本字段，缓存将很快填满并触发额外的I/O操作。查看innodb_log_waits状态变量，如果不为0，增加innodb_log_buffer_size。5运维时需要注意参数innodb_buffer_pool_size。缓冲池是缓存数据和索引的地方。属于MySQL的核心参数。默认值为128MB。一般情况下，该参数设置为物理内存的60%~70%。（不过我们的实例基本都是多实例混合，所以这个值需要根据业务规模来分析）6innodb_log_file_size这是redolog的大小。重做日志用于确保在发生崩溃时快速可靠的写入操作和恢复。如果你知道你的应用程序需要频繁写入数据并且你使用的是MySQL5.6，那么你可以从一开始就将其设置为4G。（具体大小要根据自己的业务适当调整）7innodb_support_xainnodb_support_xa可以切换InnoDB的XA两阶段事务提交。默认情况下，innodb_support_xa=true支持XA两阶段事务提交。由于XA两阶段事务提交导致flush等冗余操作，性能影响将达到10%。因此，有些DBA为了提高性能，会设置innodb_support_xa=false。这种情况下redolog和binlog不会同步，可能会出现事务在主库提交了，而binlog没有记录的情况。这也可能导致交易数据丢失。8innodb_additional_mem_pool_size该参数用于存储数据字段信息和其他内部数据结构。表越多，这里需要分配的内存就越多。如果InnoDB用完了这个池中的内存，InnoDB开始从操作系统分配内存，并向MySQL错误日志写入一条警告消息，默认为8MB。一般设置为16MB。9max_connectionsMySQL服务器默认的连接数比较少，一般只有100***左右，设置max_connections大一些。一般设置500~1000就够了。每个链接都会占用一定的内存，所以参数越大越好。有的人遇到连接数过多会加大这个参数的大小，但实际上如果业务量或者程序逻辑有问题或者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语句/存储process+thread_cache_size--线程缓存，减少重复开启线程的开销+net_buffer_length--线程连接缓冲区和读取结果缓冲区+bulk_insert_buffer_size--MyISAM表批量写入数据缓冲区)全局级内存分配globalbuffer(全局内存分配Sum)=innodb_buffer_pool_size--InnoDB高速缓冲，行数据，索引缓冲，事务+innodb_additional_mem_pool_size--InnoDB数据字典附加内存，缓存所有表数据字典+innodb_log_buffer_size--InnoDBREDO日志缓冲，提高REDOLog写入效率+key_buffer_size--MyISAM表索引缓存，提高MyISAM表索引读取和读取writeefficiency+query_cache_size--查询缓存，缓存查询结果，提高重复查询返回效率+table_cahce--表空间文件描述符缓存，提高数据表打开效率+table_definition_cache--表定义文件描述符缓存，提高数据表打开效率极致参数优化的目标是让MySQL更好的利用资源。通过合理控制内存分配，建议减少Session内存，合理占用CPU。分发。10server-id复制架构时确保server-id不同，通常masterID小于slaveID。11log_bin如果想让数据库服务器作为主节点的备份节点，需要开启二进制日志。如果这样做，请不要忘记将server_id设置为唯一值。即使只有一台服务器，如果您想进行时间点数据恢复，这（启用二进制日志记录）也很有用：从最近的备份（完整备份）恢复，并应用二进制日志中的更改（增量备份）.二进制日志一旦创建就永久保存。所以如果你不想用完磁盘空间，你可以使用PURGEBINARYLOGS来清除旧文件，或者设置expire_logs_days指定天数后日志将被自动清除。记录二进制日志不是免费的，所以如果你在非主副本节点上不需要它，建议关闭这个选项。12skip_name_resolve客户端连接数据库服务器时，服务器会解析主机名，DNS慢时，连接的建立也会慢。因此，建议在没有DNS查找的情况下启动服务器时关闭skip_name_resolve选项。唯一的限制是GRANT语句后面只能使用IP地址，因此在将此设置添加到现有系统时必须小心。13sync_binlogsync_binlog默认值为0。和操作系统刷新其他文件的机制一样，MySQL不会同步到磁盘而是依赖操作系统来刷新二进制日志。当sync_binlog=N(N>0)时，MySQL会每隔N次使用fdatasync()函数将其写入的binarylog二进制日志同步到磁盘。innodb_flush_log_at_trx_commit和sync_binlog都为1时最安全。在mysqld服务崩溃或服务器主机崩溃的情况下，二进制日志最多只能丢失一条语句或一个事务。但是鱼和熊掌不可兼得，双1会导致频繁的IO操作，所以这种模式也是最慢的方式。出于我们业务的考虑，在业务压力允许的情况下，默认配置为double1。14log_slave_update当业务中需要级联架构时，必须开启参数log_slave_update=1，否则第三级可能接收不到第一级产生的binlog，从而无法进行数据同步。15tmpdir如果内存临时表超过限制，MySQL会自动将其转换为基于磁盘的MyISAM表，并存储在指定的tmpdir目录中。因此，tmpdir尽量配置在性能好、速度快的存储设备上。16慢日志相关slow_query_log=1#打开慢日志slow_query_log_file=/mysql/log/mysql.slowlong_query_time=0.5#设置多少秒查询会进入慢日志由电子元器件组成的端存储，价格越来越为企业所接受。存储组件提速后，再使用传统机械组件的DB配置是一种浪费。因此，需要针对不同的存储技术调整MySQL配置。对于SSD，原子写不需要DoubleWriteBuffer、InnoDB压缩、单机多实例+cgroup等，分析I/O情况，动态调整innodb_io_capacity和innodb_max_dirty_pages_pct；尝试调整innodb_adaptive_flushing看看效果。2线程池设置innodb_write_io_threads和innodb_read_io_threads目前我们还没有做调优，但是我相信如果调到8或者16，系统I/O性能会更好。此外，还需要注意以下几点：任何调整都必须有数据支持和严谨分析，否则就是空谈；这种调整是非常有意义的，能够真正带来价值。所以你需要更加努力，并且尽可能去理解为什么你需要做出这样的调整。3CPU相关Innodb_thread_concurrency=0Innodb_sync_spin_loops=288table_definition_cache=20004IO相关Innodb_flush_method推荐使用O_DIRECTInnodb_io_capacity设置为磁盘支持***IOPSInnodb_wirte_io_threads=8Innodb_read_io_threads=8Innodb_purge是唯一基于primaryread方面的如果Innodb_read_io_threads=8Innodb_purge对于索引系统，建议禁用预读Innodb_random_read_ahead=off
