CN114116768A

CN114116768A - Method for performing read-write separation on database cluster

Info

Publication number: CN114116768A
Application number: CN202111429813.9A
Authority: CN
Inventors: 谭洋; 王杰
Original assignee: Highgo Base Software Co ltd
Current assignee: Highgo Base Software Co ltd
Priority date: 2021-11-29
Filing date: 2021-11-29
Publication date: 2022-03-01

Abstract

The invention discloses a method for performing read-write separation on a database cluster, which is characterized in that a middleware is arranged between the database cluster and a client, and all SQL sentences are subjected to very detailed read-write separation logic processing through the middleware, so that the SQL sentences sent by the client can be accurately distributed to a main node and a standby node to the utmost extent, and the overall efficiency of the database cluster is greatly improved.

Description

Method for performing read-write separation on database cluster

Technical Field

The invention relates to the technical field of computers, in particular to a method for performing read-write separation on a database cluster.

Background

In order to ensure the security of database cluster data, a cluster environment with one master node and multiple backup nodes is often used in a production environment, the working mode is that all read-write operations of a client are sent to a master node, and when the data of the master node changes, the data of the backup nodes correspondingly changes, so that the data information of the master node and the backup nodes is synchronized, and the aim of master-backup synchronization is fulfilled. In order to further share the pressure of the main node and improve the utilization rate of the standby node, someone can add a data query function to the standby node through read-write separation. However, the query of the object that has not been modified in the transaction and the pre-order transaction by the existing read-write separation scheme may still be allocated to the standby machine for execution, which may result in that a large number of query statements subsequent to the write statement in the long transaction cannot be allocated to the standby machine, and ultimately affects the efficiency of cluster data processing.

Disclosure of Invention

The invention provides a method for performing read-write separation on a database cluster, which aims to solve the problem that an SQL statement cannot be accurately routed by a read-write separation scheme in the prior art.

The invention provides a method for performing read-write separation on a database cluster, which comprises the following steps: judging whether the current SQL statement is sent to a main node and a standby node at the same time, if so, sending the SQL statement to the main node and the standby node, and if not, further judging whether the SQL statement is a read type SQL statement or a write type SQL statement;

for writing type SQL sentences, judging whether the SQL sentences have influence on subsequent SQL sentences or not, and recording the SQL sentences which have influence on the subsequent SQL sentences to the transaction read-write splitter or the session read-write splitter;

judging a main node or a standby node to which SQL sentences of all read sentences are sent according to the transaction read-write diverter and the session read-write diverter;

the transaction read-write splitter is created after starting a transaction to store a preset object of a write statement in the transaction, and the transaction read-write splitter is destroyed after the transaction is ended; the session read-write shunt is created when a session connection is established, is used for storing a preset object which is used by the current session and is only used by the main node, and is destroyed after the session is connected.

Optionally, before determining whether the current SQL statement is sent to the primary node and the standby node at the same time, the method further includes:

and judging the current transaction state, if the current transaction state is in a transaction, setting a transaction mark, and if the current transaction state is in a non-transaction state, setting a non-transaction mark.

Optionally, the determining whether the SQL statement affects a subsequent SQL statement and recording an object in the SQL statement that affects the subsequent SQL statement to the transaction read-write splitter or the session read-write splitter includes:

and for a preset object in the transaction, recording the preset object to the transaction read-write splitter, marking the route to the main node, and only recording the route into a cache if the preset object is a non-transaction.

And recording the temporary preset object into the session read-write shunt, and marking and routing the temporary preset object to the main node.

for the SQL statement of the write type carrying the transaction identifier, and if the SQL statement has no influence on the subsequent read statement, only the SQL route is marked to the main node;

if the SQL statement of the write type carrying the transaction identifier is uncertain in the influence range of the SQL statement, marking that the current SQL statement and all subsequent SQL statements in the transaction are sent to the main node until the transaction is ended;

and for the SQL statement carrying the write type of the transaction identifier and capable of identifying all objects in the SQL statement, recording the identified objects and the objects related to the identified objects to the corresponding transaction read-write flow divider, and marking to route the SQL statement to the main node.

