CN116244041B - Performance optimization method for database sub-transaction - Google Patents
Performance optimization method for database sub-transaction Download PDFInfo
- Publication number
- CN116244041B CN116244041B CN202211536736.1A CN202211536736A CN116244041B CN 116244041 B CN116244041 B CN 116244041B CN 202211536736 A CN202211536736 A CN 202211536736A CN 116244041 B CN116244041 B CN 116244041B
- Authority
- CN
- China
- Prior art keywords
- transaction
- sub
- database
- parent
- father
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 31
- 238000005457 optimization Methods 0.000 title abstract description 14
- 230000015654 memory Effects 0.000 claims abstract description 39
- 230000007717 exclusion Effects 0.000 claims description 7
- 230000009191 jumping Effects 0.000 claims description 3
- 230000001360 synchronised effect Effects 0.000 claims description 3
- 239000000872 buffer Substances 0.000 description 7
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 238000002955 isolation Methods 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F9/00—Arrangements for program control, e.g. control units
- G06F9/06—Arrangements for program control, e.g. control units using stored programs, i.e. using an internal store of processing equipment to receive or retain programs
- G06F9/46—Multiprogramming arrangements
- G06F9/466—Transaction processing
- G06F9/467—Transactional memory
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F9/00—Arrangements for program control, e.g. control units
- G06F9/06—Arrangements for program control, e.g. control units using stored programs, i.e. using an internal store of processing equipment to receive or retain programs
- G06F9/46—Multiprogramming arrangements
- G06F9/52—Program synchronisation; Mutual exclusion, e.g. by means of semaphores
- G06F9/526—Mutual exclusion algorithms
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Software Systems (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a performance optimization method for database sub-transactions, belongs to the technical field of databases, and can effectively improve the performance of the database. According to the method, father-son relationship in the disk file is exchanged to the local memory exclusive to the current process, when the child transaction is processed, the transaction information cached in the local memory is read preferentially, when the father-son relationship is read from the shared memory to the local, the submitted maximum xmin is used as a mark, and the transaction before the maximum xmin is ensured to be submitted; the parent-child relationships after the maximum xmin have been written to shared memory. The method optimizes the processing performance of the sub-transaction in the database through the local memory, and ensures the data consistency of the main library and the standby library during sub-transaction optimization based on xmin, thereby realizing the performance optimization of the database. Based on the scheme of the invention, the sub-transaction processing can be efficiently executed under the multi-concurrency scene.
Description
Technical Field
The invention belongs to the technical field of databases, and particularly relates to a performance optimization method for database sub-transactions.
Background
At present, with the advance of IOE, the demand of replacing the Oracle database with the domestic database is increasing, the Oracle database tends to be stable in the processing performance of sub-transactions, and the domestic database is weaker in the processing performance of sub-transactions. Most of products (more than 70%) in the domestic database are modified and developed based on an open source database PostgreSQL or MySQL, wherein the PostgreSQL is relatively heavy (more than 6).
In PostgreSQL, a database transaction (transaction) is a sequence of database operations that access and potentially manipulate various data items, either all or none, and is an integral unit of work. A database transaction consists of all database operations performed between the beginning of the transaction and the end of the transaction. A sub-transaction is an operation that allows a rollback portion to have completed or failed in a transaction, and does not affect subsequent operations of the transaction, ensuring that the entire transaction is committed or rolled back.
Currently, in databases, disk synchronization occurs when the number of sub-transactions in the database exceeds the number specified by the macro-definition variable NUM_SUBTRANS_BUFFERS sub-transaction cache (default 32 pages). A child transaction lock is generated at this time, which is an exclusive lock, and performance problems occur when there is a high concurrency access (database background waiting events appear as subtrans or subtransControlLock).
The default sub-transaction buffer occupies 32 buffer pages, and each buffer has a size of 8k, i.e. the total size of the sub-transaction buffer is 32×8k=256 k. Each transaction number occupies a size of 4 bytes, and a maximum of 256 x 1024/4=65536 sub-transactions can be stored in the cache. When the number of sub-transactions in the system exceeds 65536, the PostgreSQL exchanges the oldest replayed sub-transaction number to the disk file for storage. When a process needs to read or update a sub-transaction, a global write lock, i.e., a subTransCtl lock mutex lock, must be set first when a disk file is demagnetized for querying. As shown in FIG. 1, when the parent transaction number of the child transaction does not exist in the cache, the sub TransCtl lock mutex lock is acquired, and the mutex lock is released after the file is read from the disk to the cache. The lock is globally unique and sequentially acquires release. Thus, postgreSQL is slow to perform when sub-transaction caches are exceeded.
Disclosure of Invention
In order to solve the problems, the invention provides a performance optimization method for database sub-transactions, which can effectively improve the performance of a database.
In order to achieve the above purpose, the technical scheme of the invention is as follows:
a method for optimizing the performance of database sub-transactions comprises the following steps:
step 1, inquiring the parent transaction number of a child transaction;
step 2, judging whether the father-son relationship exists in the local memory, jumping to step 10 when the father-son relationship exists, and executing step 3 when the father-son relationship does not exist;
step 3, searching the maximum xmin in all snapshots of the process;
step 4, judging whether a parent-child relationship exists in the shared memory, executing step 5 when the parent-child relationship does not exist, and executing step 9 when the parent-child relationship exists;
step 5, obtaining a SubTransCtl lock mutual exclusion lock;
step 6, reading the file from the disk to the shared memory;
step 7, releasing the SubTransCtl lock mutual exclusion lock;
step 8, copying transaction father and son in the shared memory and the disk file to the local memory;
step 9, marking the transaction before the maximum xmin to be visible;
step 10, the father transaction number is fetched from the local memory;
step 11, judging whether the parent transaction number obtained in the step 10 is valid, returning to the step 1 when the parent transaction number is invalid, and executing the step 12 when the parent transaction number is valid;
step 12, the parent transaction number is returned.
Further, the size of the local memory is set in a self-defined manner.
Furthermore, the performance optimization method of the database sub-transaction is executed when the sub-transaction is applied for or submitted in the database main library, and when the operation of the main library sub-transaction is completed, WAL is written and synchronized to the standby library; and after the WAL of the main library is received in the standby library, the standby library is played back to complete synchronization with the main library.
Further, the performance optimization method of the database sub-transaction is realized when the backup library plays back the WAL.
The beneficial effects of the invention are as follows:
the method optimizes the processing performance of the sub-transaction in the database through the local memory, and ensures the data consistency of the main library and the standby library during sub-transaction optimization based on xmin, thereby realizing the performance optimization of the database. Based on the scheme of the invention, the sub-transaction processing can be efficiently executed under the multi-concurrency scene.
Drawings
FIG. 1 is a prior art query of database sub-transactions.
FIG. 2 is a diagram of a method for optimizing the performance of database sub-transactions according to the present invention.
Detailed Description
The technical scheme provided by the present invention will be described in detail with reference to the following specific examples, and it should be understood that the following specific examples are only for illustrating the present invention and are not intended to limit the scope of the present invention.
When the number of sub-transactions in the system exceeds 65536, the PostgreSQL exchanges the oldest replayed sub-transaction number to the disk file by adopting the slru_subtrans, and in the invention, when the parent transaction number cannot be read in the shared memory for the first time by the process, the parent-child relationship in the disk file is exchanged to the local memory local_cache_subtrans exclusive to the current process. local_cache_subtrans is stored in the client connection, i.e. each connection process shares a private memory. When the sub-transaction is processed, the transaction information cached in the local memory is read preferentially: if found, the transaction information is directly used; if the transaction information cached in the local memory local cache subtrans is insufficient, the content in the disk file is still read continuously.
In addition, the consistency of the data of the main library and the backup library of the database is considered. Data consistency inside PostgreSQL is maintained by using a multi-version model (multi-version concurrency control, MVCC). This means that each SQL statement sees only a snapshot of the data (a database version) a short period of time ago, regardless of the current state of the underlying data. Thus, the statement may be protected from inconsistent data that may be caused by other concurrent transactions that perform updates on the same data line, providing transaction isolation for each database session. xmin is a system attribute that exists natively in PostgreSQL and identifies the identity of the transaction (transaction ID) into which the version of the row is inserted. A row version is a particular version of a row, and each update to a logical row will create a new row version. Because the information of the sub-transaction is read from the local memory, and the local memories of the main library and the standby library of the database are independently applied, in order to ensure the data consistency of the main library and the standby library, the processing logic of the data visibility on the main library and the standby library needs to be synchronously adjusted: when the parent-child relationship is read from the shared memory to the local, the maximum xmin submitted is taken as a mark: ensuring that transactions before the maximum xmin have committed; the parent-child relationships after the maximum xmin have been written to shared memory.
Based on the above optimization idea, the present embodiment provides a method for optimizing performance of database sub-transactions, whose main flow is shown in fig. 2, and includes the following steps:
step 1, inquiring the parent transaction number of a child transaction;
step 2, judging whether the father-son relationship exists in the local memory, jumping to step 10 when the father-son relationship exists in the local memory, and executing step 3 when the father-son relationship does not exist in the local memory;
step 3, searching the maximum xmin in all snapshots of the process;
step 4, judging whether a parent-child relationship exists in the shared memory Buffer, executing step 5 when the parent-child relationship does not exist, and executing step 9 when the parent-child relationship exists;
step 5, obtaining a SubTransCtl lock mutual exclusion lock;
step 6, reading the file from the disk to the shared memory; because of the existence of the SubTransCtl lock mutual exclusion lock, the disk file can only be operated by a unique process;
step 7, releasing the SubTransCtl lock mutual exclusion lock;
step 8, copying all transaction father and son in the shared memory and the disk file to the local memory; obviously, after copying, when inquiring about the rest sub-transactions in the process, the sub-TransCtl lock mutex lock access disk file does not need to be acquired again. The local memory local_cache_subtrans size can be set in a self-defined mode, and the number of accommodated sub-transactions can be far more than the default number of NUM_SUBTRANS_BUFFERS, so that the acquisition of the parent-child relationship in the local memory can be ensured.
Step 9, marking the transaction before the maximum xmin as visible, namely being valid for the transaction before the maximum xmin;
step 10, the father transaction number is fetched from the local memory;
step 11, judging whether the parent transaction number obtained in the step 10 is valid, returning to the step 1 when the parent transaction number is invalid, and executing the step 12 when the parent transaction number is valid;
and step 12, returning the parent transaction number and carrying out subsequent processing.
And executing the operation of each step in the performance optimization method of the database sub-transaction when the sub-transaction is applied or submitted in the database main library, and writing the operation process into the WAL and synchronizing the operation process to the standby library when the operation of the main library sub-transaction is completed. And after the WAL of the main library is received in the standby library, the standby library is replayed to realize the performance optimization method of the sub-transaction of the database, and the standby library is synchronized with the main library, so that the visible consistency of the sub-transaction is maintained with the main library.
It should be noted that the foregoing merely illustrates the technical idea of the present invention and is not intended to limit the scope of the present invention, and that a person skilled in the art may make several improvements and modifications without departing from the principles of the present invention, which fall within the scope of the claims of the present invention.
Claims (4)
1. A method for optimizing performance of database sub-transactions, comprising the steps of:
step 1, inquiring the parent transaction number of a child transaction;
step 2, judging whether the father-son relationship exists in the local memory, jumping to step 10 when the father-son relationship exists, and executing step 3 when the father-son relationship does not exist;
step 3, searching the maximum xmin in all snapshots of the process, wherein xmin is a system attribute and identifies the transaction identity of the inserted line version;
step 4, judging whether a parent-child relationship exists in the shared memory, executing step 5 when the parent-child relationship does not exist, and executing step 9 when the parent-child relationship exists;
step 5, obtaining a SubTransCtl lock mutual exclusion lock;
step 6, reading the file from the disk to the shared memory;
step 7, releasing the SubTransCtl lock mutual exclusion lock;
step 8, copying transaction father and son in the shared memory and the disk file to the local memory;
step 9, marking the transaction before the maximum xmin to be visible;
step 10, the father transaction number is fetched from the local memory;
step 11, judging whether the parent transaction number obtained in the step 10 is valid, returning to the step 1 when the parent transaction number is invalid, and executing the step 12 when the parent transaction number is valid;
step 12, the parent transaction number is returned.
2. The method of claim 1, wherein the size of the local memory is custom set.
3. The method for optimizing the performance of a database sub-transaction according to claim 1, wherein steps 1 to 12 are executed when a sub-transaction is applied or submitted in a main database, and the WAL is written and synchronized to a standby database when the main database sub-transaction operation is completed; and after the WAL of the main library is received in the standby library, the standby library is played back to complete synchronization with the main library.
4. A method of optimizing the performance of a database sub-transaction according to claim 3, wherein steps 1-12 are performed when the backup library plays back the WAL.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211536736.1A CN116244041B (en) | 2022-12-02 | 2022-12-02 | Performance optimization method for database sub-transaction |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211536736.1A CN116244041B (en) | 2022-12-02 | 2022-12-02 | Performance optimization method for database sub-transaction |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN116244041A CN116244041A (en) | 2023-06-09 |
| CN116244041B true CN116244041B (en) | 2023-10-27 |
Family
ID=86628416
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211536736.1A Active CN116244041B (en) | 2022-12-02 | 2022-12-02 | Performance optimization method for database sub-transaction |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN116244041B (en) |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CA2350721A1 (en) * | 2001-06-15 | 2002-12-15 | Ibm Canada Limited-Ibm Canada Limitee | Method and apparatus for chunk based transaction logging with asynchronous input/output for a database management system |
| CN101089857A (en) * | 2007-07-24 | 2007-12-19 | 中兴通讯股份有限公司 | Internal store data base transaction method and system |
| KR20180082753A (en) * | 2017-01-11 | 2018-07-19 | 울산과학기술원 | Database management system and method thereof using a non-volatile memory |
| CN108460047A (en) * | 2017-02-21 | 2018-08-28 | 阿里巴巴集团控股有限公司 | Method of data synchronization and equipment |
| CA3130011A1 (en) * | 2019-10-16 | 2021-04-22 | Shenzhen Sequoiadb Database Software Company | Fragment-free recycling-based database multiversion concurrence control (mvcc) system |
Family Cites Families (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7890472B2 (en) * | 2007-09-18 | 2011-02-15 | Microsoft Corporation | Parallel nested transactions in transactional memory |
| US9146774B2 (en) * | 2013-12-12 | 2015-09-29 | International Business Machines Corporation | Coalescing memory transactions |
| US10140312B2 (en) * | 2016-03-25 | 2018-11-27 | Amazon Technologies, Inc. | Low latency distributed storage service |
-
2022
- 2022-12-02 CN CN202211536736.1A patent/CN116244041B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CA2350721A1 (en) * | 2001-06-15 | 2002-12-15 | Ibm Canada Limited-Ibm Canada Limitee | Method and apparatus for chunk based transaction logging with asynchronous input/output for a database management system |
| CN101089857A (en) * | 2007-07-24 | 2007-12-19 | 中兴通讯股份有限公司 | Internal store data base transaction method and system |
| KR20180082753A (en) * | 2017-01-11 | 2018-07-19 | 울산과학기술원 | Database management system and method thereof using a non-volatile memory |
| CN108460047A (en) * | 2017-02-21 | 2018-08-28 | 阿里巴巴集团控股有限公司 | Method of data synchronization and equipment |
| CA3130011A1 (en) * | 2019-10-16 | 2021-04-22 | Shenzhen Sequoiadb Database Software Company | Fragment-free recycling-based database multiversion concurrence control (mvcc) system |
Non-Patent Citations (2)
| Title |
|---|
| Design of a Shared Memory mechanism for efficient paralell processing in PostgreSQL;Yoshifumi Ujibashi 等;《2015 6th International Conference on Information, Intelligence, Systems and Applications》;1-6 * |
| 分布式实时数据库系统事务管理的研究;熊燕群;《中国优秀硕士学位论文全文数据库 信息科技辑》(第6期);I138-565 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN116244041A (en) | 2023-06-09 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11874746B2 (en) | Transaction commit protocol with recoverable commit identifier | |
| US10268746B2 (en) | Mechanism to run OLTP workload on in-memory database under memory pressure | |
| EP3047400B1 (en) | Multi-version concurrency control on in-memory snapshot store of oracle in-memory database | |
| EP3047397B1 (en) | Mirroring, in memory, data from disk to improve query performance | |
| US9798630B2 (en) | Hardware-supported memory temporal copy | |
| US9606921B2 (en) | Granular creation and refresh of columnar data | |
| CN105630863B (en) | Transaction control block for multi-version concurrent commit status | |
| US7490113B2 (en) | Database log capture that publishes transactions to multiple targets to handle unavailable targets by separating the publishing of subscriptions and subsequently recombining the publishing | |
| JP3593366B2 (en) | Database management method | |
| US6647510B1 (en) | Method and apparatus for making available data that was locked by a dead transaction before rolling back the entire dead transaction | |
| US7996363B2 (en) | Real-time apply mechanism in standby database environments | |
| US7587429B2 (en) | Method for checkpointing a main-memory database | |
| JP7101566B2 (en) | Multiversion Concurrency Control (MVCC) in non-volatile memory | |
| CN105808643A (en) | Method for refreshing Redis memory database | |
| US10324809B2 (en) | Cache recovery for failed database instances | |
| CN113391885A (en) | Distributed transaction processing system | |
| CN111026771A (en) | Method for ensuring consistency of cache and database data | |
| CN113220490A (en) | Transaction persistence method and system for asynchronous write-back persistent memory | |
| CN116244041B (en) | Performance optimization method for database sub-transaction | |
| JP2004062759A (en) | Database log management method, its device and its program | |
| CN114003622B (en) | Huge transaction increment synchronization method between transaction type databases | |
| CN117389696A (en) | Parallel recovery method and storage medium applied to OLTP memory database | |
| CN116910064A (en) | Method and system for on-line creation of table index in distributed system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |