MGR监控及优化点

mysql group replication官方在监控及优化方面文档较少,为了在教学中方便使用,总结如下:

监控点

可用性监控

本节点是不是online:
select member_state from replication_group_members where member_id=@@server_uuid;

当前节点是不是可以写:
select * from performance_schema.global_variables where variable_name in ('read_only', 'super_read_only');

节点是Online表示属于集群中,正常工作。 节点不可写,表示是Single-master中的非Master节点。

性能监控

复制是不是存在延迟:
对比获得到的GTID和本节点执行的GTID是不是一致:
获取的GTID:
SELECT Received_transaction_set FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier';

本节点执行的GTID:
select @@gtid_executed;

远程获取的GTID - 本节点执行的GTID = 延迟的GTID数

本节点执行队列是不是有堆积(大于0表示有延迟):
select count_transactions_in_queue from replication_group_member_stats where member_id=@@server_uuid;

流控(flow control)

在MGR中如果节点落后集群中其它成员太多,就会发起让其它节点等他完成在做的控制,这个叫流控。
当启用: group_replication_flow_control_mode=QUOTA 是表示启用流控。 流控默认通过两个参数控制:
group_replication_flow_control_applier_threshold (默认: 25000)
group_replication_flow_control_certifier_threshold (默认: 25000)

也就说默认延迟在25000个GTID时,会对整个集群Block住写操作。
当然,也可以允许,节点延迟,就如同我们主从结构,从节点延迟,不往上面发请求就可以。
关闭Flow control:
set global group_replication_flow_control_mode='DISABLED';

提示: 关闭流控制,注意查看是不是存在延迟,如果延迟,自已控制阀值不向上面发请求即可。 多IDC结构的MGR,建议关闭流控。

MGR调优参数

因为基本复制结构,所有的数据复制,还是逻辑的重放,所以优化也是复制优化点。
更改:
slave_parallel_type -> LOGICAL_CLOCK

增强sql_thread个数:
slave_parallel_workers -> 2-8

如果CPU瓶颈,网络没问题,减少CPU压缩:
group_replication_compression_threshold = 1000000 -> 2000000
由原来的1M变成2M,再进行压缩(主要针对大事务传述优化)

备注: 需要MGR安装文档及更多交流 入QQ群: 579036588 联系群主。

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

基于MySQL 5.7多源复制+Keepalived搭建高可用

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

说明

本内容来源于知数堂 公开课 : 《MySQL 5.7 高可用新玩法》–吴炳锡 相关视频推荐:https://ke.qq.com/course/172600

本些分享视频地址: http://pan.baidu.com/s/1mia6MZu

基本环境准备

使用Centos 6.X 64位系统 MySQL 使用 MySQL-5.7.17-x86_64 版本,去官方下载mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz 版本

机器名 操作系统 IP
node1 centos-6.8 192.168.11.100
node2 centos-6.8 192.168.11.101
node3 centos-6.8 192.168.11.102

对应的VIP: 192.168.11.110

特别提示: 关闭iptables chkconfig –del iptables /etc/init.d/iptables stop

关闭:selinux setenforce 0 vim /etc/sysconfig/selinux SELINUX=permissive 更改为: SELINUX=disabled

下载MySQL :

 

mkdir /data/Soft
cd /data/Soft
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

###MySQL部署约定 二进制文件放置: /opt/mysql/ 下面对应的目录 数据文件全部放置到 /data/mysql/ 下面对应的目录 原始二进制文件下载到/data/Soft/

MySQL基本安装

以下安装步骤需要在node1, node2, node3上分别执行。

  1. mkdir /opt/mysql
  2. cd /opt/mysql
  3. tar zxvf /data/Soft/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
  4. ln -s /opt/mysql/mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql
  5. mkdir /data/mysql/mysql3309/{data,logs,tmp} -p
  6. groupadd mysql
  7. useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -M mysql
  8. chown -R mysql:mysql /data/mysql/
  9. chown -R mysql:mysql /usr/local/mysql
  10. cd /usr/local/mysql/
  11. ./bin/mysqld –defaults-file=/data/mysql/mysql3309/my3309.cnf –initialize
  12. cat /data/mysql/mysql3309/data/error.log |grep password
  13. /usr/local/mysql/bin/mysqld –defaults-file=/data/mysql/mysql3309/my3309.cnf &
  14. echo “export PATH=$PATH:/usr/local/mysql/bin” >>/etc/profile
  15. source /etc/profile
  16. mysql -S /tmp/mysql3309.sock -p #输才查到密码进入MySQL
  17. mysql>alter user user() identified by ‘wubxwubx’
  18. mysql>grant replication slave on . to ‘repl’@’%’ identified by ‘repl4slave’;
  19. mysql>grant all privilegs on . to ‘wubx’@’%’ identified by ‘wubxwubx’ # 一会测试使用的帐号
  20. mysql>reset master

