Multi-user database system and method for resource usage tracking

The disclosure is directed to a multi-user database system. The multi-user database system includes at least one processor, at least one network interface coupled to the processor, an event table, an accounting table, and a session table. The network interface is configured to receive transactions from a plurality of users. The transactions include session maintenance transactions and data requests. The event table stores a data log of the session maintenance transactions. The accounting table stores data associated with the data requests. The session table is derived from the event table and the accounting table. The session table stores resource usage data associated with at least one user session.

FIELD OF THE DISCLOSURE

This disclosure is, in general, related to multi-user database systems and methods for resource usage tracking.

BACKGROUND

Large data warehouse systems are often used in decision and operational support systems. These large systems are typically accessible by multiple users through network interfaces or mainframe communications channels. Many such systems support parallel processing, and have multiple processors. Such processing capabilities permit many users to initiate sessions and request transactions with the data warehouse.

A problem arises with typical enterprise data warehouse systems when inefficient or erroneous requests are submitted. Inefficient requests may utilize excessive amounts of CPU time or input/output bandwidth. This excessive resource utilization may slow response times of the system or tax the resources available to other users. Erroneous queries compound the problem by utilizing CPU time while producing limited benefit to users. Typical enterprise data warehouses lack the ability to track and identify inefficient and erroneous transaction requests.

These typical enterprise data warehouse systems also lack the ability to track resource utilization based on particular user sessions and transaction requests. To forecast expected resource usage and to plan for resource allocation, it may be desirable to have an historical record of resource utilization associated with session and request usage. However, typical enterprise data warehouses lack the ability to track resource usage such as CPU time and input/output traffic for unique sessions or transaction requests. Therefore an improved enterprise data warehouse system would be desirable.

SUMMARY

The disclosure is generally directed to a multi-user database system. The multi-user database system includes at least one processor, at least one network interface coupled to the processor, an event table, an accounting table, and a session table. The at least one network interface is coupled to the at least one processor. The at least one network interface is configured to receive transactions from a plurality of users. The transactions include session maintenance transactions and data requests. The event table stores a data log of the session maintenance transactions. The accounting table stores data associated with the data requests. The session table is derived from the event table and the accounting table. The session table stores resource usage data associated with at least one user session.

The disclosure is also directed to a multi-user database system. The multi-user database system includes a processor, a network interface, an event table, an accounting table, and a request table. The network interface is coupled to the processor. The network interface is configured to receive transactions from a plurality of users. The transactions include session maintenance transactions and data requests. The event table stores an event log of the session maintenance transactions. The accounting table stores data associated with the data requests. The request table is derived from the event table and the accounting table. The request table stores resource usage data associated with the data requests and associated with at least one user sessions.

The disclosure is further directed to a method of tracking database system usage. The method includes determining a set of new sessions from an event log data table to form a temporary sessions data table; matching entries in the temporary sessions data table with a set of request transactions to form a matched data table; preparing a sessions-level summary from the matched data table; updating a session table; and querying the session table to track database systems usage. The session table stores resource usage data associated with the set of new sessions.

DESCRIPTION OF THE DRAWING(S)

FIG. 1depicts an exemplary embodiment of an enterprise data warehouse100. The enterprise data warehouse100may have one or more processors102, network interfaces104, programs106, an accounting database108, an event log database116, a session information database112, a sessions database114, a history database116, a request database118, and various temporary databases120. A processor or multiple processors102may interpret and perform transactions with the various databases and tables. Multiple processors may allow parallel processing and faster performance.

Various network interfaces104may provide access from remote locations to the enterprise data warehouse. From remote locations users may log on to the enterprise data warehouse100and perform transactions. These transactions may include session maintenance transactions such as opening a session or closing a session and database/table queries and maintenance transactions. Enterprise data warehouse100may also include various programs such as database engines, scripts, utilities, operating system code, and executables.

The enterprise data warehouse may have various databases or data tables such as an accounting table108, an event log110, a session info table112, a sessions table114, a history table116, a request table118and various temporary tables120. A database may include one or more tables with various logical links. Databases or tables may be alternately organized as more than one table. The accounting table108may track central processing unit (CPU) usage and input/output (IO) usage and associate these with an account name, a user name, an accounting string, or a request string. The CPU usage may, for example be expressed in processing time or clock units, among others. The10usage may, for example, be expressed as bit or byte counts, packet counts, or usage time, among others. The event log110may, for example, track session maintenance transactions such as log-on and log-off transactions. The event log110may associate log-on dates and times with user names and account names and assign session identification numbers. The session information table112may track running sessions and associate these with a user name, an account name, or a session identification number. In some cases, third part software may be used to capture query strings. Data may be extracted from the accounting table108, event log table110, and session info table112to provide a session table114, a history table116, and a request table118.

