8.1 备份恢复工具介绍：
（1）** mongoexport/mongoimport
（2）***** mongodump/mongorestore
（1）** mongoexport/mongoimport
（2）***** mongodump/mongorestore
8.2 备份工具区别在哪里？
应用场景总结:
(1) mongoexport/mongoimport （imp/exp）
json csv
1、异构平台迁移 mysql <---> mongodb
2、同平台，跨大版本：mongodb 2 ----> mongodb 3
应用场景总结:
(1) mongoexport/mongoimport （imp/exp）
json csv
1、异构平台迁移 mysql <---> mongodb
2、同平台，跨大版本：mongodb 2 ----> mongodb 3
(2) mongodump/mongorestore (dump/restore) BSON格式
日常备份恢复时使用.
日常备份恢复时使用.
8.3 导出工具mongoexport
mongoexport具体用法如下所示：
$ mongoexport --help
参数说明：
-h:指明数据库宿主机的IP
-u:指明数据库的用户名
-p:指明数据库的密码
-d:指明数据库的名字
-c:指明collection的名字
-f:指明要导出那些列
-o:指明到要导出的文件名
-q:指明导出数据的过滤条件
--authenticationDatabase admin
mongoexport具体用法如下所示：
$ mongoexport --help
参数说明：
-h:指明数据库宿主机的IP
-u:指明数据库的用户名
-p:指明数据库的密码
-d:指明数据库的名字
-c:指明collection的名字
-f:指明要导出那些列
-o:指明到要导出的文件名
-q:指明导出数据的过滤条件
--authenticationDatabase admin
1.单表备份至json格式
mongoexport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log -o /mongodb/log.json
注：备份文件的名字可以自定义，默认导出了JSON格式的数据。
mongoexport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log -o /mongodb/log.json
注：备份文件的名字可以自定义，默认导出了JSON格式的数据。
2. 单表备份至csv格式
如果我们需要导出CSV格式的数据，则需要使用----type=csv参数：
如果我们需要导出CSV格式的数据，则需要使用----type=csv参数：
mongoexport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log2 --type=csv -f uid,name,age,date -o /mongodb/log.csv
8.4 导入工具mongoimport
$ mongoimport --help
参数说明：
-h:指明数据库宿主机的IP
-u:指明数据库的用户名
-p:指明数据库的密码
-d:指明数据库的名字
-c:指明collection的名字
-f:指明要导入那些列
-j, --numInsertionWorkers=<number> number of insert operations to run concurrently (defaults to 1)
//并行
8.4 导入工具mongoimport
$ mongoimport --help
参数说明：
-h:指明数据库宿主机的IP
-u:指明数据库的用户名
-p:指明数据库的密码
-d:指明数据库的名字
-c:指明collection的名字
-f:指明要导入那些列
-j, --numInsertionWorkers=<number> number of insert operations to run concurrently (defaults to 1)
//并行
数据恢复:
1.恢复json格式表数据到log1
mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log1 /mongodb/log.json
1.恢复json格式表数据到log1
mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log1 /mongodb/log.json
2.恢复csv格式的文件到log2
（1）标准mongodb csv格式导出文件
mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log2 --type=csv --headerline --file /mongodb/log.csv
（1）标准mongodb csv格式导出文件
mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log2 --type=csv --headerline --file /mongodb/log.csv
--headerline:指明第一行是列名，不需要导入。
（2）不标准的CSV，第一行不是列名
mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log3 --type=csv -f id,name,age,date --file /mongodb/log.csv
mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log3 --type=csv -f id,name,age,date --file /mongodb/log.csv
作业： 异构平台迁移案例: MySQL 100w数据 迁移至 MongoDB
8.5 异构平台迁移案例: MySQL 100w数据 迁移至 MongoDB
mysql -----> mongodb
test数据库下t100w表进行导出，导入到mongodb
（1）mysql开启安全路径
vim /etc/my.cnf --->添加以下配置
secure-file-priv=/tmp
--重启数据库生效
/etc/init.d/mysqld restart
（2）导出mysql的city表数据
select * from test.t100w limit 10 into outfile '/tmp/2.csv' fields terminated by ',';
mysql -----> mongodb
test数据库下t100w表进行导出，导入到mongodb
（1）mysql开启安全路径
vim /etc/my.cnf --->添加以下配置
secure-file-priv=/tmp
--重启数据库生效
/etc/init.d/mysqld restart
（2）导出mysql的city表数据
select * from test.t100w limit 10 into outfile '/tmp/2.csv' fields terminated by ',';
（3）导出列名
>select group_concat(column_name) from information_schema.columns where table_schema='test' and table_name='t100w';='t100w';
>select group_concat(column_name) from information_schema.columns where table_schema='test' and table_name='t100w';='t100w';
(4)在mongodb中导入备份
mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d test -c t100w --type=csv --headerline --file /tmp/2.csv
mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d test -c t100w --type=csv --headerline --file /tmp/2.csv
扩展： mongodb 往 mysql 迁移。
mysql导出csv：
select * from test_info
into outfile '/tmp/test.csv'
fields terminated by ',' ------字段间以,号分隔
optionally enclosed by '"' ------字段用"号括起
escaped by '"' ------字段中使用的转义符为"
lines terminated by ' '; ------行以 结束
select * from test_info
into outfile '/tmp/test.csv'
fields terminated by ',' ------字段间以,号分隔
optionally enclosed by '"' ------字段用"号括起
escaped by '"' ------字段中使用的转义符为"
lines terminated by ' '; ------行以 结束
mysql导入csv：
load data infile '/tmp/test.csv'
into table test_info
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by ' ';
load data infile '/tmp/test.csv'
into table test_info
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by ' ';
8.6 mongodump和mongorestore
8.6.1介绍
mongodump能够在Mongodb运行时进行备份，它的工作原理是对运行的Mongodb做查询，然后将所有查到的文档写入磁盘。
但是存在的问题时使用mongodump产生的备份不一定是数据库的实时快照，如果我们在备份时对数据库进行了写入操作，
则备份出来的文件可能不完全和Mongodb实时数据相等。另外在备份时可能会对其它客户端性能产生不利的影响。
8.6.2 mongodump用法如下：
$ mongodump --help
参数说明：
-h:指明数据库宿主机的IP
-u:指明数据库的用户名
-p:指明数据库的密码
-d:指明数据库的名字
-c:指明collection的名字
-o:指明到要导出的文件名
-q:指明导出数据的过滤条件
-j, --numParallelCollections= number of collections to dump in parallel (4 by default)
--oplog 备份的同时备份oplog
$ mongodump --help
参数说明：
-h:指明数据库宿主机的IP
-u:指明数据库的用户名
-p:指明数据库的密码
-d:指明数据库的名字
-c:指明collection的名字
-o:指明到要导出的文件名
-q:指明导出数据的过滤条件
-j, --numParallelCollections= number of collections to dump in parallel (4 by default)
--oplog 备份的同时备份oplog
8.6.3 mongodump和mongorestore基本使用
# 全库备份
mkdir -p /mongodb/backup/full
mongodump -uroot -proot123 --port 27017 --authenticationDatabase admin -o /mongodb/backup/full
# 全库备份
mkdir -p /mongodb/backup/full
mongodump -uroot -proot123 --port 27017 --authenticationDatabase admin -o /mongodb/backup/full
# 备份test库
mkdir -p /mongodb/backup/test
$ mongodump -uroot -proot123 --port 27017 --authenticationDatabase admin -d test -o /mongodb/backup/test
mkdir -p /mongodb/backup/test
$ mongodump -uroot -proot123 --port 27017 --authenticationDatabase admin -d test -o /mongodb/backup/test
# 备份oldboy库下的log集合
mkdir -p /mongodb/backup/log
$ mongodump -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log -o /mongodb/backup/log
mkdir -p /mongodb/backup/log
$ mongodump -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log -o /mongodb/backup/log
# 压缩备份
mkdir -p /mongodb/backup/full1
mongodump -uroot -proot123 --port 27017 --authenticationDatabase admin -o /mongodb/backup/full1 --gzip
mkdir -p /mongodb/backup/full1
mongodump -uroot -proot123 --port 27017 --authenticationDatabase admin -o /mongodb/backup/full1 --gzip
# 从全备full1中恢复test库到test1库中
mongorestore -uroot -proot123 --port 27017 --authenticationDatabase admin -d test1 /mongodb/backup/full1/test --gzip
mongorestore -uroot -proot123 --port 27017 --authenticationDatabase admin -d test1 /mongodb/backup/full1/test --gzip
# 从全备full1中恢复test库下t100w表到 test 库下 t1 表
mongorestore -uroot -proot123 --port 27017 --authenticationDatabase admin -d test -c t1 --gzip /mongodb/backup/full1/test/t100w.bson.gz
注意：
drop表示恢复的时候把之前的集合drop掉(危险)
mongorestore -uroot -proot123 --port 27017 --authenticationDatabase admin -d test -c t100w --drop --gzip /mongodb/backup/full1/test/t100w.bson.gz
drop表示恢复的时候把之前的集合drop掉(危险)
mongorestore -uroot -proot123 --port 27017 --authenticationDatabase admin -d test -c t100w --drop --gzip /mongodb/backup/full1/test/t100w.bson.gz
8.7 mongodump和mongorestore 高级企业应用（--oplog）
注意：这是replica set或者master/slave模式专用
--oplog
use oplog for taking a point-in-time snapshot
8.7.1 oplog介绍
在replica set中oplog是一个定容集合（capped collection），它的默认大小是磁盘空间的5%（可以通过--oplogSizeMB参数修改）.
位于local库的db.oplog.rs，有兴趣可以看看里面到底有些什么内容。
其中记录的是整个mongod实例一段时间内数据库的所有变更（插入/更新/删除）操作。
当空间用完时新记录自动覆盖最老的记录。
其覆盖范围被称作oplog时间窗口。需要注意的是，因为oplog是一个定容集合，
所以时间窗口能覆盖的范围会因为你单位时间内的更新次数不同而变化。
想要查看当前的oplog时间窗口预计值，可以使用以下命令：
说明：
1. 相当于MySQL 中的 binlog，保存的是MongoDB修改类操作的日志
2. 他是一个定容集合，大小不变的表。
3. 默认大小可用磁盘大小5%，可以通过oplogSizeMB参数指定。
4. 单节点MongoDB没有oplog，存在于复制集中。
5. 可以通过以下命令监控是否够用。
[mongod@db01 test]$ mongo --port 28017 admin
PRIMARY> rs.printReplicationInfo()
configured oplog size: 1561.5615234375MB <--集合大小
log length start to end: 423849secs (117.74hrs) <--预计窗口覆盖时间
oplog first event time: Wed Sep 09 2015 17:39:50 GMT+0800 (CST)
oplog last event time: Mon Sep 14 2015 15:23:59 GMT+0800 (CST)
now: Mon Sep 14 2015 16:37:30 GMT+0800 (CST)
oplog什么时候可以被覆盖？相当于binlog什么时候可以被删除？
至少要预留MongoDB，1个全备的周期大小。如果备份走的日志，也可以。
在replica set中oplog是一个定容集合（capped collection），它的默认大小是磁盘空间的5%（可以通过--oplogSizeMB参数修改）.
位于local库的db.oplog.rs，有兴趣可以看看里面到底有些什么内容。
其中记录的是整个mongod实例一段时间内数据库的所有变更（插入/更新/删除）操作。
当空间用完时新记录自动覆盖最老的记录。
其覆盖范围被称作oplog时间窗口。需要注意的是，因为oplog是一个定容集合，
所以时间窗口能覆盖的范围会因为你单位时间内的更新次数不同而变化。
想要查看当前的oplog时间窗口预计值，可以使用以下命令：
说明：
1. 相当于MySQL 中的 binlog，保存的是MongoDB修改类操作的日志
2. 他是一个定容集合，大小不变的表。
3. 默认大小可用磁盘大小5%，可以通过oplogSizeMB参数指定。
4. 单节点MongoDB没有oplog，存在于复制集中。
5. 可以通过以下命令监控是否够用。
[mongod@db01 test]$ mongo --port 28017 admin
PRIMARY> rs.printReplicationInfo()
configured oplog size: 1561.5615234375MB <--集合大小
log length start to end: 423849secs (117.74hrs) <--预计窗口覆盖时间
oplog first event time: Wed Sep 09 2015 17:39:50 GMT+0800 (CST)
oplog last event time: Mon Sep 14 2015 15:23:59 GMT+0800 (CST)
now: Mon Sep 14 2015 16:37:30 GMT+0800 (CST)
oplog什么时候可以被覆盖？相当于binlog什么时候可以被删除？
至少要预留MongoDB，1个全备的周期大小。如果备份走的日志，也可以。
如果没启动可以启动一下：
mongod -f /mongodb/28017/conf/mongod.conf
mongod -f /mongodb/28018/conf/mongod.conf
mongod -f /mongodb/28019/conf/mongod.conf
mongod -f /mongodb/28020/conf/mongod.conf
mongod -f /mongodb/28017/conf/mongod.conf
mongod -f /mongodb/28018/conf/mongod.conf
mongod -f /mongodb/28019/conf/mongod.conf
mongod -f /mongodb/28020/conf/mongod.conf
use local
db.oplog.rs.find().pretty()
{
"ts" : Timestamp(1583899515, 1),
"t" : NumberLong(1),
"h" : NumberLong("5563442596211070681"),
"v" : 2,
"op" : "n",
"ns" : "",
"wall" : ISODate("2020-03-11T04:05:15.671Z"),
"o" : {
"msg" : "periodic noop"
}
}
db.oplog.rs.find().pretty()
{
"ts" : Timestamp(1583899515, 1),
"t" : NumberLong(1),
"h" : NumberLong("5563442596211070681"),
"v" : 2,
"op" : "n",
"ns" : "",
"wall" : ISODate("2020-03-11T04:05:15.671Z"),
"o" : {
"msg" : "periodic noop"
}
}
"ts" : Timestamp(1553597844, 1) ----》 GTID
"op" : "n"
"o" :
"op" : "n"
"o" :
"i": insert
"u": update
"d": delete
"c": db cmd
"n"：NOTE
"u": update
"d": delete
"c": db cmd
"n"：NOTE
例子:
my_repl:PRIMARY> use test
switched to db test
my_repl:PRIMARY> db.createCollection("y");
{
"ok" : 1,
"operationTime" : Timestamp(1583980328, 2),
"$clusterTime" : {
"clusterTime" : Timestamp(1583980328, 2),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
}
}
my_repl:PRIMARY> use local
switched to db local
my_repl:PRIMARY> db.oplog.rs.find({"op":"c"}).pretty();
{
"ts" : Timestamp(1583980328, 2),
"t" : NumberLong(1),
"h" : NumberLong("6810471136872386421"),
"v" : 2,
"op" : "c",
"ns" : "test.$cmd",
"ui" : UUID("35ec2434-257d-45d5-8f5c-8be677e8d445"),
"wall" : ISODate("2020-03-12T02:32:08.100Z"),
"o" : {
"create" : "y",
"idIndex" : {
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.y"
}
}
}
my_repl:PRIMARY> use test
switched to db test
my_repl:PRIMARY> db.createCollection("y");
{
"ok" : 1,
"operationTime" : Timestamp(1583980328, 2),
"$clusterTime" : {
"clusterTime" : Timestamp(1583980328, 2),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
}
}
my_repl:PRIMARY> use local
switched to db local
my_repl:PRIMARY> db.oplog.rs.find({"op":"c"}).pretty();
{
"ts" : Timestamp(1583980328, 2),
"t" : NumberLong(1),
"h" : NumberLong("6810471136872386421"),
"v" : 2,
"op" : "c",
"ns" : "test.$cmd",
"ui" : UUID("35ec2434-257d-45d5-8f5c-8be677e8d445"),
"wall" : ISODate("2020-03-12T02:32:08.100Z"),
"o" : {
"create" : "y",
"idIndex" : {
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.y"
}
}
}
8.7.2 oplog企业级应用
（1）实现热备，在备份时使用--oplog选项
注：为了演示效果我们在备份过程，模拟数据插入
注：为了演示效果我们在备份过程，模拟数据插入
（2）准备测试数据
[mongod@db01 conf]$ mongo --port 28017
use oldboy
for(var i = 1 ;i < 100; i++) {
db.foo.insert({a:i});
}
my_repl:PRIMARY> use local
my_repl:PRIMARY> db.oplog.rs.find({"op":"i"}).pretty()
[mongod@db01 conf]$ mongo --port 28017
use oldboy
for(var i = 1 ;i < 100; i++) {
db.foo.insert({a:i});
}
my_repl:PRIMARY> use local
my_repl:PRIMARY> db.oplog.rs.find({"op":"i"}).pretty()
oplog 配合mongodump实现热备
mkdir -p /mongodb/backup/opdata
mongodump --port 28017 --oplog -o /mongodb/backup/opdata
作用介绍：--oplog 会记录备份过程中的数据变化。会以oplog.bson保存下来
恢复:
mongorestore --port 28017 --drop --oplogReplay /mongodb/backup/opdata
mongodump --port 28017 --oplog -o /mongodb/backup/opdata
作用介绍：--oplog 会记录备份过程中的数据变化。会以oplog.bson保存下来
恢复:
mongorestore --port 28017 --drop --oplogReplay /mongodb/backup/opdata
8.8 oplog高级应用
背景：每天23点全备，oplog恢复窗口为48小时
某天，上午10点 test.t1 业务表被误删除。
恢复思路：
0、停应用
2、找测试库
3、恢复昨天晚上全备
4、截取全备之后到test.t1 误删除时间点的oplog，并恢复到测试库
5、将误删除表导出，恢复到生产库
恢复步骤：
模拟故障环境：
模拟故障环境：
1、全备数据库
模拟原始数据
mongo --port 28017
use test
for(var i = 1 ;i < 20; i++) {
db.t1.insert({a: i});
}
模拟原始数据
mongo --port 28017
use test
for(var i = 1 ;i < 20; i++) {
db.t1.insert({a: i});
}
全备:
rm -rf /mongodb/backup/*
mongodump --port 28017 --oplog -o /mongodb/backup
rm -rf /mongodb/backup/*
mongodump --port 28017 --oplog -o /mongodb/backup
--oplog功能:在备份同时,将备份过程中产生的日志进行备份
文件必须存放在/mongodb/backup下,自动命令为oplog.bson
文件必须存放在/mongodb/backup下,自动命令为oplog.bson
再次模拟数据
db.t2.insert({id:1})
db.t3.insert({id:2})
db.t2.insert({id:1})
db.t3.insert({id:2})
2、上午10点：删除test库下的t1表
10:00时刻,误删除
db.t1.drop()
show tables;
3、备份现有的oplog.rs表
mongodump --port 28017 -d local -c oplog.rs -o /mongodb/oplog/
cd /mongodb/oplog/local
cp oplog.rs.bson /mongodb/backup/oplog.bson
4、截取oplog并恢复到drop之前的位置
mongo --port 28017
my_repl:PRIMARY> use local
db.oplog.rs.find({op:"c"}).pretty();
{
"ts" : Timestamp(1583982374, 2),
"t" : NumberLong(1),
"h" : NumberLong("2154692893568044660"),
"v" : 2,
"op" : "c",
"ns" : "test.$cmd",
"ui" : UUID("5f10e418-380e-4926-a74c-fd88a6da9337"),
"wall" : ISODate("2020-03-12T03:06:14.941Z"),
"o" : {
"drop" : "t1"
}
}
my_repl:PRIMARY> use local
db.oplog.rs.find({op:"c"}).pretty();
{
"ts" : Timestamp(1583982374, 2),
"t" : NumberLong(1),
"h" : NumberLong("2154692893568044660"),
"v" : 2,
"op" : "c",
"ns" : "test.$cmd",
"ui" : UUID("5f10e418-380e-4926-a74c-fd88a6da9337"),
"wall" : ISODate("2020-03-12T03:06:14.941Z"),
"o" : {
"drop" : "t1"
}
}
获取到oplog误删除时间点位置:
"ts" : Timestamp(1583982374, 2)
5、恢复备份+应用oplog
mongorestore --port 28017 --oplogReplay --oplogLimit "1583982374:2" --drop /mongodb/backup/
"ts" : Timestamp(1583982374, 2)
5、恢复备份+应用oplog
mongorestore --port 28017 --oplogReplay --oplogLimit "1583982374:2" --drop /mongodb/backup/
扩展命令： bsondump 可以将bson格式的数据转换成json
8.9 分片集群的备份思路（了解）
方案一： 社区版，直接通过mongos进行备份。
方案二： 企业版，专门备份工具 Ops Manager 。
方案三： 自己开发分布式备份系统。
方案二： 企业版，专门备份工具 Ops Manager 。
方案三： 自己开发分布式备份系统。
1、你要备份什么？
config server
shard 节点
单独进行备份
config server
shard 节点
单独进行备份
2、备份有什么困难和问题
（1）chunk迁移的问题
人为控制在备份的时候，避开迁移的时间窗口
（2）shard节点之间的数据不在同一时间点。
选业务量较少的时候 ，将configserver 各个shard节点摘除从节点。
Ops Manager
（1）chunk迁移的问题
人为控制在备份的时候，避开迁移的时间窗口
（2）shard节点之间的数据不在同一时间点。
选业务量较少的时候 ，将configserver 各个shard节点摘除从节点。
Ops Manager
============================================
MySQL优化
MySQL优化
下午14:00开始
讲到 18:00
休息一下
19:30 继续 ~~~
讲到 18:00
休息一下
19:30 继续 ~~~
=====================
1.优化哲学
1.优化哲学
1.1 为什么优化？
为了获得成就感?
为了证实比系统设计者更懂数据库?
为了从优化成果来证实优化者更有价值?
为了获得成就感?
为了证实比系统设计者更懂数据库?
为了从优化成果来证实优化者更有价值?
但通常事实证实的结果往往会和您期待相反！
优化有风险，涉足需谨慎！
优化有风险，涉足需谨慎！
1.2 优化风险
优化不总是对一个单纯的环境进行！还很可能是一个复杂的已投产的系统。
优化手段本来就有很大的风险，只不过你没能力意识到和预见到！
任何的技术可以解决一个问题，但必然存在带来一个问题的风险！
对于优化来说解决问题而带来的问题控制在可接受的范围内才是有成果。
保持现状或出现更差的情况都是失败！
稳定性和业务可持续性通常比性能更重要！
优化不可避免涉及到变更，变更就有风险！
优化使性能变好，维持和变差是等概率事件！
优化不能只是数据库管理员担当风险，但会所有的人分享优化成果！
所以优化工作是由业务需要驱使的！！！
1.3 谁参与优化
数据库管理员
业务部门代表
应用程序架构师
应用程序设计人员
应用程序开发人员
硬件及系统管理员
存储管理员
1.4 优化方向
安全优化（业务持续性）
性能优化（业务高效性）
安全优化（业务持续性）
性能优化（业务高效性）
1.5 优化的范围及思路
优化范围：
存储、主机和操作系统:
主机架构稳定性
I/O规划及配置
Swap
OS内核参数
网络问题
应用程序:（Index，lock，session）
应用程序稳定性和性能
SQL语句性能
串行访问资源
性能欠佳会话管理
优化范围：
存储、主机和操作系统:
主机架构稳定性
I/O规划及配置
Swap
OS内核参数
网络问题
应用程序:（Index，lock，session）
应用程序稳定性和性能
SQL语句性能
串行访问资源
性能欠佳会话管理
数据库优化:（数据库设计、参数）
内存
数据库结构(物理&逻辑)
实例配置
架构
内存
数据库结构(物理&逻辑)
实例配置
架构
2. 优化工具的使用
2.1 系统层面的
2.1.1 CPU ： 进程和线程CPU利用
top
top 都要看什么？
%Cpu(s): 0.1 us, 0.1 sy, 0.0 ni, 99.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
2.1 系统层面的
2.1.1 CPU ： 进程和线程CPU利用
top
top 都要看什么？
%Cpu(s): 0.1 us, 0.1 sy, 0.0 ni, 99.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
us ： 用户程序，工作期间占用的cpu的占比实时状态。
CPU干正事的时间。
CPU干正事的时间。
sy ： 系统程序？ 工作期间占用的cpu的占比实时状态。
内核态的程序运行时占用的cpu半分比。
资源的监控，分配，回收，维护等工作。
=================================
如果MySQL数据库服务器，SYS高可能是什么问题导致的？
1. 高并发，会话过多。
2. 锁。
3. 大表全表扫描。
4. 参数配置。
=================================
id ： 空闲
wa ： cpu花在等待上的时间百分比
等待谁？IO
================
高并发，大事务。
raid 存储规划问题
缓存过小
全表扫描较多
随机IO过多
================
%CPU 某程序对于CPU使用的总占比。
[root@db01 ~]# top -Hp 1446
内核态的程序运行时占用的cpu半分比。
资源的监控，分配，回收，维护等工作。
=================================
如果MySQL数据库服务器，SYS高可能是什么问题导致的？
1. 高并发，会话过多。
2. 锁。
3. 大表全表扫描。
4. 参数配置。
=================================
id ： 空闲
wa ： cpu花在等待上的时间百分比
等待谁？IO
================
高并发，大事务。
raid 存储规划问题
缓存过小
全表扫描较多
随机IO过多
================
%CPU 某程序对于CPU使用的总占比。
[root@db01 ~]# top -Hp 1446
2.1.2 MEM
KiB Mem : 8155004 total, 7631840 free, 354772 used, 168392 buff/cache
KiB Swap: 2097148 total, 2097148 free, 0 used. 7573204 avail Mem
KiB Mem : 8155004 total, 7631840 free, 354772 used, 168392 buff/cache
KiB Swap: 2097148 total, 2097148 free, 0 used. 7573204 avail Mem
2.1.3 IO
[root@db01 ~]# iostat -dk 1
[root@db01 ~]# dd if=/dev/zero of=/mnt/bigfile bs=1M count=2048
[root@db01 ~]# iostat -dk 1
[root@db01 ~]# dd if=/dev/zero of=/mnt/bigfile bs=1M count=2048
2.2 数据库优化工具
show status
show variables
show index
show processlist
show slave status
show engine innodb status
desc /explain
slowlog
扩展类深度优化:
pt系列
mysqlslap
sysbench
information_schema
performance_schema
sys
3. 优化思路分解
3.1 硬件优化建议
3.1.1 主机
# 服务器品牌
真实的硬件（X86 PC Server）: DELL R系列 ，华为，浪潮，HP，联想(IBM)
云产品：ECS、数据库RDS、DRDS、polarDB
IBM 小型机 P6 570 595 P7 720 750 780 P8
# CPU根据数据库类型
OLTP ： 在线事务处理 IO密集型，线上系统，OLTP主要是IO密集型的业务，高并发 ，E系列（至强），主频相对低，核心数量多
OLAP : CPU密集型：数据分析数据处理，OLAP，cpu密集型的，需要CPU高计算能力（i系列，IBM power系列 I 系列的，主频很高，核心少 )
OLTP ： 在线事务处理 IO密集型，线上系统，OLTP主要是IO密集型的业务，高并发 ，E系列（至强），主频相对低，核心数量多
OLAP : CPU密集型：数据分析数据处理，OLAP，cpu密集型的，需要CPU高计算能力（i系列，IBM power系列 I 系列的，主频很高，核心少 )
# 内存
建议2-4倍cpu核心数量 （ECC）
建议2-4倍cpu核心数量 （ECC）
# 磁盘选择
SATA-III , SAS , Fc , SSD（sata）, pci-e ssd , Flash
db01 [(none)]>show variables like '%innodb_io%'
-> ;
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
+------------------------+-------+
2 rows in set (0.00 sec)
SATA-III , SAS , Fc , SSD（sata）, pci-e ssd , Flash
db01 [(none)]>show variables like '%innodb_io%'
-> ;
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
+------------------------+-------+
2 rows in set (0.00 sec)
db01 [(none)]>
innodb_io_capacity 磁盘类型
200-500 : 普通 SATA III
500-2000 ： SAS - SAS*6 raid
2000- 5000 : SSD
5000-8000 : Flash
200-500 : 普通 SATA III
500-2000 ： SAS - SAS*6 raid
2000- 5000 : SSD
5000-8000 : Flash
# 主机 RAID卡的 BBU(Battery Backup Unit)关闭
案例 1 ： 有规律的一段时间，会产生性能低谷
场景：
每隔 28-90天 一段时间的性能低谷（ 1s - 3s+）
在MySQL日常运行期间，业务上反映，每隔一段时间会出现性能下降的情况。
经过监控排查，业务的并发QPS平均值在1000-1200左右。并不是太高。
平时的slowlog有部分慢查询，但放到MySQL端直接执行都比较快。
场景：
每隔 28-90天 一段时间的性能低谷（ 1s - 3s+）
在MySQL日常运行期间，业务上反映，每隔一段时间会出现性能下降的情况。
经过监控排查，业务的并发QPS平均值在1000-1200左右。并不是太高。
平时的slowlog有部分慢查询，但放到MySQL端直接执行都比较快。
原因1： 有规律
raid卡充放电（28-90天），WB-WT转换
Raid卡 Consistency Check 一致性检查 (168H) ， Patrol Read（168H） 巡读
raid卡充放电（28-90天），WB-WT转换
Raid卡 Consistency Check 一致性检查 (168H) ， Patrol Read（168H） 巡读
建议联系: 系统管理员 关闭相应功能
了解一下： LSI卡的管理命令 , MegaCli命令
了解一下： LSI卡的管理命令 , MegaCli命令
原因2：
统计信息过旧,导致索引失效
统计信息过旧,导致索引失效
# 存储
根据存储数据种类的不同，选择不同的存储设备
配置合理的RAID级别(raid5、raid10、热备盘)
r0 :条带化 ,性能高
r1 :镜像，安全
r5 :校验+条带化，安全较高+性能较高（读），写性能较低 （适合于读多写少）
r10：安全+性能都很高，最少四块盘，浪费一半的空间（高IO要求）
避免使用LVM技术。
断电数据损坏，修复复杂。
过度条带化会增加IOPS压力
断电数据损坏，修复复杂。
过度条带化会增加IOPS压力
# 网络
1、硬件买好的（单卡单口）
2、网卡绑定（bonding），交换机堆叠
1、硬件买好的（单卡单口）
2、网卡绑定（bonding），交换机堆叠
以上问题，提前规避掉。
3.2 操作系统优化
# Swap调整
swap 被使用的策略 ，通过以下参数控制
cat /proc/sys/vm/swappiness
C7 : 30 使用量 70%
C6 : 60 使用量 40%
swap 被使用的策略 ，通过以下参数控制
cat /proc/sys/vm/swappiness
C7 : 30 使用量 70%
C6 : 60 使用量 40%
echo 0 >/proc/sys/vm/swappiness 的内容改成0（临时）
vim /etc/sysctl.conf
#添加：
vm.swappiness=0（永久）
sysctl -p
vim /etc/sysctl.conf
#添加：
vm.swappiness=0（永久）
sysctl -p
这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。
在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。
在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。
# IO调度策略
centos 7 默认是deadline ，比较适合于机械类的磁盘.
cat /sys/block/sda/queue/scheduler
说明: 如果是SSD或者flash ,我们建议电梯(noop)的调度方式.
#临时修改为deadline(centos6)
echo deadline >/sys/block/sda/queue/scheduler
echo deadline >/sys/block/sda/queue/scheduler
vi /boot/grub/grub.conf
更改到如下内容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet
更改到如下内容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet
IO ：
raid
no lvm
ext4或xfs
ssd
IO调度策略
raid
no lvm
ext4或xfs
ssd
IO调度策略
提前规划好以上所有问题，减轻MySQL优化的难度。
3.3 应用端
1. 开发过程规范,标准
2. 减少烂SQL:不走索引,复杂逻辑,切割大事务.
3. 避免业务逻辑错误,避免锁争用.
这个阶段,需要我们DBA深入业务,或者要和开发人员业务人员配合实现
优化,最根本的是"优化"人.
----oldguo
----oldguo
4. MySQL参数优化测试
虚拟机vm12.5，OS centos 6.9（系统已优化），cpu*4（I5 4440 3.1GHZ）,MEM*4GB ,HardDisk:SSD
模拟数据库数据
drop database if exists oldboy;
create database oldboy charset utf8mb4 collate utf8mb4_bin;
use oldboy;
create table t_100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);
delimiter //
create procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into t_100w values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter ;
插入100w条数据：
call rand_data(10000000);
commit;
drop database if exists oldboy;
create database oldboy charset utf8mb4 collate utf8mb4_bin;
use oldboy;
create table t_100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);
delimiter //
create procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into t_100w values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter ;
插入100w条数据：
call rand_data(10000000);
commit;
mysqlslap --defaults-file=/etc/my.cnf
--concurrency=100 --iterations=1 --create-schema='test'
--query="select * from test.t100w where k2='FGCD'" engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose
--concurrency=100 --iterations=1 --create-schema='test'
--query="select * from test.t100w where k2='FGCD'" engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose
5. 优化细节：
5.1 Max_connections *****
（1）简介
Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。
（2）判断依据
show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
show status like 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 101 |
+----------------------+-------+
（3）修改方式举例
vim /etc/my.cnf
Max_connections=1024
vim /etc/my.cnf
Max_connections=1024
补充:
1.开启数据库时,我们可以临时设置一个比较大的测试值
2.观察show status like 'Max_used_connections';变化
3.如果max_used_connections跟max_connections相同,
那么就是max_connections设置过低或者超过服务器的负载上限了，
低于10%则设置过大.
案例二：
连接数设置不生效的问题，214问题。
* soft nofile 65536
* hard nofile 65536
1.开启数据库时,我们可以临时设置一个比较大的测试值
2.观察show status like 'Max_used_connections';变化
3.如果max_used_connections跟max_connections相同,
那么就是max_connections设置过低或者超过服务器的负载上限了，
低于10%则设置过大.
案例二：
连接数设置不生效的问题，214问题。
* soft nofile 65536
* hard nofile 65536
5.2 back_log ***
（1）简介
mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它
（2）判断依据
show full processlist
发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值
（3）修改方式举例
vim /etc/my.cnf
back_log=1024
（1）简介
mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它
（2）判断依据
show full processlist
发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值
（3）修改方式举例
vim /etc/my.cnf
back_log=1024
5.3 wait_timeout和interactive_timeout ****
（1）简介
wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数
interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为7200。
wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
（2）设置建议
如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低
（3）修改方式举例
wait_timeout=120
interactive_timeout=7200
长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。
一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。
另外还可以使用类外的参数弥补。
一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。
另外还可以使用类外的参数弥补。
案例三：MySQL 连接长时间(7200和1200秒)无法释放
场景： MySQL 5.7 ， DELL730 E5-2650 96G内存 1主2从
Keepalive + LVS + 1主 2从
场景： MySQL 5.7 ， DELL730 E5-2650 96G内存 1主2从
Keepalive + LVS + 1主 2从
处理方法：
ipvsadmin -l -timeout
Timeout (tcp tcpfin udp ): 90 120 300
net.ipv4.tcp_keepalive_time = 60
ipvsadmin -l -timeout
Timeout (tcp tcpfin udp ): 90 120 300
net.ipv4.tcp_keepalive_time = 60
5.4 key_buffer_size *****
（1）简介
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度
《1》此参数与myisam表的索引有关
《2》临时表的创建有关（多表链接、子查询中、union）
在有以上查询语句出现的时候，需要创建临时表，用完之后会被丢弃
临时表有两种创建方式：
内存中------->key_buffer_size
磁盘上------->ibdata1(5.6)
ibtmp1 (5.7）
注：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值。
可以使用检查状态值created_tmp_disk_tables得知：
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度
《1》此参数与myisam表的索引有关
《2》临时表的创建有关（多表链接、子查询中、union）
在有以上查询语句出现的时候，需要创建临时表，用完之后会被丢弃
临时表有两种创建方式：
内存中------->key_buffer_size
磁盘上------->ibdata1(5.6)
ibtmp1 (5.7）
注：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值。
可以使用检查状态值created_tmp_disk_tables得知：
mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql>
通常地，我们习惯以
Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables)
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql>
通常地，我们习惯以
Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables)
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)
或者已各自的一个时段内的差额计算，来判断基于内存的临时表利用率。所以，我们会比较关注 Created_tmp_disk_tables 是否过多，从而认定当前服务器运行状况的优劣。
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)
控制在5%-10%以内
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)
控制在5%-10%以内
看以下例子：
在调用mysqldump备份数据时，大概执行步骤如下：
180322 17:39:33 7 Connect root@localhost on
7 Query /*!40100 SET @@SQL_MODE='' */
7 Init DB guo
7 Query SHOW TABLES LIKE 'guo'
7 Query LOCK TABLES `guo` READ /*!32311 LOCAL */
7 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1
7 Query show create table `guo`
7 Query show fields from `guo`
7 Query show table status like 'guo'
7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `guo`
7 Query UNLOCK TABLES
7 Quit
其中，有一步是：show fields from `guo`。从slow query记录的执行计划中，可以知道它也产生了 Tmp_table_on_disk。
所以说，以上公式并不能真正反映到mysql里临时表的利用率，有些情况下产生的 Tmp_table_on_disk 我们完全不用担心，因此没必要过分关注 Created_tmp_disk_tables，但如果它的值大的离谱的话，那就好好查一下，你的服务器到底都在执行什么查询了。
所以说，以上公式并不能真正反映到mysql里临时表的利用率，有些情况下产生的 Tmp_table_on_disk 我们完全不用担心，因此没必要过分关注 Created_tmp_disk_tables，但如果它的值大的离谱的话，那就好好查一下，你的服务器到底都在执行什么查询了。
（3）配置方法
key_buffer_size=64M
key_buffer_size=64M
5.5 query_cache_size ***
（1）简介：
查询缓存简称QC，主要缓存SQL语句hash值+执行结果。
10条语句，经常做查询。
案例四 ： 开QC ，导致性能降低。 QPS ，TPS降低。
没开起的时候。QPS 2000 TPS 500
开了之后直接降低到 800，200
为什么呢？
分区表。Query Cache 不支持。
没开起的时候。QPS 2000 TPS 500
开了之后直接降低到 800，200
为什么呢？
分区表。Query Cache 不支持。
5.6 sort_buffer_size ***
（1）简介：
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速
ORDER BY
GROUP BY
distinct
union
（2）配置依据
Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。
列如：500个连接将会消耗500*sort_buffer_size（2M）=1G内存
（3）配置方法
修改/etc/my.cnf文件，在[mysqld]下面添加如下：
sort_buffer_size=1M
（1）简介：
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速
ORDER BY
GROUP BY
distinct
union
（2）配置依据
Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。
列如：500个连接将会消耗500*sort_buffer_size（2M）=1G内存
（3）配置方法
修改/etc/my.cnf文件，在[mysqld]下面添加如下：
sort_buffer_size=1M
建议： 尽量排序能够使用索引更好。
5.7 max_allowed_packet *****
（1）简介：
mysql根据配置文件会限制，server接受的数据包大小。
（2）配置依据：
有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数
（3）配置方法：
max_allowed_packet=32M
5.8 join_buffer_size ***
select a.name,b.name from a join b on a.id=b.id where xxxx
用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
尽量在SQL与方面进行优化，效果较为明显。
优化的方法：在on条件列加索引，至少应当是有MUL索引
建议： 尽量能够使用索引优化更好。
5.9 thread_cache_size = 16 *****
(1)简介
服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.
（2）配置依据
通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。
设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。
服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)
通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。
设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。
服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)
试图连接到MySQL(不管是否连接成功)的连接数
mysql> show status like 'threads_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 8 |
| Threads_connected | 2 |
| Threads_created | 4783 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
mysql> show status like 'threads_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 8 |
| Threads_connected | 2 |
| Threads_created | 4783 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected:代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created:代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗cpu SYS资源，可以适当增加配置文件中thread_cache_size值。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。
Threads_connected:代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created:代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗cpu SYS资源，可以适当增加配置文件中thread_cache_size值。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。
(3)配置方法：
thread_cache_size=32
thread_cache_size=32
整理：
Threads_created ：一般在架构设计阶段，会设置一个测试值，做压力测试。
结合zabbix监控，看一段时间内此状态的变化。
如果在一段时间内，Threads_created趋于平稳，说明对应参数设定是OK。
如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下（内存）
Threads_created ：一般在架构设计阶段，会设置一个测试值，做压力测试。
结合zabbix监控，看一段时间内此状态的变化。
如果在一段时间内，Threads_created趋于平稳，说明对应参数设定是OK。
如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下（内存）
5.10 innodb_buffer_pool_size *****
（1）简介
对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。
（2）配置依据：
InnoDB使用该参数指定大小的内存来缓冲数据和索引。
对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%。
（3）配置方法
innodb_buffer_pool_size=2048M
（1）简介
对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。
（2）配置依据：
InnoDB使用该参数指定大小的内存来缓冲数据和索引。
对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%。
（3）配置方法
innodb_buffer_pool_size=2048M
5.11 innodb_flush_log_at_trx_commit ******
（1）简介
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；
1，
每次事务的提交都会引起redo日志文件写入、flush磁盘的操作，确保了事务的ACID；
2，每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。
（2）配置依据
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。
（3）配置方法
innodb_flush_log_at_trx_commit=1
双1标准中的一个1
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。
（3）配置方法
innodb_flush_log_at_trx_commit=1
双1标准中的一个1
5.12 innodb_thread_concurrency ***
（1）简介
此参数用来设置innodb线程的并发数量，默认值为0表示不限制。
（2）配置依据
在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：
如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，
并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，
例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，
性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，
建议设置innodb_thread_concurrency参数为80，以避免影响性能。
如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），
建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），
如果你的目标是将MySQL与其他应用隔离，你可以l考虑绑定mysqld进程到专有的虚拟CPU。
但是需 要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，
你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。
在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。
定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。
（1）简介
此参数用来设置innodb线程的并发数量，默认值为0表示不限制。
（2）配置依据
在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：
如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，
并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，
例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，
性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，
建议设置innodb_thread_concurrency参数为80，以避免影响性能。
如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），
建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），
如果你的目标是将MySQL与其他应用隔离，你可以l考虑绑定mysqld进程到专有的虚拟CPU。
但是需 要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，
你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。
在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。
定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。
128 -----> top cpu
设置标准：
1、当前系统cpu使用情况，均不均匀
top
1、当前系统cpu使用情况，均不均匀
top
2、当前的连接数，有没有达到顶峰
show status like 'threads_%';
show processlist;
show status like 'threads_%';
show processlist;
（3）配置方法：
innodb_thread_concurrency=8
innodb_thread_concurrency=8
方法:
1. 看top ,观察每个cpu的各自的负载情况
2. 发现不平均,先设置参数为cpu个数,然后不断增加(一倍)这个数值
3. 一直观察top状态,直到达到比较均匀时,说明已经到位了.
1. 看top ,观察每个cpu的各自的负载情况
2. 发现不平均,先设置参数为cpu个数,然后不断增加(一倍)这个数值
3. 一直观察top状态,直到达到比较均匀时,说明已经到位了.
5.1.3 innodb_log_buffer_size
此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
innodb_log_buffer_size=128M
设定依据：
1、大事务： 存储过程调用 CALL
2、多事务
5.14 innodb_log_file_size = 100M *****
设置 ib_logfile0 ib_logfile1
此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.
innodb_log_file_size = 100M
innodb_log_files_in_group = 3 *****
为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
read_buffer_size = 1M **
MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享
MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享
read_rnd_buffer_size = 1M **
MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。
MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。
bulk_insert_buffer_size = 8M **
change_buffer_size=8M
change_buffer_size=8M
批量插入数据缓存大小，可以有效提高插入效率，默认为8M
tokuDB percona
myrocks
RocksDB
TiDB
MongoDB
tokuDB percona
myrocks
RocksDB
TiDB
MongoDB
binary log *****
双1标准(基于安全的控制)：
sync_binlog=1 什么时候刷新binlog到磁盘，每次事务commit
innodb_flush_log_at_trx_commit=1
安全参数 *****
Innodb_flush_method=(O_DIRECT, fsync)
1、fsync ：
（1）在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
Innodb_flush_method=(O_DIRECT, fsync)
1、fsync ：
（1）在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
2、 Innodb_flush_method=O_DIRECT
（1）在数据页需要持久化时，直接写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
（1）在数据页需要持久化时，直接写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
最安全模式：
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
最高性能模式：
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync
一般情况下，我们更偏向于安全。
“双一标准”
innodb_flush_log_at_trx_commit=1 ***************
sync_binlog=1 ***************
innodb_flush_method=O_DIRECT
6. 参数优化结果
[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=4096M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock
再次压力测试 ：
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose
7. 锁的监控及处理
7.1 锁等待模拟
tx1:
USE test
UPDATE t100w SET k1='av' WHERE id=10;
USE test
UPDATE t100w SET k1='av' WHERE id=10;
## tx2:
USE test
UPDATE t100w SET k1='az' WHERE id=10;
USE test
UPDATE t100w SET k1='az' WHERE id=10;
监控锁状态:
## 1. 看有没有锁等待
SHOW STATUS LIKE 'innodb_row_lock%';
SHOW STATUS LIKE 'innodb_row_lock%';
## 2. 查看哪个事务在等待(被阻塞了)
USE information_schema
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
trx_id : 事务ID号
trx_state : 当前事务的状态
trx_mysql_thread_id:连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query : 当前被阻塞的操作(一般是要丢给开发的)
USE information_schema
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
trx_id : 事务ID号
trx_state : 当前事务的状态
trx_mysql_thread_id:连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query : 当前被阻塞的操作(一般是要丢给开发的)
7.3.查看锁源,谁锁的我!
SELECT * FROM sys.innodb_lock_waits; ## ====>被锁的和锁定它的之间关系
locked_table : 哪张表出现的等待
waiting_trx_id: 等待的事务(与上个视图trx_id 对应)
waiting_pid : 等待的线程号(与上个视图trx_mysql_thread_id)
blocking_trx_id : 锁源的事务ID
blocking_pid : 锁源的线程号
waiting_trx_id: 等待的事务(与上个视图trx_id 对应)
waiting_pid : 等待的线程号(与上个视图trx_mysql_thread_id)
blocking_trx_id : 锁源的事务ID
blocking_pid : 锁源的线程号
7.4. 找到锁源的thread_id
SELECT * FROM performance_schema.threads WHERE processlist_id=3;
====> 28
7.5. 找到锁源的SQL语句
-- 当前在执行的语句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=28;
-- 执行语句的历史
SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=28;
SELECT * FROM performance_schema.threads WHERE processlist_id=3;
====> 28
7.5. 找到锁源的SQL语句
-- 当前在执行的语句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=28;
-- 执行语句的历史
SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=28;
得出结果,丢给开发
表信息
被阻塞的
锁源SQL
表信息
被阻塞的
锁源SQL
练习:
一键获得以上信息,请写出具体的SQL语句
7.6 优化项目:锁的监控及处理
1. 背景:
硬件环境: DELL R720,E系列16核,48G MEM,SAS*900G*6,RAID10
在例行巡检时,发现9-11点时间段的CPU压力非常高(80-90%)
2. 项目的职责
2.1 通过top详细排查,发现mysqld进程占比达到了700-800%
2.2 其中有量的CPU是被用作的SYS和WAIT,us处于正常
2.3 怀疑是MySQL 锁 或者SQL语句出了问题
2.4 经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句
(1) pt-query-diagest 查看慢日志
(2) 锁等待有没有?
db03 [(none)]>show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
情况一:
有100多个current_waits,说明当前很多锁等待情况
情况二:
1000多个lock_waits,说明历史上发生过的锁等待很多
2.5 查看那个事务在等待(被阻塞了)
2.6 查看锁源事务信息(谁锁的我)
2.7 找到锁源的thread_id
2.8 找到锁源的SQL语句
3. 找到语句之后,和应用开发人员进行协商
(1)
开发人员描述,此语句是事务挂起导致
我们提出建议是临时kill 会话,最终解决问题
(2)
开发人员查看后,发现是业务逻辑问题导致的死锁,产生了大量锁等待
临时解决方案,将阻塞事务的会话kill掉.
最终解决方案,修改代码中的业务逻辑
项目结果:
经过排查处理,锁等待的个数减少80%.解决了CPU持续峰值的问题.
锁监控设计到的命令:
show status like 'innodb_rows_lock%'
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;
2.1 通过top详细排查,发现mysqld进程占比达到了700-800%
2.2 其中有量的CPU是被用作的SYS和WAIT,us处于正常
2.3 怀疑是MySQL 锁 或者SQL语句出了问题
2.4 经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句
(1) pt-query-diagest 查看慢日志
(2) 锁等待有没有?
db03 [(none)]>show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
情况一:
有100多个current_waits,说明当前很多锁等待情况
情况二:
1000多个lock_waits,说明历史上发生过的锁等待很多
2.5 查看那个事务在等待(被阻塞了)
2.6 查看锁源事务信息(谁锁的我)
2.7 找到锁源的thread_id
2.8 找到锁源的SQL语句
3. 找到语句之后,和应用开发人员进行协商
(1)
开发人员描述,此语句是事务挂起导致
我们提出建议是临时kill 会话,最终解决问题
(2)
开发人员查看后,发现是业务逻辑问题导致的死锁,产生了大量锁等待
临时解决方案,将阻塞事务的会话kill掉.
最终解决方案,修改代码中的业务逻辑
项目结果:
经过排查处理,锁等待的个数减少80%.解决了CPU持续峰值的问题.
锁监控设计到的命令:
show status like 'innodb_rows_lock%'
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;
7.7 死锁监控
show engine innodb statusG
show variables like '%deadlock%';
vim /etc/my.cnf
innodb_print_all_deadlocks = 1
innodb_print_all_deadlocks = 1
+++++++++++++++
死锁案例：
1. 并发删除数据造成的死锁
t1 表有10行数据，id PK ,num
session 1：
delete 1
delete 2
delete 3
delete 4
session : 2
delete 6
delete 5
delete 4
delete 3
delete 6
delete 5
delete 4
delete 3
2. 删除不存在数据的死锁
t1
id PK
1
5
session1
delete 1
insert 2
delete 1
insert 2
session2
delete 4
insert 4
delete 4
insert 4
3. 插入意向锁造成死锁
t2
a(pk) b
10 0
20 0
session1:
update t set b=1 where a=20
insert 11,1
update t set b=1 where a=20
insert 11,1
session2 :
select * from t lock in share mode
insert 15,2
++++++++++++++++++++++++++++++++++++++++
select * from t lock in share mode
insert 15,2
++++++++++++++++++++++++++++++++++++++++
8. 主从优化：
## 5.7 从库多线程MTS
基本要求:
5.7以上的版本(忘记小版本)
必须开启GTID
binlog必须是row模式
基本要求:
5.7以上的版本(忘记小版本)
必须开启GTID
binlog必须是row模式
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
5.7 :
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8
cpu核心数作为标准
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8
cpu核心数作为标准
CHANGE MASTER TO
MASTER_HOST='10.0.0.128',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_AUTO_POSITION=1;
start slave;
MASTER_HOST='10.0.0.128',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_AUTO_POSITION=1;
start slave;
9. 分页优化
select * from t limit 800000,5000
普通优化：
select * from (select id from t limit limit 8000000,5000) b t a where a.id=b.id;
select * from (select id from t limit limit 8000000,5000) b t a where a.id=b.id;
业务优化：
select* from t where id>last_id order by id desc limit 5000
select* from t where id>last_id order by id desc limit 5000
10. TPS和QPS指标
一、tps计算
tps = (Com_commit + Com_rollback) / Uptime
其中Com_commit是已提交事务数目，Com_rollback是回滚的事务数目，Uptime是运行时间。
一、tps计算
tps = (Com_commit + Com_rollback) / Uptime
其中Com_commit是已提交事务数目，Com_rollback是回滚的事务数目，Uptime是运行时间。
获取方式：
show global status like "Com_commit";
show global status like 'uptime';
eg：通过查看show global status得到commit为132842， rollback为435， Uptime为364，则：
tps = (132842 + 435) / 364 = 366.14560439560439560440
show global status like "Com_commit";
show global status like 'uptime';
eg：通过查看show global status得到commit为132842， rollback为435， Uptime为364，则：
tps = (132842 + 435) / 364 = 366.14560439560439560440
二、qps计算
两种计算方式
1、基于Questions计算
qps = Questions / Uptime
eg：通过查看show global status得到Questions为2417815，Uptime为364，则：
qps = 2417815 / 364 = 6642.34890109890109890110
注意到mysql的status里面还有个Queries: 2417824，比Questions大一点，这两个的区别：
Queries：这个状态变量表示，mysql系统接收的查询的次数，包括存储过程内部的查询
Questions：这个状态变量表示，mysql系统接收查询的次数，但是不包括存储过程内部的查询
所以用Questions而不是Queries
Queries：这个状态变量表示，mysql系统接收的查询的次数，包括存储过程内部的查询
Questions：这个状态变量表示，mysql系统接收查询的次数，但是不包括存储过程内部的查询
所以用Questions而不是Queries