Advanced analytics

Note

For the module to work correctly, run its installation.</pg_monitor_installation>

The advanced analytics module (including the log analysis module) allows you to quickly track the key values of database server parameters in the following aspects:

  • slow or giant queries;

  • Blocking and errors that occur

  • the frequency and results of passes [auto]VACUUM/ANALYZE.

Log analysis can be applied to the log of recorded events, taking into account the date, time interval and other filters.

The nature of logging is mainly defined in the server configuration and can be overridden via ALTER SYSTEM. Exception: “Mega queries” section.

Analytics are displayed in seven key sections. The main page of the section displays a summary of hosts, the main ones being the total number of queries that need attention and the number of patterns to which they can be reduced.

For each host, a graph of the intensity of problematic queries is displayed, divided into 24 hours (Timeline).

To go to the detailed analytics of problematic queries, click on the number of templates.

  1. Problematic queries

For the selected host, the module will provide a summary of problematic queries:

  • the number of queries for each template, to which all problematic queries for the selected period can be reduced, and their indicators;

  • Number of queries for each template

  • Number of queries per application, trigger, database object, and so on.

  1. Mega Queries

This section contains analytics for queries exceeding 1 MB of incoming or outgoing traffic.

The main page displays a list of all hosts, where the number of hosts is given for each:

  • resultset — queries received by the server;</pg_m_Мегазапросы_p_q_r_по_приложениям>

  • params - queries transmitted by the server;</pg_m_Мегазапросы_p_q_r_по_приложениям>

  • query — query texts;</pg_m_Мегазапросы_p_q_r_по_приложениям>

  • diff - comparison of the duration and execution of queries.</pg_m_Мегазапросы_diff>

Clicking on the number of queries will take you to one of the pages: /params, /query, /resultset, /mega diff respectively, which reflects:

  • Number of application-specific mega queries (By Applications tab);

  • A list of all queries, sorted chronologically or by size (By Time tab);</pg_m_Мегазапросы_по_времени>

  • Detailed heatmap with additional time interval settings (Heatmap tab).</pg_m_Мегазапросы_heatmap>

  1. Lock

On the home page, the module will provide a summary of each host in terms of the number of deadlocks, locks, and a graph of the intensity of problematic queries, divided into 24 hours.

When selecting a host and lock type, the user will be taken to the /deadlock-detect or /lock-wait page, respectively, where you can explore the summary:

  • by the number of locks of each type (“By type” tab);

  • by the number of locks corresponding to each application (“By applications” tab);

  • In chronological order, all locks without any grouping. For each block, the following will be displayed:

    • Start registration time, end date and time, and total blocking time

    • Lock type

    • pid, application, and method.

  1. Errors

On the home page, the module will provide a summary of each host in terms of the number of errors of each type (/ERROR, /FATAL, /WARNING) and a graph of the intensity of problematic queries, divided into 24 hours.

  1. System Actions

    1. The page will display a summary of the hosts

    2. Translate to:

      1. RU/SA

      2. STATS

      3. ANALYZE

      4. VACUUM

      5. CHECKPOINT

  2. Logs

    1. The page will prompt you to select the host, pid, and time;

    2. Displays logs for the selected parameters.

  3. Archive:

    1. Request page from the archive.

    2. Time page.