Обсуждение: Performance Issue on a table
Dear Team,
Recently we have noticed that in one of our DB instances there is a potential delay in querying a table from java code. could you please check the attached log and help understand what is the problem and which direction should be look into solving this delay of 4 odd mins ?
The table definition is as below, it contains around 2 billion rows.
create table "TAFJ_HASHLOCKS" (recid integer);
alter table "TAFJ_HASHLOCKS" add constraint "TAFJ_HASHLOCKS_PK" PRIMARY KEY (recid);
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl execute
FINEST: simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@13e344d, maxRows=0, fetchSize=0, flags=1
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl sendSimpleQuery
FINEST: FE=> SimpleQuery(query="SAVEPOINT PGJDBC_AUTOSAVE")
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl sendParse
FINEST: FE=> Parse(stmt=null,query="SELECT RECID FROM TAFJ_HASHLOCKS WHERE RECID = $1 FOR UPDATE NOWAIT ",oids={1043})
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl sendBind
FINEST: FE=> Bind(stmt=null,portal=null,$1=<'256292129'>,type=VARCHAR)
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl sendDescribePortal
FINEST: FE=> Describe(portal=null)
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute
FINEST: FE=> Execute(portal=null,limit=0)
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl sendSync
FINEST: FE=> Sync
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl receiveCommandStatus
FINEST: <=BE CommandStatus(RELEASE)
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl receiveRFQ
FINEST: <=BE ReadyForQuery(T)
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl receiveCommandStatus
FINEST: <=BE CommandStatus(SAVEPOINT)
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl receiveRFQ
FINEST: <=BE ReadyForQuery(T)
Jul 22, 2021 4:29:20 PM org.postgresql.core.v3.QueryExecutorImpl processResults
FINEST: <=BE ParseComplete [null]
Jul 22, 2021 4:29:20 PM org.postgresql.core.v3.QueryExecutorImpl processResults
FINEST: <=BE BindComplete [unnamed]
Jul 22, 2021 4:29:20 PM org.postgresql.core.v3.QueryExecutorImpl receiveFields
FINEST: <=BE RowDescription(1)
Thanks
The information in this e-mail and any attachments is confidential and may be legally privileged. It is intended solely for the addressee or addressees. Any use or disclosure of the contents of this e-mail/attachments by a not intended recipient is unauthorized and may be unlawful. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of TEMENOS. We recommend that you check this e-mail and any attachments against viruses. TEMENOS accepts no liability for any damage caused by any malicious code or virus transmitted by this e-mail.
Вложения
On Thu, Jul 22, 2021 at 01:54:25PM +0000, Manoj Kumar wrote:
> Recently we have noticed that in one of our DB instances there is a potential delay in querying a table from java
code.could you please check the attached log and help understand what is the problem and which direction should be look
intosolving this delay of 4 odd mins ?
I'm not familiar with the log, but it looks like the delay is in query parsing
(ParseComplete). Which seems weird. You might try running wireshark to verify
that. Or check postgres logs, and make sure the query isn't being blocked by
DDL commands. Make sure these are enabled:
log_lock_waits = 'on'
deadlock_timeout = '1s'
> 4:25:00 PM ... execute FINEST: simple execute,
handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@13e344d,maxRows=0, fetchSize=0, flags=1
> 4:25:00 PM ... sendSimpleQuery FINEST: FE=> SimpleQuery(query="SAVEPOINT PGJDBC_AUTOSAVE")
> 4:25:00 PM ... sendParse FINEST: FE=> Parse(stmt=null,query="SELECT RECID FROM TAFJ_HASHLOCKS WHERE RECID = $1 FOR
UPDATENOWAIT ",oids={1043})
> 4:25:00 PM ... sendBind FINEST: FE=> Bind(stmt=null,portal=null,$1=<'256292129'>,type=VARCHAR)
> 4:25:00 PM ... sendDescribePortal FINEST: FE=> Describe(portal=null)
> 4:25:00 PM ... sendExecute FINEST: FE=> Execute(portal=null,limit=0)
> 4:25:00 PM ... sendSync FINEST: FE=> Sync
> 4:25:00 PM ... receiveCommandStatus FINEST: <=BE CommandStatus(RELEASE)
> 4:25:00 PM ... receiveRFQ FINEST: <=BE ReadyForQuery(T)
> 4:25:00 PM ... receiveCommandStatus FINEST: <=BE CommandStatus(SAVEPOINT)
> 4:25:00 PM ... receiveRFQ FINEST: <=BE ReadyForQuery(T)
> 4:29:20 PM ... processResults FINEST: <=BE ParseComplete [null]
> 4:29:20 PM ... processResults FINEST: <=BE BindComplete [unnamed]
> 4:29:20 PM ... receiveFields FINEST: <=BE RowDescription(1)