Skip to content
Permalink

Comparing changes

Choose two branches to see what’s changed or to start a new pull request. If you need to, you can also or learn more about diff comparisons.

Open a pull request

Create a new pull request by comparing changes across two branches. If you need to, you can also . Learn more about diff comparisons here.
base repository: postgresql-cfbot/postgresql
Failed to load repositories. Confirm that selected base ref is valid, then try again.
Loading
base: cf/5012~1
Choose a base ref
...
head repository: postgresql-cfbot/postgresql
Failed to load repositories. Confirm that selected head ref is valid, then try again.
Loading
compare: cf/5012
Choose a head ref
  • 6 commits
  • 32 files changed
  • 3 contributors

Commits on May 16, 2025

  1. Machinery for grabbing an extended vacuum statistics on table relations. 

    Value of total_blks_hit, total_blks_read, total_blks_dirtied are number of
hitted, missed and dirtied pages in shared buffers during a vacuum operation
respectively.

total_blks_dirtied means 'dirtied only by this action'. So, if this page was
dirty before the vacuum operation, it doesn't count this page as 'dirtied'.

The tuples_deleted parameter is the number of tuples cleaned up by the vacuum
operation.

The delay_time value means total vacuum sleep time in vacuum delay point.
The pages_removed value is the number of pages by which the physical data
storage of the relation was reduced.
The value of pages_deleted parameter is the number of freed pages in the table
(file size may not have changed).

Tracking of IO during an (auto)vacuum operation.
Introduced variables blk_read_time and blk_write_time tracks only access to
buffer pages and flushing them to disk. Reading operation is trivial, but
writing measurement technique is not obvious.
So, during a vacuum writing time can be zero incremented because no any flushing
operations were performed.

System time and user time are parameters that describes how much time a vacuum
operation has spent in executing of code in user space and kernel space
accordingly. Also, accumulate total time of a vacuum that is a diff between
timestamps in start and finish points in the vacuum code.
Remember about idle time, when vacuum waited for IO and locks, so total time
isn't equal a sum of user and system time, but no less.

pages_frozen is a number of pages that are marked as frozen in vm during vacuum.
This parameter is incremented if page is marked as all-frozen.
pages_all_visible is a number of pages that are marked as all-visible in vm during
vacuum.

wraparound_failsafe_count is a number of times when the vacuum starts urgent cleanup
to prevent wraparound problem which is critical for the database.

Authors: Alena Rybakina <[email protected]>,
	 Andrei Lepikhov <[email protected]>,
	 Andrei Zubkov <[email protected]>
Reviewed-by: Dilip Kumar <[email protected]>, Masahiko Sawada <[email protected]>,
	     Ilia Evdokimov <[email protected]>, jian he <[email protected]>,
	     Kirill Reshke <[email protected]>, Alexander Korotkov <[email protected]>,
	     Jim Nasby <[email protected]>, Sami Imseih <[email protected]>
    Alena Rybakina authored and Commitfest Bot committedMay 16, 2025
    Configuration menu
    Copy the full SHA
    087b481View commit details
    Browse the repository at this point in the history

  2. Machinery for grabbing an extended vacuum statistics on index relations. 

    They are gathered separatelly from table statistics.

As for tables, we gather vacuum shared buffers statistics for index relations like
value of total_blks_hit, total_blks_read, total_blks_dirtied, wal statistics, io time
during flushing buffer pages to disk, delay and total time.

Due to the fact that such statistics are common as for tables, as for indexes we
set them in the union ExtVacReport structure. We only added some determination 'type'
field to highlight what kind belong to these statistics: PGSTAT_EXTVAC_TABLE or
PGSTAT_EXTVAC_INDEX. Generally, PGSTAT_EXTVAC_INVALID type leads to wrong code process.

Some statistics belong only one type of both tables or indexes. So, we added substructures
sych table and index inside ExtVacReport structure.

Therefore, we gather only for tables such statistics like number of scanned, removed pages,
their charecteristics according VM (all-visible and frozen). In addition, for tables we
gather number frozen, deleted and recently dead tuples and how many times vacuum processed
indexes for tables.

