某某茶叶有限公司欢迎您!
金沙棋牌在线 > 必赢棋牌官网 > mysql 开发进阶篇系列 17 MySQL Server(key_buffer与table_cache)

mysql 开发进阶篇系列 17 MySQL Server(key_buffer与table_cache)

时间:2019-12-29 06:38

从这篇开始,讲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%';

图片 1

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

图片 2

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

SHOW GLOBAL STATUS LIKE '%created_tmp_disk_tables%';

图片 3

总结建议:

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

一.概述

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

  1. 查看mysql server参数

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

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

图片 4

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

图片 5

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

  图片 9

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

  图片 10

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

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

  图片 11

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

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

     第一步:

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

  图片 13

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

  图片 14
  第二步:

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

  图片 15

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

  图片 16
  第三步:

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

  图片 17

SHOW  GLOBAL STATUS LIKE 'opened_tables';

  图片 18

二.  影响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 如下图所示:

     图片 19

-- 建立一个新的索引块缓存
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

图片 20

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

LOAD INDEX INTO CACHE userbymyisam

图片 21

-- 删除索引缓存,如下命令
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语句。

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

图片 22

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

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

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

    图片 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倍甚至更多。总体上还是要根据业务来判断,在性能和安全上做个选择。

         这个参数定义了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

-- 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  SELECT 268435456/1024.0/2014.0=130M。