二十八-二十九 | SQL慢查询和Redis响应延迟
项目准备使用vagrant准备两台虚拟机机器配置：2C2G服务端VM预先安装 docker、sysstat 等工具案例由三个容器组成，包括一个 MySQL 数据库应用、一个商品搜索应用以及一个数据处理的应用。其中，商品搜索应用以 HTTP 的形式提供了一个接口。/：返回 Index Page；/db/insert/products/：插入指定数量的商品信息；/products/：查询指定商品的信息
目录
项目准备
使用vagrant准备两台虚拟机机器
配置：2C2G
服务端VM预先安装 docker、sysstat 等工具
案例
由三个容器组成，包括一个 MySQL 数据库应用、一个商品搜索应用以及一个数据处理的应用。其中，商品搜索应用以 HTTP 的形式提供了一个接口。
- /：返回 Index Page；
- /db/insert/products/：插入指定数量的商品信息；
- /products/：查询指定商品的信息，并返回处理时间
项目架构
运行项目
这个项目有点复杂，需要一些前置知识，例如make、Makefile和Dockerfile的结合使用，这里应该也可以用Docker Compose或K8s完成.
git clone https://github.com/feiskyer/linux-perf-examples
makefile的内容，将三个docker 镜像管理起来。
DATA_NUM ?= 10000
.PHONY: run
run: mysql dataservice
docker run --name=app --network=container:mysql -itd feisky/mysql-slow
.PHONY: dataservice
dataservice:
docker run --name=dataservice -itd --privileged feisky/mysql-dataservice
.PHONY: mysql
mysql:
docker run --name=mysql -itd -p 10000:80 -m 800m feisky/mysql:5.6
.PHONY: init
init:
docker exec -i mysql mysql -uroot -P3306 < tables.sql
curl http://127.0.0.1:10000/db/insert/products/$(DATA_NUM)
.PHONY: build
build:
docker build -t feisky/mysql-slow -f Dockerfile .
docker build -t feisky/mysql:5.6 -f Dockerfile.mysql .
docker build -t feisky/mysql-dataservice -f Dockerfile.dataservice .
.PHONY: test
test:
curl http://127.0.0.1:10000/products/geektime
.PHONY: push
push:
docker push feisky/mysql-slow
docker push feisky/mysql:5.6
docker push feisky/mysql-dataservice
.PHONY: clean
clean:
docker rm -f app mysql dataservice || echo "Containers already deleted."
make run启动项目
跑了6-7分钟三个应用就起来了。
docker logs -f mysql
确认mysql 服务是否开启成功，如下代表启动成功，如有报错根据log排查.
商品搜索应用在 10000 端口监听是否正常
make init向mysql插入数据
由于我的机器是SSD，因此修改makefile中插入的数量，改为5000笔。
5000笔跑了30分钟没出来，还是先用1W测
访问一下商品搜索的接口
返回数据为空,而且处理时间超过 2S，太慢了.. 这可是SSD呀.
因为是SSD，因此
top
SSD盘这点压力还能扛得住. 所以iowait很少。
iostat
pidstat
show processlist 命令
show full processlist 命令保证 SQL 语句不截断
mysql> show full processlist;
+----+------+-----------------+------+---------+------+--------------+-----------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+--------------+-----------------------------------------------------+
| 27 | root | localhost | test | Query | 0 | init | show full processlist |
| 28 | root | 127.0.0.1:42262 | test | Query | 1 | Sending data | select * from products where productName='geektime' |
+----+------+-----------------+------+---------+------+--------------+-----------------------------------------------------+
2 rows in set (0.00 sec)-
db 表示数据库的名字；
-
Command 表示 SQL 类型；
-
Time 表示执行时间；
-
State 表示状态；
-
而 Info 则包含了完整的 SQL 语句。
慢查询问题？索引导致？
explain 确认索引
# 切换到 test 库
mysql> use test;
# 执行 explain 命令
mysql> explain select * from products where productName='geektime';
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | products | ALL | NULL | NULL | NULL | NULL | 10000 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)-
select_type 表示查询类型，而这里的 SIMPLE 表示此查询不包括 UNION 查询或者子查询；
-
table 表示数据表的名字，这里是 products；
-
type 表示查询类型，这里的 ALL 表示全表查询，但索引查询应该是 index 类型才对；
-
possible_keys 表示可能选用的索引，这里是 NULL；
-
key 表示确切会使用的索引，这里也是 NULL；
-
rows 表示查询扫描的行数，这里是 10000。
扫描行数高达 10000 行。你应该很容易想到优化方法，没有索引那我们就自己建立，给 productName 建立索引就可以了。
创建索引
mysql> CREATE INDEX products_index ON products (productName);
ERROR 1170 (42000): BLOB/TEXT column 'productName' used in key specification without a key length
productName 是一个 BLOB/TEXT 类型，需要设置一个长度。所以，想要创建索引，就必须为 productName 指定一个前缀长度。
那前缀长度设置为多大比较合适呢？这里其实有专门的算法，即通过计算前缀长度的选择性，来确定索引的长度。不过，我们可以稍微简化一下，直接使用一个固定数值（比如 64），执行下面的命令创建索引：
mysql> CREATE INDEX products_index ON products (productName(64));
Query OK, 10000 rows affected (14.45 sec)
Records: 10000 Duplicates: 0 Warnings: 0
curl再次访问
Got data: ()in 15.383180141448975 sec
Got data: ()in 15.384996891021729 sec
Got data: ()in 0.0021054744720458984 sec
Got data: ()in 0.003951072692871094 sec查询时间已经从 15 秒缩短到了 3 毫秒。
查看app.py
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
import pymysql
import random
import string
import time
from flask import Flask
config = {
"host": "127.0.0.1",
"user": "root",
"port": 3306,
"password": "",
"database": "test"
}
app = Flask(__name__)
@app.route('/')
def index():
return 'Index Page'
@app.route("/sleep/<num>")
def sleep(num):
time.sleep(int(num))
return "sleep " + num + "\n"
@app.route('/db/<table>/count')
def select(table):
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "SELECT count(*) FROM " + table
cursor.execute(sql)
data = cursor.fetchone()
return str(data[0]) + "\n"
@app.route('/products/<product>')
def products(product):
start = time.time()
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "select * from products where productName='%s'" % product
cursor.execute(sql)
data = cursor.fetchall()
elapsed = (time.time() - start)
return "Got data: " + str(data) + " in " + str(elapsed) + " sec \n"
@app.route('/db/insert/products/<num>')
def insert_products(num):
values = []
i = 0
while i < int(num):
i += 1
productCode = ''.join(random.choices(
string.ascii_letters + string.digits, k=10240))
productName = ''.join(random.choices(
string.ascii_letters + string.digits, k=10240))
productLine = ''.join(random.choices(
string.ascii_letters + string.digits, k=10240))
productScale = ''.join(random.choices(
string.ascii_letters + string.digits, k=10240))
productVendor = ''.join(random.choices(
string.ascii_letters + string.digits, k=10240))
productDescription = ''.join(random.sample(
string.ascii_letters + string.digits, 8))
quantityInStock = random.randint(1, 1000)
buyPrice = random.uniform(1.0, 10000.0)
MSRP = random.uniform(10.0, 20000.0)
value = (i, productCode, productName, productLine, productScale,
productVendor, productDescription, quantityInStock, buyPrice, MSRP)
values.append(value)
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO products (id, productCode, productName, productLine, productScale, productVendor, productDescription, quantityInStock, buyPrice, MSRP) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
cursor.executemany(sql, values)
db.commit()
cursor.close()
return 'insert ' + num + ' lines' + "\n"
if __name__ == '__main__':
app.run(host='0.0.0.0', debug=True, port=80)
#host一定不要用默认的"127.0.0.1"，不然容器启动，即使映射了端口，在浏览器中也仍然是无法访问服务的。
#将host设置为"0.0.0.0"，这样Flask容器可以接受到宿主的请求。重新思考
DataService 应用对查询的影响
我们启动的几个容器应用。除了 MySQL 和商品搜索应用外，还有一个 DataService 应用。为什么这个案例开始时，要运行一个看起来毫不相关的应用呢？
实际上，DataService 是一个严重影响 MySQL 性能的干扰应用。抛开上述索引优化方法不说，这个案例还有一种优化方法，也就是停止 DataService 应用。
删除索引
# 删除索引
$ docker exec -i -t mysql mysql
mysql> use test;
mysql> DROP INDEX products_index ON products;
curl重新访问
处理时间又变慢了：大概十几秒
while true; do curl http://192.168.0.10:10000/products/geektime; sleep 5; done
Got data: ()in 16.884345054626465 sec停止 DataService 应用
docker rm -f dataservice
再次curl
Got data: ()in 16.884345054626465 sec
Got data: ()in 15.238174200057983 sec
Got data: ()in 0.12604427337646484 sec
Got data: ()in 0.1101069450378418 sec
Got data: ()in 0.11235237121582031 sec停 DataService 后，处理时间从 15 秒缩短到了 0.1 秒，虽然比不上增加索引后的 3 毫秒，但相对于 15 秒来说，优化效果还是非常明显的
vmstat 命令观察服务端
vmstat 可以同时提供 CPU、内存和 I/O 的使用情况
vmstat 1
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
# 备注： DataService 正在运行
0 1 0 7293416 132 366704 0 0 32516 12 36 546 1 3 49 48 0
0 1 0 7260772 132 399256 0 0 32640 0 37 463 1 1 49 48 0
0 1 0 7228088 132 432088 0 0 32640 0 30 477 0 1 49 49 0
0 0 0 7306560 132 353084 0 0 20572 4 90 574 1 4 69 27 0
0 2 0 7282300 132 368536 0 0 15468 0 32 304 0 0 79 20 0
# 备注：DataService 从这里开始停止
0 0 0 7241852 1360 424164 0 0 864 320 133 1266 1 1 94 5 0
0 1 0 7228956 1368 437400 0 0 13328 0 45 366 0 0 83 17 0
0 1 0 7196320 1368 470148 0 0 32640 0 33 413 1 1 50 49 0
...
0 0 0 6747540 1368 918576 0 0 29056 0 42 568 0 0 56 44 0
0 0 0 6747540 1368 918576 0 0 0 0 40 141 1 0 100 0 0在 DataService 停止前，cache 会连续增长三次后再降回去，这正是因为 DataService 每隔 3 秒清理一次页缓存。而 DataService 停止后，cache 就会不停地增长，直到增长为 918576 后，就不再变了。
这时，磁盘的读（bi）降低到 0，同时，iowait（wa）也降低到 0，这说明，此时的所有数据都已经在系统的缓存中了。我们知道，缓存是内存的一部分，它的访问速度比磁盘快得多，这也就能解释，为什么 MySQL 的查询速度变快了很多。
查看dataservice的源代码
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
import time
filename = "/tmp/dataservice"
def write():
with open(filename, "w+") as f:
f.write("data")
def read():
with open(filename) as f:
return f.read()
def clean_cache():
with open("/proc/sys/vm/drop_caches", "w") as f:
f.write("1")
if __name__ == "__main__":
while True:
write()
clean_cache()
read()
time.sleep(3)
ataService 实际上是在读写一个仅包括 “data” 字符串的小文件。不过在读取文件前，它会先把 /proc/sys/vm/drop_caches 改成 1。清空了缓存，导致mysql不能充分利用缓存所以变慢。
MySQL 的 MyISAM 和Innode引擎的对缓存的区别
MySQL 的 MyISAM 引擎，主要依赖系统缓存加速磁盘 I/O 的访问。
MyISAM 的一个特点，就是只在内存中缓存索引，并不缓存数据。
InnoDB 引擎，就同时缓存了索引和数据
个人见解: 初期有很多mysql都是myisam引擎，主要是因为其值缓存index而不缓存数据，比较节省内存，到后来内存廉价之后 ，Innode引擎横空出世，既缓存index 也缓存data。
在这个案例中, 系统中还有其他应用同时运行(dataservice ：定时清空页缓存)， 所以停掉dataservice就恢复正常了。因为是共用系统，谁也说不准什么时候谁会去清空缓存。本身严重依赖OS的缓存就很不靠谱。
所以后来才有了Redis ..等缓存中间件系统。
缓存建议
一般我并不建议，把应用程序的性能优化完全建立在系统缓存上。最好能在应用程序的内部分配内存，构建完全自主控制的缓存；或者使用第三方的缓存应用，比如 Memcached、Redis 等。
Redis性能案例
机器配置
由于vagrant拉起来的VM是SSD，所以将其中一台使用ESC实例。
配置: 1c2g Centos
今天的案例由 Python 应用 +Redis 两部分组成。其中，Python 应用是一个基于 Flask 的应用，它会利用 Redis ，来管理应用程序的缓存，并对外提供三个 HTTP 接口：
-
/：返回 hello redis；
-
/init/：插入指定数量的缓存数据，如果不指定数量，默认的是 5000 条；
-
缓存的键格式为 uuid:
-
缓存的值为 good、bad 或 normal 三者之一
-
/get_cache/<type_name>：查询指定值的缓存数据，并返回处理时间。其中，type_name 参数只支持 good, bad 和 normal（也就是找出具有相同 value 的 key 列表）。
项目架构
docker启动项目
makefile已经写好了，直接make run就好了
make run启动redis 和 app
向redis中插入数据
调用接口
[root@ninesun ~]# curl http://8.142.104.69:10000/init/30000
阿里的云盘效能还可以，我实际测试时向redis写入30000笔数据
访问缓存接口
~]# curl http://8.142.104.69:10000/get_cache
{"count":1644,"data":["890752e8-79b8-11ec-a295-0242ac110002","8114a13a-79b8-11ec-a295-
...
0242ac110002","7fb1953c-79b8-11ec-a295-0242ac110002"],"elapsed_seconds":7.120203495025635,"type":"g为了方便观察，在另外一个VM中循环调用缓存接口：
mysql-slow]# while true;do curl http://8.142.104.69:10000/get_cache 2>&1 >/dev/null; done性能观察
观察Top
iowait已高达90%,cpu使用6%并不是很高。
iostat
ECS的这块云盘已经将近100%的使用率了。
w/s 写请求数达到800多，每秒写3M左右的数据(这是因为阿里云盘的限制，我电脑的SSD盘大约在300MB每秒)
pidstat
看到redis在疯狂的写数据，每秒7-8MB。结合刚才的iostat 3MB/s的吞吐量，可以断定这块盘有严重的性能问题了。
凶手就是redis。
strace查看进程调用栈
# -f 表示跟踪子进程和子线程，-T 表示显示系统调用的时长，-tt 表示显示跟踪时间
$ strace -f -T -tt -p 
这是不加-T -tt的效果
从系统调用来看， epoll_pwait、read、write、fdatasync 这些系统调用都比较频繁。那么，刚才观察到的写磁盘，应该就是 write 或者 fdatasync 导致的了。
lsof确定进程操作的文件
fd 3、4 的是一个 pipe 管道，5 是 eventpoll，7 号是一个普通文件，而 8 号是一个 TCP socket。
结合IO情况，只有 7 号普通文件才会产生磁盘写，而它操作的文件路径是 /data/appendonly.aof，相应的系统调用包括 write 和 fdatasync。
排查redis持久化参数
既然定位到是redis在做持久化，那么是在做bgwriteaof 还是正常的持久化？
显然如果是重写日志文件不会持续频繁，因为大概率是设置了不合理的redis持久化参数(可能是always)。
通过strace -e 选项指定 fdatasync
每间隔4-6ms调用一次fsync，其中<>中的是系统自身调用时间，大约在4-5ms，也就说阿里云ECS的盘效能贼好。
至此我们就找出了 Redis 正在进行写入的文件，也知道了产生大量 I/O 的原因。
redis持久化参数
疑问
什么查询时会有磁盘写呢？按理来说不应该只有数据的读取吗？不是从缓存中取数据么，又不是往redis中写数据。
文件描述符编号为 7 的是一个普通文件 /data/appendonly.aof，而编号为 8 的是 TCP socket。而观察上面的内容，8 号对应的 TCP 读写，是一个标准的“请求 - 响应”格式，即：
-
从 socket 读取 GET uuid:53522908-… 后，响应 good；
-
再从 socket 读取 SADD good 535… 后，响应 1。
对 Redis 来说，SADD 是一个写操作，所以 Redis 还会把它保存到用于持久化的 appendonly.aof 文件中。持久化参数不同，刷新频率不同。
观察更多的 strace 结果，你会发现，每当 GET 返回 good 时，随后都会有一个 SADD 操作，这也就导致了，明明是查询接口，Redis 却有大量的磁盘写。
到这里，我们就找出了 Redis 写磁盘的原因。不过，在下最终结论前，我们还是要确认一下，8 号 TCP socket 对应的 Redis 客户端，到底是不是我们的案例应用。
我们可以给 lsof 命令加上 -i 选项，找出 TCP socket 对应的 TCP 连接信息。不过，由于 Redis 和 Python 应用都在容器中运行，我们需要进入容器的网络命名空间内部，才能看到完整的 TCP 连接。
由于这两个容器共享同一个网络命名空间，所以我们只需要进入 app 的网络命名空间即可(docker的知识需要补一下了)
[root@ninesun ~]# PID=$(docker inspect --format {{.State.Pid}} app)
[root@ninesun ~]# echo $PID
1599431
# -i 表示显示网络套接字信息
[root@ninesun ~]# nsenter --target $PID --net -- lsof -i
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
redis-ser 1599369 100 6u IPv4 8252565 0t0 TCP localhost:redis (LISTEN)
redis-ser 1599369 100 8u IPv4 8252869 0t0 TCP localhost:redis->localhost:56810 (ESTABLISHED)
python 1599431 root 3u IPv4 8252816 0t0 TCP *:http (LISTEN)
python 1599431 root 4u IPv4 8280913 0t0 TCP 172.17.0.2:http->111.18.146.45:danf-ak2 (ESTABLISHED)
python 1599431 root 5u IPv4 8252868 0t0 TCP localhost:56810->localhost:redis (ESTABLISHED)
localhost:6379 是 redis-server 自己的监听端口，自然 localhost:56810就是 redis 的客户端。
最后一行localhost:56810对应的，正是我们的 Python 应用程序 PID为1599431
此外还可以再redis通过monitor来观察
优化redis持久化参数
在查看top、iostat 就没有io性能问题了
缓存接口查询0.4s左右结果就出来了。
代码优化
Python 应用把 Redis 当成临时空间，用来存储查询过程中找到的数据。不过我们知道，这些数据放内存中就可以了，完全没必要再通过网络调用存储到 Redis 中。修改后再调用接口
curl http://8.142.104.69:10000/get_cache_data返回结果不到0.2s
源代码
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
from functools import wraps
from time import time
import json
import random
import uuid
import redis
from flask import Flask, jsonify
app = Flask(__name__)
redis_client = redis.StrictRedis(
host="127.0.0.1", charset="utf-8", port=6379, db=0, decode_responses=True)
def timing(f):
'''wrapper for elapsed_seconds'''
@wraps(f)
def wrapper(*args, **kwargs):
start = time()
result = f(*args, **kwargs)
end = time()
data = json.loads(result.get_data())
data["elapsed_seconds"] = end-start
return jsonify(data)
return wrapper
@app.route('/')
def hello_world():
'''handler for /'''
return 'hello redis\n'
@app.route('/get_cache', defaults={'type_name': 'good'})
@app.route("/get_cache/<type_name>")
@timing
def get_cache(type_name):
'''handler for /get_cache'''
for key in redis_client.scan_iter("uuid:*"):
value = redis_client.get(key)
if value == type_name:
redis_client.sadd(type_name, key[5:])
data = list(redis_client.smembers(type_name))
redis_client.delete(type_name)
return jsonify({"type": type_name, 'count': len(data), 'data': data})
@app.route('/get_cache_data', defaults={'type_name': 'good'})
@app.route("/get_cache_data/<type_name>")
@timing
def get_cache_data(type_name):
'''handler for /get_cache_data'''
keys = [key for key in redis_client.scan_iter("uuid:*")]
values = redis_client.mget(keys)
matched_keys = set([keys[idx][5:] for idx, value in enumerate(values) if value == type_name ])
return jsonify({"type": type_name, 'count': len(matched_keys), 'data': list(matched_keys)})
@app.route('/init', defaults={'num':5000})
@app.route('/init/<num>')
@timing
def initialize(num):
'''handler for /init'''
redis_client.flushall()
types = ('good', 'bad', 'normal')
sample_size = int(num)
random_pool = random.sample(range(0, sample_size), sample_size)
for i in range(sample_size):
key = uuid.uuid1()
redis_client.set('uuid:{}'.format(key), types[(random_pool[i]+i) % 3])
return jsonify({"keys_initialized": sample_size})
if __name__ == '__main__':
app.run(host='0.0.0.0', port=80)
华为开发者空间，是为全球开发者打造的专属开发空间，汇聚了华为优质开发资源及工具，致力于让每一位开发者拥有一台云主机，基于华为根生态开发、创新。
更多推荐
0
0- 0
已为社区贡献10条内容
所有评论(0)