CN118069666A - PostgreSQL dynamic transferable table space method based on memory separation architecture - Google Patents

Abstract

The present invention discloses a PostgreSQL dynamic transferable tablespace method based on a storage-computation separation architecture, which comprises the following steps: monitoring data update of a PostgreSQL database, copying the original data row to a tablespace, generating a new version of the data row through the data update, and storing an Undo pointer in the row header of the new version of the data row; in response to transaction rollback of the PostgreSQL database, copying the original data row from the tablespace back to the original location; in response to all sessions no longer needing to access the original data row of the tablespace, clearing the original data row and releasing the tablespace; and configuring the tablespace Async Freeze algorithm to solve the unavailability phenomenon of overlapping XID value domains.

Description

A PostgreSQL dynamic transferable tablespace method based on storage and computing separation architecture

Technical Field

The present invention relates to the technical field of relational databases for cloud computing, and more specifically to a PostgreSQL dynamic transferable tablespace method based on a storage-computing separation architecture.

Background technique

The design of distributed systems based on traditional relational database architecture has high technical complexity. There are two main reasons for this, which are also the two problems that the distributed relational database architecture needs to solve:

1. Complex relational models place high demands on data organization and access

2. Storage and computing coupling increases the difficulty of the system in terms of horizontal expansion, resource scheduling, failover, backup and recovery, etc.

Apart from solutions that rely on proprietary hardware, the mainstream solutions in the industry start from the above two points, corresponding to two major technical directions:

1. Share-nothing: Horizontally split data and introduce a distributed execution engine to support complex queries across nodes

2. Share-storage: sink data to distributed storage, make the computing side stateless, and pool the storage layer

After years of exploration, PostgreSQL has many excellent implementations in the above two directions, but from the perspective of the two architectural problems to be solved, these implementations have only solved one of them. For example, many MPP architecture database systems adopt a share-nothing architecture, which has advantages in cluster scalability, but for a single data shard, it is still a traditional master-slave architecture, and problem 2 has not been effectively solved. Although various cloud native solutions based on storage and computing separation have developed a multi-master architecture, in order to balance the latency and performance challenges brought by distributed scenarios, optimistic locking and other methods are usually used to achieve state synchronization, which puts higher requirements on the business in actual application. If the business does not split the traffic reasonably, its write capability cannot be linearly expanded in theory, that is, problem 1 has not been effectively solved. A feasible solution is to combine the two architectures. Specifically, the MPP database can be run on a storage and computing separation architecture. This architecture combines the advantages of both and can solve the above two problems at the same time.

However, the current storage-computing separation architecture separates the data of a single database instance from the computing logic as a whole, and does not care about the organization of metadata. This prevents the MPP database from effectively taking advantage of the storage-computing separation architecture and achieving the ultimate elasticity of computing nodes. A simple scenario is: when an MPP node needs to be split, although its data has been separated from the computing, the computing cannot be split because the data is a whole. If the traditional data replication and migration method is adopted, this will return to the traditional MPP architecture, and the advantages of storage-computing separation will be lost.

In contrast, the present invention provides a PostgreSQL dynamic transferable tablespace method based on a storage-computation separation architecture, which can efficiently support data splitting, thereby organically combining the two architectures to achieve an extremely flexible architecture for computing and storage.

Summary of the invention

In view of the deficiencies in the prior art, the purpose of the present invention is to provide a PostgreSQL dynamic transferable tablespace method based on a storage-computation separation architecture, which can efficiently support data splitting, thereby organically combining the two architectures to achieve an extremely flexible architecture for computing and storage.

To achieve the above object, the present invention provides the following technical solutions:

A PostgreSQL dynamic transferable tablespace method based on storage and computing separation architecture includes the following steps:

It is monitored that data update occurs in the PostgreSQL database, the original data row is copied to the table space, the data update generates a new version data row, and an Undo pointer is stored in the row header of the new version data row;

In response to a transaction rollback in the PostgreSQL database, copying the original data row from the table space back to the original location;