Optionally, the method further comprises: and recording the SQL sentences with the influence relations for the SQL sentences with the indirect mutual influence relations of the modification of the preset object, so as to provide a basis for sending the indirectly influenced SQL sentences to the main and standby nodes.

Optionally, the method further comprises: and after the SQL statement modifies the table, processing the SQL statements in all subsequent sessions by adopting a time delay processing mechanism based on data delay between the main node database and the standby node data.

Optionally, when the SQL statement is sent to the standby node, the delay processing mechanism mode is entered, that is, by checking whether the object in the SQL is in the delay list, if yes, the SQL is sent to the host node, and if not, the SQL is sent to the standby node.

Optionally, the delay time in the delay processing mechanism is the stream replication delay time obtained from the database plus the stream replication delay time of the configuration file.

Optionally, after successfully executing SQL statements related to other objects, such as a created user, an inheritance table, a view, a trigger, and the like in a non-transaction mode, storing the objects in corresponding files, and directly emptying the cache if the objects fail;

and after the whole transaction in the transaction mode is successfully executed, storing the object in the cache into the corresponding file, and clearing the cache if the transaction fails or rolls back.

Optionally, the preset objects include tables, views, and triggers.

The invention has the following beneficial effects:

according to the invention, the middleware is arranged between the database cluster and the client, and all SQL sentences are subjected to very detailed read-write separation logic processing through the middleware, so that the SQL sentences sent by the client can be accurately distributed to the main and standby nodes to the maximum extent, and the overall efficiency of the database cluster is greatly improved.

The foregoing description is only an overview of the technical solutions of the present invention, and the embodiments of the present invention are described below in order to make the technical means of the present invention more clearly understood and to make the above and other objects, features, and advantages of the present invention more clearly understandable.

Drawings

Various other advantages and benefits will become apparent to those of ordinary skill in the art upon reading the following detailed description of the preferred embodiments. The drawings are only for purposes of illustrating the preferred embodiments and are not to be construed as limiting the invention. Also, like reference numerals are used to refer to like parts throughout the drawings. In the drawings:

fig. 1 is a schematic flowchart of a method for performing read-write separation on a database cluster according to an embodiment of the present invention;

fig. 2 is a structural diagram for performing read-write separation on a database cluster according to an embodiment of the present invention;

FIG. 3 is a schematic flow chart of determining transactions and non-transactions according to an embodiment of the present invention;

FIG. 4 is a flow chart illustrating a method for read and split processing for transactional and non-transactional modes according to an embodiment of the present invention;

fig. 5 is a schematic flow chart illustrating a read-write separation process according to an embodiment of the present invention.

Detailed Description

Aiming at the problem that the existing read-write separation can not accurately route the SQL sentences, the embodiment of the invention arranges the middleware between the database cluster and the client and carries out very detailed read-write separation logic processing on all the SQL sentences through the middleware, thereby accurately distributing the SQL sentences sent by the client to the main and standby nodes to the maximum extent and further greatly improving the overall efficiency of the database cluster. The present invention will be described in further detail below with reference to the drawings and examples. It should be understood that the specific embodiments described herein are merely illustrative of the invention and do not limit the invention.

Aiming at the current common read-write separation processing logic, SQL is divided into a transaction scene and a non-transaction scene for respective processing: when the conversation is in a non-transaction state, sending a read statement to a standby state, sending a write statement to a main state, and when the conditions of a temporary view, a temporary table and the like are met, sending all subsequent statements in the conversation to the main state; when in transaction, the read type SQL is sent to the standby node, and all subsequent SQL statements are sent to the master node until the write type SQL is encountered, so that the accuracy of data is ensured.

In the scheme, the query of the object which is not modified in the transaction and the preorder transaction can be actually distributed to the standby machine for execution, but the existing scheme mechanically causes that a large number of query statements after the write statement in the long transaction cannot be distributed to the standby machine, and the overall efficiency of the database cluster is reduced.

That is, in the process of read-write separation, it is necessary to consider common read-write separation, that is, write SQL is sent to the master node, read SQL is sent to the slave node, then it is necessary to consider that the related SQL of the temporary table, the temporary view, the temporary domain, etc. can only be sent to the master node, and at the same time, it is also necessary to consider the influence of the write statement in the transaction on the SQL after the statement, and consider the influence of the modification of one table on other tables or views, so that after considering that one table is modified, all tables and views affected by the modification should be sent to the master node, and when one table is modified, the table may cause the modification of other associated tables, which finally results in query errors.

Based on the above consideration, an embodiment of the present invention provides a method for performing read-write separation on a database cluster, and referring to fig. 1, the method includes:

s101, judging whether the current SQL statement is sent to a main node and a standby node at the same time, if so, entering S102, and if not, entering S103;

s102, sending the SQL statement to a main node and a standby node;

s103, judging whether the SQL statement is a reading type SQL statement or a writing type SQL statement;

s104, judging whether the SQL statement has influence on the subsequent SQL statement or not for the writing type SQL statement, and recording the SQL statement having influence on the subsequent SQL statement to the transaction read-write shunt or the session read-write shunt;

s105, judging a main node or a standby node to which all SQL statements of the read statements are sent according to the transaction read-write shunt and the session read-write shunt;

the transaction read-write splitter in the embodiment of the invention is created after a transaction is started to store a preset object of a write statement in the transaction, and the transaction read-write splitter is destroyed after the transaction is ended; the session read-write shunt is created when a session connection is established, is used for storing a preset object which is used by the current session and is only used by the main node, and is destroyed after the session is connected.

As shown in fig. 2, in the embodiment of the present invention, the middleware is disposed between the database cluster and the client, and all SQL statements are subjected to very detailed read-write separation logic processing by the middleware, so that the SQL statements sent by the client can be accurately distributed to the active and standby nodes to the maximum extent, and the overall efficiency of the database cluster is greatly improved.

In specific implementation, before step S101, the embodiment of the present invention further includes: and judging the current transaction state, if the current transaction state is in a transaction, setting a transaction mark, and if the current transaction state is in a non-transaction state, setting a non-transaction mark. The detailed process is shown in detail in fig. 3.

The invention analyzes and uses the SQL statement in the follow-up by setting the transaction mark and the non-transaction mark.

That is, for a postgres database cluster, partial statements such as partial SET statements and transaction control statements need to be sent to the active and standby database nodes at the same time. If only read or write is distinguished, some settings of the main and standby databases are easy to be inconsistent. In the embodiment of the invention, SQL sentences are not simply divided into select sentences and non-select sentences, but each SQL sentence is analyzed, and specific read-write separation judgment is carried out by referring to the read-write attribute of the SQL and the transaction read-write splitter and the session read-write splitter. Therefore, the method and the system can accurately distribute the SQL sentences sent by the client to the main and standby nodes, and finally greatly improve the user experience.

In specific implementation, step S104 in the embodiment of the present invention specifically includes:

and recording the preset object to the transaction read-write splitter and marking the route to the main node for the preset object in the transaction, if the preset object is not the transaction, only recording the preset object into the cache, and recording the temporary preset object into the session read-write splitter and marking the route to the main node.

It should be noted that the preset objects described in the embodiments of the present invention are tables, views, triggers, and the like, and those skilled in the art can set the preset objects according to actual needs, and the present invention is not limited to this.

For read-write separation, the embodiment of the invention firstly analyzes each SQL and judges the read-write attribute of the current SQL. And then further reading and writing judgment is carried out according to the actual conditions such as transaction, non-transaction, session reading and writing shunt, transaction reading and writing shunt and the like.

Specifically, the embodiment of the invention only marks the SQL route to the main node when the SQL statement carries the writing type SQL statement of the transaction identifier and the SQL statement has no influence on the subsequent reading statement; if the SQL statement of the write type carrying the transaction identifier is uncertain in the influence range of the SQL statement, marking that the current SQL statement and all subsequent SQL statements in the transaction are sent to the main node until the transaction is ended; and for the SQL statement carrying the write type of the transaction identifier and capable of identifying all objects in the SQL statement, recording the identified objects and the objects related to the identified objects to the corresponding transaction read-write flow divider, and marking to route the SQL statement to the main node.

As shown in fig. 4, in specific implementation, the embodiment of the present invention first determines whether the current SQL should be sent to the active/standby nodes at the same time (for example, part of the SET type SQL and the transaction control statement need to be sent to the active/standby nodes). If yes, the SQL is directly sent to the master node and the standby node at the same time;

