Abstract: SysCache is a ThreadLocal structure. Each thread has its own SysCache. The cache information stored in SysCache is determined by the business executed.

This document is shared with the Principles of GaussDB(DWS)CBB Component SysCache in Huawei cloud community.

What is SysCache? Why do we need SysCache?

Before addressing this issue, we need to briefly explain how GaussDB (DWS) works.

After we connect to the database, the database assigns us a separate thread in the background that is responsible for executing the requests we send, assuming we send the following statements in order:

a. create table abc (a int, b int, c int);

b. insert into abc values (1, 2, 3);

c. insert into abc values (2, 3, 4);

d. insert into abc values (3, 4, 5);

After table ABC is created, three consecutive data are inserted into ABC. Each insert statement requires access to metadata of ABC, including but not limited to:

I. pg_authID: Reads user information,

Ii. pg_resource_pool: Reads information about a resource pool.

Iii. pg_class: reads metadata related to the table.

Iv. pgxc_group: Reads information about nodegroup.

V. pg_namespace: Reads schema information.

Vi. Pg_type: reads the column type,

Vii. pg_attribute: Reads the column attribute information of the table.

As can be seen, although a simple insert statement is executed, the parsing and verification logic involved is extremely complex, which will be associated with a large number of system table-related query operations. To speed up the system table query rate, a SysCache cache is built in GaussDB (DWS) for system table query operations. In the above example, statement B) accesses related system table metadata during execution and is cached by SysCache. Later, when statement C) and statement D) are executed, the corresponding cache information is directly obtained from SysCache to speed up execution.

  • NOTE: In fact, the principle of GaussDB (DWS) SysCache is the same as that of PG SysCache. The difference is that GaussDB (DWS) has been changed from PG multi-process model to multi-thread model.

  • NOTE: SysCache is the GaussDB (DWS) kernel table Tuple cache.

SysCache storage structure

SysCache is a ThreadLocal structure. Each thread has its own SysCache, where the cache information stored is determined by the business being performed. The following diagram shows the general structure of SysCache:

Figure 1 SysCache structure

SysCache is stored in memory as an array of Pointers. Each element in the array is a CatCache structure, which stores metadata information in system tables. CatCache may contain a large number of cached data. In order to speed up the search, CatCache designs a hash array, which contains multiple hash buckets. Each hash bucket records the metadata linked list information, and the final metadata is stored in the hash bucket in the form of a bidirectional linked list.

As an example, suppose you execute the following statement: select * from public.abc;

When the statement is executed, the system performs the following three steps in SysCache:

1. Find the CatCache corresponding to PG_class: This statement has three input parameters. Query metadata information about the Schema named public and table named ABC from PG_class. Therefore, when this statement is executed, the CatCache corresponding to PG_class is queried directly.

2. Calculate the hash value: Calculate the hash value of the public and ABC parameters to locate the hash bucket.

3. Traverse the hash bucket: Traverse the metadata information in the hash bucket. If the metadata of public. ABC is matched, the metadata is directly returned.

  • SysCache storage structure: SysCache storage structure is essentially multiple hash buckets, each hash bucket in the form of a two-way linked list of metadata stored.

SysCache External interface

The SysCache external interfaces are classified into two types: Query SysCache and release SysCache. These two methods must be used together. After SysCache returns the query result, you need to invoke the Release SysCache interface again to release SysCache data.

The following figure shows the main steps for querying SysCache:

Figure 2 Flowchart for querying the SysCache function

Before querying the SysCache function, ensure that CatCache is initialized. After that, calculate the hash value based on the queried key information to locate the hash bucket. For SysCache, data can have a maximum of four keys. In the preceding example, key1=public, key2= ABC, key3, and key4 are completed with 0.

After the hash value is calculated, the two-way linked lists in the corresponding hash bucket will be scanned for matching one by one. If no matching is found, the data pages of the system table will be further scanned. If the data does not exist in the data page, it indicates that the data does not exist, and a false CatCTup needs to be created and marked as negative to improve the efficiency of miss query. If the data page contains the data, the data is encapsulated, the CatCTup is established, and the data is inserted into the corresponding hash bucket.

After a CatCTup match is made, the current CatCTup reference count (refcount) needs to be added by one and returned. Refcount counts references to prevent error release.

For releasing SysCache, the main steps are to reduce the number of references (refcount) by one and to release the resource whose refcount is 0.

Figure 3 Flowchart for releasing the SysCache

  • NOTE: Tuple is bound to CurrentResourceOwner during SysCache query and release. The main purpose of this operation is to check for resource leakage in case of thread exit or transaction commit.

  • NOTE: SysCache supports fuzzy query, and the interface is SearchCatCacheList. If you are interested, you can search for it by yourself.

SysCache failure mechanism

Before explaining why SysCache fails, you need to explain why SysCache needs to fail. SysCache is a ThreadLocal structure. If thread 1 caches the metadata of table ABC in SysCache and thread 2 deletes table ABC, then the metadata of ABC in thread 1 needs to be invalidated. The SysCache failure mechanism in GaussDB (DWS) is implemented through the failure message queue.

Figure 4 SysCache failure mechanism

The figure above shows the flow chart of sending SysCache failure messages during statement execution.

At the beginning of a transaction, this thread actively receives and processes messages in the stale message queue. CurrnetCmdInvalidMsgs; PriorCmdInvalidMsgs; CurrnetCmdInvalidMsgs; CurrnetCmdInvalidMsgs; It is used to store invalid messages generated from the start of a transaction to the current point in time. CurrnetCmdInvalidMsgs and PriorCmdInvalidMsgs are ThreadLocal structures.

Before each statement is executed, the invalidation message for the previous statement is moved from CurrnetCmdInvalidMsgs to PriorCmdInvalidMsgs. The validation message is then added to the CurrnetCmdInvalidMsgs after the statement is executed.

When a subtransaction is committed, there are two cases. If the transaction is rollback, all PriorCmdInvalidMsgs of the local transaction need to be invalidated. If the transaction commits, you need to add the PriorCmdInvalidMsgs and CurrnetCmdInvalidMsgs of the child transaction to the PriorCmdInvalidMsgs of the parent transaction and switch the current transaction information to the parent transaction environment.

When the parent transaction is committed, there are also two cases. If the transaction is rollback, all PriorCmdInvalidMsgs of the local transaction need to be invalidated. If a transaction commits, all the invalidation messages generated by that thread need to be submitted to the invalidation message queue.

  • NOTE: To explain the SysCache invalidation mechanism, the kernel will actively submit the invalidation messages generated by this thread to the invalidation message queue when the transaction is successfully committed for other threads to synchronize.

  • NOTE: Invalid message queue is not explained in detail here. It can be regarded as a black box. Invalid message queue provides invalid message submission and reading interface for invalid message synchronization.

For more information about GuassDB(DWS), please search “GaussDB DWS” on wechat to follow the wechat public account, and share with you the latest and most complete PB series warehouse black technology, the background can also obtain many learning materials ~

Click to follow, the first time to learn about Huawei cloud fresh technology ~