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 ' '(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 2G *************************** 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)
数据库中的效果
