mysql 开垦进级篇系列 20 MySQL Server(innodb_lock_wait_timeout,innodb_support_xa,innodb _log_*)

by admin on 2019年10月7日

一.概述

         mysql
提供了很多参数来进行服务器的设置,当服务第一次启动的时候,所有启动参数值都是系统默认的。这些参数在很多生产环境下并不能满足实际的应用需求。在这个系列中涉及到了liunx
服务器,我这里是centos7.4, mysql 5.7,Xshell6。

  1. 查看mysql server参数

         通过show variables和show
status命令查看mysql的服务器静态参数值和动态运行状态信息。前者是在数据库启动后不会动态更改的值。比如缓冲区大小,字符集,数据文件名称等;
后者是数据库运行期间的动态变化的信息,比如锁等待,当前连接数等。下面来简单查看下两个命令

--  mysql服务静态参数值
SHOW VARIABLES;

图片 1

--  mysql服务运行状态值
SHOW STATUS;

图片 2

从这篇开始,讲innodb存储引擎中,对于几个重要的服务器参数配置。这些参数以innodb_xx
开头。

一.key_buffer

  上一篇了解key_buffer设置,key_buffer_size指定了索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads
/key_read_requests应该尽可能的低,至少是1:100,1:1000更好(理解为key_reads物理IO次数越少越好)。

--   一共有Key_read_requests个索引请求,一共发生了Key_reads次物理IO
SHOW GLOBAL STATUS LIKE '%key_read%';

图片 3

--  Key_reads/Key_read_requests ≈ 0.1%以下比较好
SELECT 693206.0/94745304.0

图片 4

  key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值,可以使用检查状态值created_tmp_disk_tables得知详情。

SHOW GLOBAL STATUS LIKE '%created_tmp_disk_tables%';

图片 5

总结建议:

    对于1G内存的机器,如果不使用MyISAM表,推荐值是16M(8-64M)。
    单个key_buffer的大小不能超过4G。
    建议key_buffer设置为物理内存的1/4(针对MyISAM引擎),在很多情况下数据要比索引大得多。
    如果机器性能优越,可以设置多个key_buffer,分别让不同的key_buffer来缓存专门的索引。
    Key_reads/Key_read_requests的大小正常情况下得小于0.01。

一. 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';

    图片 6

  1.2 修改参数值

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

           [root@xuegod64 ~]# cd /etc

           [root@xuegod64 etc]# vim my.cnf
    图片 7

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

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

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

    图片 8

1. innodb_lock_wait_timeout

二.  影响mysql性能的重要参数

  在上面介绍了mysql server 端的参数查看方法 ,对于这么多参数,
实际大多数参数是不需要用户调整的,下面介绍一些重要参数。先介绍下MyISAM存储引擎的key_buffer_size和table_cache。

  1. key_buffer_size设置

                   key_buffer_size是用来设置索引块(index
Blocks)缓存的大小,它被所有线程共享,此参数只应于MYISAM存储引擎。在mysql
5.1后,系统除了默认的索引块缓存,还可以配置多个key_buffer,可以将指定的表索引,缓存入指定的key_buffer,这样可以更小地降低线程之间的竞争。

-- 查看默认设置
 SHOW VARIABLES LIKE 'key_buffer_size';  

    8388608/1024.0/1024.0=M  默认8M 如下图所示:

     图片 9

-- 建立一个新的索引块缓存
SET  GLOBAL hot_cache2.key_buffer_size=128*1024

  对于GLOBAL
表示对每一个新的连接,此参数都将生效,hot_cache2是新的key_buffer名称,可以随时进行重建,例如:

SET  GLOBAL hot_cache2.key_buffer_size=200*1024

  把相关表的索引,放到指定的索引块缓存中如下:

-- 将表(userbymyisam )索引放入指定的索引块中 
CACHE INDEX userbymyisam IN hot_cache2

图片 10

  想将索引预装到默认的key_buffer中,可以使用load index into
cache语句,例如预装表userbymyisam的所有索引 预装到默认的key_buffer。

LOAD INDEX INTO CACHE userbymyisam

图片 11

-- 删除索引缓存,如下命令
SET  GLOBAL hot_cache2.key_buffer_size=0

  注意:默认的key_buffer是不能删除的如:  SET  GLOBAL
key_buffer_size=0 下次重启时还会有。

 总结: cache
index命令在一个表和key_buffer之间建立一种联系,但每次服务器重启时key_buffer中的数据将清空,如果想每次服务器重启时相应表的索引能自动放到key_buffer中,可以在配置文件中设置init-file选项来指定包含cache
index语句文件路径,然后在对应的文件中写入cache index语句。

   下面创建二个缓存索引块:

图片 12

  每次服务器启动时,执行mysqld_init.sql中的语句,