Controversally for indexes we gather number of deleted pages and deleted tuples only.

As for tables, deleted pages and deleted tuples reflect the overall performance of the vacuum
for the index relationship.

Since the vacuum cleans up references to tuple indexes before cleaning up table tuples,
which adds some complexity to the vacuum process, namely the vacuum switches from cleaning up
a table to its indexes and back during its operation, we need to save the vacuum statistics
collected for the heap before it starts cleaning up the indexes.
That's why it's necessary to track the vacuum statistics for the heap several times during
the vacuum procedure. To avoid sending the statistics to the Cumulative Statistics System
several times, we save these statistics in the LVRelState structure and only after vacuum
finishes cleaning up the heap, it sends them to the Cumulative Statistics System.

Authors: Alena Rybakina <[email protected]>,
   Andrei Lepikhov <[email protected]>,
   Andrei Zubkov <[email protected]>
Reviewed-by: Dilip Kumar <[email protected]>, Masahiko Sawada <[email protected]>,
       Ilia Evdokimov <[email protected]>, jian he <[email protected]>,
       Kirill Reshke <[email protected]>, Alexander Korotkov <[email protected]>,
       Jim Nasby <[email protected]>, Sami Imseih <[email protected]>
    Alena Rybakina authored and Commitfest Bot committedMay 16, 2025
    Configuration menu
    Copy the full SHA
    8185c75View commit details
    Browse the repository at this point in the history

  3. Machinery for grabbing an extended vacuum statistics on databases. 

    Database vacuum statistics information is the collected general
vacuum statistics indexes and tables owned by the databases, which
they belong to.

In addition to the fact that there are far fewer databases in a system
than relations, vacuum statistics for a database contain fewer statistics
than relations, but they are enough to indicate that something may be
wrong in the system and prompt the administrator to enable extended
monitoring for relations.

So, buffer, wal, statistics of I/O time of read and writen blocks
statistics will be observed because they are collected for both
tables, indexes. In addition, we show the number of errors caught
during operation of the vacuum only for the error level.

wraparound_failsafe_count is a number of times when the vacuum starts
urgent cleanup to prevent wraparound problem which is critical for
the database.

Authors: Alena Rybakina <[email protected]>,
   Andrei Lepikhov <[email protected]>,
   Andrei Zubkov <[email protected]>
Reviewed-by: Dilip Kumar <[email protected]>, Masahiko Sawada <[email protected]>,
       Ilia Evdokimov <[email protected]>, jian he <[email protected]>,
       Kirill Reshke <[email protected]>, Alexander Korotkov <[email protected]>,
       Jim Nasby <[email protected]>, Sami Imseih <[email protected]>
    Alena Rybakina authored and Commitfest Bot committedMay 16, 2025
    Configuration menu
    Copy the full SHA
    4222e2aView commit details
    Browse the repository at this point in the history

  4. Add documentation about the system views that are used in the machine… 

    …ry of vacuum statistics.
    Alena Rybakina authored and Commitfest Bot committedMay 16, 2025
    Configuration menu
    Copy the full SHA
    4826d12View commit details
    Browse the repository at this point in the history

  5. []: ./vacuum_stats.diff

    Commitfest Bot committedMay 16, 2025
    Configuration menu
    Copy the full SHA
    70206c8View commit details
    Browse the repository at this point in the history

  6. [CF 5012] v22 - Vacuum statistics 

    This branch was automatically generated by a robot using es from an
email thread registered at:

https://commitfest.postgresql.org//5012

The branch will be overwritten each time a new  version is posted to
the thread, and also periodically to check for bitrot caused by changes
on the master branch.

(es): https://www.postgresql.org/message-id/[email protected]
Author(s): Andrei Lepikhov, Andrei Zubkov, Alena Rybakina
    Commitfest Bot committedMay 16, 2025
    Configuration menu
    Copy the full SHA
    16110b2View commit details
    Browse the repository at this point in the history
Loading