PL / SQL global caching method based on openGauss database
By introducing PL/SQL global cache in openGauss, using the global cache manager and reference counting mechanism, the problems of compilation time and memory consumption under high concurrency are solved, and multi-session shared cache is realized, which improves performance and memory utilization. Improves the stability of the database.
Patent Information
- Application Number
- CN202411118953.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-15
- Publication Date
- 2024-11-15
AI Technical Summary
In high concurrency and complex PL/SQL business scenarios, the compilation of openGauss takes a long time and consumes a lot of memory. The separate cache design of sessions results in low cache utilization and inability to effectively optimize.
Introduce the PL/SQL global cache method based on openGauss, and manages the PL/SQL cache data of each session through the instance-level global cache manager, and uses a hash table to manage the global cache items, and ensures concurrency security through reference counting and state transfer mechanisms. Priority is given to session cache and global cache to reduce the frequency of self-compilation.
It significantly improves performance and memory usage in high concurrency and complex PL/SQL scenarios, improves database robustness, and reduces compilation time and memory consumption.
Smart Images

Figure CN118964427A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of PL / SQL caching methods of openGauss database, and in particular to a PL / SQL global caching method and system based on openGauss database. Background Art
[0002] With the development of database technology, various databases have emerged in the market. Many database vendors support users to write their own SQL functions or stored procedures very conveniently through procedural languages. In openGauss, the procedural language provided is called PL / SQL (PLpgSQL). Through PL / SQL, users can group a whole block of calculations and a series of queries inside the database server, which can effectively prevent multiple rounds of query parsing, reduce the communication overhead between the client and the server, and avoid the transmission of intermediate results.
[0003] The processing flow of PL / SQL program blocks in openGauss is roughly divided into two parts: compilation and execution. The first part is compilation, the main task of which is to interpret the PL / SQL program block and translate it into variables, namespaces, and syntax trees that can be recognized by the kernel; the second part is execution, which is executed according to the syntax tree obtained by compilation. Obviously, the more complex the statement block is and the more functions are involved, the longer the compilation process takes. In order to speed up the execution of PL / SQL, openGauss provides a session-level compilation cache, which retains the compilation products of functions or stored procedures defined by PL / SQL in the session memory. As long as the definition of the function or stored procedure has not changed and there are no factors that will affect the execution result, it only needs to be compiled once in the same session, and subsequent executions can use the previous session cache.
[0004] However, in scenarios where sessions are frequently switched to execute PL / SQL functions or stored procedures, this design makes the cache less meaningful, the cache utilization is very low, and a lot of time is spent in the compilation phase. In particular, openGauss supports packages. For complex package scenarios, a single execution can easily trigger the compilation of thousands of package functions, and the compilation time is so long that it is unbearable. In addition, the design of separate caching for each session naturally has the problem of multiple caches, high memory consumption, and inability to actively clean up in high-concurrency, multi-connection scenarios.
[0005] In summary, in high-concurrency and complex PL / SQL business scenarios, the compilation of openGauss takes a very long time and consumes a lot of memory, so it is urgent to design and invent related optimization methods. Summary of the invention
[0006] In order to overcome the defects of long compilation time and large memory consumption of PL / SQL in openGauss in high concurrency and complex business scenarios, the present invention proposes a new PL / SQL global caching method based on openGauss database, which optimizes both performance and memory usage through global caching.
[0007] Since the compilation cache of PL / SQL in openGauss is currently the session cache, the compilation time is long and the memory consumption is large in high-concurrency and complex business scenarios. We found that the reason for the above situation is that each session needs its own compilation cache, and there are many redundant and repeated parts between caches. Therefore, the present invention aims to provide a PL / SQL global cache based on openGauss. By sharing a global cache for the entire instance, the performance and memory usage performance in high-concurrency and complex PL / SQL scenarios can be significantly improved without the user frequently rebuilding or deleting PL / SQL related objects.
[0008] The overall design of the present invention is: based on the original PL / SQL session cache in the openGauss database, a new instance-level global cache is added, and the PL / SQL cache data of each session is uniformly managed through a global cache manager, that is, an instance-level global cache hash table is used to manage the encapsulated global cache items. The main difficulties of this design are:
[0009] (1) How to ensure that global cache items are error-free under high concurrency, and that there is no erroneous cache or unavailable cache.
[0010] (2) How to enable each session to share as much global cache as possible to save memory and improve performance.
[0011] The overall architecture design of the global cache system of the present invention is as follows Figure 1 As shown:
[0012] From the user level, whether it is the execution function, package function, trigger, or session cache management interface, the PL / SQL global cache manager is responsible for the interaction. In order to minimize the impact on the original design of the openGauss session cache, this solution embeds the global cache as a secondary cache into the PL / SQL compilation process when the session-level cache cannot be found. The processing priority of the system of the present invention is: use session cache (fastest) => use global cache (second fastest) => self-compile of this session (slower).
[0013] In the design of the global cache system, it is particularly important to manage the security of global cache items in concurrent scenarios. The following points need to be achieved:
[0014] (1) For a function or package of the same version, there can be only one global cache entry.
[0015] (2) Before the global cache entry is guaranteed to be available, the session can only compile functions or packages on its own.
[0016] (3) During the process of using the global cache item in any session, the actual memory of the global cache item is not allowed to be released or deleted.
[0017] In order to achieve the above objectives, the present invention introduces the concept of cache item state to ensure the security of global cache in concurrent scenarios. The state transfer process is as follows: Figure 2 As shown (all the following status updates are atomic operations to ensure concurrency safety):
[0018] (1) When a new cache item needs to be added, the cache item data is first updated to the "uninitialized" state.
[0019] (2) Before starting compilation, change the cache item status to "initializing". Other sessions will not try to use the global cache item in this state, but will choose to compile on their own.
[0020] (a) If compilation fails, the cache item status returns to the "uninitialized" state.
[0021] (b) If the compilation is successful, the cache item status is changed to "initialized" and the cache item can be shared by other sessions.
[0022] Furthermore, in order to protect the security of global cache items under high concurrency, the following measures are taken:
[0023] (1) For each global cache item, record a reference count. As long as the cache item is in the global cache hash table, the reference count is at least 1 to ensure that all cache items in the global cache hash table are available.
[0024] (2) When a session obtains the global cache hash table and puts it into the session cache, the reference count is increased by 1; when a session releases its own cache, if the cache is obtained from the global cache, the reference count is reduced by 1; the cache item memory is released only when the reference count is reduced to 0.
[0025] (3) When reading the global cache, part of it is copied to the session memory. At this time, the cache needs to be split into two parts: one part is a read-only cache that will not be modified after compilation; the other part is a writable cache that may be modified by the session during the execution of the function. When copying, only the writable cache is copied to each session, and multiple sessions share a read-only cache.
[0026] Specifically, the present invention provides a PL / SQL global caching method based on openGauss database, such as Figure 4 As shown, the method comprises the following steps:
[0027] S1. Call a function, package function or stored procedure to search for a global cache entry in the global cache table. If no global cache entry is found, add a new global cache entry.
[0028] S2. Check whether the data of the found or newly added global cache item is "initialized", if not, check whether the data of the global cache item is "initializing", if yes, release the reference of the global cache item, if not, start compiling the data;
[0029] S3. If the data of the global cache item found or added is "initialized", check whether the data version is consistent. If the data version is consistent, use the global cache item to obtain the compiled data, and then release the reference to the global cache item;
[0030] S4. If the data versions are inconsistent, the global cache item is replaced, and the new global cache item is stored in the global cache table, and then the data is compiled;
[0031] S5. Save the data compiled in steps S2 and S4, and then release the global cache item reference.
[0032] Furthermore, when the PL / SQL global cache method based on the openGauss database of the present invention cannot find the session-level cache, the global cache is embedded into the compilation process of PL / SQL as the secondary cache. The processing priority of this method is: use session cache (fastest speed) => use global cache (second fastest speed) => self-compilation of this session (slower speed); in this method:
[0033] (1) For the same version of a function or package, there is only one global cache entry;
[0034] (2) Before the global cache item is guaranteed to be available, the session can only compile functions or packages on its own;
[0035] (3) During the process of using the global cache item in any session, the actual memory of the global cache item is not allowed to be released or deleted.
[0036] Furthermore, the state transfer process of cache items in the PL / SQL global cache method based on the openGauss database of the present invention is as follows (all the following state updates are atomic operations to ensure concurrency safety):
[0037] (1) When a new cache item needs to be added, the cache item data is first updated to the "uninitialized" state;
[0038] (2) Before starting to compile, change the cache item status to "initializing". Other sessions will not try to use the global cache item in this state, but will choose to compile on their own:
[0039] (a) If compilation fails, the cache item status returns to the "uninitialized" state;
[0040] (b) If the compilation is successful, the cache item status is changed to "initialized" and the cache item can be shared by other sessions.
[0041] Furthermore, the global cache item described in step S1 of the PL / SQL global cache method based on the openGauss database of the present invention contains the following information: the syntax tree of the stored procedure obtained after PL / SQL compilation, the cached data type (such as function, package, etc.), the data in the cache item, the status information of the cache item (including data status, reference count, etc.), and the system table meta information.
[0042] Furthermore, the global cache table described in step S1 of the PL / SQL global cache method based on the openGauss database of the present invention is used to manage all global cache items. The global cache table stores the results of compiling functions and packages in all PL / SQL and is shared by all threads. The global cache table contains the following information:
[0043] (1) Hash table: the key value is the cache key, and the value is the cache item. There are several LWLocks, and concurrency control is performed through the segmented lock mechanism.
[0044] (2) Information: capacity, load factor, memory context;
[0045] (3) LRU queue: used to control the total cache size and eliminate cache items that have not been used for a long time.
[0046] Furthermore, the global cache table described in step S1 of the PL / SQL global cache method based on the openGauss database of the present invention provides functions of searching for cache items, adding cache items, replacing cache items, and deleting cache items;
[0047] The global cache table holds a memory context. When adding a cache item, it switches to the memory context of the cache table. When deleting a cache item, the memory is returned.
[0048] The global cache table records the total memory size of cache items. When the memory of cache items reaches the upper limit, they are eliminated. The global cache table supports active elimination and passive elimination mechanisms: active elimination replaces the cache when it detects that the tuple version of the cache and the system table are inconsistent; passive elimination controls the memory usage of cache items and controls the elimination objects through the LRU queue;
[0049] The global cache table supports expansion. When the number of cache items reaches the threshold, the global cache table automatically expands.
[0050] Furthermore, the newly added global cache item described in step S1 of the PL / SQL global cache method based on the openGauss database of the present invention includes:
[0051] (1) First insert a global cache item with the status of "initializing" as a placeholder;
[0052] (2) After local compilation, the local cache is deeply copied to the global cache item;
[0053] (3) After completing the deep copy, the state of the global cache item is changed to "initialized", and the global cache item is truly available.
[0054] Furthermore, in order to protect the use safety of global cache items under high concurrency, the present invention adopts the following processing methods in the PL / SQL global cache method based on the openGauss database:
[0055] (1) For each global cache item, record a reference count. As long as the global cache item is in the global cache hash table, the reference count is at least 1 to ensure that all global cache items in the global cache hash table are available.
[0056] (2) When a session obtains the global cache hash table and puts it into the session cache, the reference count is increased by 1; when a session releases its own cache, if the cache is obtained from the global cache hash table, the reference count is reduced by 1; the cache item memory is released only when the reference count is reduced to 0;
[0057] (3) When reading the global cache, copy part of it to the session memory and split it into two parts: one part is a read-only cache that will not be modified after compilation; the other part is a writable cache that can be modified by the session during the execution of the function; when copying, only copy the writable cache to each session, and multiple sessions share a read-only cache.
[0058] Furthermore, the data version inconsistency described in step S4 of the PL / SQL global cache method based on the openGauss database of the present invention refers to the inconsistency between the system table metadata and the metadata recorded in the global cache item.
[0059] On the other hand, the present invention also provides a PL / SQL global cache system based on an openGauss database. When the system is running, the steps of the above-mentioned PL / SQL global cache method based on an openGauss database are implemented. The system includes:
[0060] Global cache key: an identifier used to find cache items in the global cache hash table. Each global cache key can uniquely correspond to a global cache item. The global cache key contains the cache data type, cache hash key, database Oid, and other parameters that can affect the compilation product.
[0061] Global cache items: used to store the actual PL / SQL cache. The global cache items contain the following information: the syntax tree of the stored procedure obtained after PL / SQL compilation, the cache data type (such as function, package, etc.), the data in the cache item, the status information of the cache item (including data status, reference count, etc.), and the system table meta information;
[0062] Global cache table: used to manage all global cache items. The global cache table stores the results of compiling all functions and packages in PL / SQL and is shared by all threads. The global cache table provides the functions of finding cache items, adding cache items, replacing cache items, and deleting cache items. The global cache table contains the following information: hash table (where the key value is the cache key and the value is the cache item), capacity, load factor, memory context, and LRU queue.
[0063] In addition, the present invention also provides a computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, the steps of the above-mentioned PL / SQL global caching method based on openGauss database are implemented.
[0064] In summary, the PL / SQL global caching method based on the openGauss database of the present invention significantly improves the performance of complex PL / SQL scenarios under multi-session and high concurrency by allowing multiple sessions to share the PL / SQL cache, reduces memory usage, and improves the robustness of the database. BRIEF DESCRIPTION OF THE DRAWINGS
[0065] In order to more clearly illustrate the technical solution of the present invention, the following briefly introduces the drawings required for use in the description of the present invention. Obviously, the following drawings are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without paying any creative work.
[0066] Figure 1 It is a schematic diagram of the overall architecture of the global cache system of the present invention.
[0067] Figure 2 Schematic diagram of the global cache item state transfer process in the method of the present invention.
[0068] Figure 3 It is a framework diagram of the implementation process of the method of the present invention.
[0069] Figure 4 The figure is a flow chart of the overall implementation of the method of the present invention. DETAILED DESCRIPTION
[0070] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of them. The present invention can also be implemented or applied through other different specific implementation methods. The details in this specification can also be modified or changed in various ways based on different viewpoints and applications without departing from the spirit of the present invention.
[0071] At the same time, it should be understood that the protection scope of the present invention is not limited to the specific embodiments described below; it should also be understood that the terms used in the embodiments of the present invention are for describing specific embodiments rather than for limiting the protection scope of the present invention.
[0072] Embodiment: A PL / SQL global cache method based on openGauss database
[0073] The technical solution of the present invention and its implementation in the openGauss database are explained below in conjunction with a flow chart.
[0074] First, the description of cache keys, cache items, and cache tables in the global cache is given:
[0075] (I) The global cache key is an identifier used to find cache items in the global cache hash table. Each cache key can uniquely correspond to a cache item. The cache key contains the following information:
[0076] (1) Cache data type: records whether the current cache item is a function or a package.
[0077] (2) Other influencing factors: other parameters that can affect the compiled product, such as the current GUC parameters.
[0078] (3) Cache hash key: PLpgSQL_func_hashkey or PLpgSQL_pkg_hashkey plus database Oid.
[0079] (ii) Global cache items store the actual PL / SQL cache, including the syntax tree of the stored procedure obtained after compilation, various variables and types required, and necessary system table metadata. The cache items contain the following information:
[0080] (1) The type of data being cached: function or package.
[0081] (2) Data in the cache item: PLpgSQL_function or PLpgSQL_package.
[0082] (3) The status of the cache item: including data status and reference count.
[0083] (3) The global cache table is the core structure of the global cache and is used to manage all global cache items.
[0084] The global cache table stores the results of all PLpgSQL function and package compilations and is shared by all threads. The global cache table allows expansion, and will expand when the number of cache items reaches the threshold.
[0085] The global cache table holds a memory context. When adding a cache item, you need to switch to the memory context of the cache table. When deleting a cache item, the memory is returned. The global cache table records the total memory size of the cache item. When the memory of the cache item reaches the upper limit, it will be eliminated.
[0086] The global cache table provides the functions of searching for cache items, adding cache items, replacing cache items, and deleting cache items. In addition, the global cache table has active elimination and passive elimination mechanisms - active elimination replaces the cache when it detects that the cache and system table tuple versions are inconsistent; passive elimination controls the memory usage of cache items and controls the elimination objects through the LRU queue.
[0087] The global cache table contains the following information:
[0088] (1) Hash table: The key value is the cache key, and the value is the cache item. There are several LWLocks, and concurrency control is performed through the segmented lock mechanism.
[0089] (2) Information: capacity, load factor, memory context, etc.
[0090] (3) LRU queue: used to control the total cache size and eliminate cache items that have not been used for a long time.
[0091] like Figure 3 As shown, when the PL / SQL global cache method based on the openGauss database of the present invention calls a stored procedure / function, the execution process of the global cache includes the following steps:
[0092] 1. First, check whether there is a session cache. If it exists, use the session cache directly. The process is the same as the current process of openGauss and will not be repeated here.
[0093] 2. If there is no session cache, try to find the global cache item in the global cache table. If you can find a global cache item that is available and has the correct version, use it directly.
[0094] 3. If the global cache item found has expired (the metadata in the system table is inconsistent with the metadata recorded in the cache item), you need to replace the global cache item, recompile, and put the new global cache item into the global cache table.
[0095] 4. If the global cache item is not found, you need to insert a global cache item with the status of "initializing" as a placeholder. After local compilation, deep copy the local cache to the global cache item. After the deep copy is completed, you can change the global cache item status to "initialized", and the global cache item is truly available.
[0096] 5. If there is a global cache item that is "initializing", this session will compile itself, and the execution process is consistent with the original logic of openGauss.
[0097] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Any technician familiar with the profession can make some changes or modifications to the technical contents disclosed above without departing from the scope of the technical solution of the present invention to obtain equivalent embodiments of equivalent changes. However, any simple modification, equivalent changes and modifications made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solution of the present invention should be included in the scope of protection of the claims of the present invention.
Claims
1. A PL / SQL global cache method based on openGauss database, It is characterized in that The method comprises: S1. Call a function, package function or stored procedure to search for a global cache entry in the global cache table. If no global cache entry is found, add a new global cache entry. S2. Check whether the data of the found or newly added global cache item is "initialized", if not, check whether the data of the global cache item is "initializing", if yes, release the reference of the global cache item, if not, start compiling the data; S3. If the data of the global cache item found or added is "initialized", check whether the data version is consistent. If the data version is consistent, use the global cache item to obtain the compiled data, and then release the reference to the global cache item; S4. If the data versions are inconsistent, the global cache item is replaced, and the new global cache item is stored in the global cache table, and then the data is compiled; S5. Save the data compiled in steps S2 and S4, and then release the global cache item reference.
2. The PL / SQL global cache method based on openGauss database according to claim 1, It is characterized in that This method embeds the global cache as a secondary cache into the compilation process of PL / SQL when the session-level cache cannot be found; in this method: (1) For the same version of a function or package, there is only one global cache entry; (2) Before the global cache item is guaranteed to be available, the session can only compile functions or packages on its own; (3) During the process of using a global cache item in any session, the actual memory of the global cache item is not allowed to be released or deleted.
3. The PL / SQL global cache method based on openGauss database according to claim 2, It is characterized in that The state transfer process of the cache item in this method is as follows: (1) When a new cache item needs to be added, the cache item data is first updated to the "uninitialized" state; (2) Before starting to compile, change the cache item status to "initializing". Other sessions will not try to use the global cache item in this state, but will choose to compile on their own: (a) If compilation fails, the cache item status returns to the "uninitialized" state; (b) If the compilation is successful, the cache item status changes to "initialized" and the cache item can be shared by other sessions.
4. The PL / SQL global cache method based on openGauss database according to claim 1, It is characterized in that The global cache item in step S1 contains the following information: the syntax tree of the stored procedure obtained after PL / SQL compilation, the cache data type, the data in the cache item, the status information of the cache item, and the system table meta information.
5. The PL / SQL global cache method based on openGauss database according to claim 1, It is characterized in that The global cache table described in step S1 is used to manage all global cache items. The global cache table stores the compilation results of all PL / SQL functions and packages and is shared by all threads. The global cache table contains the following information: (1) Hash table: the key value is the cache key, and the value is the cache item. There are several LWLocks, and concurrency control is performed through the segmented lock mechanism. (2) Information: capacity, load factor, memory context; (3) LRU queue: used to control the total cache size and eliminate cache items that have not been used for a long time.
6. The PL / SQL global cache method based on openGauss database according to claim 5, It is characterized in that The global cache table described in step S1 provides the functions of searching cache items, adding cache items, replacing cache items, and deleting cache items; The global cache table holds a memory context. When adding a cache item, it switches to the memory context of the cache table. When deleting a cache item, the memory is returned. The global cache table records the total memory size of cache items. When the memory of cache items reaches the upper limit, they are eliminated. The global cache table supports active elimination and passive elimination mechanisms: active elimination replaces the cache when it detects that the tuple version of the cache and the system table are inconsistent; passive elimination controls the memory usage of cache items and controls the elimination objects through the LRU queue; The global cache table supports expansion. When the number of cache items reaches the threshold, the global cache table automatically expands.
7. The PL / SQL global cache method based on openGauss database according to claim 1, It is characterized in that The newly added global cache item in step S1 includes: (1) First insert a global cache item with the status of "initializing" as a placeholder; (2) After local compilation, the local cache is deeply copied to the global cache item; (3) After completing the deep copy, the state of the global cache item is changed to "initialized", and the global cache item is truly available.
8. The PL / SQL global cache method based on openGauss database according to claim 1, It is characterized in that In order to protect the security of global cache items under high concurrency, this method adopts the following processing methods: (1) For each global cache item, record a reference count. As long as the global cache item is in the global cache hash table, the reference count is at least 1 to ensure that all global cache items in the global cache hash table are available. (2) When a session obtains the global cache hash table and puts it into the session cache, the reference count is increased by 1; when a session releases its own cache, if the cache is obtained from the global cache hash table, the reference count is reduced by 1; the cache item memory is released only when the reference count is reduced to 0; (3) When reading the global cache, it is split into two parts: one part is a read-only cache that will not be modified after compilation; the other part is a writable cache that can be modified by the session during the execution of the function; when copying, only the writable cache is copied to each session, and multiple sessions share a read-only cache.
9. The PL / SQL global cache method based on openGauss database according to claim 1, It is characterized in that The data version inconsistency described in step S4 refers to the inconsistency between the system table metadata and the metadata recorded in the global cache item.
10. A PL / SQL global cache system based on openGauss database, It is characterized in that When the system is running, the steps of implementing the PL / SQL global cache method based on the openGauss database according to any one of claims 1 to 9 are implemented, and the system includes: Global cache key: an identifier used to find cache items in the global cache hash table. Each global cache key can uniquely correspond to a global cache item. The global cache key contains the cache data type, cache hash key, database Oid, and other parameters that can affect the compilation product. Global cache items: used to store the actual PL / SQL cache. The global cache items contain the following information: the syntax tree of the stored procedure obtained after PL / SQL compilation, the cache data type, the data in the cache item, the status information of the cache item, and the system table meta information; Global cache table: used to manage all global cache items. The global cache table stores the results of compiling all functions and packages in PL / SQL and is shared by all threads. The global cache table provides the functions of searching for cache items, adding cache items, replacing cache items, and deleting cache items. The global cache table contains the following information: hash table, capacity, load factor, memory context, and LRU queue.