In response to all sessions no longer needing to access the original data row in the table space, clearing the original data row and releasing the table space;

The configuration tablespace Async Freeze algorithm solution is used to solve the problem of XID value range overlap and unavailability.

As a further improvement of the present invention, the Async Freeze algorithm comprises the following steps:

Add the tablespace mount version number to the data page. Each time the tablespace is mounted to a new PG instance, the version number is incremented by 1. When the page is updated, the current version number is written. By judging the version number, it is possible to determine whether the last update of the current page was initiated by the current instance.

After the tablespace is mounted to the new instance, start the asynchronous task to execute Async Freeze, which scans all tablespace pages, identifies the unupdated pages by version number, and executes the Freeze operation to reclaim the XID.

After the tablespace is mounted to the new instance, it can support read and write operations. If the page read is an unfreezed page, the Freeze operation is initiated immediately.

After the page is frozen, its mounting version number is also updated synchronously and it is treated as a normal page.

As a further improvement of the present invention, based on PostgreSQL on the cloud native architecture, dynamic cross-instance migration of tablespaces is performed, which mainly includes the following transformation steps:

Table space storage is connected to distributed storage;

Tablespace unload operation;

Tablespace mount operation.

As a further improvement of the present invention, the table space unloading operation includes the following steps:

Stop writing to the target tablespace;

Execute checkpoint to ensure that the tablespace data is flushed;

Delete the tablespace-related metadata from PostgreSQL;

Unmount the Distributed File System.

As a further improvement of the present invention, the table space mounting operation includes the following steps:

Mount the distributed file system;

Add tablespace related metadata in PostgreSQL;

Enable tablespace read and write operations;

Start the tablespace Async Freeze operation.

As a further improvement of the present invention, the monitoring of data update in the PostgreSQL database, copying the original data row to the table space, the data update generating a new version of the data row, and storing an Undo pointer in the row header of the new version of the data row include the following steps: the PostgreSQL database runs an UndoLauncher process to monitor whether the running operation is a write operation; in response to the UndoLauncher process monitoring that a write operation is running, the UndoLauncher process sends a write operation notification to the UndoWorker process; the UndoWorker process receives the write operation notification and copies the original data row to the table space; the write operation in the PostgreSQL database is executed in situ to obtain a new version of the data row; and the Undo pointer is stored in the row header of the new version of the data row, and the Undo pointer points to the original data row in the table space.

As a further improvement of the present invention, in response to all sessions no longer needing to access the original data rows of the table space, cleaning up the original data rows and releasing the table space, comprises the following steps: monitoring through the UndoLauncher process that all sessions no longer need to access the original data rows, notifying the UndoCleanWorker process to perform data cleaning; the UndoCleanWorker process receives the data cleaning notification, cleans up the original data rows and releases the table space.

As a further improvement of the present invention, it also includes:

A data update module is used to monitor data updates in the PostgreSQL database, copy the original data rows to the table space, generate new version data rows through data updates, and store Undo pointers in the headers of the new version data rows; a transaction rollback module is used to copy the original data rows from the table space back to their original locations in response to transaction rollbacks in the PostgreSQL database; a data cleanup module is used to clean up the original data rows and release the table space in response to all sessions no longer needing to access the original data rows in the table space.

A computer-readable storage medium stores a computer program, which, when executed by a processor, implements the PostgreSQL dynamic transferable tablespace method based on the storage-computation separation architecture as described above.

A device comprising:

A memory for storing instructions;

The processor is used to execute the instruction so that the device performs operations to implement the PostgreSQL dynamic transferable tablespace method based on the storage and computing separation architecture as described above.

Beneficial effects of the present invention:

In the traditional MPP architecture, the expansion and contraction of data nodes requires migration and deletion of data, which will affect the business load. At the same time, the whole process has many steps, takes a long time, and has a low success rate. Through the present invention, the above shortcomings can be effectively solved, so that the expansion and contraction of computing nodes can be completed in seconds. At the same time, combined with the advantages of cloud native architecture, the ultimate elasticity from computing to storage is truly realized.

BRIEF DESCRIPTION OF THE DRAWINGS

Figure 1 is a diagram of the MPP architecture;

Figure 2 shows the storage and computing separation architecture;

Figure 3 shows the cloud-native distributed architecture;

Figure 4 shows the cloud-native distributed architecture with tablespace added.

Detailed ways

In order to make the purpose, technical solution and advantages of the embodiment of the present invention clearer, the technical solution of the embodiment of the present invention will be clearly and completely described below in conjunction with the drawings of the embodiment of the present invention. Obviously, the described embodiment is a part of the embodiment of the present invention, not all of the embodiments. Based on the described embodiment of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

Unless otherwise defined, the technical terms or scientific terms used in the present invention should be understood by people with ordinary skills in the field to which the present invention belongs. "First", "second" and similar words used in the present invention do not indicate any order, quantity or importance, but are only used to distinguish different components. "Include" or "comprise" and similar words mean that the elements or objects appearing before the word include the elements or objects listed after the word and their equivalents, without excluding other elements or objects. "Connect" or "connected" and similar words are not limited to physical or mechanical connections, but may include electrical connections, whether direct or indirect. "Up", "down", "left", "right" and the like are only used to indicate relative positional relationships. When the absolute position of the described object changes, the relative positional relationship may also change accordingly.

In order to keep the following description of the embodiments of the present invention clear and concise, detailed descriptions of well-known functions and well-known components are omitted.

Referring to FIG. 1 to FIG. 4 , a specific implementation of a PostgreSQL dynamic transferable tablespace method based on a storage-computation separation architecture of the present invention is shown, and the method includes the following steps:

It is monitored that data update occurs in the PostgreSQL database, the original data row is copied to the table space, the data update generates a new version data row, and an Undo pointer is stored in the row header of the new version data row;

In response to a transaction rollback in the PostgreSQL database, copying the original data row from the table space back to the original location;

In response to all sessions no longer needing to access the original data row in the table space, clearing the original data row and releasing the table space;

The configuration tablespace Async Freeze algorithm solution is used to solve the problem of XID value range overlap and unavailability.

The data updates of the PostgreSQL database monitored also include:

Step 101, the PostgreSQL database runs the UndoLauncher process to monitor whether the running operation is a write operation;

Step 102, in response to the UndoLauncher process detecting that a write operation is being performed, the UndoLauncher process sends a write operation notification to the UndoWorker process;

Step 103, the UndoWorker process receives the write operation notification and copies the original data row to the Undo tablespace;

Step 104, the write operation in the PostgreSQL database is performed in situ to obtain a new version of the data row;

Step 105: store an Undo pointer in the row header of the new version data row, wherein the Undo pointer points to the original data row in the Undo tablespace. Specifically, data update operations include INSERT operations, UPDATE operations, and DELETE operations.

The method monitors data update in the PostgreSQL database, copies the original data row to the table space, generates a new version of the data row through the data update, and stores an Undo pointer at the row header of the new version of the data row, including the following steps: the PostgreSQL database runs an UndoLauncher process to monitor whether the operation being run is a write operation; in response to the UndoLauncher process monitoring that a write operation is being run, the UndoLauncher process sends a write operation notification to the UndoWorker process; the UndoWorker process receives the write operation notification and copies the original data row to the table space; the write operation in the PostgreSQL database is executed in situ to obtain a new version of the data row; and the Undo pointer is stored at the row header of the new version of the data row, the Undo pointer pointing to the original data row in the table space.

Wherein, in response to the transaction rollback occurring in the PostgreSQL database, the step further includes:

Step 201, monitoring whether the operation being run in the PostgreSQL database is a write operation through the UndoLauncher process;

Step 202, if it is a write operation, the UndoLauncher process sends a write operation notification to the UndoWorker process;

Step 203: The UndoWorker process receives the write operation notification and copies the original data row to the Undo tablespace;

Step 204, the write operation is performed in situ to obtain a new version of the data row, and the Undo pointer is stored in the DB_ROL_TRX field of the new version of the data row;

Furthermore, the Undo pointer points to the location of the original data row, so that the query SQL reads the original data of the row in the Undo tablespace for query.

Step 205: Write the operations in step 203 and step 204 into the WAL log.

Further, the operations in step 203 and step 204 are written into the WAL log, which is used to check the data consistency of the original data row in the Undo space and the persistence of the data. Among them, the WAL log is the WriteAheadLog pre-written log, which is written into the log file before the data modification is submitted. When the server crashes, the log can be effectively replayed to restore the data to before the crash. That is, after the server crashes and restarts, the program can check the log file, compare the operation content planned to be executed when the server crashes with the operation content actually executed, and choose to cancel the executed operation, continue to complete the executed operation or keep it as it is according to the execution of the transaction, so as to realize the recoverability of the data and the persistence of the successfully submitted data to the disk.

In response to all sessions no longer needing to access the original data rows of the tablespace, cleaning up the original data rows and releasing the tablespace, the steps include: monitoring through the UndoLauncher process that all sessions no longer need to access the original data rows, notifying the UndoCleanWorker process to perform data cleaning; the UndoCleanWorker process receives the data cleaning notification, cleans up the original data rows and releases the tablespace.

Also includes:

A data update module is used to monitor data updates in the PostgreSQL database, copy the original data rows to the table space, generate new version data rows through data updates, and store Undo pointers in the headers of the new version data rows; a transaction rollback module is used to copy the original data rows from the table space back to their original locations in response to transaction rollbacks in the PostgreSQL database; a data cleanup module is used to clean up the original data rows and release the table space in response to all sessions no longer needing to access the original data rows in the table space.

The present invention is based on a cloud-native distributed architecture, which integrates the MPP distributed computing framework on top of the cloud-native architecture with storage and computing separation to achieve extreme elasticity in computing and storage. As can be seen from Figure 3, the computing node is a typical MPP architecture, each node is responsible for reading and writing part of the data, and the data partitions of each node do not overlap. All data is uniformly persisted in the distributed storage system, so that the storage of each node is elastic, and no backup machine is required to ensure the reliability of the data. However, the computing nodes are not elastic, and we can only improve the performance of a single node by upgrading the hardware. If we want to add nodes, we need to migrate part of the data in the original node to the new node, and then delete some unnecessary data separately.

For example, the data of the entire cluster is divided into 26 partitions [A, Z]. One node is responsible for the read and write of [A, F]. Now we want to split the node into two nodes responsible for the read and write of [A, C] and [D, F] respectively. The traditional approach is to copy the original node to a new node, then delete the data belonging to [D, F] in the original node, and delete the data belonging to [A, C] in the new node. These operations are based on the synchronization and deletion of a large amount of data, which is very costly.

PostgreSQL has the concept of tablespaces. We have unified the concepts of tablespaces and data partitions. At the same time, we have added the logic of unloading and mounting tablespaces (non-empty tablespaces containing data) in PostgreSQL. These two operations only require modifying a small amount of metadata related to the tablespace and can be completed quickly. The actual tablespace data is in the distributed storage system and does not need to be migrated or copied. In this way, fast migration of tablespaces (data partitions) across nodes can be achieved. On this basis, node expansion and contraction, load scheduling, and disaster recovery switching can be achieved in seconds.

To implement dynamic migration of tablespaces across instances in PostgreSQL, the problem of XID recycling needs to be solved. Because the range of XID values is limited to the database instance that generates the XID, once the tablespace is mounted to another instance, the range of these XID values will overlap with the range of the instance's XID and become unavailable.

The Async Freeze algorithm includes the following steps:

Add the tablespace mount version number to the data page. Each time the tablespace is mounted to a new PG instance, the version number is incremented by 1. When the page is updated, the current version number is written. By judging the version number, it is possible to determine whether the last update of the current page was initiated by the current instance.

After the tablespace is mounted to the new instance, start the asynchronous task to execute Async Freeze, which scans all tablespace pages, identifies the unupdated pages by version number, and executes the Freeze operation to reclaim the XID.

After the tablespace is mounted to the new instance, it can support read and write operations. If the page read is an unfreezed page, the Freeze operation is initiated immediately.

After the page is frozen, its mounting version number is also updated synchronously and it is treated as a normal page.

Based on PostgreSQL's cloud-native architecture, dynamic cross-instance migration of tablespaces is performed. The main transformation steps are as follows:

Table space storage is connected to distributed storage;

Tablespace unload operation;

The tablespace unload operation consists of the following steps:

Stop writing to the target tablespace;

Execute checkpoint to ensure that the tablespace data is flushed;

Delete the tablespace-related metadata from PostgreSQL;

Unmount the Distributed File System.

Tablespace mount operation.

The tablespace mount operation includes the following steps:

Mount the distributed file system;

Add tablespace related metadata in PostgreSQL;

Enable tablespace read and write operations;

Start the tablespace Async Freeze operation.

Those skilled in the art will appreciate that embodiments of the present invention may be provided as methods, systems, or computer program products. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

The present invention is described with reference to the flowchart and/or block diagram of the method, device (system), and computer program product according to the embodiment of the present invention. It should be understood that each process and/or box in the flowchart and/or block diagram, as well as the combination of the process and/or box in the flowchart and/or block diagram can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device produce a device for implementing the functions specified in one or more processes in the flowchart and/or one or more boxes in the block diagram.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer-readable memory produce a manufactured product including an instruction device that implements the functions specified in one or more processes in the flowchart and/or one or more boxes in the block diagram.

These computer program instructions may also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, whereby the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in one or more processes in the flowchart and/or one or more boxes in the block diagram.

In addition, although exemplary embodiments have been described in the present invention, the scope includes any and all embodiments based on the present invention with equivalent elements, modifications, omissions, combinations (e.g., various embodiments intersecting schemes), adaptations or changes. The elements in the claims will be interpreted broadly based on the language adopted in the claims, and are not limited to the examples described in this specification or during the implementation of this application, and the examples will be interpreted as non-exclusive. Therefore, this specification and examples are intended to be considered as examples only, and the true scope and spirit are indicated by the following claims and the full scope of their equivalents.

The above description is intended to be illustrative rather than restrictive. For example, the above examples (or one or more of them) can be used in combination with each other. For example, a person of ordinary skill in the art can use other embodiments when reading the above description. In addition, in the above-mentioned specific embodiments, various features can be grouped together to simplify the present invention. This should not be interpreted as an intention that a disclosed feature that is not required to be protected is necessary for any claim. On the contrary, the subject matter of the present invention may be less than all the features of a specific disclosed embodiment. Thus, the following claims are incorporated into the specific embodiments as examples or embodiments, wherein each claim is independently used as a separate embodiment, and it is considered that these embodiments can be combined with each other in various combinations or arrangements. The scope of the present invention should be determined with reference to the attached claims and the full scope of equivalent forms granted by these claims.

The above embodiments are only exemplary embodiments of the present invention and are not intended to limit the present invention. The protection scope of the present invention is defined by the claims. Those skilled in the art may make various modifications or equivalent substitutions to the present invention within the essence and protection scope of the present invention, and such modifications or equivalent substitutions shall also be deemed to fall within the protection scope of the present invention.

Claims (10)

1. A PostgreSQL dynamic transferable tablespace method based on storage and computing separation architecture, characterized by comprising the following steps: It is monitored that data update occurs in the PostgreSQL database, the original data row is copied to the table space, the data update generates a new version data row, and an Undo pointer is stored in the row header of the new version data row; In response to a transaction rollback in the PostgreSQL database, copying the original data row from the table space back to the original location; In response to all sessions no longer needing to access the original data row in the table space, clearing the original data row and releasing the table space; The configuration tablespace Async Freeze algorithm solution is used to solve the problem of XID value range overlap and unavailability. 2. According to a PostgreSQL dynamic transferable tablespace method based on storage and computing separation architecture according to claim 1, it is characterized in that: the Async Freeze algorithm comprises the following steps: Add the tablespace mount version number to the data page. Each time the tablespace is mounted to a new PG instance, the version number is incremented by 1. When the page is updated, the current version number is written. By judging the version number, it is possible to determine whether the last update of the current page was initiated by the current instance. After the tablespace is mounted to the new instance, start the asynchronous task to execute Async Freeze, which scans all tablespace pages, identifies the unupdated pages by version number, and executes the Freeze operation to reclaim the XID. After the tablespace is mounted to the new instance, it can support read and write operations. If the page read is an unfreezed page, the Freeze operation is initiated immediately. After the page is frozen, its mounting version number is also updated synchronously and it is treated as a normal page. 3. According to a PostgreSQL dynamic transferable tablespace method based on a storage-computing separation architecture according to claim 2, it is characterized in that: based on PostgreSQL on the basis of a cloud native architecture, a cross-instance dynamic transfer of tablespace is performed, which mainly includes the following transformation steps: Table space storage is connected to distributed storage; Tablespace unload operation; Tablespace mount operation. 4. A PostgreSQL dynamic transferable tablespace method based on storage and computing separation architecture according to claim 3, characterized in that: the tablespace unloading operation comprises the following steps: Stop writing to the target tablespace; Execute checkpoint to ensure that the tablespace data is flushed; Delete the metadata related to the tablespace from PostgreSQL; Unmount the Distributed File System. 5. A PostgreSQL dynamic transferable tablespace method based on storage and computing separation architecture according to claim 4, characterized in that: the tablespace mounting operation comprises the following steps: Mount the distributed file system; Add tablespace related metadata in PostgreSQL; Enable tablespace read and write operations; Start the tablespace Async Freeze operation. 6. According to claim 5, a PostgreSQL dynamic transferable tablespace method based on a storage-computation separation architecture is characterized in that: when data update is detected in the PostgreSQL database, the original data row is copied to the tablespace, the data update generates a new version of the data row, and an Undo pointer is stored in the header of the new version of the data row, comprising the following steps: the PostgreSQL database runs an UndoLauncher process to monitor whether the operation being run is a write operation; in response to the UndoLauncher process detecting that a write operation is being run, the UndoLauncher process sends a write operation notification to the UndoWorker process; the UndoWorker process receives the write operation notification and copies the original data row to the tablespace; the write operation in the PostgreSQL database is executed in situ to obtain a new version of the data row; the Undo pointer is stored in the header of the new version of the data row, and the Undo pointer points to the original data row in the tablespace. 7. A PostgreSQL dynamic transferable tablespace method based on storage-computation separation architecture according to claim 6, characterized in that: in response to all sessions no longer needing to access the original data rows of the tablespace, cleaning up the original data rows and releasing the tablespace, comprises the following steps: monitoring through the UndoLauncher process that all sessions no longer need to access the original data rows, notifying the UndoCleanWorker process to perform data cleaning; the UndoCleanWorker process receives the data cleaning notification, cleans up the original data rows, and releases the tablespace. 8. The PostgreSQL dynamic transferable tablespace method based on the storage and computing separation architecture according to claim 7, characterized in that it also includes: A data update module is used to monitor data updates in the PostgreSQL database, copy the original data rows to the table space, generate new version data rows through data updates, and store Undo pointers in the headers of the new version data rows; a transaction rollback module is used to copy the original data rows from the table space back to their original locations in response to transaction rollbacks in the PostgreSQL database; a data cleanup module is used to clean up the original data rows and release the table space in response to all sessions no longer needing to access the original data rows in the table space. 9. A computer-readable storage medium having a computer program stored thereon, characterized in that when the computer program is executed by a processor, the method for dynamically transferable tablespaces of PostgreSQL based on a storage-computation separation architecture as described in any one of claims 1 to 8 is implemented. 10. A device, comprising: A memory for storing instructions; A processor is used to execute the instruction so that the device performs operations to implement the PostgreSQL dynamic transferable tablespace method based on the storage-computation separation architecture as described in any one of claims 1 to 8.