Re: PostgreSQL debug log doesn't record whole procedure(from receiving request to sending response)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Chen, Yan-Jack (NSB - CN/Hangzhou)" <yan-jack(dot)chen(at)nokia-sbell(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: PostgreSQL debug log doesn't record whole procedure(from receiving request to sending response)
Date: 2021-11-15 15:09:27
Message-ID: 2122088.1636988967@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Chen, Yan-Jack (NSB - CN/Hangzhou)" <yan-jack(dot)chen(at)nokia-sbell(dot)com> writes:
> We recently encounter one issue about PostgreSQL ODBC client doesn¡¯t receive response from PostgreSQL server in time (client set 5 seconds timeout) occasionally (1 or 2 times per 24 hours). Both PostgreSQL and its client are deployed in VM against. It took us days to debug where cause the timeout. We enable PostgreSQL server debug log via below configuration.

We can see from this that the server spent 10 seconds in
CommitTransaction, so the question is what took so long.
I'd wonder first about end-of-transaction triggers (have you got foreign
keys on that table? maybe an event trigger?), and then second about
delays in writing/fsyncing WAL (what's the underlying storage? do you
have synchronous_commit replication turned on?).

> This mail is to ask why PostgreSQL debug log doesn¡¯t really include the response message delay which may cause misleading why troubleshooting. It looks to me the debug log doesn¡¯t record the whole procedure. If there are some developer options include the missing part but we didn¡¯t enable?

[ shrug... ] Sure, we could put an elog(DEBUG) after every line of code
in the server, and then high-level debugging logs would be even more
impossibly voluminous than they are now. I'd say the existing logging
gave you plenty of clue where to look.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Olarte 2021-11-15 15:23:50 Re: help with a query
Previous Message Bruce Momjian 2021-11-15 14:31:28 Re: help with a query