【BCC动态跟踪PostgreSQL】

2024-01-03 06:10:07

BPF Compiler Collection (BCC)是基于eBPF的Linux内核分析、跟踪、网络监控工具。其源码存放于GitCode - 开发者的代码家园
?

image.png


想要监控PostgreSQL数据库的相关SQL需要在编译PostgreSQL的时候开启dtrace。下文主要介绍几个和PostgreSQL相关的工具,其他工具可根据需求自行了解。

1.dbslower:跟踪慢查询,然后打印超出延时的,默认延时是1ms

//终端 1:
[root@localhost tools]# ./dbslower postgres -p 58474

//终端 2:
postgres=# select pg_backend_pid();
 pg_backend_pid
----------------
          58474
(1 row)
postgres=# select pg_sleep(3);
 pg_sleep
----------

(1 row)
postgres=# select pg_sleep(2);
 pg_sleep
----------

(1 row)
postgres=# select pg_sleep(1);
 pg_sleep
----------

(1 row)
postgres=# select pg_sleep(2);
 pg_sleep
----------

(1 row)

/终端1:

[root@localhost tools]# ./dbslower postgres -p 58474
Tracing database queries for pids 58474 slower than 1 ms...
TIME(s)        PID          MS QUERY
1.893949       58474  3004.362 select pg_sleep(3);
8.492827       58474  2002.784 select pg_sleep(2);
23.334969      58474  2004.094 select pg_sleep(2);

image.png

2.dbstat:跟踪PostgreSQL数据库进程查询,并显示查询延时的直方图

//终端1:
[root@localhost tools]# ./dbstat postgres -p 58518

//终端2:
postgres=# select pg_backend_pid();
 pg_backend_pid
----------------
          58518
(1 row)
postgres=# select pg_sleep(0.2);
 pg_sleep
----------

(1 row)
postgres=# select pg_sleep(0.2);
 pg_sleep
----------

(1 row)
postgres=# select pg_sleep(0.01);
 pg_sleep
----------

(1 row)
postgres=# select pg_sleep(2);
 pg_sleep
----------

(1 row)


//ctrl+c 终端1:
[root@localhost tools]# ./dbstat postgres -p 58518
Tracing database queries for pids 58518 slower than 0 ms...
[02:37:57]
     query latency (ms)  : count     distribution
         0 -> 1          : 0        |                                        |
         2 -> 3          : 0        |                                        |
         4 -> 7          : 0        |                                        |
         8 -> 15         : 1        |********************                    |
        16 -> 31         : 0        |                                        |
        32 -> 63         : 0        |                                        |
        64 -> 127        : 0        |                                        |
       128 -> 255        : 2        |****************************************|
       256 -> 511        : 0        |                                        |
       512 -> 1023       : 0        |                                        |
      1024 -> 2047       : 1        |********************                    |

image.png

3.execsnoop:跟踪新进程创建,跟踪exec函数

//终端1:
[root@localhost tools]# ./execsnoop

//终端2:
[postgres@localhost data_uuid]$ psql
psql (11.20)
Type "help" for help.
postgres=# \q
[postgres@localhost data_uuid]$ psql
psql (11.20)
Type "help" for help.
postgres=# \q
[postgres@localhost data_uuid]$ ls
base          pg_ident.conf  pg_serial     pg_tblspc    postgresql.auto.conf
global        pg_logical     pg_snapshots  pg_twophase  postgresql.conf
pg_commit_ts  pg_multixact   pg_stat       PG_VERSION   postmaster.opts
pg_dynshmem   pg_notify      pg_stat_tmp   pg_wal       postmaster.pid
pg_hba.conf   pg_replslot    pg_subtrans   pg_xact
[postgres@localhost data_uuid]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

//终端1:
[root@localhost tools]# ./execsnoop
PCOMM            PID    PPID   RET ARGS
psql             58579  43545    0 /home/postgres/soft-11-20_uuid/bin/psql
psql             58582  43545    0 /home/postgres/soft-11-20_uuid/bin/psql
ls               58584  43545    0 /bin/ls --color=auto
cat              58585  43545    0 /bin/cat /etc/hosts

image.png

4.filetop:文件读和写的top(文件系统相关)

[root@localhost tools]# ./filetop

02:45:02 loadavg: 0.06 0.10 0.76 2/141 58566

TID     COMM             READS  WRITES R_Kb    W_Kb    T FILE
58566   clear            2      0      8       0       R xterm
58565   filetop          1      0      4       0       R retprobe
58565   filetop          1      0      4       0       R type
24865   irqbalance       3      0      3       0       R stat
24865   irqbalance       3      0      3       0       R interrupts
58565   filetop          2      0      2       0       R loadavg
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity
24865   irqbalance       1      0      1       0       R smp_affinity

image.png

文章来源:https://blog.csdn.net/weixin_47308871/article/details/135346711
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。