Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

pg_stat_monitor: uses significant CPU,memory resources

Description

Reported by  on this pg_stat_monitor Review - Sept 16, 2021 Confluence page.

  • It would be great to understand whether it capture the real time execution plan when the query was executed or later in time when pg_stat_monitor view is called. The real time execution plan would be useful as it will help to troubleshoot the problematic query by looking at execution plan which was executed at around that time. 

  • When “pg_stat_monitor.pgsm_enable_query_plan” parameter is enabled, the future query call to pg_stat_monitor view takes time to return the results and uses significant CPU,memory resources. Considering that the plan would be of significance when there is a performance issue on the server, the resource utilisation by this view may become an issue

Environment

None

Smart Checklist

Activity

Show:

Ibrar Ahmed November 16, 2021 at 7:25 PM

We have already gained much performance boots by this commit.

 

commit a93ba37ac300f36d78f23570a18996317a5a4636

Author: Ibrar Ahmed <ibrar.ahmad@gmail.com>

Date:   Tue Oct 5 20:33:34 2021 +0000

 

    , , : Fix performance issue while querying the pg_stat_monitor.

    

    This performance fix resolves two more issues (, ).

 

Ibrar Ahmed November 16, 2021 at 7:04 PM
Edited

The pg_stat_monitor query times reduce from 19min to milliseconds now. The only issue left when pg_stat_monitor.pgsm_enable_query_plan is enabled and we don't have control over that because it uses PostgreSQL core functions 

  ExplainBeginOutput(es);

  ExplainPrintPlan(es, queryDesc);

  ExplainEndOutput(es);

Done

Details

Assignee

Reporter

Labels

Components

Fix versions

Priority

Smart Checklist

Created October 1, 2021 at 11:10 AM
Updated March 5, 2024 at 9:35 PM
Resolved November 22, 2021 at 1:34 PM