互联网金融MySQL优化参数标准

发布时间：2021-01-16 03:11 所属栏目：53 来源：网络整理

导读：《互联网金融MySQL优化参数标准》要点：本文介绍了互联网金融MySQL优化参数标准，希望对您有用。如果有疑问，可以联系我们。作者介绍张小虎, 甜橙金融持久化组负责人,多年数据库优化、运维及开发经验.擅长不同业务场景下的数据库架构设计及持久化解决方案

《互联网金融MySQL优化参数标准》要点：
本文介绍了互联网金融MySQL优化参数标准，希望对您有用。如果有疑问，可以联系我们。

作者介绍

张小虎,甜橙金融持久化组负责人,多年数据库优化、运维及开发经验.擅长不同业务场景下的数据库架构设计及持久化解决方案.

前言

日常的MySQL运维中说起调优,MySQL的配置文件my.cnf是不可忽略的.MySQL的默认参数并不能满足我们日常线上业务的需求,因此对参数进行优化也是不可缺少的环节.这里不想列出my.cnf配置中有多少项和每一项的意思,这些都可以在官方文档上查到.以下仅对日常工作用应该注意的一些参数进行说明.

下面针对一些参数进行说明.当然还有其它的设置可以起作用,取决于你的负载或硬件：在慢内存和快磁盘、高并发和写密集型负载情况下,你将需要特殊的调整.然而这里的目标是让你可以快速地获得一个稳健的MySQL配置,而不用花费太多时间在调整一些无关紧要的MySQL设置或读文档,找出哪些设置对你来说是重要的.

InnoDB配置

从MySQL 5.5版本开始,InnoDB就是默认的存储引擎并且它比任何其它存储引擎的使用要多得多.那也是为什么它需要小心配置的原因.

1.innodb_file_per_table

表的数据和索引存放在共享表空间里或者单独表空间里.我们的工作场景安装是默认设置了innodb_file_per_table = ON,这样也有助于工作中进行单独表空间的迁移工作.MySQL 5.6中,这个属性默认值是ON.

2.innodb_flush_log_at_trx_commit

默认值为1,表示InnoDB完全支持ACID特性.当你的主要关注点是数据安全的时候这个值是最合适的,比如在一个主节点上.但是对于磁盘(读写)速度较慢的系统,它会带来很巨大的开销,因为每次将改变flush到redo日志都需要额外的fsyncs.

如果将它的值设置为2会导致不太可靠(unreliable).因为提交的事务仅仅每秒才flush一次到redo日志,但对于一些场景是可以接受的,比如对于主节点的备份节点这个值是可以接受的.如果值为0速度就更快了,但在系统崩溃时可能丢失一些数据：只适用于备份节点.说到这个参数就一定会想到另一个sync_binlog.

3.innodb_flush_method

这项配置决定了数据和日志写入硬盘的方式.一共有三种方式,我们默认使用O_DIRECT?.O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的,并不用通过操作系统的缓冲,而真正的完成也是在flush这步,日志还是要经过OS缓冲.

4.innodb_log_buffer_size

这项配置决定了为尚未执行的事务分配的缓存.其默认值(1MB)一般来说已经够用了,但是如果你的事务中包含有二进制大对象或者大文本字段的话,这点缓存很快就会被填满并触发额外的I/O操作.看看Innodb_log_waits状态变量,如果它不是0,增加innodb_log_buffer_size.

5.innodb_buffer_pool_size

这个参数应该是运维中必须关注的了.缓冲池是数据和索引缓存的地方,它属于MySQL的核心参数,默认为128MB,正常的情况下这个参数设置为物理内存的60%~70%.(不过我们的实例基本上都是多实例混部的,所以这个值还要根据业务规模来具体分析.)

6.innodb_log_file_size

这是redo日志的大小.redo日志被用于确保写操作快速而可靠并且在崩溃时恢复.如果你知道你的应用程序需要频繁地写入数据并且你使用的是MySQL 5.6,那么你可以一开始就把它这是成4G.(具体大小还要根据自身业务进行适当调整)

7.innodb_support_xa

innodb_support_xa可以开关InnoDB的XA两段式事务提交.默认情况下,innodb_support_xa=true,支持XA两段式事务提交.由于XA两段式事务提交导致多余flush等操作,性能影响会达到10%,所有为了提高性能,有些DBA会设置innodb_support_xa=false.这样的话,redolog和binlog将无法同步,可能存在事务在主库提交,但是没有记录到binlog的情况.这样也有可能造成事务数据的丢失.

8.innodb_additional_mem_pool_size

该参数用来存储数据字段信息和其他内部数据结构.表越多,需要在这里分配的内存越多.如果InnoDB用光了这个池内的内存,InnoDB开始从操作系统分配内存,并且往MySQL错误日志写警告信息,默认8MB.一般设置16MB.

9.max_connections

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的内存分配.

10.server-id

复制架构时确保 server-id 要不同,通常主ID要小于从ID.

11.log_bin

（编辑：ASP站长网）