Free cookie consent management tool by TermsFeed

TrimTables Script

Purpose

New table entries are constantly being added to the Appian engines as various functions are executed throughout the system. Although the data may clear from these table entries when specific actions occur, like deletions and removals, there is still underlying metadata that remains which doesn’t allow the table entry to fully clear from the engine.

In certain circumstances, it may be beneficial for the system to clear, or “trim”, these excess table entries from the engine in order to reduce memory usage and reclaim some performance, provided that the rows exist at the end of the table. This script is a utility that allows the administrator to view and identify potential tables which can be trimmed, view basic statistics for these specified tables, and ultimately request the engines to trim off excess rows of the specified tables. These functions can all be performed without having to restart the system.

When to Use

It is best practice to consult and work with the Appian Support team before executing this script in order to determine what kind of performance, if any, may be reclaimed. There are a few common scenarios where running this script can be effective:

  • If you've recently run the Sizing Script and the "Pre-Allocated Space Size" column reveals large values
  • If you've recently executed a large bulk delete or archival of process data

If any of the above scenarios are true, then run the stats function of this script to determine if any tables may be marked as ready for trimming, indicated by wherever the isTableReadyForTrim column is true. Work with the Appian Support Team to determine if then running the trim function of this script to clear the unused entries of these tables may result in memory savings on the engine.

Additional Notes on Usage

  • No Appian components need to be stopped in order to run this script
  • Although this script can be run while a site is fully up, it can be resource intensive and should only be run during non-business hours
  • Trimmed rows are beneficial in that it allows the system to write new data to them. In order to release the memory, an engine restart would still be required.
  • For sites with a High Availability (HA) configuration, this script only needs to be run on the primary engine, but all replicas of that engine would need to be restarted in order to release memory

Location

<APPIAN_HOME>/server/_scripts/tools/trimtables.sh (.bat)

Script Options

Option Parameters Required Meaning
help No Show usage information
show Yes List of all the tables which are ever eligible to be trimmable on each engine
stats Yes Statistics for all of the eligible trimmable tables
trim Yes Issues a call to each specified engine to trim the specified tables. For each engine and table pair, a status code indicating the result of the trim is displayed.

Script Usage

1

./trimtables.sh <option> <parameters>

Help

This command returns the help text for the script and usage information for each command.

Usage

1

./trimtables.sh help

Show

This command returns a list of the engines on the system and internal tables that exist on the engines, which are ever eligible to be trimmed.

Parameters and Usage

Parameter Required Function
-e Yes Accepts one or more engine names or "all" 
1

./trimtables.sh show -e <engines>

Examples

1
2

./trimtables.sh show -e all
./trimtables.sh show -e process-analytics process-design process-execution0

Stats

This command returns detailed information for each of the above tables that are eligible to be trimmed.

Parameters and Usage

Parameter Required Function
-e Yes Accepts one or more engine names or "all"
-t Yes Accepts one or more table names or "all", where "all" will return stats for all trimmable tables on each engine specified in the -e parameter. 
1

./trimtables.sh stats -e <engines> -t <tables>

Examples

1
2
3
4

./trimtables.sh stats -e all -t all
./trimtables.sh stats -e all -t ilst workq
./trimtables.sh stats -e process-analytics process-design process-execution0 -t all
./trimtables.sh stats -e process-analytics process-design process-execution0 -t ilst

Detailed Table Information

The following table statistics will be returned:

Column Name Description
Engine The engine that the trimmable table is located on
Table The name of the trimmable table
isTableReadyForTrim Boolean value to determine if the eligible table can be trimmed or not, based on table conditions like overall size and free rows
usedRows Of the number of allocated rows, the number of rows that have data present
freeRows Of the number of allocated rows, the number of rows that are without data
excludedRows Of the number of allocated rows, the number of rows that are currently inaccessible to any data processing
allocatedRows The total number of rows allocated for the table by the engine

Trim

This command will trim the eligible entries from the specified engines and tables.

Parameters and Usage

Parameter Required Function
-e Yes Accepts one or more engine names or "all"
-t Yes Accepts one or more table names or "all", where "all" will trim all tables on each engine specified in the -e parameter. 
1

./trimtables.sh trim -e <engines> -t <tables>

Examples

1
2
3
4

./trimtables.sh trim -e all -t all
./trimtables.sh trim -e all -t ilst workq
./trimtables.sh trim -e process-analytics process-design process-execution0 -t all
./trimtables.sh trim -e process-analytics process-design process-execution0 -t ilst

Status Codes

For each engine and table pair, the following status codes indicating the result of the trim will be displayed after the command execution:

Status Description
SUCCESSFUL_TRIM The specified rows were trimmed off of the end of the table successfully
LARGE_EXCLUDED_LIST_NO_TRIM Tables with a large amount of excluded rows will only be trimmed once a day. If another attempt to trim these tables is made, the trim will be aborted, and this status code is returned.
FREE_ROWS_BELOW_THRESHOLD There are not enough free rows to trim off of the specified table at this time
TRIMMABLE_ROWS_BELOW_THRESHOLD There are enough free rows to trim, however there are not enough free rows at the end of the table, which is the only supported part of the table to trim.

Feedback