Log Analysis (Advanced Analytics)

The log analysis module allows you to quickly monitor key values of database server performance parameters in the following areas:

  • Slow or gigantic queries;

  • Blocks and errors that occur;

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

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

Logging is mostly defined in the server configuration and can be overridden via ALTER SYSTEM (except for the «Megaqueries» section).

Analytics is displayed in seven key sections. The main page of the section displays a summary by host, the main ones being the total number of queries for attention and the number of templates to which they can be reduced.

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

You should click on the number of templates to go to detailed problem query analysis.

  1. Problem queries

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

The number of queries for each template to which all problem queries for the selected period can be reduced and their indicators; Number of queries for each template; * number of queries for each application, trigger, database object, etc.

  1. Mega queries

The section contains analytics for queries exceeding 1 MB of traffic (inbound or outbound).

The main page shows a list of all hosts, with the number for each:

  • resultset - queries received by the server;

  • params - server transmitted queries;

  • query - query texts;

  • diff- comparison of query duration and execution.

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

  • Number of megaqueries related to applications (pg_m_Megaqueries_p_q_r_by_application);

A list of all queries sorted chronologically or by size (:ref:); * detailed heatmap with additional time interval settings (:ref:).

  1. Blockages

On the home page, the module will provide a summary for each host in terms of the number of mutual locks (deadlocks), locks (locks) and a 24-hour graph of the intensity of problem queries.

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

  • by the number of locks of each type (pg_m_Locks_by_type);

  • by the number of locks corresponding to each application (pg_m_Blocks_by_application);

  • in chronological order all locks without any grouping. Each lock will display:

    • start registration time*end date and time* and total lock time;

    • lock type;

    • pid, application and method.

  1. Errors

On the home page, the module will provide a summary for each host of the number of errors of each type (/ERROR, /FATAL, /WARNING) and a graph of the rate of occurrence of problem queries, broken down by 24 hours.

  1. System Action

    1. The page will display a summary by host

    2. Translate to:

      1. RU/SA

      2. STATS

      3. ANALYZE

      4. VACUUM

      5. CHECKPOINT

  2. Logs

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

    2. Displays the logs for the selected parameters.

  3. Archive:

    1. Query page from the archive.

    2. Time page.