每个节点按上面进行,遇到初始化和启动故障请认真阅读/data/mysql/mysql3309/data/error.log 信息。 my3309.cnf 可以从相应的目录下载或是加入QQ群: 579036588 下载,有问题入裙讨论。

搭建主从结构

 

node1上执行:

mysql -S /tmp/mysql3309.sock -pwubxwubx

mysql>change master to master_host=’192.168.11.101′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_101_3309′;

mysql>change master to master_host=’192.168.11.102′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_102_3309’;

mysql>start slave; mysql>show slave status\G; #确认同步OK

node2上执行:

mysql -S /tmp/mysql3309.sock -pwubxwubx

mysql>change master to master_host=’192.168.11.100′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_100_3309′;

mysql>change master to master_host=’192.168.11.102′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_102_3309’;

mysql>start slave; mysql>show slave status\G; #确认同步OK

node3上执行:

mysql -S /tmp/mysql3309.sock -pwubxwubx

mysql>change master to master_host=’192.168.11.100′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_100_3309′;

mysql>change master to master_host=’192.168.11.101′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_101_3309’;

mysql>start slave;

mysql>show slave status\G; #确认同步OK

安装keepalived

node1, node2, node3 上分别执行: 安装keepalived

yum install keepalivled

安装python依赖模块:

yum install MySQL-python.x86_64
yum install python2-filelock.noarch

keepalived配置

配置文件放置在: /etc/keepalived/keepalived.conf 内容如下:

vrrp_script vs_mysql_82 {
    script "/etc/keepalived/checkMySQL.py -h 127.0.0.1 -P 3309"
    interval 15
}
vrrp_instance VI_82 {
    state backup
    nopreempt
    interface eth1
    virtual_router_id 82
    priority 100
    advert_int 5
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        vs_mysql_82
    }
    notify /etc/keepalived/notify.py
    virtual_ipaddress {
        192.168.11.110
    }
}

##Keepalived启动 node1, node2, node3分别执行:

/etc/init.d/keepalived start

观查每个系统上的/var/log/messages 内容输出

##测试用例 在其它机器上使用:

mysql -h 192.168.11.110 -P 3309 -uwubx -pwubxwubx -e "select @@hostname"

自已触发一下切换看看能不能完成自动化的切换。

关注知数堂公从号 第一时间参加技术交流

知数堂公众号

知数堂my.cnf生成器

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

MySQL的默认配置比较保守。 有好的硬件还是需要有好的配置才可以发可以发挥MySQL的性能。
如果你是开发人员,为MySQL配置发愁时,可以来用一下 http://zhishutech.net/my-cnf-wizard.html
如果你是DBA初级人员,为MySQL配置纠时,可以来用一下: http://zhishutech.net/my-cnf-wizard.html
如果你是DBA高级人员,来一块优化http://zhishutech.net/my-cnf-wizard.html 这个吧。

让我们听到你的声音。

Innodb表select查询顺序不对?

今天知数堂一个学生反馈说在优化课中老师讲Innodb是以主键排序存储,读取的时间以主键为顺序读取,但发现个例外,如下:

CREATE TABLE zst_t1 (

uid int(10) NOT NULL AUTO_INCREMENT,

id int(11) NOT NULL,

PRIMARY KEY (uid),

KEY idx_id (id)

) ENGINE=InnoDB;’

写入数据:

INSERT INTO zst_t1 VALUES (1,1),(12,1),(22,1),(23,1),(33,1),(2,2),(3,2),(10,2),(11,2),(4,4),(13,4),(14,4);

执行查询:

select * from zst_t1;

为什么这个顺序是乱的,不按顺序排列呢?难道Innodb表并不是全按主键存储?

使用innodb_ruby这个工具查看一下存储结构什么样

看样子存储还是按主键排序存储的。没毛病。

再来看一下该表的索引:

看到这里应该明白了怎么会事了吧,原来这个查询是走的索引覆盖,没有在进行回表读取原数据。另外,也在此说明,Innodb二索索引包含了主键存储。

来继续证明一下:

看到using index 吧,表示这个查询利用索引查询出来结果,不用读取原表。

那么我们给造一个通过主键读取数据操作:

select * from zst_t1 use index(primary);

select * from zst_t1 use index(primary);  #确认一下。

总结:

这个其实就是一个索引包含的查询案例。 如果静下来思考一下,也许很快就明白了。也不用这样去查问题。

技术在于折腾,多搞搞就明白了:)。

MySQL_5.7-innodb-buffer-pool-size新特性

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

MySQL 5.7.5后Innodb_buffer_pool_size一方面可以动态分配。但另一方面也引入了一个新特性。 bp分配必须是innodb_buffer_pool_chunk_size的倍数。同时最好是:innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances.

innodb_buffer_pool_chunk_size默认是128M.

当Innodb_buffer_pool_size分配小于innodb_buffer_pool_chunk_size时,innodb_buffer_pool_chunk_size收缩到等于innodb_buffer_pool_size/innodb_buffer_pool_instances.
当于innodb_buffer_pool_size 大于innodb_buffer_pool_chunk_size时,innodb_buffer_pool_chunk_size自动取innodb_buffer_pool_chunk_size的倍数从而获取更好的性能。

所以于MySQL5.7.5对于Buffer的分配需要提前计算一下。 尽量让innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 从而获取一个较佳的性能。

Antelope 和Barracuda区别

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

Antelope是innodb-base的文件格式, Barracude是innodb-plugin后引入的文件格式,同时Barracude也支持Antelope文件格式。两者区别在于:

文件格式 支持行格式 特性
Antelope

(Innodb-base)

ROW_FORMAT=COMPACT

ROW_FORMAT=REDUNDANT

Compact和redumdant的区别在就是在于首部的存存内容区别。

compact的存储格式为首部为一个非NULL的变长字段长度列表

redundant的存储格式为首部是一个字段长度偏移列表(每个字段占用的字节长度及其相应的位移)。

在Antelope中对于变长字段,低于768字节的,不会进行overflow page存储,某些情况下会减少结果集IO.

Barracuda

(innodb-plugin)

ROW_FORMAT=DYNAMIC

ROW_FORMAT=COMPRESSED

 

这两者主要是功能上的区别功能上的。 另外在行里的变长字段和Antelope的区别是只存20个字节,其它的overflow page存储。

另外这两都需要开启innodb_file_per_table=1

(这个特性对一些优化还是很有用的)

备注:

这里有一点需要注意,如果要使用压缩,一定需要先使用innodb_file_format =Barracuda格式,不然没作用。

下面我们看一下区别:

(testing)root@localhost [(none)]> use wubx;

Database changed

(testing)root@localhost [wubx]> CREATE TABLE t1

->  (c1 INT PRIMARY KEY)

->  ROW_FORMAT=COMPRESSED

->  KEY_BLOCK_SIZE=8;

Query OK, 0 rows affected, 4 warnings (0.01 sec)

报出来4个warnings查看一下报错:

(testing)root@localhost [wubx]> show warnings;

+———+——+———————————————————————–+

| Level   | Code | Message                                                               |

+———+——+———————————————————————–+

| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.        |

| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8.                                    |

| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. |

| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                                  |

+———+——+———————————————————————–+

4 rows in set (0.00 sec)

从以上报错可以看出来不支持压缩。但看一下表结构如下:

(testing)root@localhost [wubx]> show create table t1;

+——-+———————————————————————————————————————————————–+

| Table | Create Table                                                                                                                                  |

+——-+———————————————————————————————————————————————–+