文件中几个表,分别对应hot_cache和cold_cache:
图片 13

  2. 通过操作系统来设置key_buffer

    如果要设置mysql服务系统参数可以在liunx里设置,先要找到my.cnf
文件位置,一般会放在/etc/my.cnf,/etc/mysql/my.cnf。
    如下图所示,尝试修改默认key_buffer_size改为12M:
    图片 14
    图片 15
    停止服务再重启sql服务
    图片 16
    图片 17
    再次查询如下

    图片 18

  1. innodb_buffer_pool_size的设置

二. table_cache (table_open_cache)  

  上面讲了索引缓存,这里讲表缓存 table_cache,在mysql
5.1之后叫做”table_open_cache”。这个参数表示数据库用户打开表的缓存数量(最大限制数),用于设置table高速缓存的数量。由于每个客户端连接都会至少访问一个表,因此此参数的值与max_connections有关。例如
对于200个并行运行的连接,应该让表的缓存至少有200 *
N。这里N是可以执行的查询的一个连接中表的最大数量(表数量)。
  表缓存机制是:当某一连接访问一个表时,MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开,则会直接访问缓存中的表已加快查询速度;如果该表未被缓存,则会将当前的表添加进缓存并进行查询。
  在执行缓存操作之前,table_cache用于限制缓存表的最大数目:如果当前已经缓存的表未达到table_cache,则会将新表添加进来;若已经达到此值,MySQL将根据缓存表的最后查询时间、查询率等规则释放之前的缓存(释放机制与sqlserver一样)。

-- 表缓存限制数(默认是2000次)
SHOW VARIABLES LIKE 'table_open_cache';    

  图片 19

-- 最大并发连接数
SHOW VARIABLES LIKE 'max_connections';

  图片 20

  可以通过检查mysqld的状态变量open_tables和opened_tables确定table_cache参数是否过小。
open_tables表示当前打开的表缓存数,如果执行flush
tables操作,则系统会关闭一些当前没有使用的表缓存,而使得些状态值减小。opened_tables表示曾经打开的表缓存数(历史的),会一直进行累加。执行flush
tables值不会减少。

-- 当前打开的表缓存数
SHOW  GLOBAL STATUS LIKE 'open_tables';

  图片 21

-- 曾经打开的表缓存数
SHOW  GLOBAL STATUS LIKE 'opened_tables';

  图片 22
  2.1演示下open_tables和opened_tables值的变化(在另一台mysql上进行)

     第一步:

-- 清空表缓存
FLUSH TABLES;
-- 查看值为1(代表当前连接)
SHOW  GLOBAL STATUS LIKE 'open_tables';

  图片 23

-- 历史值为111
SHOW  GLOBAL STATUS LIKE 'opened_tables';

  图片 24
  第二步:

-- 执行一个查询
SELECT COUNT(1) FROM User1
-- 再次查询当前缓存数
SHOW  GLOBAL STATUS LIKE 'open_tables';

  图片 25

--历史值也累加到113
SHOW  GLOBAL STATUS LIKE 'opened_tables';

  图片 26
  第三步:

-- 再执行一个相同查询,  会发现值没有增加,因为读的是缓存。
SELECT COUNT(1) FROM User1
SHOW  GLOBAL STATUS LIKE 'open_tables';

  图片 27

SHOW  GLOBAL STATUS LIKE 'opened_tables';

  图片 28

二. 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';

  图片 29

  总结: 在数据安全与性能以日志文件作为出发点时,我认为功能上与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倍甚至更多。总体上还是要根据业务来判断,在性能和安全上做个选择。

  mysql
可以自动监测行锁导致的死锁并进行相应的处理,但是对于表锁导致的死锁不能自动监测,所以该参数主要用于,出现类似情况的时候等待指定的时间后回滚。系统默认值是50秒。用户可以根据业务自行设置。生产环境不推荐使用过大的
innodb_lock_wait_timeout 参数值。

        
这个参数定义了innodb存储引擎的表数据和索引数据的最大内存缓冲区大小,和myisam不同,myisam的key_buffer_size只缓存索引键,而innodb_buffer_pool_size是同时为数据块和索引块做缓存的。这个特性与oracle是一样的,这个值设得越高,访问表中数据需要的磁盘i/o就越少(物理I/O)。在一个专用的数据库服务器上,可以设置这个参数达机器物理内存大小的50–80%。考虑点:在单独给
MySQL
使用的主机里,内存分配还包括系统使用,线程独享,myisam缓存等。还有允许的并发连接数。还有建议不要把它设置得太大,因为对物理内存的竞争可能在操作系统上导致内存调度。

三. 修改table_cache值      

  下面来尝试修改table_cache值, 还是一样找到my.cnf
  [root@xuegod64 etc]# vim my.cnf
  图片 30

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