The session table114may provide a cumulative accounting of resource usage attributed to various sessions. The session table114may include entries for storing statistical characteristics and cumulative usage of CPU and IO usage, among others, associated with a given session. The request table118may hold entries that associate transaction requests with CPU and I/O usage statistics. The entries in the request table118may also be associated with user sessions.

The history table116may be used to indicate which transactions have been incorporated into the sessions table114and the request table118. Periodically, the accounting table108may be purged. Between purges, the history table116may track transaction entries that have been processed into the session table114or the request table118. In creating the sessions table114and the request table118, various temporary databases120may be used.

In addition to the databases listed above, the enterprise data warehouse100may be used to store or access many other databases and tables. Furthermore, the enterprise data warehouse100may include various connections to other computational systems such as mainframes or other database systems. The enterprise data warehouse may also include software to capture query strings. One exemplary embodiment of the enterprise data warehouse100is a data warehouse by Teradata®.

FIGS. 2-A,2-B, and2-C depict a data flow useful in accumulating information from an accounting table204, an event log table206, and a sessions information table254to create a session table240and a request table246. As shown inFIG. 2-A, a history table202may be queried with the accounting table204to determine which entries in the accounting table204have not been previously processed. The history table202may include a listing of those entries within the accounting table204that have been previously processed. The accounting table204may be periodically purged. If the periodic purging occurs less frequently than the processing of the transaction data, the history table202provides a listing of those transactions in the accounting table204that had been previously processed. The history table202may be purged at the same time as the accounting table204. The query is performed as shown in transaction212to select the current transactions and store them in the current transactions accounting table214. Once the current accounting transactions table214has been used in the process below, the history table202may be updated with an update history transaction210.

The sessions table208may be used in conjunction with the event log206to determine which sessions are new or open. As shown in transaction216, the event log206is compared to the session table208to determine a set of session entries that occurred after the last processing or maintenance. These may be stored in a “sessions since last maintenance” table218.

As shown inFIG. 2-B, the current accounting table214may be compared with the “sessions since last maintenance” table218. The current accounting table214stores information associated with CPU and I/O usage. The “sessions since last maintenance” table218stores data associated with new and open sessions. As shown with transaction220, the tables may be matched based on user name and session identification. This comparison may yield a partially matched data table222. Some entries within the current accounting table may not include a session identification field. In some records within the current accounting table214, the accounting string will include session identification. These records may be parsed to determine the session identification. A second request224may be performed using the user name and account string on the partially matched data to provide a matched data set226having more matched data. The matched data set226may store transaction resource usage entries associated with session or request data. The request level statistics and session level statistics may then be summarized with a request level summary transaction228and a sessions-level summary transaction230.

As shown inFIG. 2-C, the sessions-level summary transaction230results in a session update data table232, and the request-level summary transaction228results in a request updates table234. The session update table232may include sessions that were open as of the last processing and have new data or new sessions that were started after the previous processing. An update sessions transaction236may update the data associated with open sessions, and a insert sessions transaction238may insert new entries associated with new sessions occurring since the previous processing. These transactions236and238may be performed on a sessions table240. Similarly, the request updates data table234may include open requests having new data and new requests. The update request transaction242may update request or transaction data in the request database246and the insert request transaction244may update the request table246with new entries associated with new requests or transactions.

In one exemplary embodiment, the event log252may be queried through transaction248to determine which sessions have been closed with log-off events. The session info database254may also be queried with transaction250to determine if any sessions are not running and close those sessions in table240.

The sessions table240and the sessions table208may take the same format and represent the same table at different points in the data flow. In one exemplary embodiment, the sessions table240and the sessions table208are snap shots of the same table at different points in time. Similarly, the event log252and the event log206may represent the same table at the same or different points in the data flow. In one exemplary embodiment, the event log252and the event log206are snap shots of the same table at different points in time.

FIG. 3depicts an exemplary method for creating the sessions and requests table data. Further, this method may be used to determine historical trends in CPU and I/O usage and may also be useful in determining which transactions cause problems or result in excessive CPU and I/O usage, thus, enabling improved data warehouse management. As shown in step302, a current set of accounting transactions may be determined. The current set of accounting transactions may include transactions not previously processed. In one exemplary embodiment, the current set of transactions may be determined by comparing an accounting database with a history table. A set of new and open sessions may also be determined as shown in step304. The set of new and open sessions may, for example, be determined by comparing a session table with an event log. These two steps,302and304may occur simultaneously, or in various orders.