| t1    | CREATE TABLE t1 (

c1 int(11) NOT NULL,

PRIMARY KEY (c1)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 |

+——-+———————————————————————————————————————————————–+

1 row in set (0.00 sec)

这个是比较坑的地方,所以在使用压缩需要注意。

 

(testing)root@localhost [wubx]>create table t2 ( c1 int(11) NOT NULL, primary key(c1));

(testing)root@localhost [wubx]> insert into t2 select * from t1;

Query OK, 5417760 rows affected (37.12 sec)

Records: 5417760  Duplicates: 0  Warnings: 0

 

创建支持压缩的表:

(testing)root@localhost [wubx]>SET GLOBAL  innodb_file_per_table=1

(testing)root@localhost [wubx]>SET GLOBAL innodb_file_format=Barracuda;

(testing)root@localhost [wubx]>CREATE TABLE t3

(c1 INT PRIMARY KEY)

ROW_FORMAT=COMPRESSED

KEY_BLOCK_SIZE=8;

(testing)root@localhost [wubx]> insert into t3 select * from t1;

Query OK, 5417760 rows affected (1 min 10.98 sec)

Records: 5417760  Duplicates: 0  Warnings: 0

 

看一下表的物理大小如下:

-rw-rw—- 1 mysql mysql 8.4K Jul  5 16:58 t1.frm

-rw-rw—- 1 mysql mysql 136M Jul  5 19:40 t1.ibd

-rw-rw—- 1 mysql mysql 8.4K Jul  5 19:43 t2.frm

-rw-rw—- 1 mysql mysql 136M Jul  5 19:44 t2.ibd

-rw-rw—- 1 mysql mysql 8.4K Jul  5 19:46 t3.frm

-rw-rw—- 1 mysql mysql  96M Jul  5 19:47 t3.ibd

 

可见t1, t2都没进行压缩, t3是支持压缩的。

 

 

Innodb IO优二 — 数据库表设计

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

数据库表设计这块学问比较多,我这里单从互联网角度出发同时结合Innodb的特性给出一些设计方法供大家参考。本文构建大概分两分部分:Innodb的特性及设计中如何利用这种特性。

Innodb特性:

  • Innodb是索引聚集表, 存储结构是BTREE
  • Innodb的表的数据存储是有顺序的,默认是以主建排序,主建即是数据本身,不单独存放。
  • 如果没有主建,Innodb以第一个唯一索引排序,如果连唯一索引也没,Innodb内部会产生一个6字节的字段排序(这个也是性能杀手,所以对这块如果不想花太多时间去想这个事,就添加一个自增的列无业务意义做为主建即可)
  • Innodb表普通索引存储需要包含主建(或是Innodb表聚集的字段)

如何利用这些特性:

  • 对于Insert操作要求比高的表:  建议增加一个自增的列做主建,这样减少数据的写入造成Innodb表在存储上不断的拆叶排序的操作。 通过添加一个自增的列做主建从而达到Innodb表的写入都是顺序IO的形态。所以这种情况,保证在1W/S左右的Insert也是比较容易的。添加一个主建还有另外一个好处,真正的条件将会成为唯一索引或是普通索引, 这样索引单独存放起来后,整体上比原来的表文件会小很多,这样基于条件的查询,可以从一个较少的文件快速定位到需要的行。这样也有机会利用到索引覆盖。
  • 另一种场景,写入少,同时每次读多(读取不是一条记录),这种场景可以考虑不要使用自增的列做为主建,就使用查询条件或是查询条和其它通达到唯一,定义为主建。这样查询就可以一次读到数据。还一种场景如存好友关系,或是股票信息,特别好友关系表类的数据,可以考虑使用两个用户的Id做联合主建,查询时条件中只用自已的Id读所有用户的数据,这样就是一个顺序IO的请求。同样这种情景下对一些数据就可以考虑冗余,减少请求反向的数据操作。
  • 更新最好基于主建或是唯一索引来做,这样才能有机会利用Innodb的行级锁。
  • 互联网中还有一种观点是:页面展现什么,就存成什么样的表。这种在CMS中还能适用,在WEB2.0及相关的应用这种观念就行不通。但可以考虑适当的多处写,实现数据的快速读取及索引表的引入。

从业务形态上来看:

  建议数据在设计阶段就要考虑那些是大表,可以分为多少个业务,怎么能核心功能拆分。这块举个例子:大家经常听到的淘宝的用户库,商品库,收藏夹库,交易库,评论库等。及其它我们经常也能听到的:认证库,好友关系库等等。到业务形态上后,可以根据不同的形态选择不同的软件来做不要只看到mysql了,这块的设计可以把nosql类的东西也要考虑进来,最终设计数据库的模型。

数据库表的设计这块有可能都是各有建解,欢迎有不同意见的同学一块来讨论mail我的邮箱即可。

Innodb IO优化-配置优化

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

对于数据库来讲大多瓶颈都出现在IO问题上,所以现在SSD类的设备也才能大行其道。那数据库的IO这块有什么可以优化的吗? 我这里大致谈一下我的看法,希望能达到一个抛砖引玉的效果。
这里谈一下数据库本身的配置方面
具体如下:
配置方面对于IO优化的原则:尽可能能缓存,减少读对数据库的随机IO的请求;同时减少写的随机IO的随时发生,利用各种buffer去缓存。下面来看一下这块的参数:

  • innodb_buffer_pool_size : 这是Innodb最重要的一个配置参数,这个参数控制Innodb本身的缓大小,也影响到,多少数据能在缓存中。建议该参数的配置在物理内存的70%-80%之间。
  • innodb_flush_method: 这个控制Innodb的IO形为,什么:fsync, O_DSYNC之类的,这里不做过多介绍, 建议使用: O_DIRECT, 这样减少操作系统级别VFS的缓存使用内存过多和Innodb本身的buffer的缓存冲突,同时也算是给操作系统减少点压力。
  • innodb_io_capacity: 这个参数据控制Innodb checkpoint时的IO能力,一般可以按一块SAS 15000转的磁盘200个计算,6块盘的SAS做的Raid10这个值可以配到600即可。如果是普通的SATA一块盘只能按100算。(innodb-plugin, Percona有这个参数)
  • innodb_max_dirty_pages_pct : 这个参数据控制脏页的比例如果是innodb_plugin或是MySQL5.5以上的版本,建议这个参数可以设制到75%-90%都行。如果是大量写入,而且写入的数据不是太活跃,可以考虑把这个值设的低一点。 如果写入或是更新的数据也就是热数据就可以考虑把这个值设为:95%
  • innodb_log_file_size : 这个可以配置256M以上,建议有两个以前的日志文件(innodb_log_files_in_group). 如果对系统非常大写的情况下,也可以考虑用这个参数提高一下性能,把文件设的大一点,减少checkpiont的发生。 最大可以设制成:innodb_log_files_in_group * innodb_log_file_size < 512G(percona, MySQL 5.6) 建议设制成: 256M -> innodb_buffer_pool_size/innodb_log_file_in_group 即可。
  • innodb_log_buffer_size : 如果没在大事务,控制在8M-16M即可。

其它对IO有影响的参数(以5.6为准)

  • innodb_adaptive_flushing 默认即可
  • innodb_change_buffer_max_size 如果是日值类服务,可以考虑把这个增值调到 50
  • innodb_change_buffering 默认即可
  • innodb_flush_neighors 默认是开的, 这个一定要开着,充分利用顺序IO去写数据。
  • innodb_lru_scan_depth: 默认即可 这个参数比较专业。
  • innodb_max_purge_lag 默认没启用,如果写入和读取都量大,可以保证读取优先,可以考虑使用这个功能。
  • innodb_random_read_ahead 默认没开启,属于一个比较活跃的参数,如果要用一定要多测试一下。 对用passport类应用可以考虑使用
  • innodb_read_ahead_threshold 默认开启:56 预读机制可以根据业务处理,如果是passprot可以考虑关闭。如果使用innodb_random_read_ahead,建议关闭这个功能
  • innodb_read_io_threads 默认为:4 可以考虑8
  • innodb_write_io_threads 默认为:4 可以考虑8
  • sync_binlog 默认即可: 0
  • innodb_rollback_segments 默认即可: 128

另外5.6的undo log也可以独立配置了,建议单独配置出来。

MySQL数据库负载很高连接数很多怎么处理

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

在MySQL数据库连接数很多,而且大多属于活跃的状态时MySQL机器基本上负载很高,属于基本上快要死去的状态了.
这时怎么办呢?

有可能两个办法.
第一先限制Innodb的并发处理.如果innodb_thread_concurrency = 0 可以先改成 16或是64 看机器压力,如果
非常大,先改成16让机器的压力下来,然后慢慢增达,适应自已的业务.
处理方法: set global innodb_thread_concurrency=16;

第二: 对于连接数已经超过600或是更多的情况,可以考虑适当的限制一下连接数,让前端报一下错,也别让DB挂了.
DB在了,总是可以用来加载一下数据,当数据加载到了nosql里了,慢慢的DB压力也会降下来的.
限制单用户连接数在500以下. 如:
set global max_user_connections=500;

(MySQL随着连接数的增加性能会是下降的,这也是thread_pool出现的原因)
另外对于有的监控程序会读取information_schema下面的表的程序可以考虑关闭下面的参数
innodb_stats_on_metadata=0
set global innodb_stats_on_metadata=0;

这个参数主要防止对读取information_schema时造成大量读取磁盘进行信息统计(如果慢查询中出现关于information_schema中表时,也可以考虑禁用该参数)

处理依据:
当学校的一个食堂一分钟只能为两个打饭, 忽然来了100个时人来打饭,又没排队, 不出会现了打饭的师傅要用点时间
去选择为那个用户服务了, 人越多,场面就越乱, 难免出现用户大吼该他的场面, 最后有可能就出现不是打饭了,而时之间相互
打架了,打饭的师傅也将收到同时有90个以上的Server too busy. 如果能排一下队.最多也就50分钟能处理完了.

以前办法,应该可以让MySQLD不会挂掉.如果业务支撑受到限制,还是想办法处理一下.

问题:
高峰期的业务业务支撑数就是服务器的最终需求数吗?

从MySQL源码学习运维Innodb buffer命中率计算

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.
按官方手册推荐Innodb buffer Hit Ratios的计算是:

100-((iReads / iReadRequests)*100)
iReads : mysql->status->Innodb_buffer_pool_reads
iReadRequests: mysql->status->Innodb_buffer_pool_read_requests

出处: http://dev.mysql.com/doc/mysql-monitor/2.0/en/mem_graphref.html
搜”Hit Ratios”
推荐有兴趣的同学把这个页面都看一下应该也会有很大收获.
另外在hackmysql: www.hackmysql.com网站上的: mysqlsqlreport中关于buffer命中计算是:

$ib_bp_read_ratio = sprintf "%.2f",
($stats{'Innodb_buffer_pool_read_requests'} ?
100 - ($stats{'Innodb_buffer_pool_reads'} /
$stats{'Innodb_buffer_pool_read_requests'}) * 100 :0);

即:

ib_bp_hit=100-(Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100

另外我们知道查看Innodb Buffer Hit Ratios的地方是:
show engine innodb status\G;
Buffer pool hit rate : XXXX/1000;
那个XXX/1000即是buffer pool hit ratios的命中.
这样也可以从代码里看一下这个bp命中计算:

storage/innobase/buf/buf0buf.c # void buf_print_io
storage/innodbase/include/buf0buf.h #struct buf_block_struct

在buf0buf.c 中的buf_print_io函数中可以看到:

void
buf_print_io(
…

if (buf_pool->n_page_gets > buf_pool->n_page_gets_old) {
fprintf(file, "Buffer pool hit rate %lu / 1000\n",
(ulong)
(1000 - ((1000 * (buf_pool->n_pages_read
- buf_pool->n_pages_read_old))
/ (buf_pool->n_page_gets
- buf_pool->n_page_gets_old))));
} else {
fputs("No buffer pool page gets since the last printout\n",
file);
}

buf_pool->n_page_gets_old = buf_pool->n_page_gets;
buf_pool->n_pages_read_old = buf_pool->n_pages_read;
…
}

结合:
storage\innobase\include\buf0buf.h中

struct buf_block_struct{
…
ulint n_pages_read; /* number read operations */
…
ulint n_page_gets; /* number of page gets performed;
also successful searches through
the adaptive hash index are
counted as page gets; this field
is NOT protected by the buffer
pool mutex */
…
ulint n_page_gets_old;/* n_page_gets when buf_print was
last time called: used to calculate
hit rate */
…
ulint n_pages_read_old;/* n_pages_read when buf_print was
last time called */
…
}

从这个来看innodb buffer hit Ratios的命中计算需要本次取的值和上次值做一个减法公式应该为

ib_bp_hit=1000 – (t2.iReads – t1.iReads)/(t2.iReadRequest – t1.iReadRequest)*1000

t(n): 时间点 两个时间间隔最少是30秒以上,在小意义不大.
iReads: Innodb_buffer_pool_reads
iReadRequest: Innodb_buffer_pool_read_requests

对innodb的输出参数有兴趣的可以关注: storage/innobase/buf/Srv0srv.c 中的:
void srv_export_innodb_status()

思考:
对于innodb_buffer_pool_read_requests, innodb_buffer_pool_reads这种累加值,当很大时进行: innodb_buffer_pool_reads/innodb_buffer_pool_read_requests 相来讲只能得到从开始到现在的命中率的表现了. 如果想得到现在近五分钟,近一分钟或是8点到9点每分钟的命中率情况,如果还是按着innodb_buffer_pool_reads/innodb_buffer_pool_read_requests 进行计算,只能得到mysqld开起累计在8点-9点的每分钟的累计平均命中情况.
所以如果想到每(五)分钟的命中情况,就需要本次取得的值和一(五)分钟前的值进行相减,然后进行运算.这样才能得到一个当下的bp命中情况.
两种方法没实质的对错的问题,但相对于源码中的那种计算方式更容让发现数据库的抖动问题.

能解决的问题:
偶而的数据库性能抖动能直观的反应出来.