1. Field
The following generally relates to database systems, and more particularly to parallel propagation of view table record updates, which are based on updates to base table records.
2. Related Art
Modern database systems comprise base tables that have directly updated data, and view tables that are derived from data obtained, directly or indirectly, from base tables (derived data). For example, a web store may use a base table for tracking inventory and another base table for tracking customer orders, and another for tracking customer biographical information. A person maintaining the web store may, for example, desire to analyze the data to prove or disprove certain hypotheses, such as whether a certain promotion was or would be successful, given previous order behavior, and other information known about customers. Such analysis can involve creating different views derived from, and dependent on, the base data.
The base tables are updated as changes are required to be reflected in the data. In other words, the base tables generally track or attempt to track facts, such as order placement, inventory, addresses, click history, and any number of other conceivable facts that may be desirable to store for future analysis or use.
Thus, when base tables are updated, view tables that depend on data in those updated base tables ultimately should be updated to reflect those updates. However, one concern is avoiding interference with transactions involving applications making changes to the base tables, because the responsiveness of such systems can affect a user's experience with the applications themselves (e.g., responsiveness of a web store or a search engine). Since derived data (e.g., the view tables) are used mostly for analytics and business planning, updates from base tables to view tables can occur “off-line”, to avoid burdening the systems that are supposed to be most responsive to users. For example, adjustments to a base table tracking inventory for a product need to be made when a unit of the product is sold. There may be a number of views that depend on a current inventory for that product.
In such traditional models of using base table data to derive various other ways to “view” or consider the meaning of the base table data, it is not imperative to provide elaborate mechanisms to avoid burdening real-time transaction systems or to ensure consistency in the view data during updating of such tables. Instead, it can often be enough that a simple stream or sequential log of each base table change can be provided to a view manager for processing. Such updates arrive in the log in an application-sequential order (could be time-sequential) and are processed in that order to update the view tables, thereby avoiding an issue of whether one base table update may be propagated to views before a factually earlier update. “Maintaining Views Incrementally” by Gupta, et al. SIGMOD 1993 (Washington D.C.) discloses background as to how a view can be incrementally maintained from base table updates spread through time.
However, views were updated more promptly, approximately a real-time update of each view every time a unit of that product were sold (and a unit for each of hundreds or thousands of other products), then such updating may pose a substantial burden on one or more of the system components.
Yet, simple parallelization of view updating does not ensure consistency of “view” (derived) data during base table updates. For example, a person sells 100 shares of CSCO and uses the proceeds to buy YHOO. Each of these transactions would be reflected as an update in one or more base tables, and factually (i.e., in the real-world), the sale occurred before the buy. However, if the base table update for the buy is reflected in a view (e.g., an account summary for the person) before the base table update for the sale, then that view will show an account state for the user that is factually inaccurate.
Some work has been done related to concerns about how to ensure that a view requiring multiple sources of base data is maintained with such base table data in a proper order. For example, “View Maintenance in a Warehousing Environment” by Zhuge, et al. SIGMOD 1995 (San Jose, Calif.) concerns situations where sources of base table updates can trigger a view update, but the view update is also dependent on other base data. Zhuge proposes a mechanism directed to using a proper version of the other base data, with respect to the base table update triggering the view update. Thus, Zhuge concerns avoiding using stale or out of sequence base data when two or more sources of base data are needed to maintain a view. However, Zhuge does not address concerns about increasing parallelization of base table record updates propagation to view updates.
Aspects include a system with a view manager configuration comprising a plurality of view managers that each track/propagate base table record updates by performing corresponding updates to the view table records. The view managers collectively may update in parallel the same view table record based on different updates to different base table records, and may update in parallel different view table records based on different updates to the same base table record. However, multiple view managers may not update in parallel the same view table record based on different updates to the same base table record. The view managers may execute on one or more computing resources.
The system includes a view manager configuration comprising a plurality of view managers that each may map multiple different base table record update. The view managers collectively may update in parallel the same view table record with different base data record updates, and may update in parallel multiple view table records with the same base table record update. However, multiple view managers may not use a single base table record update in updating in parallel the same view table record. The view managers may execute on one or more computing resources.
Such systems may further comprise a configuration manager operable to assign maintenance of views to view manager computing resources based on increasing parallelism of view maintenance and avoiding configurations where multiple of the view managers map one base table record update for updating the same view table record.
Other aspects include a database system analysis method comprising the receipt of data specifying a configuration of a system having one or more base tables. Each base table may be partitioned across one or more computing resources. Each partition is operable for producing indicators of base table record updates for reception by one or more log segments. A plurality of view managers is configured for receiving updates from the log segments and maintaining view records. The method also comprises identifying, based on the configuration, flows of base table record updates through the plurality of view managers, to update the view records.
Multiple of the view records may be updated based on any one base table record update. Multiple view records may be updated based on any one or more base table record updates, and one view record may be updated based on multiple base table record updates. The method also comprises flagging as improper any two or more flows that each cause the same base table record update to reach two or more different view managers, which also use that base table record update in maintaining the same view record.
Other aspects include methods and computer readable media embodying program code for effecting methods according to the examples described. Still other aspects include methods and systems allowing planning for new and/or revised view update programs, allocation, and reallocation of view management resources for supporting parallelization of view updating according to the following description.
It was described in the background that a way to implement view table updates from base table updates is to provide a sequenced single log for a number of base tables to a number of views. In such an implementation, the single log receives base table updates sequentially at a tail end, and a view manager pulls log entries from a head end of the log, which can be seen to be a serial process that would be difficult to scale.
Providing parallelism to this serial updating process would be desirable, but the concerns of (1) keeping base table updating responsive and (2) keeping factually correct ordering of view updates dictate that parallelism be approached with caution.
Information that may be associated with each record entry includes a transaction ID, a ticker symbol, what type of trade, a number of shares, a date, a time, a price, and an account number. Of course, other information also could be associated with a record of this type, but the following provides an example for purposes of illustration. Thus, each time a trade occurs in a NASDAQ listed stock, the base table tracking such transactions would need to be updated to store a record for that trade. As can be quickly discerned, with over two billion shares of NASDAQ listed stocks being traded every day, keeping a base table current with a record of all such trades is resource intensive.
A database manager 210 communicates with the base data storage 205a-205n, and also with applications 215a-215n. Database manager 210 operates by receiving base table record updates from applications 215a-215n, such as stock trades in the example of Table 1, item sales in a retail establishment, and so on.
As such, applications 215a-215n can be any source of updates to the base data 205a-205n, and in the stock example, may include web interfaces receiving orders from online brokerage users, streams from private exchanges, and any other source of stock trades. In search, the applications can be various search engines that submit query and user interaction data for storage in base data 205a-205n. As can be understood by these examples, the applications represent any source of updates for base data 205a-205n.
In response to committing base table record updates to their respective memories each base data 205a-205n generates output to a log 220.
As previously discussed, improper results can occur if base table record updates are applied out of an order presented in the queue. For example, a last trade price tracker necessarily needs to track the price of the last trade, and the order cannot be altered. Thus, outputs from base data 205a-205n to log 220, and from log 220 to view manager 230 are sequentially ordered.
As such, although there is a parallelization of base data, there is a serialization of updates coming from the storage of base data, through a FIFO log to a single view manager, in order to maintain correctness of view data updates. Although natural speed ups and progress of technology allow for increases in the speed of serial updating of such view data, such updating speed increases are largely incremental, and so this view updating strategy does not scale well. Such a situation may be acceptable, so long as the view data is used for post hoc analysis purposes, but many uses for more current view data would be enabled if the data were more current. Herein, parallelization of data flows used in updating the view data is provided, which can provide better scaling of such updating.
As explained herein, parallelization of updating of view tables is provided by parallelizing update paths through log segments, parallel view managers which can assume or be assigned portions of the updating workload, and parallel accessibility to the view records themselves. However, simple provision of parallel resources for these tasks would not yield correct results.
Generally, it is preferred to map base data table partitions to log segments in a way that avoids a potential for sending updates relating to the same base table record to two or more log segments of log segments 315a-315n. Interfacing the base data table partitions to the log segments in this way allows an assumption that no single base table record update appears in multiple log segments when analyzing flows of record updates from base table partitions to view table partitions.
View managers 320a-320n are each operable to run one or more programs that define processes or implement propagation of view data. In other words, view managers 320a-320n each can obtain base data updates and produce/update various derivations of such data, and store or otherwise transmit or provide such derivations, which are identified as views 325a-325n. Each view would generally include multiple records, as illustrated with records 1-n of view 325b. To obtain the inputs for such derivations, each view manager subscribes to log segments from log segments 315a-315n to receive update indications from appropriate base tables. For example, if a view manager is maintaining a view for total trade volumes in INTC, then that view manager would subscribe to each log segment that had indications of updates for any record relating to an INTC trade. Or, if several view managers were maintaining such a view, then each may subscribe to a portion of the log segments, as described in more detail below.
Ultimately, the view managers update records in the views 325a-325n. In some cases, a view manager, when updating a view data record, can read a current value of the record, and perform an operation on that value, and then write a new value back. For example, if maintaining a total trade volume for a stock, then a present total trade volume would be read, incremented by a given trade size, and then the incremented value would be written back to the view table record.
Example mappings between log segments 315a-315n and view managers 320a-320n are respectively numbered 340-344. For example, log segment 315a is mapped to view manager 320a, while log segment 315b is mapped both to view manager 320b and 320c.
Likewise, view managers 320a-320n are shown as respectively maintaining records within certain of views 325a-325n, as shown by mappings 360-365. For example, view manager 320a maintains view 325a, as shown by mapping 360, while view manager 320b and view manager 320c are shown as maintaining record 1 of view 325b with mapping 361 and mapping 362 respectively. Likewise, view 325n is shown by mappings 364 and 365 as being maintained by view managers 320c and 320n.
In the above description, mappings of view managers to view records has largely been abstracted for clarity and ease of understanding. For example, a given view may have subtotal records for each of various items that all contribute to a record of an overall total of such items. Thus, in practice, a mapping of view managers to individual view records is preferably maintained, so that flows between base table record updates and view record updates are mapped, allowing greater parallelism.
In the organization shown in
To that end, any update to a base table record should be able to be provided to any number of view managers, and those view managers can propagate an update to any number of view records using that base table record update, so long as no two separate view managers attempt to update the same view table record with that single base table record update. For example, it is permissible to allow any base table update record to flow through any number of view managers to any number of distinct view table records. Likewise, many different base table record updates can flow through different view managers to update one view table record.
By particular example in
However, it is not necessarily the case that each of view manager 320b and 320c uses each update present in log segment 315b to update a view table record, as each view manager may only need to obtain a portion of such updates for its own view maintenance purposes.
View manager 320b updates records only in view 325b (arrow 361), while view manager 320c also updates view records in view 325b and in view 325n (arrow 364). So long as the same view record is not updated by view manager 320b and by view manager 320c, based on a common base table record update (e.g., from log segment 315b), this configuration is permissible. So, it is determined whether any single record in view is updated by both view managers 320b and 320c, and if there is no such view record, then this flow is acceptable. However, if there is such a view record, then it must then be determined whether both view managers 320b and 320c use the same base table record update in updating that identified view record. Where more than one such view record is identified, this analysis must be undertaken for each such view record. Of course, the analysis of this data flow example could have proceeded oppositely, where commonality of base table update records used by view managers 320b and 320c was first detected. Then, for any base table update records used in common by these view managers, it would be determined whether there was any common view record updated with such base table record.
Another example configuration is that view manager 320c receives base table record updates from log segments 315b and 315c (arrows 342 and 343, respectively), and maintains view 325n, view manager 320n receives base table record updates from log segment 315n, and maintains view 325n. In this example configuration, so long as no single base table record update is available from any of log segments 315b, 315c, or 315n then there would not be a conflict between these view managers in updating any record in view 325n.
The above description described aspects of parallel data usage and updating (e.g., using in parallel base table record updates and updating in parallel view table records.) These aspects also can be described from a perspective of concurrent information usage and updating. For example, it was described that view managers can be performing a plurality of processing components to propagate base table record updates to view tables, including receiving base table record updates, performing computations on data, and then updating such view records based on the computations. Thus, each of a plurality of view managers may perform such processing components. In such a case, these processing components of plurality can be scheduled for concurrent execution on a processing resource, where the processing components are scheduled to be performed. For example, the components can be interleaved, can run in different threads, can be pipelined to use different system resources, and so on. Other examples of concurrent execution include using a plurality of physically distinct hardware resources, using virtual partitions of a computing resource, and so on. In any such cases, a plurality of view managers would be prevented from concurrently using the same base table record update for concurrently updating the same view table record update.
So, database manager 410 controls where the constituent information parts are stored among resources 405a-405n, and then appropriate updates indicative of the new or updated information are sent from resources 405a-405n to respective log segments 420a-420n. The information in the log segments is provided across a communication network 425 to view managers 430a-430n; the communication network can comprise segments of a Local Area Network, Wide Area Networks, wireless broadbank links and so on. It is preferable that there is low latency between a log segment receiving a base table update and a view manager receiving that update from the log segment below, and so the communication network preferably is selected and/or designed with that goal. Also, the communication network 425 can have a plurality of physical and/or virtual paths such that each log segment can output data to multiple view managers 430a-430n.
As explained above, each view manager 430a-430n is responsible for maintaining one or more views stored in view data 435a-435n (can be shared responsibility with other of the view managers 430a-430n). As also explained above, each view manager 430a-430n would subscribe to receive updates from log segments containing updates to base table record(s) used in deriving its views (and new records that are needed in maintaining such views).
In
From the perspective that updates to view table records are used as inputs in business decision logic, or as triggers for events, the view table records and the view tables themselves can be virtual, in that persistent storage of them is not required. For example, an update to a view table record can be generated, and used as a trigger for a certain event, such as selection and placement of an advertisement on a web page, and that update may not ultimately affect any content in persistent storage.
Some of the base tables can be partitioned among multiple of the physically distinct resources. Similarly, one view update routine for updating a particular view can be executed by multiple view managers running on different of the computing resources for executing such update routines. Likewise, any view table also can be partitioned among multiple distinct resources for storage. Thus, large amounts of data and/or processing to update such data can be handled in parallel.
Information about how a given set of base tables, log segments, view managers, and view tables are configured supports the analysis steps identified in method 500. A first analysis step is that base table record updates are mapped to resources executing view update/management routines. In an example, base table record updates from a particular base table partition (if partitioned) can be mapped to one log segment (see
Then, based on the mappings identified in 510 and 515, flows of base table record updates from the physical resources where those updates originate (e.g., base data 405a-405n), through view managers (e.g., view managers 430a-430n) to view table records stored in potentially physically distinct resources (e.g., view data 435a-435n) are identified (520). So, in 520, dependencies between a particular update to a base table record and a particular view table record (including an intermediate path through a particular view manager) can be determined.
These flows are analyzed, and for any flow where more than one base table record update flows through multiple view managers to be used in updating the same view table record, there is a flag, or other indication, provided (535) that such a flow is potentially problematic and should be reviewed and/or revised. Method 500 then can end (530) after flagging any improper flows or otherwise failing to identify any improper flows.
Based on existing configuration information determined in the steps described with respect to
Methods, programs, and systems according to the above examples can help increase implementation of parallel view updating to create derived data. Examples may also include computer-readable media for carrying or having computer-executable instructions or data structures stored thereon. Such computer-readable media can be any available media that can be accessed by a general purpose or special purpose computer. By way of example, and not limitation, such computer-readable media can comprise RAM, ROM, EEPROM, CD-ROM or other optical disk storage, magnetic disk storage or other magnetic storage devices, or any other medium which can be used to carry or store desired program code means in the form of computer-executable instructions or data structures. When information is transferred or provided over a network or another communications connection (either hardwired, wireless, or combination thereof) to a computer, the computer properly views the connection as a computer-readable medium. A “tangible” computer-readable medium expressly excludes software per se (not stored on a tangible medium) and a wireless, air interface. Thus, any such connection is properly termed a computer-readable medium. Combinations of the above should also be included within the scope of the computer-readable media.
Computer-executable instructions include, for example, instructions and data which cause a general purpose computer, special purpose computer, or special purpose processing device to perform a certain function or group of functions. Computer-executable instructions also include program modules that are executed by computers in stand-alone or network environments. Generally, program modules include routines, programs, objects, components, and data structures, etc. that perform particular tasks or implement particular abstract data types. Computer-executable instructions, associated data structures, and program modules represent examples of the program code means for executing steps of the methods disclosed herein. The particular sequence of such executable instructions or associated data structures represents examples of corresponding acts for implementing the functions described in such steps. Program modules may also comprise any tangible computer-readable medium in connection with the various hardware computer components disclosed herein, when operating to perform a particular function based on the instructions of the program contained in the medium.
Those of skill in the art will appreciate that embodiments may be practiced in distributed computing environments where tasks are performed by local and remote processing devices that are linked (either by hardwired links, wireless links, or by a combination thereof) through a communications network. In a distributed computing environment, program modules may be located in both local and remote memory storage devices.