KingbaseESV7 日志查看

Q: 日志查看技巧

A:

一.日志切割

  现场日志较大，不方便直接vim打开，需要先使用split命令进行切割，会分割出来已xa开头的文件

  命令使用：

   split -b 200m kingbase_20160202.log 

  (以200m大小为一个文件对日志进行切割)

二.日志查看

   方法1.例如在数据库中通过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 | <IDLE> in transaction

   1.1 对日志进行初步过滤,以ip为条件

    cat kingbas.log|grep 192.16.3.7 > YTHTJFX.log

   1.2 每个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的日志

     

    方法2 在日志中查找执行时间较长的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语句