mysql 开发进阶篇系列 19 MySQL Server(innodb_flush_log_at_trx_commit与sync_binlog)

发布时间:2018-11-16  栏目:MySQL  评论:0 Comments

于这篇开始,讲innodb存储引擎中,对于几只基本点之服务器参数配置。这些参数为innodb_xx
开头。

一. innodb_flush_log_at_trx_commit

 这个参数名称有只log,一看便是和日志有关。是乘:用来支配缓冲区(log
buffer)中之数目写入到日志文件(log
file),以及日志文件数量刷新到磁盘(flush)的操作会。对这参数的装置值,可以本着数据库在性及数码安全间,进行折中。 

  参数值解释:

    当参数是0:日志缓冲数据会,每秒一蹩脚地写入到日志文件,并且将日记文件刷新到磁盘操作。该模式下于业务提交的时节,不会见积极点写副磁盘的操作。

    当参数是1:每次事务提交时,日志缓冲被写及日志文件,并且针对日记文件举行磁盘刷新操作,该模式也系统默认。但由于每次事务都用进行磁盘I/O,所以啊太缓慢。

    当参数是2:每次事务提交时,日志缓冲被描绘到日志文件,但怪日志文件举行磁盘刷新操作。对日记文件每秒执行同一次等,刷到磁盘操作。

  当设置innodb_flush_log_at_trx_commit=1不时,
是默认值,也是太安全的装,但是当这种模式下性能有早晚之损失。
如果设置成0或2 属性会有所改善,但产生多少丢失的风险。
  设置成0则数据库崩溃的时节,那些从没于写副日志文件的作业丢失,最多有失1秒钟之事务,是极致不安全之,但也是效率最高的。
  设置成2则只是没刷新到磁盘,但曾写入日志文件,所以若操作系统没有崩溃,
那么连没有数据丢失, 比设置成0更安全。
  以mysql官方中,
为了保证业务之持久性和复制设置的一致性,都是建议将以此参数值设置为1。对于部分多少一致性与完整性要求无强之使用,配置为
2 就足足了;如果为了最高性能,可以设置为
0。有些应用,如开发服务,对一致性和完整性要求老高,所以就极缓慢,也极其好设置为
1。

参数值

数据安全性

I/O性能

0

安全最差。当数据库崩溃,有丢失1秒钟的事务风险

最优

1

安全最好。无丢失数据

最差

2

安全折中。当操作系统崩溃, 有丢失1秒钟的事务风险

折中

  1.1 查看日志提交方式

  SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

    manbet手机客户端3.0 1

  1.2 修改参数值

           还是同找到my.cnf, 修改参数值

           [root@xuegod64 ~]# cd /etc

           [root@xuegod64 etc]# vim my.cnf
    manbet手机客户端3.0 2

    [root@xuegod64 ~]# systemctl stop mysqld.service

    [root@xuegod64 ~]# systemctl start  mysqld.service

-- 再次查看
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

    manbet手机客户端3.0 3

  1. innodb_buffer_pool_size的设置

二. sync_binlog  

  这个参数是对此MySQL系统的话是重大的,他不只影响及Binlog对MySQL所带动的性质损耗,而且还影响至MySQL中数据的完整性。在MySQL中网默认的装置是sync_binlog=1。对于“sync_binlog”参数的各种设置的验证如下:

  sync_binlog=0:当事务提交之后,不主动刷新二进制日志文件的数目到磁盘上,而是由操作系统决定。

  sync_binlog=n:每于亚前进制日志文件写入N条SQL或N个事务后,则将二上制日志文件的数刷新到磁盘上。

  而当装为“1”的时,是不过安全而性能损耗最充分的安。因为当装也1的时段,即使系统Crash,也极多丢失binlog_cache中未得的一个作业,对实际多少尚未其余实质性影响。|

--  查看binlog写入方式
SHOW VARIABLES LIKE 'sync_binlog';

  manbet手机客户端3.0 4

  总结: 在数据安全暨特性为日记文件作为出发点时,我当效果上与sql
server 的数据恢复模式于相如,但实现之思绪是匪等同的。
  innodb_flush_log_at_trx_commit和sync_binlog是MySQL
innodb引擎的一定量独重点的参数,其中innodb_flush_log_at_trx_commit是将事情日志从innodb
log buffer刷新到磁盘,sync_binlog是用第二前进制日志文件刷新到磁盘上。
  innodb_flush_log_at_trx_commit和sync_binlog
