Routing requests in shared memory database systems
By partitioning the database table into an affinity group and generating an affinity group mapping, route querying to the target node, solving the performance and scalability problems caused by inter-node lock requests and data access in the cluster shared disk database system, achieving efficient Data access and cache management improve system performance and stability.
Patent Information
- Application Number
- CN201880061838.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Priority Date
- 2017-12-18
- Filing Date
- 2018-08-21
- Publication Date
- 2024-12-06
- Estimated Expiration
- 2038-08-21
AI Technical Summary
In cluster shared disk database systems, lock requests and data access between nodes lead to performance and scalability problems, and the prior art is difficult to effectively manage cache coherence and data routing, resulting in performance degradation and availability challenges.
By partitioning the database table into multiple affinity groups and assigning each affinity group to a specific node, affinity group mapping is generated to route query to the target node, avoiding lock requests and data replication between nodes, and achieving efficient data Access and cache management.
It improves the performance and scalability of the database system, reduces communication delay and lock waiting time between nodes, and enhances the stability and responsiveness of the system.
Smart Images

Figure CN111164587B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to database management systems, and in particular to routing requests for shared storage database systems. Background Art
[0002] Database systems typically store database objects (e.g., tables, indexes, etc.) on disk and load data items from these database objects into volatile memory as needed. Once loaded into volatile memory, data items can remain cached in volatile memory so that subsequent accesses to the same data item do not incur the overhead of accessing the data item from disk.
[0003] A clustered shared disk database system consists of multiple nodes. If several nodes access the same data items, these data items can be cached in the volatile memory of each node. However, lock and update mechanisms are required to maintain cache consistency between different nodes. In addition, performing lock requests between nodes and fetching data items from the volatile memory of other nodes can negatively affect the performance and scalability of the database system.
[0004] One possible approach is to load a copy of the database object into volatile memory on a single node in the cluster. To gain the benefit of having the object preloaded, work related to the object can be shipped to the node that preloaded the object. Alternatively, data can be sent from the node that preloaded the object to the node that receives the work. Although shipping the work or receiving the data may be faster than accessing the object on disk, shipping the work or requesting the data still requires additional time.
[0005] Another possible approach is to partition the database data and store it in separate databases. Each database is assigned to a specific node. This avoids having to maintain cache coherence between nodes, but nodes do not directly access data in databases to which they are not assigned. Therefore, if the work requested by one database requires data items stored in other databases, it will take longer for that database to complete the work.
[0006] A third possible approach is to hardcode the database application to route requests for specific database data to specific nodes so that each node handles a disjoint subset of the data. However, to continue to maintain performance, if access patterns change, the size of the data set changes, or the number of available database instances changes, the application must be updated with the new data-to-node mapping.
[0007] The approaches described in this section are approaches that could be pursued, but are not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section. BRIEF DESCRIPTION OF THE DRAWINGS
[0008] In the attached picture:
[0009] Figure 1 is a block diagram illustrating a database system in which embodiments may be implemented.
[0010] Figure 2 is a block diagram illustrating database objects divided into affinity groups according to an embodiment;
[0011] Figure 3 is a block diagram illustrating affinity group mapping according to an embodiment;
[0012] Figure 4 is a flow chart illustrating steps for determining how to route a request according to an embodiment;
[0013] Figure 5 is a block diagram illustrating a computer system that can be used to implement the techniques described herein. DETAILED DESCRIPTION
[0014] In the following description, for the purpose of explanation, many specific details are set forth in order to provide a thorough understanding of the present invention. However, it will be apparent that the present invention can be practiced without these specific details. In other cases, well-known structures and devices are shown in block diagram form to avoid unnecessary obscurity of the present invention.
[0015] Overview
[0016] Techniques for routing queries to specific nodes of a multi-node database system based on the query are described herein. According to one embodiment, a database table is partitioned into a plurality of affinity groups. Each affinity group is assigned to a specific node of the multi-node database system.
[0017] A specific node is the master node for an affinity group. The master node handles lock requests for the affinity group. If a node requires a lock for data in an affinity group, the node sends a lock request to the master node. However, if the master node requires a lock for an affinity group, the master node does not need to send a request.
[0018] According to one embodiment, a database system generates an affinity group map that indicates the node assignments to which the affinity groups are assigned. The database system sends the affinity group map to a query source that stores the map locally. Before the database query source sends a query to the database system, the query source uses the map to determine a target node in the database system to which the query is to be routed. The query source then sends the query to the target node. Examples are described herein where the query source is a client application executing on a client node. However, the query source can be any source from which the database system receives queries.
[0019] In some embodiments, the query router determines a target node to which to route the query. The query router may be, for example, a service, function, application, or other feature of a client application, a connection pool, a database listener, a software or hardware router, or a network switch. In one embodiment, the query router receives a database request, determines a target node, and forwards the database request to the target node. Alternatively, the query router may indicate the target node to a query source (such as a client application).
[0020] System Overview
[0021] Figure 1 FIG. 1 is a block diagram showing a multi-node database system according to one embodiment. Figure 1 In the illustrated embodiment, database system 100 includes four nodes 102 , 112 , 122 , and 132 .
[0022] Nodes 102, 112, 122, and 132 have one or more processors 108, 118, 128, and 138, respectively, and local volatile memory 104, 114, 124, and 134. In addition, nodes 102, 112, 122, and 132 are executing database server instances 106, 116, 126, and 136, respectively. Although in the illustrated embodiment, each node is executing a single database server instance, in alternative embodiments, a single node may execute more than one database server instance.
[0023] Nodes 102, 112, 122, and 132 can access the same database 160. For illustrative purposes, database 160 is shown as being stored on a single shared disk 150, but in alternative embodiments, database 160 can be distributed across multiple disks, and each of nodes 102, 112, 122, and 132 can access the multiple disks. In addition, database 160 can be stored in other types of computer-readable storage media (such as flash memory or non-volatile memory). Database 160 includes table 170. Each of nodes 102, 112, 122, and 132 can access data items from table 170 from a copy of table 170 residing on disk 150.
[0024] In some embodiments, database 160 is a distributed database including multiple databases, each of which is stored in one or more corresponding storage media. Each node can access a specific database in the multiple databases, rather than sharing access to the multiple databases. In other embodiments, nodes can share access to one or more databases in the multiple databases. For example, a first group of nodes can share access to a first database in the multiple databases, and a second group of nodes can access a second database in the multiple databases.
[0025] A query router 180 is connected to the database system 100. A query router can be any hardware or software that receives a query and determines a target node to which the query is to be routed. For example, a query router can be a service, function, application, or other feature such as a client application, a connection pool, a database listener, a software or hardware router, or a network switch. In one embodiment, a query router receives a database request, determines a target node, and forwards the database request to the target node. Alternatively, the query router can indicate the target node to a query source (such as a client application).
[0026] In one embodiment, query router 180 is a database client connected to database system 100. Query router 180 may include a database application running on a client node. Query router 180 interacts with instances of database system 100 (such as instances 106, 116, 126, and 136) by submitting commands that cause the instances to perform operations on data stored in the database. For example, a command may be a request to access or modify data from a database, perform operations on the data, and / or return data to a client.
[0027] In addition, multiple commands can be sent from a database client to a database instance in a single request to perform work. The database instance can return results to the database client in a single response to all commands submitted in the request. As an example, as described in more detail below, a database client can send a request that includes a specific query and a request for affinity group mappings. Processing multiple commands in a single round-trip request and response can result in more efficient use of database connections.
[0028] Affinity Group
[0029] In order to assign portions of a database object to a particular node, a database object, such as table 170, is partitioned into multiple affinity groups. An affinity group is a portion of a database object that is assigned to a node for the purpose of routing points and / or range queries.
[0030] In one embodiment, the database table is partitioned into affinity groups based on data values in the database table. For example, each affinity group may correspond to one or more values or ranges of values in a column of the database table.
[0031] For the purpose of illustrating a clear example, assume that a "Customers" database table stores customer data and includes a "lastName" column. The "Customers" table can be partitioned into affinity groups based on the values stored in this "lastName" column. The first affinity group can correspond to last name values that begin with the letters A through D, the second affinity group can correspond to last name values that begin with the letters E through H, the third affinity group can correspond to last name values that begin with the letters I through K, and so on.
[0032] The key column (or columns) by which a database table is partitioned is referred to herein as the “affinity group key.” Each affinity group corresponds to a specific value, a specific group of values, or a specific range of values of the affinity group key.
[0033] The partitions used for affinity group purposes may or may not correspond to the on-disk structure of the database data. In the example where the affinity group partitions are based on on-disk partitions, the database table may be stored in multiple data blocks. The database table may be partitioned based on data blocks so that each affinity group corresponds to one or more of the multiple data blocks. Each affinity group may still correspond to a range of values for the affinity group key.
[0034] In one embodiment, each affinity group may correspond to an equal or nearly equal number of data chunks. However, the ranges of affinity group key values may be different for each affinity group based on the amount of data associated with each range. Referring to the example above, a first affinity group may correspond to last name values beginning with the letters A through C, a second affinity group may correspond to last name values beginning with the letters D through K, and each affinity group corresponds to a similar number of data chunks.
[0035] A data block is a unit of permanent storage and is used by the database server to store one or more database rows or portions of rows, including one or more columns of a row. When a row is read from permanent storage, the data block containing the row is copied to a data block buffer in the database server's RAM and / or main memory. A data block typically contains multiple rows, as well as data block metadata that describes the contents of the data block.
[0036] A data block is the smallest unit of database data that a database server can request from a permanent storage device. For example, when a database server looks up a row stored in a data block, the database server may only be able to read the row from the permanent storage device by reading the entire data block. Partitioning a database table based at least in part on the data blocks in which the database table is stored avoids assigning different nodes to the same data block.
[0037] In one embodiment, the database table may be a partitioned database table including a plurality of table partitions. A plurality of affinity groups may correspond to a plurality of partitions. An affinity group key may be a partition key used to partition the database table. For example, each table partition may be stored in a corresponding group of one or more data blocks. All data blocks in a group may belong to the same affinity group and / or to an affinity group assigned to the same node.
[0038] Each of the plurality of affinity groups is assigned to a particular node in the database system. The node to which the affinity group is assigned is the master node for the affinity group. The master node manages lock requests for the affinity group. If a node receives a request for data stored in an affinity group for which the node is the master, the node does not have to request a lock from another node. This allows the node to process requests more quickly.
[0039] Affinity groups may be assigned to nodes in a database system using any method for associating portions of a database object with a particular node. Example methods include assigning in a round-robin fashion; partitioning multiple affinity groups into equal or semi-equal groups and assigning each group to a particular node; using a function to map affinity group key values to nodes; and the like.
[0040] Figure 2 Example affinity groups of table 170 are shown. Table 170 is divided into eight affinity groups (affinity groups 202-216). In the example shown, affinity groups 202 and 204 are assigned to node 102, affinity groups 206 and 208 are assigned to node 112, affinity groups 210 and 212 are assigned to node 122, and affinity groups 214 and 216 are assigned to node 132.
[0041] Each node is the master node of the affinity group to which it is assigned. As an example, if node 102 receives a request for data in affinity group 202, node 102 may grant itself a lock on the corresponding data block. If node 102 receives a request for data in affinity group 208, node 102 requests a lock from node 112 and waits for the lock to be granted before accessing the data.
[0042] If a node is deleted from the database system, each affinity group that had been assigned to the failed node is reassigned to one of the remaining nodes in the database system. The node may be temporarily deleted from the database system (such as for maintenance purposes) or permanently deleted due to a node failure.
[0043] Redistribution can be performed similarly to the initial allocation process described above. In one embodiment, the reallocation does not affect affinity groups whose nodes have not failed. As an example, if node 112 fails, affinity groups 206 and 208 will each be reallocated to one of nodes 102, 122, or 132. However, affinity groups 202, 204, 210, 212, 214, and 216 will not be reallocated because the nodes they are allocated to have not failed. In other embodiments, the node allocations of all affinity groups are re-determined, for example, to maintain a uniform distribution of the allocations of affinity groups to nodes.
[0044] Similarly, if a node joins or is re-added to the database system, one or more affinity groups may be selected to be reassigned to the new node. If there are fewer affinity groups than nodes, no affinity groups are reassigned to the new node. In one embodiment, only selected affinity groups are reassigned. In other embodiments, node assignments for all affinity groups are re-determined.
[0045] Affinity Group Mapping
[0046] After assigning affinity groups to nodes, the database system generates an affinity group map. The affinity group map includes metadata indicating the primary node of each affinity group. The affinity group map is sent to database clients connected to the database system. Each database client stores a corresponding copy of the affinity group map. In one embodiment, if multiple database objects are divided into affinity groups, a corresponding affinity group map is stored for each database object. Additional metadata may be stored indicating the corresponding affinity group map for each database object.
[0047] Figure 3 An example affinity group map is shown of table 170. In the example shown, the affinity group map includes affinity group key data 310 indicating the affinity group keys upon which the affinity groups are based and table 320 indicating the primary node for each affinity group.
[0048] Table 320 includes four columns: an affinity group ID column, a node ID column, an affinity group key start value column, and an affinity group key end value column. The affinity group ID indicates a specific affinity group. The node ID indicates the master node of the specific affinity group. The affinity group key start value indicates the lower limit of the affinity group key value range, and the affinity group key end value indicates the upper limit of the affinity group key value range of the specific affinity group.
[0049] In one embodiment, after generating the affinity group mapping, the database system sends a notification to one or more database clients indicating that the affinity group mapping has been generated. The client requests the affinity group mapping from the database system. The node in the database system receives the request and sends the affinity group mapping to the client. The request for the affinity group mapping can be combined with a request to perform other work (such as a query). In other embodiments, the database system can send the affinity group mapping to the client after generating the affinity group mapping without the client requesting the mapping. For example, instead of sending a notification to the database client, the database system sends the affinity group mapping.
[0050] When the database system reassigns one or more affinity groups to different nodes, the database system generates an updated affinity group mapping. In one embodiment, the database system sends a notification to the client indicating that an updated affinity group mapping is available. The client can request a new affinity group mapping from the database system. Alternatively, the database system can send the affinity group mapping using or in lieu of a notification.
[0051] Request Routing
[0052] Database clients can route queries to specific nodes based on affinity group mappings and queries. Figure 4 An example process by which a query router determines how to route a particular query is shown.
[0053] At step 400, the query router determines whether an affinity group mapping is stored for the database table targeted by the particular query. If no affinity group mapping exists, the query router sends the particular query to the database system without using the affinity group mapping. The database system may select a particular node to process the query without regard to the content of the particular query. For example, the database system may use load balancing techniques to select a particular node from one or more nodes available to process the request, or the database system may randomly select a particular node.
[0054] If an affinity group mapping is stored for the database table, the method proceeds to step 402. For example, assume that query router 180 receives the query:
[0055] SELECT * from Customers WHERE lastName='Smith'
[0056] The query router 180 determines whether an affinity group mapping is stored for the "Customers" table. For the purpose of illustrating a clear example, assume that the query router 180 is storing an affinity group mapping for the "Customers" table, such as Figure 3 shown.
[0057] At step 402, the query router determines whether the query can be routed using the affinity group mapping. In one embodiment, if the query includes a predicate based on the affinity group key, the query can be routed based on the affinity group mapping. Referring to the example above, the affinity group key of the "Customers" table is "lastName". Since the query includes the predicate "WHERE lastName = 'Smith'" based on the affinity group key "lastName", the query router 180 determines that the query can be routed based on the affinity group mapping.
[0058] Queries that target a specific affinity group key value, such as the query shown in the example, are called point queries. Queries that target a range of values are called range queries. In one embodiment, affinity group mappings may be used to route only point queries. In other embodiments, the query router may also use affinity group mappings to route range queries based on the range of values specified by the query.
[0059] The query may also include multiple predicates on multiple columns. Each of the multiple columns may belong to a different table. In one embodiment, a specific table is specified and the query is routed based on an affinity group mapping for the specific table. For example, the query router may be configured to route the query based on an affinity group mapping for a specific table. As another example, the query specifies a specific table and one or more other tables that have not been divided into affinity groups.
[0060] In some embodiments, if several tables have been divided into affinity groups, a query that specifies two or more tables can be routed if each table is divided into related affinity groups. For example, a first table and a second table may each include a "customer name" column, and both the first table and the second table are divided into affinity groups based on the "customer name" affinity group key. In other embodiments, the query router can be configured with a table hierarchy that indicates the order in which the query should be routed using affinity group mappings for a set of tables. For example, if a query specifies an "orders" table and a "customers" table, but the "customers" table is higher in the hierarchy, the query can be routed based on the affinity group mapping associated with the "customers" table.
[0061] If the query cannot be routed using the affinity group mapping, the query router sends the particular query to the database system without using the affinity group mapping for routing, as described above. If the query router determines that the query can be routed using the affinity group mapping, the method proceeds to step 404 .
[0062] At step 404, the query router determines the node to which the query should be routed based on the query and the affinity group mapping. If the query is a point query, the query router determines the specific affinity group to which the affinity group key specified by the query belongs. The target node is the master node of the specific affinity group. The query router determines the master node based on the affinity group mapping.
[0063] In one embodiment, if the query is about a range of values, the query router determines whether the range of values falls within a specific affinity group. If the range of values falls within the specific affinity group, the target node is the master node of the specific affinity group.
[0064] In some embodiments, if the range of values falls within several affinity groups, the query router determines which affinity group includes the largest range of values and selects the primary node of the affinity group as the target node. Additionally or alternatively, the query router may select multiple target nodes, and the query may be routed to a specific node among the multiple target nodes. The specific node may be selected based on, for example, a load balancing operation between the nodes.
[0065] In one embodiment, if the query does not specify a particular value or range of values, the query router determines which affinity group includes the largest range of affinity group key values and selects the master node of that affinity group as the target node.
[0066] Referring to the above example, affinity group 214 is associated with a range of values from "S" to "U." Therefore, affinity group 214 includes the last name value "Smith" specified by the query. Affinity group mapping table 320 indicates that the primary node of affinity group 214 is node 132. Based on the affinity group mapping, query router 180 selects node 132 as the target node.
[0067] In one embodiment, the query router may receive an affinity group key associated with the query. A target node may be selected based on the received affinity group key rather than the content of the query.
[0068] At step 406, the query router sends the query to the target node. The target node receives and processes the query. If no target node is selected, the query router sends the query to any node in the database system. The database system can select a node from multiple available nodes to receive the query. In the current example, query router 180 sends the query to node 132.
[0069] In one embodiment, if the target node is not available, the database system sends the query to another node. For example, if a node fails, but the affinity group mapping stored by the query router has not been updated, the query router can select the failed node as the target node. The database system receives a request to send the query to the failed node and routes the query to a different available node.
[0070] If the target node is no longer the primary node of the affinity group, the target node can still process the received query. For example, if the affinity group is reassigned to a different primary node, but the affinity group mapping stored by the query router has not been updated, the query router may choose a node that is not the primary node as the target node. However, the target node can still process the query by accessing the requested data from the database after acquiring the necessary locks from the current primary node.
[0071] In one embodiment, the query router includes a request for an affinity group mapping or an updated affinity group mapping in addition to the query. The node receiving the query may send the affinity group mapping to the query router in addition to any data requested by the query. For example, if the query router previously received a notification from the database system indicating that an affinity group mapping is available, the query router may wait until it sends the query to send the request for the affinity group mapping.
[0072] Hardware Overview
[0073] According to one embodiment, the techniques described herein are implemented by one or more special-purpose computing devices. Special-purpose computing devices may be hardwired to perform these techniques, may include digital electronic devices such as one or more application-specific integrated circuits (ASICs) or field programmable gate arrays (FPGAs) that are permanently programmed to perform the techniques, or may include one or more general-purpose hardware processors that are programmed to perform the techniques according to program instructions in firmware, memory, other memory, or a combination. Such special-purpose computing devices may also combine customized hard-wired logic, ASIC (s) or FPGA (s) with customized programming to implement the techniques. Special-purpose computing devices may be desktop computer systems, portable computer systems, handheld devices, network devices, or any other device that combines hard-wired and / or program logic to implement the techniques.
[0074] For example, Figure 5 is a block diagram illustrating a computer system 500 upon which embodiments of the present invention may be implemented. Computer system 500 includes a bus 502 or other communication mechanism for communicating information, and a hardware processor 504 coupled to bus 502 for processing information. Hardware processor 504 may be, for example, a general purpose microprocessor.
[0075] Computer system 500 also includes a main memory 506, such as a random access memory (RAM) or other dynamic storage device, coupled to bus 502 for storing information and instructions to be executed by processor 504. Main memory 506 may also be used for storing temporary variables or other intermediate information during execution of instructions to be executed by processor 504. When such instructions are stored in a non-transitory storage medium accessible to processor 504, such instructions cause computer system 500 to become a special-purpose machine customized to perform the operations specified in the instructions.
[0076] Computer system 500 also includes a read only memory (ROM) 508 or other static storage device coupled to bus 502 for storing static information and instructions for processor 504. A storage device 510, such as a magnetic disk, optical disk, or solid state drive, is provided and coupled to bus 502 for storing information and instructions.
[0077] The computer system 500 may be coupled to a display 512, such as a cathode ray tube (CRT), via the bus 502 for displaying information to a computer user. An input device 514, including alphanumeric and other keys, is coupled to the bus 502 for communicating information and command selections to the processor 504. Another type of user input device is a cursor control 516, such as a mouse, a trackball, or cursor direction keys, for communicating direction information and command selections to the processor 504 and for controlling cursor movement on the display 512. The input device typically has two degrees of freedom in two axes, a first axis (e.g., x) and a second axis (e.g., y), which allows the device to specify a position in a plane.
[0078] The computer system 500 may implement the techniques described herein using custom hard-wired logic, one or more ASICs or FPGAs, firmware, and / or program logic that is combined with the computer system to make or program the computer system 500 into a special purpose machine. According to one embodiment, the techniques herein are performed by the computer system 500 in response to the processor 504 executing one or more sequences of one or more instructions contained in the main memory 506. Such instructions may be read into the main memory 506 from another storage medium, such as the storage device 510. Execution of the sequences of instructions contained in the main memory 506 causes the processor 504 to perform the processing steps described herein. In alternative embodiments, hard-wired circuitry may be used in place of or in combination with software instructions.
[0079] The term "storage medium" as used herein refers to any non-temporary medium that stores data and / or instructions that cause a machine to operate in a specific manner. Such storage media may include non-volatile media and / or volatile media. Non-volatile media include, for example, optical disks, magnetic disks, or solid-state drives, such as storage device 510. Volatile media include dynamic memory, such as main memory 506. Common forms of storage media include, for example, floppy, diskettes, hard disks, solid-state drives, magnetic tapes or any other magnetic data storage media, CD-ROMs, any other optical data storage media, any physical media with hole patterns, RAM, PROMs and EPROMs, FLASH-EPROMs, NVRAMs, any other memory chips, or cassette tapes.
[0080] Storage media are distinct from, but may be used in conjunction with, transmission media. Transmission media participate in the transmission of information between storage media. For example, transmission media include coaxial cables, copper wires, and optical fibers, including the wires that make up bus 502. Transmission media may also take the form of acoustic or light waves, such as those generated during radio wave and infrared data communications.
[0081] When one or more sequences of one or more instructions are transmitted to the processor 504 for execution, various forms of media may be involved. For example, the instructions may initially be carried on a disk or solid-state drive of a remote computer. The remote computer can load the instructions into its dynamic memory and then send the instructions over a telephone line using a modem. A modem local to the computer system 500 can receive the data on the telephone line and convert the data into an infrared signal using an infrared transmitter. An infrared detector can receive the data carried in the infrared signal, and appropriate circuits can place the data on the bus 502. The bus 502 transmits the data to the main memory 506, from which the processor 504 retrieves and executes the instructions. The instructions received by the main memory 506 can optionally be stored on the storage device 510 before or after execution by the processor 504.
[0082] Computer system 500 also includes a communication interface 518 coupled to bus 502. Communication interface 518 provides bidirectional data communication coupled to a network link 520 connected to a local area network 522. For example, communication interface 518 can be an integrated services digital network (ISDN) card, a cable modem, a satellite modem, or a modem that provides a data communication connection to a telephone line of a corresponding type. As another example, communication interface 518 can be a local area network (LAN) card that provides a data communication connection to a compatible LAN. A wireless link can also be implemented. In any such implementation, communication interface 518 sends and receives electrical, electromagnetic, or optical signals that carry digital data streams representing various types of information.
[0083] The network link 520 typically provides data communications through one or more networks to other data devices. For example, the network link 520 can provide a connection through the local area network 522 to a host computer 524 or data equipment operated by an Internet Service Provider (ISP) 526. The ISP 526 in turn provides data communication services through the global packet data communication network now commonly referred to as the "Internet" 528. Both the local area network 522 and the Internet 528 use electrical, electromagnetic or optical signals that carry digital data streams. The signals through the various networks and the signals on the network link 520 and through the communication interface 518 are example forms of transmission media that carry the digital data to and from the computer system 500).
[0084] Computer system 500 can send messages and receive data, including program code, through the network(s), network link 520, and communication interface 518. In the Internet example, server 530 can send requested application code through Internet 528, ISP 526, local area network 522, and communication interface 518.
[0085] The received code may be executed by processor 504 as it is received, and / or stored in storage device 510 or other non-volatile storage for later execution.
[0086] In the foregoing specification, embodiments of the present invention have been described with reference to numerous specific details that may vary from implementation to implementation. Therefore, the specification and drawings should be regarded as illustrative rather than restrictive. The sole and exclusive indication of the scope of the invention, and what the applicant intends as an indication of the scope of the invention, is the literal meaning and equivalent range of the claims published in accordance with the specific form of such claims published (including any subsequent amendments) according to the patent application.
Claims
1. A computer-implemented method comprising: partitioning a database table permanently stored on one or more non-transitory computer-readable media into a plurality of affinity groups; wherein the database table includes a first portion assigned to a first affinity group among the plurality of affinity groups and a second portion assigned to a second affinity group among the plurality of affinity groups; Wherein, the database table is managed by a multi-node database management system DBMS including multiple nodes; wherein each of the plurality of nodes executes a database server instance; wherein the database server instance on each of the plurality of nodes shares access to the database table on the one or more non-transitory computer-readable media; For each affinity group in the plurality of affinity groups, assigning a specific node in the plurality of nodes as a master node of the each affinity group; wherein the step of assigning a specific node among the plurality of nodes as a master node of each affinity group comprises assigning a first affinity group to a first master node among the plurality of nodes and assigning the second affinity group to a second master node among the plurality of nodes; sending a map indicating a master node for each of the plurality of affinity groups to a query router separate from and executing remotely relative to the plurality of nodes; storing, by the query router, the mapping; Before sending a specific query requiring data from the database table to a target node among the plurality of nodes, the query router selects the target node for the specific query based on: which portion of the database table is accessed by the particular query, and the mapping; and The specific query is sent to the target node to cause the database server instance on the target node to process the specific query.
2. The method of claim 1, wherein: The query router is a client application that executes on a client node.
3. The method of claim 1, further comprising: Before sending the specific query, determining, based on the specific query, whether the specific query should be routed based on the mapping; Wherein selecting the target node is performed in response to determining that the particular query should be routed based on the mapping.
4. The method of claim 1, wherein: The specific query is a first query, and the method further includes: Before sending a second query requiring data from the database table to the multi-node DBMS, determining based on the second query that the second query should not be routed based on the mapping; and A node on which to perform a second query is selected from the plurality of nodes without regard to the mapping.
5. The method of claim 4, wherein: The target node of the first query is different from a node selected to execute the second query.
6. The method of claim 3, wherein: Partitioning the database table into the plurality of affinity groups is based on an affinity group key, and wherein determining whether the particular query should be routed based on the mapping is based on whether the particular query has a predicate based on the affinity group key.
7. The method of claim 6, wherein: Each affinity group corresponds to one or more ranges of the affinity group key, and wherein selecting the target node comprises associating the target node in the mapping with an affinity group corresponding to an affinity group-specific key value range within which the affinity group key value specified in the particular query falls.
8. The method of claim 7, wherein: The affinity group key specified in the specific query is specified via a query-specified range of affinity group key values, and wherein the query-specified range of affinity group key values is included in the affinity group-specific key value range.
9. A method as claimed in claim 7, wherein the affinity group key value specified in the specific query is specified via a query-specified range of the affinity group key value, and wherein the affinity group-specific key value range is included in one or more ranges associated with the target node, and the one or more ranges collectively include a maximum portion of the query-specified range of the affinity group key value specified in the specific query.
10. The method of claim 1, further comprising: detecting that a node assigned to one or more of the plurality of affinity groups has been removed; Allocating a different specific node to each of the one or more affinity groups as a master node of each affinity group; as well as An updated map is sent to the query router, the updated map indicating a master node for the one or more affinity groups.
11. The method of claim 1 , further comprising: Detect that additional nodes have been added; assigning the additional node to one or more affinity groups of the plurality of affinity groups as the master node of the one or more affinity groups; as well as An updated map is sent to the query router, the updated map indicating a master node for the one or more affinity groups.
12. The method of claim 1, further comprising: include: in, The database table is a first database table and the plurality of nodes is a first plurality of nodes; wherein the multi-node DBMS includes a second plurality of nodes; wherein each node in the second plurality of nodes executes a database server instance; wherein the database server instance on each of the second plurality of nodes shares access to a second database table managed by the multi-node DBMS; wherein the database server instance on each node of the second plurality of nodes does not share access to the first database table; and wherein the database server instance on each node of the first plurality of nodes does not share access to the second database table; partitioning the second database table into a second plurality of affinity groups; for each affinity group in the second plurality of affinity groups, assigning a particular node in the second plurality of nodes as a master node for the each affinity group; and A second map is sent to the query router, the second map indicating a master node for each affinity group in the second plurality of affinity groups.
13. One or more non-transitory computer-readable media storing one or more sequences of instructions which, when executed by one or more hardware processors, cause the method of any one of claims 1-12 to be performed.
14. A computer-implemented system comprising: one or more hardware processors; and One or more non-transitory computer-readable media storing one or more sequences of instructions which, when executed by one or more hardware processors, cause the method of any one of claims 1-12 to be performed.
15. A computer program product comprising instructions, which when executed by one or more processors cause the method of any one of claims 1 to 12 to be performed.