if the judgment result is a read statement and the current transaction is in the transaction, whether the transaction read-write splitter and the session read-write splitter have the SQL object needs to be judged, if yes, the label is sent to the main node, and if not, the label is sent to the standby node.

If the statement is judged to be written, the following processing needs to be executed:

if the transaction is in the transaction, recording the object to the transaction read-write splitter and marking and routing the object to the main node; if the transaction is not in transaction, only recording the transaction into a cache;

if the current session is established, a temporary table, a temporary view and the like are created, the object of the current session is recorded into the session read-write splitter, and the route is marked to the main node;

if the transaction is a write statement and has no influence on subsequent read statements, only marking SQL routing to the main node;

if the transaction is a write statement and the influence range is temporarily uncertain, marking that the current SQL and all subsequent SQL in the transaction are sent to the main node until the transaction is ended;

if the transaction is a write statement and all objects in SQL can be identified, the object and its associated objects (table, view, etc.) are recorded in the transaction read-write splitter, and SQL routing is marked to the master node.

Therefore, the invention analyzes and records the object information of the write SQL modified related table, view and the like in the transaction, can judge the inheritance table and view influenced by the object and the object influenced by the trigger and the like, stores the object in the transaction read-write shunt and provides the read-write basis for the subsequent SQL.

The method of the embodiment of the invention also comprises the following steps: and recording the SQL sentences with the influence relations for the SQL sentences with the indirect mutual influence relations of the modification of the preset object, so as to provide a basis for sending the indirectly influenced SQL sentences to the main and standby nodes.

Specifically, the embodiment of the present invention has an indirect interaction relationship for modifying a table, such as inheriting objects such as tables, views, and triggers, and needs to record the dependency relationship therebetween. Therefore, the method provides a basis for sending the indirectly influenced SQL sentences to the main and standby nodes.

For the inheritance tables, the embodiment of the invention acquires the mutual relation of all the inheritance tables during starting, saves the relation by using the data structure of key: value, and can search the indirectly modified tables from the data structure of the inheritance tables when certain SQL is analyzed to be the modified table value. When the inheritance table is created through the middleware, the relation between the child table and the parent table (including the parent table depended by the parent table) is analyzed and stored.

For the views and the triggers, the processing mode similar to that of the inheritance table is adopted for the same views and the triggers, and the dependency relationships of the views and the triggers and the tables are saved in a key-value format. When the table is modified, the affected view and table can be retrieved from the dependency relationship in time, so that a basis is provided for accurately judging the subsequent affected SQL statement.

Due to the existence of data delay between the main database and the standby database and short time. In this short time, the information is inquired at the standby node, and an error result may be inquired, so the invention adopts a time delay processing mechanism to judge the SQL main/standby.

The method of the embodiment of the invention also comprises the following steps: and after the SQL statement modifies the table, processing the SQL statements in all subsequent sessions by adopting a time delay processing mechanism based on data delay between the main node database and the standby node data. The delay time in the delay processing mechanism is the sum of the stream copying delay time obtained from the database and the stream copying delay time of the configuration file.

When the SQL statement is sent to the standby node, a time delay processing mechanism mode is entered, namely, whether an object in the SQL is in a time delay list is checked, if the object exists, the SQL is sent to the main node, and if the object does not exist, the SQL is sent to the standby node.

Specifically, the latency processing mechanism of the embodiment of the present invention includes:

and acquiring the minimum stream copying delay time from the configuration file during starting, and acquiring the stream copying delay time from the database at regular time.

The stream replication delay time is calculated as the stream replication delay time obtained from the database plus the profile time.

When SQL is sent to the standby node, a time delay processing mechanism mode is entered, namely, whether the SQL object is in a time delay list is checked, if yes, the SQL object is sent to the main node, and if not, the SQL object is sent to the standby node.

The invention further improves the user experience to a certain extent by adopting a time delay processing mechanism to process the problem of inconsistent data in a short time caused by the flow replication between the main and standby.

As shown in fig. 5, in specific implementation, after successfully executing SQL statements related to other objects, such as a create user, an inheritance table, a view, a trigger, and the like in a non-transaction mode, the embodiment of the present invention stores the objects in corresponding files, and if the SQL statements fail, directly clears the cache; and after the whole transaction in the transaction mode is successfully executed, storing the object in the cache into the corresponding file, and clearing the cache if the transaction fails or rolls back.

Generally speaking, the invention specifically judges SQL is sent to a main node or a standby node by setting a transaction read-write shunt and a session read-write shunt, and for the problem of data inconsistency between the main node and the standby node in a short time, a time delay mechanism is adopted for processing, a temporary table, a temporary view, a temporary time domain and the like are judged and recorded, object information such as a related table, a view and the like modified by writing SQL in a transaction is analyzed and recorded, an inherited table and a view influenced by the object and an object influenced by a trigger can be judged, the object is stored in the transaction read-write shunt, a read-write basis is provided for subsequent SQL, SQL statements sent by a client can be accurately distributed to the main node and the standby node to the utmost extent by the processing, and the overall efficiency of a cluster is greatly improved.

The method according to the invention will be explained and illustrated in detail below by means of a specific example in connection with fig. 3-5:

the problem to be solved by the read-write separation in the embodiment of the invention is as follows: common read-write separation needs to be considered in the read-write separation process, namely, a DML type statement needs to send a write type statement to a master, a read type statement to a slave, and other DDL type statements, transaction control statements and the like need to be judged to be sent to the master or the slave node or even to be sent to the master and the slave simultaneously according to actual conditions; considering that each SQL statement needs to be parsed and processed whether or not in a transaction, SQL cannot be simply distinguished into select and non-select statement processing. Because partial SQL requires all nodes to send simultaneously; because the master-slave consistency problem may cause errors in data query, the influence of each SQL on other sessions needs to be considered; secondly, the related SQL such as a temporary table, a temporary view, a temporary domain and the like is considered to be sent to the main node only; considering the influence of using DDL statements, transaction control statements, and write statements in a transaction on SQL following the statement in the transaction; considering the influence of the modification of one table on other tables or views in the transaction, so that after considering the modification of one table, all the tables and views influenced by the modification should be sent to the master node; when a modification is made to a table, the table may cause other associated tables to be modified, resulting in a query error.

In order to solve at least one of the above problems, the invention adopts the following scheme:

the method comprises the following steps: judging the current transaction state, if the current transaction state is in the transaction, setting a transaction mark, and then entering a transaction processing mode; setting a non-transaction flag in a non-transaction, and entering a non-transaction processing mode, as specifically shown in fig. 3;

step two: for read-write separation, each SQL needs to be analyzed first, and the read-write attribute of the current SQL is determined, and then further read-write determination is performed according to the actual conditions such as transaction, non-transaction, session read-write shunt, transaction read-write shunt, and the like, as shown in fig. 4 specifically;

first, it is determined whether the current SQL should be sent to the active/standby nodes at the same time (for example, part of the SET type SQL and the transaction control statement needs to be sent to the active/standby nodes). If yes, the SQL is directly sent to the master node and the standby node at the same time;

if the judgment result is a read statement and the current transaction is in the transaction, judging whether the transaction read-write splitter and the session read-write splitter have the SQL object, if so, marking to send to the main node, otherwise, sending to the standby node;

if the statement is judged to be written, detailed processing of the following steps is required;

if the transaction is in the transaction, recording the object to the transaction read-write splitter and marking and routing the object to the main node; if the transaction is not in transaction, only counting in the cache;

if the transaction is a write statement and all objects in SQL can be identified, the objects and the associated objects (tables, views and the like) are required to be recorded into a transaction read-write flow divider, and SQL routing is marked to a main node;

step three: for the modification of the table, there are indirect mutual influence relations, such as objects of inheritance tables, views, triggers, etc., and we need to record their mutual dependency relations. Therefore, the method provides a basis for sending the indirectly influenced SQL sentences to the main and standby nodes.

Inheritance table: when the SQL is analyzed to be a modification table value, the indirectly modified table can be searched from the data structure of the inheritance table. When the inheritance table is created through the middleware, the relation between the child table and the parent table (including the parent table depended by the parent table) is analyzed and stored.

View, trigger: similarly, the view and the trigger adopt a similar processing mode as the inheritance table, and the dependency relationship of the view, the trigger and the table is saved in a key: value format. When the table is modified, the affected view and table can be retrieved from the dependency relationship in time, so that a basis is provided for accurately judging the subsequent affected SQL statement.

