Logging Analysis

[ehynd]

Is there an existing issue for this?

• I confirm that I have not found an existing or similar issue.

Description

On one of the client teams, turning on logging broke the db and added tons of latency on query times. Once turned back off, querying returned to normal.

Unclear exactly where the issue lies with the logging but know its a BE isse.

Possible Causes:
-Client team reporting is on a cluster
-Adding in a new function messes with the DB
-something else?

Relevant Feature or Code

No response

Requirements (what)

• Do an analysis of how logging is working within the backend to diagnose where the issue is coming from - turning on logging should not substantially change/break how applications are running

[samarthKharote]

Hello @ehynd ,
I have fully analyzed the client's system logs and as per the system's logs, issue was coming from PostgresQueryUtil file.

• In PostgresQueryUtil, enhanceConnection() method was always attempting to execute CREATE OR REPLACE FUNCTION for SMSS_DATEDIFF, which fails in read-only transactions or when the function already exists.
• Currently, Fix has been added into the main branch (commit) which

1. adds a proper check to see if the function exists before creating it, avoiding unnecessary creation attempts.
2. ensures the correct schema is used and uses the proper SQL statement type, making the existence check and function creation more reliable and preventing the original error.

[samarthKharote]

Additionally, worked on AUDIT_LOGS db to optimize it's overall performance. .
In AuditLogsDbUtils Class, worked on

• Query Optimization for getAuditLogsTimeLineDatas() method:
  Added PROJECT_ID and LOG_TIMESTAMP (and other optional filters like USER_ID, ENGINE_ID, ROOM_ID, SESSION_ID) inside the MIN_MAX_DURATION subquery.
  -This ensures the aggregation (MIN/MAX) runs only on relevant rows instead of the full AUDIT_LOGS table.

• Indexes Addition for AUDIT_LOGS table

1. REQUEST_ID index
2. Composite index on (PROJECT_ID, LOG_TIMESTAMP)
3. Composite index on (USER_ID, LOG_TIMESTAMP)
4. Composite index on (ENGINE_ID, LOG_TIMESTAMP)
5. Index on SESSION_ID
6. Index on ROOM_ID

These indexes improve query execution for filtering + ordering (ORDER BY LOG_TIMESTAMP DESC LIMIT ).