文档章节

Mysql参数调优

yeahcheung
 yeahcheung
发布于 2015/04/13 00:05
字数 1183
阅读 21
收藏 1

innodb_buffer_pool_size

这是mysql/innodb调优中最重要的参数。mysql把磁盘中的数据缓存到内存中以节省访问时间,它使用这个参数来决定它自己可以使用多少内存。在高负载的环境中(如只读服务器),需要尽可能的把这个参数调整到最大来避免使用交换区。推荐值是服务器内存的90%。

在写负载很高的机器上或只负责备份的从服务器上这个参数远没有这么重要,可能设置能合适的值以节省内存。


innodb_flush_log_at_trx_commit

这个设置是用来在I/O性能与轻微数据丢失(1秒)之间取得平衡的一个参数。当参数设置为1时,mysql会立刻写入数据更改到硬盘中的InnoDB 日志文件。然而每次事务都写入日志到硬盘是很慢的,也会使用珍贵的IOPS(普通硬盘为200 IOPS)。

在主库上,这个参数应该始终设置为1,设置为其它值是有可能数据丢失。

在从库上,这个参数可以设置为2,mysql会立即写入数据变更到InnoDB日志文件,但允许系统缓存写入数据到内存中,每台进程每秒运行一次调用fsync写入数据到硬盘。这样从服务器上最多丢失一秒的数据。但这样换来的是从服务器上节省了很多的IOPS来处理数据查询。

innodb_flush_method 

这个参数有许多可选项,我们只需简单的设置为O_DIRECT即可。告诉系统文件系统无需缓存任何Mysql数据库的数据,因为

innodb_buffer_pool_size已经做了缓存的工作。如果不设置为O_DIRECT,会缓存两份数据,造成内存浪费。

sync_binlog 

此参数用来记录非SELECT查询日志。它是MySQL复制中非常重要的一个组合。在主库上,它应该被设置为1,用来空间里记录SQL执行日志,以防止数据丢失及数据库崩溃后没有记录日志造成从服务器上数据不全。

从库上此参数应该设置为0或移除,如果需要记录更新日志可以设置log_slave_updates参数。

innodb_log_file_size

此参数用来做数据存储时的缓冲。由于直接把数据写入mysql数据文件中需要计算过程和随机IO操作,不能立即响应用户,可以先把数据写入日志文件中,随后写入到数据文件。

如果日志文件过小,InnoDB不能有效的组合和排列要写入数据。如果文件过大,数据恢复时则需要很长的时间。

最佳值:数据写入高峰期时一个小时内的写入量。

show global status where variable_name like "%Innodb_os_log_written%"; 
#value: 234825649664
select sleep(60);
show global status where variable_name like "%Innodb_os_log_written%";
#value: 234828058624
	
#234828058624-234825649664=2408960bytes
#2.29MB*60minutes=137.4MB 
#innodb有两个独立的日志文件,每个日志文件大小大概可以设置为 	137.4/2 = 70M

更改参数后,停止mysql服务器。删除 ib_logfile0和ib_logfile1后开启mysql服务器。 

max_connections 

max_connections = pm.max_children * number of application servers

PS: Mysql为每个连接创建一个线程,大概占用192KB的内存,如果最大连接数据为2000时,大概需要400MB的内存。确保在最大连接时有足够的内存。总内存 - 系统所有内存 - innodb_buffer_pool_size > 最大连接所需内存

innodb_io_capacity

告诉innodb硬盘允许最大IOPS是多少, 防止IO阻塞或IO不能有效利用。

PS:IOPS测试,此处假如你的/mysql为你的mysql安装目录

apt-get instal fio
cd /mysql
mkdir /mysql/fio
vi random-rw.fio
[random_rw]
rw=randrw
size=128M(好磁盘可以做更改)
directory=/mysql/fio

fio random-rw.fio

mysql使用的IO类型

  1. 连接IO用于InnnoDB 日志文件写入

  2. 很多随机IO用于InnoDB 数据文件写入

  3. 很多随机IO用于InnoDB 数据文件读取

innodb_read_io_threads 与 innodb_write_io_threads

InnoDB使用后台线程管理数据读取和写入工作。默认设置为4个。应该设置为4 x CPU内核数

query_cache_size

优点:缓存SQL解析、查询数据到内存中

缺点:

  1. 由于不能智能的处理过期数据,数据库每次有变更时需要刷新缓存。在数据更改较多是影响性能。

  2. 缓存刷新时使用唯一的全局锁,数据变更是影响性能。

PS:数据变更频繁时禁用缓存,把query_cache_size 和 query_cache_type 设置 0.因为InnoDB有自己内部的缓存机制,并且没有此限制。

XtraDB/InnoDB与 MyISAM比较

MyISAM缺点:在写入时锁定整张表,影响其他并发的读取和写入。


© 著作权归作者所有

yeahcheung
粉丝 1
博文 19
码字总数 7150
作品 0
西安
架构师
私信 提问
MySQL · 特性分析 · Statement Digest

背景 在对数据库进行性能调优的时候,除了参数、配置的调整以外,SQL调优也是重要的手段,同时也是收益最大的一环。 当DBA对业务库进行sql调优的时候,如何做到有的放矢,投入产出受益最大?...

阿里云RDS-数据库内核组
2015/11/03
0
0
腾讯云CDB的AI技术实践:CDBTune

欢迎大家前往腾讯云+社区,获取更多腾讯海量技术实践干货哦~ 作者:邢家树,高级工程师,目前就职于腾讯TEG基础架构部数据库团队。腾讯数据库技术团队维护MySQL内核分支TXSQL,100%兼容原生M...

腾讯云加社区
2018/06/05
0
0
centos7+mysql5.7二进制安装

一、优化部分 1、操作系统参数调优 2、数据库参数调优 3、防火墙设置等 二、安装部分 1、创建用户和组 # groupadd mysql useradd -g mysql mysql 2、到安装目录,解压安装文件 #cd /data ta...

francisxys
2018/05/18
0
0
性能调优概述

大纲: 一、概述 二、什么是性能调优?(what) 三、为什么需要性能调优?(why) 四、什么时候需要性能调优?(when) 五、什么地方需要性能调优?(where) 六、什么人来进行性能调优?(who) 七、怎...

陈明乾
2014/07/14
0
0
Centos下多种PHP拓展安装方法

Centos下,PHP有多种方式来安装拓展, 主要有 包管理式的yum安装、pecl安装, 以及底层的源码编译安装。 包管理式的安装卸载尤为方便,而源码编译式的安装则方便参数调优。 一般的搭建本机开...

麦拂沙
2015/10/17
3.4K
0

没有更多内容

加载失败,请刷新页面

加载更多

全面兼容IE6/IE7/IE8/FF的CSS HACK写法

浏览器市场的混乱,给设计师造成很大的麻烦,设计的页面兼容完这个浏览器还得兼容那个浏览器,本来ie6跟ff之间的兼容是很容易解决的。加上个ie7会麻烦点,ie8的出现就更头疼了,原来hack ie...

前端老手
7分钟前
3
0
常用快递电子面单批量打印api接口对接demo-JAVA示例

目前有三种方式对接电子面单: 1.快递公司:各家快递公司逐一对接接口 2.菜鸟:支持常用15家快递电子面单打印 3.快递鸟:仅对接一次,支持常用30多家主流快递电子面单打印 目前也是支持批量打...

程序的小猿
10分钟前
5
0
Yii 框架中rule规则必须搭配验证函数才能使用

public $store_id;public $user_id;public $page;public $limit;public $list;public $mch_list;public $cart_id;public $is_community;public $shop_id;public $cart_typ......

chenhongjiang
13分钟前
2
0
Flutter使用Rammus实现阿里云推送

前言: 最近新的Flutter项目有“阿里云推送通知”的需求,就是Flutter的App启动后检测到有新的通知,点击通知栏然后跳转到指定的页面。在这里我使用的是第三方插件Rammus来实现通知的推送,之...

EmilyWu
13分钟前
26
0
Knative 实战:三步走!基于 Knative Serverless 技术实现一个短网址服务

短网址顾名思义就是使用比较短的网址代替很长的网址。维基百科上面的解释是这样的: 短网址又称网址缩短、缩短网址、URL 缩短等,指的是一种互联网上的技术与服务,此服务可以提供一个非常短...

阿里巴巴云原生
28分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部