Step four: processing the influence on SQL sentences in all subsequent sessions within a certain time after the SQL modification table is processed by adopting a time delay mechanism;

due to the existence of data delay between the main database and the standby database and short time. In this short time, the information is queried at the standby node, and an erroneous result may be queried, so that the primary and standby SQL nodes need to be determined by adopting a delay processing mechanism.

The time delay processing mechanism comprises: and acquiring the minimum stream copying delay time from the configuration file during starting, and acquiring the stream copying delay time from the database at regular time. The stream replication delay time is calculated as the stream replication delay time obtained from the database plus the profile time.

When SQL is sent to the standby node, a time delay processing mechanism mode is entered, namely, the SQL object is checked whether to be in a time delay list, if yes, the SQL is sent to the main node, and if not, the SQL is sent to the standby node;

step five: in the non-transaction mode, if SQL statements such as a user, an inheritance table, a view, a trigger and the like are successfully created and executed, the object is stored in a corresponding file, and if the SQL statements fail, the cache is directly emptied; in the transaction mode, when the whole transaction is successfully executed, the object to be cached is stored in the corresponding file, and when the transaction fails or rolls back, the cache is cleared, as shown in fig. 5.

Compared with the existing read-write separation scheme, the embodiment of the invention adopts more detailed and reasonable read-write separation processing logic, so that whether in a transaction or a non-transaction, the read-write separation module can very accurately judge whether the specified SQL statement is read, and can finely judge whether the table information inquired by the read statement is influenced by the previous SQL statement, thereby determining which node the SQL is sent to. Through the processing of the read-write separation module, the pressure of the standby node in the database cluster can be obviously improved, and the pressure of the main node is reduced, so that the overall efficiency of the database cluster is obviously improved.

Although the preferred embodiments of the present invention have been disclosed for illustrative purposes, those skilled in the art will appreciate that various modifications, additions and substitutions are possible, and the scope of the invention should not be limited to the embodiments described above.

Claims

1. A method for performing read-write separation on a database cluster is characterized by comprising the following steps:

judging whether the current SQL statement is sent to a main node and a standby node at the same time, if so, sending the SQL statement to the main node and the standby node, and if not, further judging whether the SQL statement is a read type SQL statement or a write type SQL statement;

the transaction read-write splitter is created after starting a transaction to store a preset object of a write statement in the transaction, and the transaction read-write splitter is destroyed after the transaction is finished; the session read-write shunt is created when a session connection is established, is used for storing a preset object which is used by the current session and is only used by the main node, and is destroyed after the session is connected.

2. The method of claim 1, wherein before determining whether the current SQL statement is sent to the primary node and the backup node at the same time, the method further comprises:

3. The method according to claim 1, wherein the determining whether the SQL statement affects the subsequent SQL statement and recording the object in the SQL statement that affects the subsequent SQL statement to the transaction read-write splitter or the session read-write splitter comprises:

4. The method according to claim 2, wherein the determining whether the SQL statement affects the subsequent SQL statement and recording the object in the SQL statement that affects the subsequent SQL statement to the transaction read-write splitter or the session read-write splitter comprises:

5. The method of claim 1, further comprising:

and recording the SQL sentences with the influence relations for the SQL sentences with the indirect mutual influence relations of the modification of the preset object, so as to provide a basis for sending the indirectly influenced SQL sentences to the main and standby nodes.

6. The method according to any one of claims 1-5, further comprising:

and after the SQL statement modifies the table, processing the SQL statements in all subsequent sessions by adopting a time delay processing mechanism based on data delay between the main node database and the standby node data.

7. The method of claim 6,

8. The method of claim 6,

the delay time in the delay processing mechanism is the sum of the stream replication delay time obtained from the database and the stream replication delay time of the configuration file.

9. The method according to any one of claims 1 to 5,

after SQL statements related to other objects, such as a created user, an inheritance table, a view, a trigger and the like in a non-transaction mode are successfully executed, the objects are stored in corresponding files, and if the objects fail, the cache is directly emptied;

10. The method according to any one of claims 1 to 5,

the preset objects include tables, views, and triggers.