With the set of current accounting transactions and a set of new and open sessions, the data from these two sets may be matched on the user name and session identification as seen in step306. In systems where the session identification may not be recorded with all entries, a further match may be performed using the user name and account string, as shown in step308. In alternate embodiments, various matches may be performed in various orders. With these matches, a data table may be prepared from which a request summary may be drawn as seen in step310and a sessions summary may be drawn as seen in step312.

With the summaries, session tables and request tables may be updated or new entries may be added. As shown in step314, the session table may be updated with sessions that were previously opened and have new data. As shown in step316, new sessions that were opened since the previous processing may be inserted into the sessions table. Similarly, the request table may updated as shown in step318or new requests may be inserted in the request table as shown in step320. These steps,314,316,318and320may be performed in various orders or simultaneously, depending upon the capabilities of the enterprise data warehouse system.

The session table may be further updated by closing sessions that exhibit log-off events as shown in step322. Further, the session table may be updated by closing sessions that are not running, as indicated by a session information table, as shown in step324. If a history table is being used to monitor those transactions within the accounting table that have been previously processed, the history table may be updated as shown in step326.

Once the sessions table and request table have been created, they may be queried to determine CPU and I/O usage, as shown in step328. For example, the sessions table may be queried to determine the historical trend of CPU and I/O usage among various users. Such a trend may be useful in forecasting or allocating data warehouse usage and resources. In another example the request table may be queried to determine which transactions represent large uses of data warehouse resources. For example, transactions that utilize more than a select level of CPU or I/O bandwidth may be identified. In another example, transactions with relatively high resource utilization may be identified. Such queries may be useful in determining which transactions may be improved or may be monitored or modified to further improve data warehouse performance or reduce CPU and I/O usage. A query may also be useful in identifying problem transactions that may be corrected to enhance data warehouse performance. The MVS data fields and the MVS system or the source identification fields and the query capturing software may be used to identify inefficient transactions. As shown in step330, code associated with inefficient transaction requests may be manipulated to improve database performance. For example, the code may be manipulated to request a smaller set of data or, the transaction may be deleted.

FIG. 4depicts an exemplary session table. The table may include, for example, such fields as user ID, host ID, session ID, log-on date, log-on time, log-off date, log-off time, log-on performance group, log-off event, session duration, session CPU, session I/O, MVS data, IP address, process ID, account name, load data, load time, among others. The user ID may identify a user. The host ID may identify a host. The session ID may provide a unique identification number to a session. The log-on date, log-on time, log-off date, and log-off time fields may provide temporal data associated with log-on and log-off events. However, other methods may be used to represent such data, such as using computer clock times that incorporate date and time. The log-on performance group field may be useful in ascertaining priority. The log-off event may include indications of whether the log-off was intentional or a timed-out event. The session duration field may present a total time or duration of session. The session CPU and session I/O fields may indicate CPU usage and input/output bandwidth usage. In addition to these fields, other fields may be included that incorporate statistics on the CPU and I/O or resource usage such as averages per transaction, maximum transaction usage, and minimum transaction usage.

Sessions may be manipulated or initiated from various sources. In some cases these sources may be a mainframe. As such, data associated with the mainframe transactions may be stored in the Multiple Virtual Systems (MVS) data fields. In one exemplary embodiment, the MVS data fields contain data that permits a user to look up a job on the MVS system to find more information about a transaction such as a query string. Other transactions may be manipulated through various network connections. As such, fields may be provided to record the source of these transactions such as where the source is identified by an IP address. In addition, software may be used to capture query strings from these other sources. The strings may then be identified using the source data. Further, the session table may associate other information with the session such as the process identification, the account names, and load dates and times. The load dates and times may indicate the date and time the entry was created or last updated.

FIG. 5is an exemplary embodiment of a request table. A request table may include a host ID, a session ID, a user ID, log-on dates and times, request start date and request start times, request performance groups, access module processor (AMP) count, CPU and I/O data, parallelism data, ratios, and load dates and times. The host ID may identify the host. The session ID may provide a unique identification number for identifying the session. The user ID may identify the user. The log-on date and log-on time may be associated with the session and may be stored in various formats, such as computer clock times. Similarly the request start date and request start time identify the start date and time of the request and may be stored in other formats. The request performance group identifies the priority provided to the request. In multiprocessor and parallel systems, a request may be performed by more than one processor or more than one process. The request table may also include data associated with how many processes are assigned to the request. This data may be stored in the AMP count. In addition, statistical data associated with CPU usage and I/O usage may be stored in various fields including the CPU and I/O fields. For example, average, minimum, and maximum resource usage per AMP may be stored in a related field. Other statistics such as parallelism and CPU-I/O ratio may be used in characterizing the performance of a transaction. The load date and time may be useful in determining when an entry was inserted into the table or updated.