Обсуждение: How to trace the postgres?

Hi team,

Now I am start to analyse the process of the postgres, using the gdb.

As you can see, at the beginnning of my work, I use the ps to find the postgres's process.

[postgres@fedora postgresql]$ ps -ef | grep postgres
postgres   67468    1599  0 08:11 ?        00:00:01 /home/postgres/pgsql/lib/bin/postgres -D /home/postgres/pgsql/data
postgres   67471   67468  0 08:11 ?        00:00:00 postgres: checkpointer
postgres   67472   67468  0 08:11 ?        00:00:00 postgres: background writer
postgres   67474   67468  0 08:11 ?        00:00:00 postgres: walwriter
postgres   67475   67468  0 08:11 ?        00:00:00 postgres: autovacuum launcher
postgres   67476   67468  0 08:11 ?        00:00:00 postgres: logical replication launcher
root       90486   90459  0 14:32 pts/0    00:00:00 su postgres
postgres   90494   90486  0 14:32 pts/0    00:00:00 bash
postgres   90526   90494  0 14:32 pts/0    00:00:00 psql
postgres   90527   67468  0 14:32 ?        00:00:00 postgres: postgres postgres [local] idle
root       90680   90652  0 14:33 pts/2    00:00:00 su postgres
postgres   90683   90680  0 14:33 pts/2    00:00:00 bash
postgres   90766   90683  0 14:33 pts/2    00:00:00 ps -ef
postgres   90767   90683  0 14:33 pts/2    00:00:00 grep --color=auto postgres

After that, I use the gdp to debug one of the postgres's process

[postgres@fedora postgresql]$ gdb -p 67468

WaitEventSetWait (set=0x25221d8, timeout=60000, occurred_events=0x7ffe2ea9df70, nevents=64, wait_event_info=0) at latch.c:1478
1478    in latch.c
(gdb) next
1480    in latch.c
(gdb) next
1481    in latch.c
(gdb) next
1484    in latch.c
(gdb) next
1487    in latch.c
(gdb) next
1490    in latch.c
(gdb) next
1492    in latch.c
(gdb) next
1493    in latch.c
(gdb) next
1494    in latch.c
(gdb) next
1495    in latch.c
(gdb) next

With the gdb's work, I use the psql to send the query to the postgres.

One thing confuses me is, I really don't know how to trace the postgres's process, I try to trace the background writer, the walwriter but I still don't know what is them work logical.

I think they seem to work in a loop(I read the related code, that's true), but what I want to verify is to identify all the functions and objects involved in the entire process of PGSQL (from syntax parsing to rewriting, from rewriting to optimization, and from optimization to execution).

Can someone give me some advice?

Thanks in advance!

Yours,

Wen Yi

That's works,

Thanks very much!

Yours,

Wen Yi

 

On Mon, Jun 12, 2023 at 6:38 PM Wen Yi <896634148@qq.com> wrote:
>
> Hi team,
> Now I am start to analyse the process of the postgres, using the gdb.
> As you can see, at the beginnning of my work, I use the ps to find the postgres's process.
>
> [postgres@fedora postgresql]$ ps -ef | grep postgres
> postgres   67468    1599  0 08:11 ?        00:00:01 /home/postgres/pgsql/lib/bin/postgres -D /home/postgres/pgsql/data
> postgres   67471   67468  0 08:11 ?        00:00:00 postgres: checkpointer
> postgres   67472   67468  0 08:11 ?        00:00:00 postgres: background writer
> postgres   67474   67468  0 08:11 ?        00:00:00 postgres: walwriter
> postgres   67475   67468  0 08:11 ?        00:00:00 postgres: autovacuum launcher
> postgres   67476   67468  0 08:11 ?        00:00:00 postgres: logical replication launcher
> root       90486   90459  0 14:32 pts/0    00:00:00 su postgres
> postgres   90494   90486  0 14:32 pts/0    00:00:00 bash
> postgres   90526   90494  0 14:32 pts/0    00:00:00 psql
> postgres   90527   67468  0 14:32 ?        00:00:00 postgres: postgres postgres [local] idle
> root       90680   90652  0 14:33 pts/2    00:00:00 su postgres
> postgres   90683   90680  0 14:33 pts/2    00:00:00 bash
> postgres   90766   90683  0 14:33 pts/2    00:00:00 ps -ef
> postgres   90767   90683  0 14:33 pts/2    00:00:00 grep --color=auto postgres
>
> After that, I use the gdp to debug one of the postgres's process
>
> [postgres@fedora postgresql]$ gdb -p 67468
>
67468 is the postmaster process, you should gdb the 90527, which shows
idle, that means it's waiting for your next psql command.

> WaitEventSetWait (set=0x25221d8, timeout=60000, occurred_events=0x7ffe2ea9df70, nevents=64, wait_event_info=0) at latch.c:1478
> 1478    in latch.c
> (gdb) next
> 1480    in latch.c
> (gdb) next
> 1481    in latch.c
> (gdb) next
> 1484    in latch.c
> (gdb) next
> 1487    in latch.c
> (gdb) next
> 1490    in latch.c
> (gdb) next
> 1492    in latch.c
> (gdb) next
> 1493    in latch.c
> (gdb) next
> 1494    in latch.c
> (gdb) next
> 1495    in latch.c
> (gdb) next
>
> With the gdb's work, I use the psql to send the query to the postgres.
>
> One thing confuses me is, I really don't know how to trace the postgres's process, I try to trace the background writer, the walwriter but I still don't know what is them work logical.
>
> I think they seem to work in a loop(I read the related code, that's true), but what I want to verify is to identify all the functions and objects involved in the entire process of PGSQL (from syntax parsing to rewriting, from rewriting to optimization, and from optimization to execution).
>
> Can someone give me some advice?
> Thanks in advance!
>
> Yours,
> Wen Yi



--
Regards
Junwang Zhao