RE: PostgreSQL debug log doesn't record whole procedure(from receiving request to sending response)
От | Chen, Yan-Jack (NSB - CN/Hangzhou) |
---|---|
Тема | RE: PostgreSQL debug log doesn't record whole procedure(from receiving request to sending response) |
Дата | |
Msg-id | a5d754c145e2443db972d9baa8558bbb@nokia-sbell.com обсуждение исходный текст |
Ответ на | Re: PostgreSQL debug log doesn't record whole procedure(from receiving request to sending response) (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > 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?), andthen second about delays in writing/fsyncing WAL (what's the underlying storage? do you have synchronous_commit replicationturned on?). We have the same as your second suspicion. Delays in fsyncing WAL as the backend ceph cluster storage and we see there wassignificant latency increased during that time. Yes. the synchronous_commit is on. fsync | on | Forces synchronizationof updates to disk. synchronous_commit | on | Sets the current transaction's synchronizationlevel. > [ shrug... ] Sure, we could put an elog(DEBUG) after every line of code in the server, and then high-level debugging logswould be even more impossibly voluminous than they are now. I'd say the existing logging gave you plenty of clue whereto look. Actually, do not need to write log for every line code. The last debug log we can see is for CommitTransaction which stateis INPROGRESS. But we can't see when CommitTransaction state is DONE/COMPLETE from the debug log which I think thiskind of log is valuable which can help to identify where the delay comes, from server or client from the postgres serverdebug log only shall be enough without to monitoring and analyzing the TCP message. [2701833-618d1b70.293a09-173273] 2021-11-13 22:25:58.051 GMT <169.254.0.21 UPDATE> DEBUG: 00000: CommitTransaction(1) name:unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 23280/1/1 [2701833-618d1b70.293a09-173274] 2021-11-13 22:25:58.051 GMT <169.254.0.21 UPDATE> LOCATION: ShowTransactionStateRec, xact.c:5333 Best Regards It always takes longer than you expect, even when you take into account ---------------------------------------------------------------------------------------------- Yan-Jack Chen (陈雁) Tel: +8613957141340 Addr: No.567 XinCheng Rd, Binjiang District, Hangzhou, China, 310053 -----Original Message----- From: Tom Lane <tgl@sss.pgh.pa.us> Sent: 2021年11月15日 23:09 To: Chen, Yan-Jack (NSB - CN/Hangzhou) <yan-jack.chen@nokia-sbell.com> Cc: pgsql-general@postgresql.org Subject: Re: PostgreSQL debug log doesn't record whole procedure(from receiving request to sending response) "Chen, Yan-Jack (NSB - CN/Hangzhou)" <yan-jack.chen@nokia-sbell.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 deployedin VM against. It took us days to debug where cause the timeout. We enable PostgreSQL server debug log via belowconfiguration. 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?), andthen second about delays in writing/fsyncing WAL (what's the underlying storage? do you have synchronous_commit replicationturned on?). > This mail is to ask why PostgreSQL debug log doesn¡¯t really include the response message delay which may cause misleadingwhy troubleshooting. It looks to me the debug log doesn¡¯t record the whole procedure. If there are some developeroptions 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 logswould be even more impossibly voluminous than they are now. I'd say the existing logging gave you plenty of clue whereto look. regards, tom lane
В списке pgsql-general по дате отправления: