JP2012078993A - Method and system to scale up and scale down relational database - Google Patents

Abstract

PROBLEM TO BE SOLVED: To realize scale-up/down of an RDB run as an instance on IaaS without stopping Web service, by dynamically performing replacement of the instance at low cost.SOLUTION: This invention comprises: first means to store a copy of an SQL statement performing data update; means to determine whether the copy of the SQL statement performing data update is stored or not; means to send the copy of the SQL statement performing data update to a second relational database different from the relational database of a replacement source of an instance, reflect it and synchronize data stored in the first and second relational databases based on the determination result; and means to change the instance run by the relational database of the replacement source of the instance into an instance having different performance after synchronization establishment of the data in the both relational databases and change an instance run by a relational database of a replacement destination into a different instance.

Description

  In the present invention, a relational database (hereinafter referred to as RDB) operating on a virtual machine instance provided by IaaS (Infrastructure as a Service) or the like is dynamically used by using a proxy that relays an SQL query without stopping the system. In particular, the present invention relates to a technique for realizing scale-up and scale-down.

Currently, there are many IaaSs that provide virtual machine instances. Among them, like EC2 of Amazon Web Services (hereinafter abbreviated as “AWS”), IaaS that can use various performance instances with hourly billing is a computing resource that matches the increase or decrease of the load accompanying the increase or decrease of service users. Since it is possible to scale the usage cost of the Internet, it is being used in many Web services.
Such a web service is generally composed of a web application and a database, but for web applications, a scale OUT / IN method that dynamically increases or decreases instances according to the load is generally used. Yes. The scale OUT / IN function is already provided by the IaaS vendor itself or a third party.

As for the database, several methods for changing the processing performance according to the load have been proposed. As a method for realizing the scale OUT / IN for changing the processing performance by increasing or decreasing the number of instances, there is a database classified as NoSQL represented by Non-Patent Document 1. In addition, although update performance has been a problem in the conventional RDB cluster technology, cluster technologies that can improve the update performance linearly according to the number of devices have appeared, such as Non-Patent Document 2 and Non-Patent Document 3. .

However, these methods are inferior to the performance of one unit unless the same function as the conventional RDB is provided, the same interface as the conventional RDB is not provided, or when three or more units are not always operated. There are various problems. Also, a method for dynamically increasing or decreasing the number has not been established.
On the other hand, there is also a method for realizing scale UP / DOWN in which an instance (= virtual machine) in which RDB operates is replaced with an instance having different performance to change the performance according to the load. For example, using pgpool-II's replication and online recovery functions, it is possible to swap instances. However, even during online recovery, it is necessary to temporarily stop the RDB connection, and the service stops during that time.
If Patent Document 1 is used, it is possible to replace the instance without stopping, but the RDBMS itself needs to have a function of performing the processing shown in Patent Document 1. For this reason, it is difficult to easily implement this method in an existing RDBMS.
Further, Patent Document 1 assumes that a server failure occurs in a scene where there is little change in the machine environment in an actual machine environment, or that a slave server is added. When changing the processing performance of the RDB according to the load on IaaS, the instances must be replaced every few hours or every day. In the method of Patent Document 1, the data transfer amount and the processing time for copying and the like are long, and the data transfer amount and replacement cost increase. As a result, the time required for replacement increases, and the cost of IaaS charged by the data transfer amount and the instance startup time increases. Therefore, in such a case, the method of Patent Document 1 is not suitable.

JP 2009-252149 A

CouchDB http://oss.infoscience.co.jp/couchdb/main/docs/overview.html Postgres-XC http://postgres-xc.sourceforge.net/ VoltDB http://voltdb.com/content/voltdb-oltp-database-youll-never-outgrow

  The present invention has been made in view of the above problems, and its purpose is to change the scale of an RDB that operates on an instance on IaaS dynamically and at low cost without stopping the Web service. It is to provide a method and a system which are realized by performing.

  In order to achieve the above object, the present invention stores a relay means for selectively relaying transmission and reception of SQL statements to the first and second relational databases, and a first copy for storing SQL statements for updating data. And a means for determining whether or not a copy of the SQL statement that performs data update is stored, and a copy of the SQL statement that performs data update based on the determination result is different from the relational database from which the instance is replaced. Means for synchronizing the data stored in the first and second relational databases, and the relational database from which the instance is replaced after the synchronization of the data of both relational databases is established. Change running instances to instances with different performance Together with further be characterized by comprising a means for changing the instance relational database exchange destination was busy at different instances.

  In addition, the system according to the present invention includes a relay unit that selectively relays transmission and reception of SQL statements to the first and second relational databases, a first unit that stores a copy of the SQL statement that performs data update, A means for determining whether or not a copy of the SQL statement for performing data update is stored, and a second copy that is different from the relational database from which the instance is replaced by replacing the copy of the SQL statement for performing data update based on the determination result A means for synchronizing the data stored in the first and second relational databases by transmitting to the relational database and reflecting it, and the relational database from which the instance was replaced was in operation after the synchronization of the data in both relational databases was established Change an instance to an instance with a different performance Together, characterized in that it comprises a means for changing the instance relational database exchange destination was busy at different instances.

  According to the present invention, it is possible to scale up / down an RDB used in a web service or the like dynamically without stopping the service, in a short time and at a low cost.

It is a system configuration figure showing an embodiment concerning the present invention. It is the system block diagram before replacement, and the flow figure of the SQL sentence. It is a flowchart which shows the outline | summary of the process of a replacement process. It is a flowchart which shows replacement | exchange preparation processing. It is a system configuration figure after exchange preparation processing, and a flow chart of a SQL sentence. It is a flowchart which shows DB switching processing. It is a SQL process flow figure of the control proxy part 122 in DB switching process. It is a system block diagram during DB switching processing and a flow diagram (1) of an SQL sentence. It is a system block diagram during DB switching processing and a flow diagram (2) of the SQL sentence. It is a processing flow figure of processing after exchange.

Hereinafter, an embodiment for carrying out the present invention will be specifically described with reference to the drawings.
FIG. 1 shows a configuration diagram of the system of the present invention.
This system includes an instance group using a virtual machine service on IaaS 100 and a storage group using a storage service.
On the IaaS 100, a Web application instance 110, a control proxy instance 120, a DBMS instance A140 constituting the DB system (A) 130, a storage (A) 150 for storing data, and a DB system (B) 160 are constituted. A DBMS instance B 170 to be stored and a storage (B) 180 for storing data are arranged.

In the web application instance 110, a web application 111 and a series of programs for operating the web application 111 are arranged. The Web application 111 issues an SQL statement to the control proxy unit 122 on the control proxy instance 120 described later instead of the RDBMS, and receives the result.
In the control proxy instance 120, a control unit 121, a control proxy unit 122, an RDBMS 123, and an SQL statement store X124 are arranged. The control unit 121 performs control such as start / stop of an instance or start / stop of an RDBMS on an instance at the time of instance replacement through an interface such as an API provided by the IaaS 100.

The control proxy unit 122 issues the SQL sentence received from the Web application 111 to the DB system (A) 130 or the DB system (B) 160 described later instead of the Web application 111, and receives the result to the Web application 111. Returns or determines an SQL sentence that includes an update from the SQL sentence received from the Web application 111 (hereinafter abbreviated as an update-type SQL sentence), and stores it in an appropriate one of various SQL stores to be described later. To do.
The RDBMS 123 reflects the accumulated update SQL statement in appropriate DB data when the processing load of the control proxy instance 120 is sufficient. The SQL statement store X124 is a repository for accumulating update SQL statements during instance replacement processing.

The DB system (A) 130 and the DB system (B) 160 are DB systems to be replaced and replaced. Both have the same configuration, and RDBMS- (A) 141 and RDBMS- (B) 171 which are general RDBMSs are arranged in the DBMS instance (A) 140 and the DBMS instance (B) 170, respectively. ing.
Further, the storage (A) 150 and the storage (B) 180 are stored in the SQL statement store Z151 and the SQL statement store Y181 that store update SQL statements before and after the replacement process, and RDBMS- (A) 141 and RDBMS-. (B) DB data (A) 152 and DB data (B) 182 used by 171 are arranged.

In the following description, it is assumed that the replacement source used at the start of replacement is the DB system (A) 130, and the replacement destination used at the end of replacement is the DB system (B) 160.
Here, before explaining the replacement process, the state before the start of the replacement will be described with reference to FIG.
In the state before the start of replacement, the DB system (A) 130 is used, and the DBMS instance (B) 170 is not operating. However, if the SQL statement store Y181 is reflected in the DB data (B) 182 in the storage (B) 180, the DB data (such as the DB data (A) 152 in the storage (A) 150 becomes the same data ( B) Assume that 182 and the SQL statement store Y181 exist in advance. These can be realized by using an existing DB data copy technique and accumulation of SQL sentences described below.

In the state before the start of replacement, the SQL statement issued by the Web application 111 is sent to the control proxy unit 122 of the control proxy instance 120 as indicated by the black arrow 201. The control proxy unit 122 sends all SQL statements to the RDBMS- (A) 141 of the DBMS instance (A) 140 as indicated by the black arrow 202.
The RDBMS- (A) 141 processes the SQL sentence using the DB data (A) 152 of the storage (A) 150 and returns the result to the Web application 111 via the control proxy unit 122.

In the state before the start of replacement, the control proxy instance 120 is also connected to the storage (B) 180, and the control proxy unit 122 updates the update SQL statement among the SQL statements received from the Web application 111. Is stored in the SQL statement store Y181 in the storage (B) 180 as indicated by a white arrow 203.
Furthermore, when the control proxy instance 120 has a sufficient processing capacity, the RDBMS 123 updates the update SQL stored in the SQL statement store Y181 of the storage (B) 180 as indicated by white arrows 204 and 205. The sentence is reflected in the DB data B 182 of the storage (B) 180.
Thus, in the state before the start of replacement, the DB system (A) 130 is used, and only the update SQL statement is accumulated in the SQL statement store Y181 in the storage (B) 180, and the processing of the control proxy instance 120 is performed. The update SQL statement is reflected in the DB data (B) 182 of the storage (B) 180 according to the capacity margin.

Next, an outline flow of the replacement process is shown in FIG.
The replacement process is performed in three phases: a replacement preparation process (step 301), a DB system switching process (step 302), and a post-replacement process (step 303).
Hereinafter, detailed processing will be described in this order.
A detailed processing flow of the replacement preparation process (step 301) is shown in FIG.
First, the control unit 121 on the control proxy instance 120 creates the SQL statement store X140 on the control proxy instance 120, and the control proxy unit 122 of the control proxy instance 120 has previously stored the storage (B). The setting of the control proxy unit 122 is changed so that the update SQL statement stored in the SQL statement store Y181 on 180 is stored in the SQL statement store X124 (step 401).

Next, the control unit 121 disconnects the connection between the control proxy instance 120 and the storage (B) 180 and activates the DBMS instance (B) 170 in a form connected to the storage (B) 180 (steps). 402). Then, the RDBMS- (B) 171 is activated on the DBMS instance (B) 170 with settings to use the DB data (B) 182 on the storage (B) 180 (step 403).
Finally, the control unit 121 causes the activated RDBMS- (B) 171 to reflect the updated SQL statement stored in the SQL statement store Y181 on the storage (B) 180 in the DB data (B) 182 ( Step 404).

The above is the replacement preparation process (step 301). FIG. 5 shows a state where the replacement preparation process is completed.
Compared to FIG. 2, the DB system (B) 180 is changed, and the update SQL statement is accumulated in the SQL statement store X124 as indicated by the white arrow 501, but the DB system (A) 130 side is changed. Since the DB system (A) 130 is still used for the processing of all SQL statements sent by the Web application 111, the Web application 111 can continue the operation including the SQL statement processing.

Next, a detailed process flow of the DB system switching process (step 302) is shown in FIG.
First, the control unit 121 changes the setting of the control proxy unit 122 to process the SQL sentence received from the Web application 111 with the processing flow shown in FIG. 7 (step 601).
Here, the processing flow of FIG. 7 will be described. The control proxy unit 122 that has received the SQL sentence from the Web application 111 sends the SQL sentence to the DB system (A) 130 (step 701). Next, it is determined whether it is an update SQL statement (step 702). If it is determined in step 702 that the statement is not an update SQL statement, the SQL statement processing result is received from the DB system (A) 130 (step 703), and the result is returned to the web application 111 (step 704). ).

If it is determined in step 702 that the SQL statement is an update SQL statement, it is next determined whether or not there is any accumulation of SQL statements in the SQL statement store X124 (step 705). If the SQL sentence is stored in the SQL sentence store X124, the SQL sentence determined to be the update SQL statement in step 702 is stored in the SQL sentence store X124 (step 706). Thereafter, the processing of step 703 and step 704 is performed in the same manner as described above.
If there is no SQL sentence stored in the SQL sentence store X124 in step 705, the SQL sentence determined to be the update SQL statement in step 702 is sent to the DB system (B) 160 (step 707). At this stage, the data is synchronized. Then, the SQL statement processing result is received from the DB system (B) 160 (step 708). Then, the SQL statement processing result is received from the DB system (A) 130 in the same manner as described above (step 703), and the result is returned to the web application 111 (step 704).

At this time, as in the conventional replication technique, the SQL statement processing results received in step 708 and step 703 may be compared to perform appropriate error processing.
In step 601, after setting the setting of the control proxy unit 122 to be the processing flow shown in FIG. 7, the control unit 121 sends an SQL statement to the RDBMS- (B) 171 on the DBMS instance (B) 170. The SQL sentence stored in the sentence store X124 is sent and reflection processing is performed (step 602).
At this time, when the processing result is returned from the RDBMS- (B) 171, the SQL sentence is deleted from the SQL sentence store X124.

  Next, the control unit 121 checks whether or not all the SQL sentences stored in the SQL sentence store X124 have been reflected (step 603). If all the SQL sentences stored in the SQL sentence store X124 are not reflected in the middle of step 603 due to the influence of the process of step 706 of the control proxy unit 122, the reflection process of step 602 is performed again.

  If all the SQL statements stored in the SQL statement store X124 are reflected in step 603, the processing of the control proxy unit 122 does not proceed to step 706 in FIG. 7, and the update-type SQL statement is white in FIG. As indicated by an arrow 801, the data is also copied and sent to the DB system (B) 160 side. Therefore, next, the control unit 121 changes the setting of the control proxy unit 122 and replaces the DB system (A) 130 and the DB system (B) 160 (step 604).

Specifically, the DB system (A) and the DB system (B) in the SQL processing flow shown in FIG. 7 are exchanged so that all SQL statements are processed by the DB system (B) 160, and the update system SQL is changed. Only the sentence is sent to the DB system A130. This state is shown in FIG.
As indicated by the black arrow 201, the control proxy unit 122 receives the SQL sentence from the Web application 111 as before, but as shown by the black arrow 901, all the SQL sentences are transmitted to the DB system (B) 160. Then, the result received from the DB system (B) 160 is returned to the Web application 111.

  Further, the update SQL statement is sent to the DB system (A) 130 and reflected in the DB data (A) 152 on the storage (A) 150 of the DB system (A) 130 as indicated by the white arrow 902. .

Thus, the DB system switching process (step 302) ends.
After the DB system switching process (step 302) is completed, the DB system on which the process is substantially performed is switched from the DB system (A) 130 to the DB system (B) 160. Here, the update SQL statement is processed only by the DB system (A) 130 until step 604, and the subsequent processing is performed by both the DB system (A) 130 and the DB system (B) 160.
In addition, SQL statements other than the update system are processed by the DB system (A) 130 up to step 604 and the DB system (B) 160 thereafter. For this reason, there is no timing at which the SQL sentence is not processed, and the Web application 111 can continue the operation including the SQL sentence processing.

Next, FIG. 10 shows a detailed processing flow of the post-replacement processing (step 303).
First, the control unit 121 changes the setting of the control proxy unit 122, and stores the update SQL statement sent to the DB system (A) 130 in the SQL statement store X124 without sending it to the DB system (A) 130. (Step 1001). As a result, the DB system (A) 130 and the DB system (B) 160 are switched in FIG.
Next, the control unit 121 stops the DBMS instance A140 (step 1002). Since no SQL statement is sent in step 1001, this stop does not cause a problem. Next, the storage A 150 is connected to the control proxy instance 120, the SQL statement store Z 151 is created on the storage A 150, the setting of the control proxy unit 122 is changed, and the update SQL statement is stored in the SQL statement store Z 151. (Step 1003).

Next, the update SQL statement stored in the SQL statement store X124 is merged into the SQL statement store Z151 without changing the order of the SQL statements (step 1004).
Next, when the setting of the RDBMS 123 on the control proxy instance 120 is changed, and the processing load of the control proxy instance 120 is sufficient, the SQL statement store Z151 is stored in the DB data (A ) 152 (step 1005).

  Here, instead of the merge processing in step 1004, the RDBMS 123 on the control proxy instance 120 is activated, and the SQL statement stored in the SQL statement store X 124 is converted into the DB data (A) on the storage (A) 150. 152 may be reflected.

Thus, the post-replacement process (step 303) ends.
Thereby, compared with the state before the start of replacement shown in FIG. 2, the DB system A 130 and the DB system B 160 are completely replaced.

In the present invention, replacement can be performed again, and as shown above, it is not necessary to migrate all DB data. In addition, since the SQL sentence from the Web application 111 is processed by the DB system (A) 130 or the DB system (B) 160 having synchronized data at any time, dynamic replacement can be realized.
As described above, dynamic scale UP and DOWN can be realized by replacing instances of different performances provided by IaaS with the above system.

DESCRIPTION OF SYMBOLS 100 ... IaaS, 110 ... Web application instance, 111 ... Web application, 120 ... Control proxy instance, 121 ... Control part, 122 ... Control proxy part, 123 ... RDBMS, 124 ... SQL sentence store X, 130 ... DB system (A), 140 ... DBMS instance (A), 141 ... RDBMS- (A), 150 ... storage (A), 151 ... SQL statement store Z, 152 ... DB data (A), 160 ... DB system (B) 170 ... DBMS instance (B), 171 ... RSBMS- (B), 180 ... storage (B), 181 ... SQL statement store Y, 182 ... DB data (B)

Claims (2)

A method for scaling up and down an instance in a system with virtual machines on which first and second relational databases run, comprising:
The system is
A relay unit that selectively relays transmission / reception of SQL statements to / from the first and second relational databases, a first step of storing a copy of the SQL statement that updates data, and a copy of the SQL statement that updates data A second step for determining whether or not the data is stored, and a copy of the SQL statement for updating data based on the determination result is sent to a second relational database different from the relational database from which the instance is replaced The third step of reflecting and synchronizing the data stored in the first and second relational databases is different from the instance in which the relational database from which the instance was replaced was operating after the synchronization of the data of both relational databases was established. When changing to a performance instance, , How to scale up and scale down a relational database, characterized in that it comprises a fourth step of changing the instance relational database exchange destination was busy at different instances.   There are relay means for selectively relaying transmission and reception of SQL statements to and from the first and second relational databases, first means for storing a copy of the SQL sentence for updating data, and a copy of the SQL sentence for updating data. A means for determining whether or not the data is stored, and a copy of the SQL statement for performing data update based on the determination result is transmitted to the second relational database different from the relational database from which the instance is replaced, and reflected; The means for synchronizing the data stored in the first and second relational databases and the instance in which the relational database from which the instance was replaced was established after the synchronization of the data in the two relational databases was established are changed to instances of different performance. Together with the destination relay System for scale up and scale down a relational database, characterized in that it comprises a means for changing the instance that the database was busy at different instances.

Images