两只参数是决定MySQL
磁盘写副策略和数安全性的最主要参数,当半单参数还设置也1底时光写副性能最差,
网上也发出说以innodb_flush_log_at_trx_commit=2,sync_binlog=500
或1000。有说对于高并发事务的体系吧,“sync_binlog”设置为0同安也1之系描绘副性能差距可能大及5倍增甚至又多。总体上或者如根据业务来判定,在性和安达成开只挑选。

        
这个参数定义了innodb存储引擎的发明数据及目录数据的无比老外存缓冲区大小,和myisam不同,myisam的key_buffer_size只休息存索引键,而innodb_buffer_pool_size是还要也数据块和索引块做缓存的。这个特点以及oracle是同等的,这个值设得越来越强,访问表中数据要的磁盘i/o就越少(物理I/O)。在一个专用的数据库服务器上,可以安装是参数上机械物理内存大小的50–80%。考虑点:在独叫
MySQL
使用的主机里,内存分配还包系统应用,线程独享,myisam缓存等。还有允许的连发连接数。还有建议不用将她装得最为要命,因为对物理内存的竞争或于操作系统及造成内存调度。

-- innodb缓存区大小(kb)
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

manbet手机客户端3.0 5  SELECT
268435456/1024.0/2014.0=130M。

查看内存大小:
[root@xuegod64 ~]# cat /proc/meminfo

    manbet手机客户端3.0 6

  上面内存约等于2031912/1024.0=1984M。
2. buffer_pool 运行参数

-- 下面是buffer_pool 运行相关参数
SHOW STATUS LIKE  'Innodb_buffer_pool_%';

  manbet手机客户端3.0 7

Innodb_buffer_pool_pages_total 

缓存池页总数目。
共占用了16382 页 。单位page

Innodb_buffer_pool_pages_free

缓存池剩余的页数目。
在16382 页中有2000页没有使用。 单位page

Innodb_buffer_pool_pages_data

缓存池中包含数据的页的数目,包括脏页。
14273个页含有数据。单位page

Innodb_buffer_pool_read_requests

innodb进行逻辑读的数量。
529670886次请求读。单位次数

Innodb_buffer_pool_reads

进行逻辑读取时无法从缓冲池中获取而执行单页读取的次数。
941147次是物理I/0读取。单位次数

Innodb_buffer_pool_write_requests

写入 InnoDB 缓冲池的次数。
48606702次请求写入。单位次数

Innodb_buffer_pool_read_ahead_rnd

记录进行随机读的时候产生的预读次数。
0次

Innodb_buffer_pool_read_ahead

预读到innodb buffer pool里次数。
1465370次。  单位page

Innodb_buffer_pool_read_ahead_evicted

预读的页数,但是没有被读取就从缓冲池中被替换的页的数量,一般用来判断预读的效率。 0次

Innodb_buffer_pool_wait_free

数据要写入buffer pool的时候,需要等待空闲页的次数。是指缓存池里没有干净页的时候读取或创建页,要先等待页被刷新。
2927次。 单位次数。

Innodb_buffer_pool_pages_dirty

buffer pool缓存池中脏页的数目。
0次。单位是page

Innodb_buffer_pool_pages_flushed

buffer pool缓存池中刷新页请求的数目。
15437744次。单位page

Innodb_buffer_pool_pages_misc

buffer pool缓存池中当前页已经被用作管理用途或hash index而不能用作为普通数据页的数目。
109次。单位page

Innodb_buffer_pool_pages_old

在旧区域存放着多少个页。
5249次。单位page

Innodb_buffer_pool_pages_made_young

移动到新区域的有多少个页。
353059次。单位page

Innodb_buffer_pool_pages_made_not_young

没有移动到新区域的有多少个页。
31725809次。单位page

  Buffer Pool使用率:14328.0/16382.0 *100=87.46%
  缓存读命中率: (529670886-941147)/529670886.0 *100 =99.82%
  实际占有空间是:16382 *16(页单位)*1024=268402688 字节。
上面给buffer_pool_size分配的凡268435456字节。

  1. 设置buffer_pool参数

    — 从134217728设置成268435456 (另一台mysql)
    SET GLOBAL innodb_buffer_pool_size= 268435456

  由于SHOW
VARIABLES下的参数都是静态值。当mysql重开时,上面的缓存设置以失效。

[root@xuegod64 ~]# systemctl stop mysqld.service
[root@xuegod64 ~]# systemctl start  mysqld.service

  重开后或者134217728.如下图
  manbet手机客户端3.0 8

  要永久改变,需要在操作系统里以vim my.cnf 来修改. 如下图所著,
去丢#再也定义值。

  manbet手机客户端3.0 9

 

留下评论

网站地图xml地图