centos环境通过记录mysql的processlist过程信息对性能问题进行定位
在日常的mysql数据库运维中,经常会碰到一些性能问题,比如锁表、慢查询等,可以通过定时获取mysql数据库的 processlist 的信息作为数据库的执行过程收集,为性能优化做参考
大概的思路是:
创建 processlist 相关的表,然后通过脚本定时去mysql中获取processlist信息插入到表中,对这些过程信息分析解决mysql数据库的性能问题
1.创建数据库和收集信息的表结构
# 创建库
CREATE DATABASE monitors_eus DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
# 创建表
CREATE TABLE `all_mysql_processlist` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
`server_ip` varchar(64) NOT NULL DEFAULT '' COMMENT '服务器IP',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`pid` varchar(32) NOT NULL DEFAULT '' COMMENT 'processID',
`USER` varchar(32) NOT NULL DEFAULT '' COMMENT '连接的用户',
`HOST` varchar(64) NOT NULL DEFAULT '' COMMENT '连接的主机',
`DB` varchar(64) DEFAULT NULL COMMENT '连接的数据库',
`COMMAND` varchar(16) NOT NULL DEFAULT '' COMMENT '状态',
`TIME` int(7) NOT NULL DEFAULT '0' COMMENT '运行时间',
`STATE` varchar(64) DEFAULT NULL,
`INFO` longtext COMMENT '执行SQL',
`TIME_MS` bigint(21) NOT NULL DEFAULT '0',
`ROWS_SENT` bigint(21) unsigned NOT NULL DEFAULT '0',
`ROWS_EXAMINED` bigint(21) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_server_ip` (`server_ip`) USING BTREE,
KEY `idx_create_time` (`create_time`) USING BTREE,
KEY `idx_time` (`TIME`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='所有process统计收集表';
CREATE TABLE `all_mysql_processlist_v5` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
`server_ip` varchar(64) NOT NULL DEFAULT '' COMMENT '服务器IP',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`pid` varchar(32) NOT NULL DEFAULT '' COMMENT 'processID',
`USER` varchar(32) NOT NULL DEFAULT '' COMMENT '连接的用户',
`HOST` varchar(64) NOT NULL DEFAULT '' COMMENT '连接的主机',
`DB` varchar(64) DEFAULT NULL COMMENT '连接的数据库',
`COMMAND` varchar(16) NOT NULL DEFAULT '' COMMENT '状态',
`TIME` int(7) NOT NULL DEFAULT '0' COMMENT '运行时间',
`STATE` varchar(64) DEFAULT NULL,
`INFO` longtext COMMENT '执行SQL',
`TIME_MS` bigint(21) NOT NULL DEFAULT '0',
`ROWS_SENT` bigint(21) unsigned NOT NULL DEFAULT '0',
`ROWS_EXAMINED` bigint(21) unsigned NOT NULL DEFAULT '0',
`ROWS_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_server_ip` (`server_ip`) USING BTREE,
KEY `idx_create_time` (`create_time`) USING BTREE,
KEY `idx_time` (`TIME`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='所有process统计收集表 MySQL5.X版本';
CREATE TABLE `all_mysql_processlist_yt` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
`server_ip` varchar(64) NOT NULL DEFAULT '' COMMENT '服务器IP',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`pid` varchar(32) NOT NULL DEFAULT '' COMMENT 'processID',
`USER` varchar(32) NOT NULL DEFAULT '' COMMENT '连接的用户',
`HOST` varchar(64) NOT NULL DEFAULT '' COMMENT '连接的主机',
`DB` varchar(64) DEFAULT NULL COMMENT '连接的数据库',
`COMMAND` varchar(128) NOT NULL DEFAULT '' COMMENT '状态',
`TIME` int(7) NOT NULL DEFAULT '0' COMMENT '运行时间',
`STATE` varchar(128) DEFAULT NULL,
`INFO` longtext COMMENT '执行SQL',
`ROWS_SENT` bigint(21) unsigned NOT NULL DEFAULT '0',
`ROWS_EXAMINED` bigint(21) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_server_ip` (`server_ip`) USING BTREE,
KEY `idx_create_time` (`create_time`) USING BTREE,
KEY `idx_time` (`TIME`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='所有process统计收集表 MySQL8.0版本';
2.在需要被收集信息的数据库中添加相关的账号
用户:sql_monitor_user
权限:process
3.编写收集mysql数据库信息的脚本
# vim 192.168.1.16_db1.sh
#!/bin/bash
# 收集会话
# DATE=`date +%m%d_[%T]`
#定义目标端数据库时使用的用户名和密码
dbuser='sql_monitor_user'
dbpasswd='pass'
port="3306"
server_ip="192.168.1.16"
#server_ip=$1
data_time=`date +%m%d_[%T]`
#定义load数据到服务端时使用的用户名和密码
#GRANT PROCESS, FILE ON *.* TO `monitor_manager_user`@`localhost`
#GRANT SELECT, INSERT, UPDATE, DELETE ON `monitors`.* TO `monitor_manager_user`@`localhost`
#GRANT SELECT, INSERT, UPDATE, DELETE ON `monitors_eus`.* TO `monitor_manager_user`@`localhost`
local_dbuser='monitor_manager_user'
local_dbpasswd='pass'
local_port="3306"
local_host="localhost"
agent_exe_sql="SELECT CONCAT('${server_ip}','|',sysdate(),'|',id, '|', USER, '|', HOST, '|', DB, '|', COMMAND, '|', TIME, '|', STATE,'|', IFNULL(INFO, ''), '|', TIME_MS, '|', ROWS_SENT, '|', ROWS_EXAMINED ) as monitor_result FROM information_schema. PROCESSLIST t where COMMAND <> 'Sleep' AND t.state <> '' AND t.info <> '' ORDER BY time DESC;"
#收集目标机器所有会话
all_mysql_processlist_stat(){
#/usr/local/mysql/bin/mysql -u${dbuser} -p${dbpasswd} -e "${exe_sql1}"
# if [ $? -ne 0 ]; then
echo "begin ...."
result=$(/usr/local/mysql/bin/mysql -u${dbuser} -h${server_ip} -P${port} -p${dbpasswd} -e "${agent_exe_sql}")
if [ $? -eq 0 ]; then
echo "$data_time -- $result" >>/tmp/$(date +%a)_$server_ip.txt
echo "$result" |grep -v 'monitor_result'> /tmp/$server_ip.txt
sed -i '/NULL/d' /tmp/$server_ip.txt
echo "sql result dump ok"
fi
# fi
}
# 通过shell进行导入 --不使用
insert_server_data(){
#需要读取文本的路径
file="/tmp/$server_ip.txt"
# 判断文件是否为空 或者 文本包含 “NULL”
grep -q "NULL" $file
if [ ! -s ${file} ] || [ "$?" -eq "0" ]; then
echo "待导入的文件为空"
exit;
else
echo "文件ok"
fi
#临时设置默认分隔符为|
IFS="|"
OLF_IFS=$IFS
cat $file | while read server_ip create_time pid USER HOST DB COMMAND TIME STATE INFO TIME_MS ROWS_SENT ROWS_EXAMINED
do
#处理INFO自动内容
#INFO字段处理很麻烦,需要处理单引号和双引号的情况,所以使用load数据的方式入库
server_exe_sql="INSERT INTO monitors_eus.all_mysql_processlist_insert_tmp(server_ip,create_time,pid,USER,HOST,DB,COMMAND,TIME,STATE,INFO,TIME_MS,ROWS_SENT,ROWS_EXAMINED) select '${server_ip}','${create_time}','${pid}','${USER}','${HOST}','DB','${COMMAND}','${TIME}','${STATE}','${INFO}','${TIME_MS}','${ROWS_SENT}','${ROWS_EXAMINED}' from dual"
/usr/local/mysql/bin/mysql -u${local_dbuser} -h${local_host} -P${local_port} -p${local_dbpasswd} monitors_eus <<EOF
$server_exe_sql;
EOF
if [ $? -eq 0 ]; then
echo "insert ok"
fi
done
}
#通过load的方式进行导入
load_data_server(){
#需要读取文本的路径
file="/tmp/$server_ip.txt"
#判断文件是否为空 或者 文本包含 “NULL”
grep -q "NULL" $file
if [ ! -s ${file} ] || [ "$?" -eq "0" ]; then
echo "${server_ip} 待导入的文件为空"
exit;
else
echo "${server_ip} 文件 ok"
fi
load_exe_sql="load data infile '$file' into table monitors_eus.all_mysql_processlist fields terminated by '|' lines terminated by '\n'(server_ip,create_time,pid,USER,HOST,DB,COMMAND,TIME,STATE,INFO,TIME_MS,ROWS_SENT,ROWS_EXAMINED) ;"
/usr/local/mysql/bin/mysql -u${local_dbuser} -h${local_host} -P${local_port} -p${local_dbpasswd} -e "$load_exe_sql"
if [ $? -eq 0 ]; then
echo "load ok"
fi
}
#load_data_server
##################### MAIN ###############################
usage () {
echo ""
echo " Please Input args infomation!"
echo ""
echo " USAGE: `basename $0` [all_processlist|lock_processlist]"
echo ""
}
if [ $# != 1 ]
then
usage >&2
exit 1
fi
OPT=$1
case $OPT in
all_processlist)
echo "all_mysql_processlist_stat `basename $0` "
all_mysql_processlist_stat && load_data_server
;;
*)
echo " USAGE: `basename $0` [all_processlist|lock_processlist]"
exit 1
esac
4.创建自动化计划任务1分钟执行一次性能收集
#eus_cart_db2
*/1 * * * * /bin/bash /usr/local/worksh/db_tools/192.168.1.16_db1.sh all_processlist > /dev/null 2>&1
# 收集到的信息
# more /tmp/Sun_192.168.1.16.txt
0301_[00:00:01] -- monitor_result
NULL
0301_[00:01:01] -- monitor_result
NULL
192.168.1.16|2020-03-01 16:06:01|27416829|chinasoft_v2_write|172.17.0.40:38838|chinasoft_online|Execute|0|query end|INSERT INTO task_0 (user_id,task_id,org_file_name,operate_type,start_time,status,password,multi_id,create_time)VALUE('0','aa','105141_5d78613d4fdc2.pdf','pdf_to_pptx','2020-03-01 16:06:01','3','','aa
5a62e4','2020-03-01 16:06:01')|0|0|0
192.168.1.16|2020-03-01 16:06:01|27416830|chinasoft_v2_write|172.17.0.40:38842|chinasoft_online|Execute|0|query end|UPDATE task_0 SET end_time='2020-03-01 16:06:01',download_url='files/chinasoft/bb.docx',output_file_name='2018考试上午真题.docx',status='0' WHERE user_id=0 AND task_id='bb'|0|0|1
NULL
0301_[00:07:01] -- monitor_result
# 收集到数据库中的信息
#
[monitors_eus]> select * from all_mysql_processlist_yt limit 2\G
*************************** 1. row ***************************
id: 1
server_ip: chinasoft.rds.amazonaws.com
create_time: 2020-09-17 12:38:16
pid: 13462643
USER: chinasoft_cloud_write
HOST: 1.1.1.1:57326
DB: chinasoft
COMMAND: Execute
TIME: 0
STATE: delayed commit ok initiated
INFO: update `max_analysis` set `disconnect` = ? where `date` = ?
ROWS_SENT: 0
ROWS_EXAMINED: 0
*************************** 2. row ***************************
id: 2
server_ip: chinasoft.rds.amazonaws.com
create_time: 2020-09-17 12:39:51
pid: 13463423
USER: chinasoft_cloud_write
HOST: 172.31.20.187:59689
DB: edrawusers
COMMAND: Execute
TIME: 0
STATE: Sending data
INFO: select `user_info`.`user_id`, `user_info`.`user_pwd`, `nick_name`, `user_info`.`user_name`, `email`, `mobile`, `user_storage`.`max_storage`, `user_storage`.`used_storage`, `lang`, `profile`, `birthday`, `country`, `region`, `sex`, `activation`, `buy_deadtime`, `reg_time`, `avatar`, `avatar_url`, `migrate`, `weibo_login`.`weibo_id` as `weibo`, `qq_login`.`open_id` as `qq`, `weixin_login`.`open_id` as `weixin` from `user_info` left join `weibo_login` on `user_info`.`user_id` = `weibo_login`.`user_id` left join `qq_login` on `user_info`.`user_id` = `qq_login`.`user_id` left join `weixin_login` on `user_info`.`user_id` = `weixin_login`.`user_id` left join `user_storage` on `user_info`.`user_id` = `user_storage`.`user_id` where `user_info`.`user_id` = '135' limit 1
ROWS_SENT: 0
ROWS_EXAMINED: 0
2 rows in set (0.00 sec)
数据库中的效果