16.20 - Queue Table Cache - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

Teradata Database maintains a cache that holds row information for a number of unconsumed rows for each queue table. The system creates this queue table cache, which resides in its own Dispatcher partition, during system startup. There can be a queue table cache task on each PE in your system.

Cache row entries are shared by all queue tables that hash to that PE. Each queue table row entry is a pair of QITS and rowID values for each row to be consumed from the queue, sorted in QITS value order. The entry for a given queue table exists only in 1 queue table cache on the system. The determination of which system PE is assigned to an active queue table is made within the Dispatcher (see Teradata Vantage™ SQL Request and Transaction Processing, B035-1142) partition by hashing its table ID using the following algorithm.



where:

Syntax element … Specifies the …
queue_table_cache_PE_number position number within the configuration map array of the PE containing the queue table cache to which the given queue table entry is assigned.
tableID[1] numeric value of the second word of the double word tableID value (where the first word is notated as tableID[0]).
MOD modulo function.
number_system_PEs number of PEs in the system configuration.

For example, suppose a system has 6 online PEs and the value for tableID[1]=34.



This value points to the fifth position in the system-maintained configuration map array of online PEs, which begins its numbering at position 0. As a result of the calculation, the system assigns entries for this queue table to the cache on the fifth online PE listed in the system configuration map. If that PE goes offline, then the system reassigns the queue table to the first online PE in the configuration map.

During startup, Teradata Database allocates 64KB to the queue table cache on each PE and increases its size dynamically in 64KB increments to a maximum of 1MB per PE as required. The system initializes all the fields in the cache during startup and allocates space for 100 table entries. As queue tables are activated, they populate these slots beginning with the lowest numbered slot and proceeding upward from there. When the maximum cache size is reached, the system flushes it, either partially or entirely, depending on the number of bytes that must be inserted into the cache.

Assume the following definitions.

TotalRow Count = the number of unconsumed rows in a queue table on the AMPs.

RowCount = the number of rows in the queue table cache on a PE.

Each queue table cache entry has a maximum of approximately 2,000 row entries. As with other system maxima, this number refers to an isolated case. The actual limit on the number of row entries is determined by the size of the queue table cache. For example, 1 table can have 10 row entries, another can have 100, another can have 1,000, and so on until the limit of 1 MB is reached, at which point the system begins to flush entries from the cache. But no individual table can have more than approximately 2,000 row entries in the cache at a time.

When the maximum is reached, the system increments only the TotalRowCount value and does not add a new row entry to the cache.

IF the value for … THEN …
TotalRowCount > RowCount there are more rows in the queue table than there are rows in the queue table cache.
RowCount = 0 AND TotalRowCount > 0 the system triggers a row collection operation to repopulate the queue table cache.
Note that there are three events that can trigger a partial or full purge of row entries for a table from the cache without their being consumed.
  • Exceeding the maximum 100 queue table entry slots.

    This event causes a partial purge of row entries for the subject table. The system purges only as many cached row entries as are necessary to make room for the new entries. Note that this event can occur even if the total cache size is less than 1 MB.

  • Exceeding the maximum 1 MB (approximately 20,000 row entries) queue table cache size limit.

    This event causes a partial purge of row entries for the subject table. The system purges only as many cached row entries as are necessary to make room for the new entries. Note that this event can occur even if fewer than 100 queue table entry slots have been used.

  • An UPDATE, DELETE, or MERGE operation on a queue table.

    This event category purges all entries for the subject table from the cache.

Insertion of a table entry into 1 of the 100 table entry slots in the cache is triggered by either of the following events.
  • An insert operation into the table.
  • A consume operation from the table.

When the cache reaches its maximum limits of either 100 queue table entries or 20,000 total row entries for that PE, the next incoming request triggers the flush.

The process is outlined in the following table.

  1. The system attempts to flush any queue table entries marked as spoiled.
    • If there are no spoiled queue table entries, then Teradata Database checks to see if there are any pending in-queue requests or if it is in the process of collecting rows.

      If the most recent entry has pending in-queue requests or is in the process of collecting rows, then Teradata Database continues the search for an entry to flush beginning with the next lowest entry in the list.

      If the most recent entry has no pending in-queue requests or is in the process of collecting rows, then Teradata Database flushes it from the cache.

      Because it is possible to control the ordering of queue entries by inserting rows with user-selected QITS values in place of system-determined default QITS values, the entries that are flushed might not actually be those most recently added to the queue. See Ordering Queue Table Rows.

    • If there are spoiled queue table entries, then Teradata Database flushes them.
  2. If no qualifying entry can be found, the system returns an error to the requestor.

When the queue table cache reaches its maximum size of 1 MB (approximately 20,000 row entries), the next incoming request triggers a flush.

The process is outlined in the following sequence of events.

  1. The system attempts to flush any queue table entries marked as spoiled.
    IF there are … THEN Teradata Database …
    no spoiled queue table entries does the following.

    Checks each entry to determine if there are any pending in-queue requests or if it is in the process of collecting rows.

    If so, the entry is not eligible to be purged from the cache.

    Retrieves the cardinality of each qualifying queue table entry.

    Entries are ranked by their cardinality, with the largest entry being targeted for the first purge attempts.

    Computes the quantity of space required to insert the incoming request into the cache.

    Beginning with the most recently added rows in the first qualifying entry, based on their timestamp value, purges enough rows to reclaim the space needed to insert the incoming request into the queue table cache.

    Because it is possible to control the ordering of queue entries by means of inserting rows with user-selected QITS values in place of system-determined default QITS values (see Ordering Queue Table Rows), the entries that are flushed might not actually be those most recently added to the queue.

    This process continues until enough cache space is freed to accommodate the incoming request.

    spoiled queue table entries flushes them.
  2. If no qualifying entry can be found, the system aborts the transaction and returns an error to the requestor.