-- 服务停止重启后再次查看表缓存限制数。
SHOW VARIABLES LIKE 'table_open_cache';

  图片 31

-- 查看事务超时时间
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- innodb缓存区大小(kb)
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

四.table_cache总结

  open_tables是当前表缓存数,类似于sql server的逻辑查询而非物理查询。
该open_tables的值对设置table_cache值有重要的参考价值。
  如果Open_tables的值已经接近table_cache的值,且Opened_tables还在不断变大,则说明mysql正在将缓存的表释放以容纳新的表,此时可能需要加大table_cache的值。下面这台mysql服务器正是这种情况,1990接近最大限制2000,且历史值还在不断变大。
如下图:
  图片 32

  比较适合的值建议:

  Open_tables / Opened_tables >= 0.85

  当前mysql的值:SELECT 1990.0/3286078.0=0.00061

  Open_tables / table_cache <= 0.95

  当前mysql的值:1990.0/2000.0=0.99500

  图片 33
  也可以对当前会话进行超时设置如: set
innodb_lock_wait_timeout=1000。关于产生死锁的原因,如何查看分析死锁问题,
如何优化尽量避免死锁,请查看”mysql 开发进阶篇 锁问题系列”。

图片 34  SELECT
268435456/1024.0/2014.0=130M。

2. innodb_support_xa

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

        
通过该参数设置,是否支持分布式事务。默认值是ON或者1,表示支持分布式事务。
   分布事事务分两类:

    图片 35

  (1)是外部xa事务(支持多实例分布式事务)。

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

  (2)是支持内部xa事务(支持binlog和redo_log之间数据一致性)。

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

如果关闭这个参数,据前辈们说可能会影响到:1是主从复制binlog与redo_log不一致,2是binlog与redo_log事务顺序不一致性。

  图片 36

--  查看是否支持分布式事务
SHOW VARIABLES LIKE 'innodb_support_xa';

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

  图片 37

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

3. innodb _log_buffer_size

  1. 设置buffer_pool参数

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

      
这个参数是指日志缓存的大小。默认的设置在中等强度写入负载以及较短事务的情况下,一般都可以满足服务器的性能要求。如果存在更新操作峰值或者负载较大,就应该考虑加大它的值。
如果值设置太高,可能会浪费内存,因为它每秒都会刷新一次,因此无需设置超过1秒所需的内存空间(理解是每1秒刷新后,日志缓存会清空)。通常设置为8~16MB就足够了。系统默认是16M。

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

--  查看日志缓存空间大小
 SHOW VARIABLES LIKE 'innodb_log_buffer_size';
[root@xuegod64 ~]# systemctl stop mysqld.service
[root@xuegod64 ~]# systemctl start  mysqld.service

  图片 38

  重启后还是134217728.如下图
  图片 39

    16777216.0/1024.0/1024.0=16M

  要永久改变,需要在操作系统里使用vim my.cnf 来修改. 如下图所示,
去掉#重新定义值。

4. innodb_log_file_size
  这个参数是一个日志组(log
group)中每个日志文件的大小,也叫事务日志文件大小。此参数在高写入负载尤其是大数据集的情况下很重要.这个值越大则性能相对越高,但副作用是当系统发生灾难时恢复时间会加大。系统默认是48M。
  (1)
小日志文件使写入速度更慢,崩溃恢复速度更快。原因是由于事务日志相当于一个写缓冲,而小日志文件会很快的被写满,这时候就需要频繁地刷新到硬盘,速度就慢了。如果产生大量的写操作,会增加checkpoint写的次数,如果不能足够快地刷新数据,那么写性能将会降低,。相反文件空间大,在刷新操作发生之前给你足够的空间来使用。
  (2) 大日志文件使写入更快,崩溃恢复速度更慢。

  图片 40

--  查看每个日志文件的大小
 SHOW VARIABLES LIKE 'innodb_log_file_size';

 

  图片 41
  268435456.0/1024.0/1024.0=256M

5. innodb_log_compressed_pages

         这个参数是指:日志文件页存储压缩。系统默认是ON , 将减少redo
log的写入量。

6. innodb_log_checksums

         这个参数是指:写入redo log到文件之前,redo
log的每一个block都需要加上checksum校验位,以防止apply损坏redo log。

7.  innodb_log_write_ahead_size

         这个参数是指: redo log写前的块大小。系统默认是8192字节。

8. innodb_log_files_in_group

      
这个参数是指:该变量控制日志文件数。默认值为3。日志是以顺序的方式写入。结合innodb_buffer_pool_size设置其大小。一般不用设置。

9. innodb_log_group_home_dir

    这个参数是指:日志组所在的路径。

--  所有日志参数如下
 SHOW VARIABLES LIKE 'innodb_log%';

  图片 42

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图