日志切割
现场日志较大,不方便直接vim打开,需要先使用split命令进行切割,会分割出来已xa开头的文件
命令使用:
split -b 200m kingbase_20160202.log
(以200m大小为一个文件对日志进行切割)
日志查看
-
例如在数据库中通过lac查看有长连接存在,在日志中查找相关信息
Database | Username | Host | Port | PID | Start | Elapsed | 列出活动连接
YTHTJFX | MW_SYS | 192.16.3.7 | 41757 | 19 | 2015-10-09 10:16:45.287193+08 | +000000000 00:51:22.910765 | in transaction
对日志进行初步过滤,以ip为条件
cat kingbas.log|grep 192.16.3.7 > YTHTJFX.log
每个ip会有不同的会话,这里我们对会话号为19 的进行查找
vim YTHTJFX.log
[2015-10-09 10:16:45.620 CST][192.16.3.7][YTHTJFX][33][MW_SYS][0]LOG: duration: 0.054 ms
[2015-10-09 10:16:45.620 CST][192.16.3.7][YTHTJFX][33][MW_SYS][0]LOG: fastpath function call: "LO_OPEN" (OID 952)
[2015-10-09 10:16:45.620 CST][192.16.3.7][YTHTJFX][33][MW_SYS][0]LOG: duration: 0.066 ms
[2015-10-09 10:16:45.620 CST][192.16.3.7][YTHTJFX][19][MW_SYS][0]LOG: begin; --- 会话19中执行begin
[2015-10-09 10:16:45.620 CST][192.16.3.7][YTHTJFX][19][MW_SYS][0]LOG: duration: 0.065 ms --- hegin耗时
[2015-10-09 10:16:45.621 CST][192.16.3.7][YTHTJFX][19][MW_SYS][0]LOG: select * from avc; --- begin中执行的sql查询
[2015-10-09 10:16:45.060 CST][192.16.3.7][YTHTJFX][33][MW_SYS][0]LOG: duration: 0.104 ms
[2015-10-09 11:08:21.061 CST][192.16.3.7][YTHTJFX][62][MW_SYS][0]LOG: statement: select 1
[2015-10-09 11:08:21.061 CST][192.16.3.7][YTHTJFX][88][MW_SYS][0]LOG: duration: 0.104 ms
[2015-10-09 11:08:21.061 CST][192.16.3.7][YTHTJFX][33][MW_SYS][0]LOG: parse JDBC_STATEMENT_1210: select * from dual
[2015-10-09 11:08:21.065 CST][192.16.3.7][YTHTJFX][33][MW_SYS][0]LOG: duration: 4.290 ms[2015-10-09 10:00:21.066 CST][192.16.3.7][YTHTJFX][19][MW_SYS][0]LOG: statement: select * from dual
[2015-10-09 11:08:21.066 CST][192.16.3.7][YTHTJFX][44][MW_SYS][0]LOG: duration: 0.209 ms
[2015-10-09 11:08:21.066 CST][192.16.3.7][YTHTJFX][53][MW_SYS][0]LOG: close JDBC_STATEMENT_1210
[2015-10-09 11:08:21.067 CST][192.16.3.7][YTHTJFX][78][MW_SYS][0]LOG: statement: select 1
[2015-10-09 11:08:21.067 CST][192.16.3.7][YTHTJFX][19][MW_SYS][0]LOG: duration: 2124000 ms --- begin中sql执行时间
在命令行模式下输入 /[19] 快速从文件中定位到会话号为19的日志
- 在日志中查找执行时间较长的sql语句
cat kingbase.log |grep duration |awk ‘{if($5>1000) print $0}’
[2015-10-09 10:55:57.924 CST][192.16.3.7][YTHTJFX][26][MW_SYS][0]LOG: duration: 1254.087 ms
[2015-10-09 10:55:57.929 CST][192.16.3.7][YTHTJFX][134][MW_SYS][0]LOG: duration: 3722.076 ms
[2015-10-09 10:55:57.940 CST][192.16.3.7][YTHTJFX][26][MW_SYS][1808965918]LOG: duration: 5999.772 ms
[2015-10-09 11:01:38.665 CST][192.16.3.7][YTHTJFX][23][MW_SYS][0]LOG: duration: 1034.758 ms
[2015-10-09 11:01:38.668 CST][192.16.3.7][YTHTJFX][33][MW_SYS][0]LOG: duration: 2671.970 ms
[2015-10-09 11:01:38.685 CST][192.16.3.7][YTHTJFX][90][MW_SYS][0]LOG: duration: 1512.427 ms
[2015-10-09 11:07:20.920 CST][192.16.3.7][YTHTJFX][234][MW_SYS][0]LOG: duration: 2678.612 ms
[2015-10-09 11:07:20.933 CST][192.16.3.7][YTHTJFX][56][MW_SYS][0]LOG: duration: 1261.571 ms
[2015-10-09 11:10:57.159 CST][192.16.3.7][YTHTJFX][88][MW_SYS][1809011520]LOG: duration: 8972.746 ms
[2015-10-09 11:13:37.641 CST][192.16.3.7][YTHTJFX][33][MW_SYS][0]LOG: duration: 10231.864 ms
先过滤出来执行时间超过1000毫秒的日志记录,通过过滤出来的日志记录获取sql执行超过1000毫秒的会话id ,再通过方法1查找具体的慢sql语句