Checking Append-Optimized Tables

Checking Append-Optimized Tables

The gp_toolkit schema includes a set of diagnostic functions you can use to investigate the state of append-optimized tables.

When an append-optimized table (or column-oriented append-optimized table) is created, another table is implicitly created, containing metadata about the current state of the table. The metadata includes information such as the number of records in each of the table's segments.

Append-optimized tables may have non-visible rows—rows that have been updated or deleted, but remain in storage until the table is compacted using VACUUM. The hidden rows are tracked using an auxiliary visibility map table, or visimap.

The following functions let you access the metadata for append-optimized and column-oriented tables and view non-visible rows. Some of the functions have two versions: one that takes the oid of the table, and one that takes the name of the table. The latter version has "_name" appended to the function name.

__gp_aovisimap_compaction_info(oid)

This function displays compaction information for an append-optimized table. The information is for the on-disk data files on Greenplum Database segments that store the table data. You can use the information to determine the data files that will be compacted by a VACUUM operation on an append-optimized table.

Note: Until a VACUUM operation deletes the row from the data file, deleted or updated data rows occupy physical space on disk even though they are hidden to new transactions. The configuration parameter gp_appendonly_compaction controls the functionality of the VACUUM command.

This table describes the __gp_aovisimap_compaction_info function output table.

Table 1. __gp_aovisimap_compaction_info output table
Column Description
content Greenplum Database segment ID.
datafile ID of the data file on the segment.
compaction_possible The value is either t or f. The value t indicates that the data in data file be compacted when a VACUUM operation is performed.

The server configuration parameter gp_appendonly_compaction_threshold affects this value.

hidden_tupcount In the data file, the number of hidden (deleted or updated) rows.
total_tupcount In the data file, the total number of rows.
percent_hidden In the data file, the ratio (as a percentage) of hidden (deleted or updated) rows to total rows.
Note: If you upgraded your cluster to Greenplum Database 4.3.5.0 or later, you can create the __gp_aovisimap_compaction_info function in an existing Greenplum database by running run the script $GPHOME/share/postgresql/compaction_info.sql once for each database. For example, to install the functions in database testdb, use this command:
$ psql -d testdb -f $GPHOME/share/postgresql/compaction_info.sql

If you created the database with Greenplum Database 4.3.5.0 or later, this function is automatically created in the database.

__gp_aoseg_name('table_name')

This function returns metadata information contained in the append-optimized table's on-disk segment file.

Table 2. __gp_aoseg_name output table
Column Description
segno The file segment number.
eof The effective end of file for this file segment.
tupcount The total number of tuples in the segment, including invisible tuples.
varblockcount The total number of varblocks in the file segment.
eof_uncompressed The end of file if the file segment were uncompressed.
modcount The number of data modification operations.
state The state of the file segment. Indicates if the segment is active or ready to be dropped after compaction.

__gp_aoseg_history(oid)

This function returns metadata information contained in the append-optimized table's on-disk segment file. It displays all different versions (heap tuples) of the aoseg meta information. The data is complex, but users with a deep understanding of the system may find it usefulfor debugging.

The input argument is the oid of the append-optimized table.

Call __gp_aoseg_history_name('table_name') to get the same result with the table name as an argument.

Table 3. __gp_aoseg_history output table
Column Description
gp_tid The id of the tuple.
gp_xmin The id of the earliest transaction.
gp_xmin_status Status of the gp_xmin transaction.
gp_xmin_commit_ The commit distribution id of the gp_xmin transaction.
gp_xmax The id of the latest transaction.
gp_xmax_status The status of the latest transaction.
gp_xmax_commit_ The commit distribution id of the gp_xmax transaction.
gp_command_id The id of the query command.
gp_infomask A bitmap containing state information.
gp_update_tid The ID of the newer tuple if the row is updated.
gp_visibility The tuple visibility status.
segno The number of the segment in the segment file.
tupcount The number of tuples, including hidden tuples.
eof The effective end of file for the segment.
eof_uncompressed The end of file for the segment if data were uncompressed.
modcount A count of data modifications.
state The status of the segment.

__gp_aocsseg(oid)

This function returns metadata information contained in a column-oriented append-optimized table's on-disk segment file, excluding non-visible rows. Each row describes a segment for a column in the table.

The input argument is the oid of a column-oriented append-optimized table. Call as __gp_aocsseg_name('table_name') to get the same result with the table name as an argument.

Table 4. __gp_aocsseg(oid) output table
Column Description
gp_tid The table id.
segno The segment number.
column_num The column number.
physical_segno The number of the segment in the segment file.
tupcount The number of rows in the segment, excluding hidden tuples.
eof The effective end of file for the segment.
eof_uncompressed The end of file for the segment if the data were uncompressed.
modcount A count of data modification operations for the segment.
state The status of the segment.

__gp_aocsseg_history(oid)

This function returns metadata information contained in a column-oriented append-optimized table's on-disk segment file. Each row describes a segment for a column in the table.The data is complex, but users with a deep understanding of the system may find it useful for debugging.

The input argument is the oid of a column-oriented append-optimized table. Call as __gp_aocsseg_history_name('table_name') to get the same result with the table name as argument.

Table 5. __gp_aocsseg_history output table
Column Description
gp_tid The oid of the tuple.
gp_xmin The earliest transaction.
gp_xmin_status The status of the gp_xmin transaction.
gp_xmin_ Text representation of gp_xmin.
gp_xmax The latest transaction.
gp_xmax_status The status of the gp_xmax transaction.
gp_xmax_ Text representation of gp_max.
gp_command_id ID of the command operating on the tuple.
gp_infomask A bitmap containing state information.
gp_update_tid The ID of the newer tuple if the row is updated.
gp_visibility The tuple visibility status.
segno The segment number in the segment file.
column_num The column number.
physical_segno The segment containing data for the column.
tupcount The total number of tuples in the segment.
eof The effective end of file for the segment.
eof_uncompressed The end of file for the segment if the data were uncompressed.
modcount A count of the data modification operations.
state The state of the segment.

__gp_aovisimap(oid)

This function returns the tuple id, the segment file, and the row number of each non-visible tuple according to the visibility map.

The input argument is the oid of an append-optimized table.

Use __gp_aovisimap_name('table_name') to get the same result with the table name as argument.

Column Description
tid The tuple id.
segno The number of the segment file.
row_num The row number of a row that has been deleted or updated.

__gp_aovisimap_hidden_info(oid)

This function returns the numbers of hidden and visible tuples in the segment files for an append-optimized table.

The input argument is the oid of the append-optimized table.

Call __gp_aovisimap_hidden_info_name('table_name') to get the same result with a table name argument.

Column Description
segno The number of the segment file.
hidden_tupcount The number of hidden tuples in the segment file.
total_tupcount The total number of tuples in the segment file.

__gp_aovisimap_entry(oid)

This function returns information about each visibility map entry for the table.

The input argument is the oid of an append-optimized table.

Call __gp_aovisimap_entry_name('table_name') to get the same result with a table name argument.

Table 6. __gp_aovisimap_entry output table
Column Description
segno Segment number of the visibility map entry.
first_row_num The first row number of the entry.
hidden_tupcount The number of hidden tuples in the entry.
bitmap A text representation of the visibility bitmap.