Systems, methods, and media associated with a cloud computing environment

Abstract

According to some embodiments, methods and systems can be associated with a cloud computing environment. A workload prediction framework can receive observed workload information associated with a database (e.g., a database as a service (“DBaaS”)) in the cloud computing environment. Based on the observed workload information, a statement arrival rate (“SAR”) prediction can be generated. Further, a host variable assignment prediction can be generated based on the observed workload information. The workload prediction framework can then automatically create a workload prediction for the database using the SAR prediction and the host variable assignment prediction. A physical database design advisor (e.g., a table partition advisor) can receive the workload prediction and, in response to the workload prediction, automatically generate a recommended physical layout for the database (e.g., using a cost model, a current physical layout, and an objective function).

Description

Background Technology

[0001] Enterprises can leverage cloud computing environments to enable users to perform tasks. For example, an enterprise can allow various users to run applications through a cloud computing environment to process purchase orders, adjust human resources information, generate invoices, and so on. A cloud computing environment can be associated with one or more databases, and the physical layout of these databases can substantially affect performance and memory consumption. As a result, an inappropriate physical layout can significantly degrade performance or increase memory consumption, thereby increasing the hardware costs for the organization operating the database (an increase in hardware costs can be a consequence of poor performance or increased memory consumption). Because the amount of dynamic random access memory (“DRAM”) provided determines hardware costs, Database-as-a-Service (“DBaaS”) providers, in particular, must carefully consider the economic trade-offs between memory consumption and performance. Finding the optimal physical layout, for example, regarding memory consumption or performance, is a complex and time-consuming process typically performed by database experts. As a result, academia and industry have developed tools for automating physical database design. Typically, such automation methods focus on static workloads. However, it is important to note that workloads change over time. Whenever workload changes are not addressed in a timely manner, the current physical layout may no longer be optimal, leading to significant performance degradation or increased memory consumption. The question of how to find the appropriate physical layout for workloads that will change over time is largely unresolved, despite its high practical relevance.

[0002] Therefore, it is desirable to provide accurate and efficient workload predictions, which can serve as input for physical database design consultants. This allows the physical layout to adapt to workload changes in a timely manner and thus be optimized for future workloads, thereby achieving high performance and / or low memory consumption in the future. Summary of the Invention

[0003] According to some embodiments, the methods and systems can be associated with a cloud computing environment. A workload prediction framework can receive observed workload information associated with a database (e.g., DBaaS) in the cloud computing environment. Based on the observed workload information, a statement arrival rate (“SAR”) prediction can be generated. Furthermore, a host variable assignment prediction can be generated based on the observed workload information. The workload prediction framework can then automatically create workload predictions for the database using the SAR predictions and the host variable assignment predictions. A physical database design consultant (e.g., a table partitioning consultant) can receive the workload predictions and, in response, automatically generate a recommended physical layout for the database (e.g., using a cost model, the current physical layout, and an objective function). Note that the current physical layout can be considered in this decision, as switching from one layout to another may degrade performance or increase memory consumption.

[0004] Some embodiments include means for receiving observed workload information associated with a database in a cloud computing environment by a computer processor of a workload prediction framework; means for generating SAR predictions based on the observed workload information; means for generating host variable assignment predictions based on the observed workload information; means for automatically creating workload predictions for a database using SAR predictions and host variable assignment predictions; and means for automatically generating recommended physical layouts for a database by a physical database design consultant in response to the workload predictions.

[0005] Some of the technical advantages of the embodiments disclosed herein lie in providing improved systems and methods for accurate and efficient workload prediction, which can be used as input for physical database design consultants. Attached Figure Description

[0006] Figures 1A to 1B The table partition layout is shown.

[0007] Figure 2 It is a high-level block diagram associated with a cloud-based computing system according to some embodiments.

[0008] Figure 3 These are more detailed diagrams of the system according to some embodiments.

[0009] Figure 4 This is a custom adapter creation method based on some embodiments.

[0010] Figure 5 A system according to some embodiments is shown.

[0011] Figure 6 This is a more detailed system based on some embodiments.

[0012] Figure 7 This is an example of workload drift according to some embodiments.

[0013] Figure 8 A static workload according to some embodiments is shown.

[0014] Figure 9 This is a SAR method based on some embodiments.

[0015] Figure 10A and Figure 10B The relationship between the discrete Fourier transform of SAR and observed SAR is shown according to some embodiments.

[0016] Figures 11A to 11F Examples of SAR detection and classification for various SQL statements are shown according to some embodiments.

[0017] Figure 12 It is a directed acyclic graph (“DAG”) classifier according to some embodiments for determining the type of workload drift.

[0018] Figure 13 This is a host variable assignment method based on some embodiments.

[0019] Figure 14A and Figure 14B The classification of assignments according to some embodiments is shown.

[0020] Figure 15 It is a probability mass function based on some embodiments.

[0021] Figures 16A to 16C These are examples of assignment predictions based on some implementation examples.

[0022] Figure 17 Illustrations are shown according to some embodiments The impact on future database layout choices.

[0023] Figure 18 This is based on workload prediction and physical database design according to some embodiments.

[0024] Figure 19 It is an apparatus or platform according to some embodiments.

[0025] Figure 20 This is a table portion of the workload prediction data storage according to some embodiments.

[0026] Figure 21 A tablet computer according to some embodiments is shown. Detailed Implementation

[0027] In the following detailed description, numerous specific details are set forth in order to provide a thorough understanding of the embodiments. However, those skilled in the art will understand that the embodiments can be practiced without these specific details. In other instances, well-known methods, processes, components, and circuits have not been described in detail so as not to obscure the embodiments.

[0028] One or more specific embodiments of the invention will now be described. To provide a concise description of these embodiments, not all features of the actual implementation may be described in the specification. It should be understood that, as in any engineering or design project, numerous implementation-specific decisions must be made in the development of any such implementation to achieve the developer’s specific objectives, such as compliance with system-related and business-related constraints, which may vary from implementation to implementation. Furthermore, it should be understood that such development efforts may be complex and time-consuming, but remain routine tasks of design, fabrication, and manufacturing for those skilled in the art who benefit from this disclosure.

[0029] Real-world applications are characterized by workloads where the arrival rate and parameterization of Structured Query Language (“SQL”) statements can change over time. Without continuously adjusting the physical layout to reflect such workload drift, memory consumption and performance can significantly degrade. Existing methods for automating physical database design, particularly table partitioning advisors, fail to address workload drift because they propose physical layouts based on observed workloads. Some embodiments described herein provide a framework for continuously adapting the physical layout under workload drift. Some embodiments predict future workloads based on workload drift detected in arrival rates and SQL statement parameterizations. The predicted workload is then fed into a physical database design advisor. Compared to existing methods, this approach, using real-world applications, enables accurate prediction of future workloads and significant hardware cost reductions for commercial cloud databases.

[0030] The parameterized SQL statement flow (SELECT, INSERT, UPDATE, DELETE, ...) is considered as a workload, where each statement is contained in a host variable assigned a value at runtime by the parameter. Workload drift is then characterized by changes in the statement arrival rate or the time-varying parameter values ​​assigned to the host variable. Whenever workload drift is not resolved promptly, the current physical layout may no longer be optimal, leading to a significant increase in workload costs.

[0031] A straightforward approach to handling workload drift is to repeatedly feed observed workloads, for example, into a physical database design consultant at fixed intervals. This approach is inherently backward-looking, as the proposed physical layout lags behind workload drift and may already be suboptimal when data reorganization (e.g., table repartitioning) begins. In contrast, some embodiments described herein offer a forward-looking approach that uses predictions of future workloads to determine the new physical layout. Typical workload predictors are only able to predict future statement arrival rates. However, physical database design consultants, particularly table partitioning consultants, often rely on fine-grained workload statistics, such as SQL statement parameterization. Consequently, some embodiments described herein also predict future parameter values ​​assigned to host variables (in addition to statement arrival rates).

[0032] As a real-world example of workload drift, consider the historical test results stored in databases by many software development projects to identify vulnerabilities or authorize patches. For instance, the SAP® HANA development project maintains statistics from over 30 billion test runs. The corresponding test results database groups relevant test cases (e.g., TPC-H benchmark queries) into test profiles. Therefore, the statement "SELECT status FROM test_cases WHERE id_test_profile = :1" returns the status (e.g., running, executed successfully, or failed) of all test cases with a given test profile ID.

[0033] Figures 1A to 1B The table partition layout is shown. Specifically, Figure 1A It is the reverse approach 110, where the currently observed workload 111 ( ) used in layout Create partition and (Regardless of future workload 112) (What it looks like). It's important to note that in future workloads, the majority of statement instances will be above 72,154,000, with only a small number below 72,154,000. Therefore, The four pages are hot because only these pages are frequently accessed (and need to remain in DRAM), while All pages are cold because they are rarely accessed (and can be evicted to cheaper secondary storage). It contains four hot pages (which need to remain in DRAM) to handle statement instances higher than 72,154,000, while It has cold pages to handle statement instances below 72,154,000. Note that each partition 113 can be paired with multiple columns (e.g., one for status, one for id_test_profile, etc.). Figure 1A The data is correlated with the workload observed during a six-hour time window (from 08:00 to 14:00) on a typical workday (shown by the dashed line in the diagram). Each point represents a statement execution at a specific time (x-axis), assigning a specific parameter value (y-axis) to the host variable 1. Note that the workload drifts over time as the statement is instantiated later and the parameter value is larger.

[0034] Using the reverse approach (method 110), the table partitioning advisor proposes a layout. It groups frequently accessed records into hot partitions. And group all other records into the cold partition. In comparison, Figure 1B It is the forward-looking method 120, in which the predicted workload 121 ( ) used in layout Creating partitions in 123 , and (and Figure 1A Compared to this, it is closer to future workloads 122 ( (The appearance of) It includes two hot pages (which need to remain in DRAM) to handle statement instances higher than 72,164,000, while and It features cold pages to handle statement instances below 72,164,000. The proposed layout... Records that are frequently accessed are grouped into the hot partition P3, while records that are rarely accessed or never accessed are grouped into the cold partition. and To evaluate how the two methods 110 and 120 affect memory consumption, actual future workloads 122 can be performed in SAP® HANA's column store using two layouts 113 and 123. The data is stored on pages, not clustered by id_test_profile, and there is no index on id_test_profile. Figure 1B It shows and The `status` and `id_test_profile` columns in the profile categorize pages as either "hot" or "cold" (based on a five-minute rule). Overall, this aligns with the reverse approach suggested in method 110. In comparison, the forward-looking approach 120 suggests a different layout. The number of hot pages stored in DRAM has been reduced by half. The main reason is that, compared to... Large partition , Small and hot partitions This allows for more aggressive partition pruning during predicate selection evaluation, as most statement instances will exceed 72,164,000 in actual future workloads. Therefore, for most statement instantiations, only access to... partitions in and partitions in .because In It contains only two pages, and In Contains four pages, therefore with compared to, Memory consumption can be reduced by half.

[0035] Therefore, the objective of some embodiments may be to reduce the observed workload. Find the predicted workload This makes the predicted workload Near-future workloads However, when attempting to base decisions on observed workload... Predict future workloads Several challenges may arise. In real-world applications, various workload drift types have been identified (e.g., linear, exponential, recurring, static, and irregular). Furthermore, these drift types can overlap. For individual statements, arrival rates and parameter assignments to host variables can be affected by different drift types. Therefore, workload predictors must be able to handle multiple drift types (and at least some combinations thereof) at the arrival rate and assignment levels.

[0036] Furthermore, many real-world applications are characterized by constantly shifting workloads, necessitating periodic physical layout resizing. However, the data reorganization resulting from changing the physical layout (e.g., table repartitioning) can be extremely expensive. Therefore, changing the physical layout is only recommended when the expected benefits of reducing workload costs outweigh the costs of data reorganization.

[0037] To address these challenges, some embodiments described herein provide solutions such as Figure 2The system 200, shown, comprises two phases. In the first phase, a workload prediction framework 210 predicts future workloads. In the second phase, the predicted workloads, along with information about the current physical layout, are provided to a physical database design consultant 250, which can then automatically create a recommended future physical layout for the database. Note that the current physical layout can be used as input for the physical database design consultant (because a change in physical layout is only recommended if the expected benefits of workload cost reduction outweigh the cost of data reorganization). The term "automatically" can refer to a process requiring little or no human intervention. Figure 3 As shown in system 300, the workload prediction framework 310 can predict future statement arrival rates (“SAR”) 320 and assign future parameter values ​​to host variables 330 to create a predicted workload, which, along with information about the current physical layout, is provided to the physical database design consultant 350. Some embodiments may incorporate table repartitioning costs during the physical design recommendation phase. Furthermore, system 300 may allow the swapping or scaling of all components to support workload migration in new environments. Additionally, the two phases can be repeated periodically to ensure continuous adaptation of the physical layout.

[0038] As used herein, devices including those associated with system 300 and any other devices described herein can exchange information through any communication network, which can be one or more of a local area network (“LAN”), a metropolitan area network (“MAN”), a wide area network (“WAN”), a private network, a public switched telephone network (“PSTN”), a wireless application protocol (“WAP”) network, a Bluetooth network, a wireless LAN network, and / or an Internet Protocol (“IP”) network (such as the Internet, an intranet, or an extranet). Note that any device described herein can communicate through one or more of such communication networks.

[0039] The workload prediction framework 310 and physical database design consultant 350 can store information in and / or retrieve information from various data stores (e.g., various predictions or recommendations), which can be stored locally or remotely from the workload prediction framework 310 and / or physical database design consultant 350. Although Figure 3 The diagram illustrates a single workload prediction framework 310 and a physical database design advisor 350, but may include any number of such devices. Furthermore, various devices described herein may be combined according to embodiments of the invention. For example, in some embodiments, the workload prediction framework 310 and the physical database design advisor 350 may comprise a single device. The functionality of system 300 may be performed by a group of networked devices, such as those in a distributed processing or cloud-based architecture.

[0040] According to any of the embodiments described herein, an administrator can access system 300 via a remote device (e.g., a personal computer (“PC”), tablet, or smartphone) to view information about operational information and / or manage operational information. In some cases, an interactive graphical user interface (“GUI”) display allows operators or administrators to define and / or adjust certain parameters via a remote device (e.g., adjust prediction rules or logic, change...). and / or (Mapping elements to data sources, etc.) and / or providing or receiving automatically generated recommendations, results, or alerts associated with System 300.

[0041] Figure 4 It can be referenced Figure 2 and Figure 3 Method 400 is performed by some or all elements of the described systems 200, 300. The flowcharts described herein do not imply a fixed order of steps, and embodiments of the invention can be practiced in any feasible order. Note that any method described herein can be performed by hardware, software, or any combination of these methods. For example, a computer-readable storage medium may store instructions thereon that, when executed by a machine, result in performance according to any embodiment described herein.

[0042] In S410, the computer processor of the workload prediction framework can receive observed workload information associated with a database (e.g., DBaaS), such as a database implemented in a cloud computing environment. In S420, based on the observed workload information, the system can generate SAR predictions. In S430, based on the observed workload information, the system can also generate host variable assignment predictions. In S440, the system can then automatically create workload predictions for the database using the SAR predictions and host variable assignment predictions. In response to the workload predictions and the current physical layout, in S450, a physical database design advisor automatically generates a recommended physical layout for the database.

[0043] To discuss the problem of predicting future workloads based on observed workloads, the symbols listed in Table I will be used.

[0044]

[0045] It can be defined as A collection of parameterized SQL statements (e.g., SELECT, INSERT, UPDATE, and DELETE statements). Each statement Include A vector of host variables Triples It can be defined in timestamps Place has A vector of parameter values of Instantiation. Statement instantiation. Include Each assignment makes the parameter value timestamp The value is assigned to the host variable. ,in express The domain. Workload It can be defined as A collection instantiated by a statement. Furthermore, It can be defined to assign certain parameter values ​​to the workload at certain timestamps. Host variables in The set of all assignments (note, yes (part of)

[0046]

[0047] set up With four timestamps, The observed workload is defined as... , making : Define future workloads , making Some of the embodiments described herein are based on observed workloads. Find the predicted workload ,in This makes the predicted workload Future-like workloads .

[0048] Figure 5 This is an overview of a system 500 according to some embodiments. It consists of a workload prediction framework 510 and a physical database design consultant 550. The workload prediction framework 510 predicts future workloads based on observed workloads on a per-statement basis. Since it may be computationally infeasible to consider all statements in the observed workload for prediction, some embodiments only utilize statements in the SQL plan cache, as they typically represent more than 99% of the workload cost.

[0049] For each statement The system predicts future workload in two independent phases. In Phase I, SAR detection 521, SAR classification 522, and SAR prediction 523 are used to predict statement arrival rates. In Phase II, host variable assignment detection 531, host variable assignment classification 532, and host variable assignment prediction 533 are used to predict future parameter values ​​to be assigned to host variables. The predicted workload is then obtained by combining the results of the two phases. Advisor 550 uses this information, along with information about the current physical layout, to base its prediction on the workload. and current physical layout Propose a future physical layout with minimal combined workload and data reorganization costs. According to some embodiments, the workload prediction framework 510 and advisor 550 periodically repeat these steps to adopt the physical layout with small and inexpensive adjustments.

[0050] Figure 6 This is a more detailed system 600 according to some embodiments. As previously described, it comprises a workload prediction framework 610 and a physical database design consultant 650. The workload prediction framework 610 is based on observed workloads. To predict with SQL statements Associated future workloads. For each statement This method predicts future workloads based on SAR prediction and host variable assignment prediction. SAR prediction utilizes SAR detection621 and SAR classification622 (e.g., through a classifier). ) and SAR prediction 623 (e.g., via predictor) to Assigning future parameter values ​​to host variables; using host variable assignment for detection 631; classifying host variable assignment 632 (e.g., through a classifier). ) and host variable assignment prediction 633 (e.g., via predictor) to Then, the predicted workload can be obtained by combining the results of SAR and host variable assignment predictions. Consultant 650 will Along with information about the current physical layout The information is used together to propose a future physical layout with minimal combined workload and data reorganization costs. According to some embodiments, the workload prediction framework 610 and advisor 650 periodically repeat these steps to adopt a physical layout.

[0051] Note that workloads can change in various ways over time. For example, real-world application workloads may drift in a linear, exponential, recurring, static, or irregular manner.

[0052] Linear or exponential workload drift is characterized by a linear or exponential increase (or decrease) in the arrival rate of statements as parameter values ​​assigned to host variables, respectively. For example, the parameter value of the id_test_profile domain may grow linearly over time (e.g., as...). Figure 1A (See 111 for observed workloads). For example, when certain statements are instantiated exponentially more frequently than during the regular development phase, exponential drift may occur closer to a new version release of the system (or when new features are added to the system).

[0053] Because databases frequently interact with humans, workloads can follow recurring patterns. For example, to test a specific build, you can use the SQL statement "INSERT INTO install_sessions (id_make, id_server) VALUES(:1, :2)" to create a test environment called an "install session" on a dedicated test server. Figure 7 Example 700 shows the workload drift instantiation of this statement using a specific build ID (y-axis) at some point between December 11, 2020 and December 31, 2020 (x-axis on Figure 710). Two recurring drift patterns can be observed. First, there is a weekly pattern, where id_make alternates between a sharp increase on weekdays and a slight increase on weekends. Second, there is an annual pattern, where id_make grows more moderately around Christmas compared to other times of the month. Because id_make follows both recurring patterns and grows linearly, Figure 7 Example 700 shows two types of overlapping drift.

[0054] Static workloads are those where time drift is not observable. For example, the dimension table test_case_info represents existing tests (e.g., their SQL strings), and the statement "SELECT id FROM test_cases WHERE id_test_case_info = :1" returns all test case executions with a given test case ID from the fact table test_cases. Figure 8 Figure 810 illustrates a static workload of 800, showing all instances of the statement between 08:00 and 20:00 on a typical workday. No changes in parameter values ​​were observed during the considered time period. This could be expected, for example, when the same test is repeatedly executed over a long period to identify vulnerabilities or authorize patches.

[0055] Irregular workload drift is characterized by sudden and unexpected changes in statement arrival rates or parameter values. For example, occasional stress testing activities, in addition to regular testing, can cause irregular drift in statement arrival rates. Unlike other types of drift, irregular workload drift cannot be modeled or predicted. Nevertheless, some embodiments described herein can handle irregular workload drift.

[0056] Figure 9 This is a SAR prediction method 900 according to some embodiments. S910 begins by discretizing the observed workload to reduce noise and other short-term fluctuations. For this purpose, a discretization interval between two consecutive discrete timestamps can be used. To define a set of equally spaced timestamps. For example, It can be set to five minutes.

[0057] It can be defined as a start timestamp and an end timestamp. The set of equidistant timestamps between:

[0058] .

[0059] To calculate the observed statement arrival frequency, one can target the timestamp. Cluster falling into the interval Within the observed workload All statement instances in the observed workload. timestamp Observed statement arrival frequency at [location] It can be defined as:

[0060] .

[0061] Finally, the observed statement arrival rate can be defined as the observed workload. In the start timestamp and end timestamp , The frequency of arrival of a series of consecutive statements. In the observed workload. For statements The observed statement arrival rate (observed SAR) can be defined as:

[0062] .

[0063] Figure 10A and Figure 10B The relationship between the discrete Fourier transforms of SAR and observed SAR according to some embodiments is shown. In particular, Figure 10AThe discretized interval with five minutes is shown for the time between 08:00 and 14:00. 1010 observations of equidistant timestamps , its from Figure 1A The observed workload 111 was calculated. Note that there are wavy patterns with peaks at 08:30, 11:00 and 13:00.

[0064] Refer again Figure 9 In the S920, the system can detect which workload drift types exist in the observed SAR. According to some embodiments, for each drift type, a condition-based detector can be used in conjunction with the observed SAR. To detect linear workload drift, embodiments can use the Pearson correlation between a discrete timestamp series and a statement arrival frequency series (the two series are abbreviated as...). If the Pearson correlation coefficient Greater than or equal to the threshold Then the condition is met:

[0065] It is linear. .

[0066] To detect exponential workload drift, the implementation can utilize the fact that if the logarithm of a function grows linearly, then the function grows exponentially. Therefore, if the Pearson correlation coefficient between the discrete timestamp series and the logarithmic series of arrival frequencies for each statement is... Greater than or equal to the threshold (Note that logarithms only apply when...) Statement arrival frequency in If the application is applied to a series of timestamps but not to a discrete timestamp series, then the condition is met:

[0067] It is an exponential. .

[0068] To detect recurring workload drift, an embodiment may first compute a Discrete Fourier Transform (“DFT”), transforming the observed SAR from the time domain to the frequency domain. The system can then evaluate... Does the amplitude exist that is greater than or equal to the threshold? The sine curve. To specify the threshold. The implementation example can first determine the statements in the observed workload. Minimum and maximum observed statement arrival frequencies and Given a fixed threshold Obtained as Multiply by the maximum possible range, but at least Multiply by the minimum statement arrival frequency.

[0069]

[0070]

[0071]

[0072] It reappeared .

[0073] Figure 10B This shows the 1020 Discrete Fourier Transform (DFT). This is a diagram illustrating the mapping of amplitude to the period (in minutes) of a sine curve. Each point represents a sine curve with a given period (x-axis) and amplitude (y-axis). The arrival frequencies of the minimum (1498) and maximum (11491) statements are also depicted. Calculated threshold It can be seen that the amplitudes of the two sine curves with periods of 120 minutes and 180 minutes exceeded the threshold, which constitutes the re-emerging drift.

[0074] To identify static workloads, the implementation can check whether the observed SAR is stable, i.e., it only stabilizes at the average statement arrival frequency. Fluctuations within a narrow range in the vicinity. Given a fixed... If the observed statement arrival frequency and The root mean square error (“RMSE”) between them is less than or equal to Multiply Then the condition is met:

[0075]

[0076]

[0077] It is static. .

[0078] By definition, irregular workload drift is sudden and unexpected, so conventional conditions may be useless for handling these types of drift. According to some embodiments, a workload is classified as irregular if none of the above conditions are met for linear, exponential, recurring, or static drift. Note that the embodiments described herein are scalable, allowing additional conditions to be inserted to detect irregular drift.

[0079] To demonstrate how to use the above conditions to detect workload drift. Figures 11A to 11FSix SQL statements are shown. to ), observed SAR and conditions satisfied ( Still not satisfied () Due to space constraints, only the sine curves with maximum amplitude under the condition of recurring drift are presented in these figures. The following parameters were used: , , ,as well as Note that in some cases, multiple drift types are detected, for example, in Figure 11B In the process, exponential drift and recurring drift were detected.

[0080] Refer again Figure 9 In S930, conflicts between multiple detected workload drift types can be resolved, allowing observed SARs to be classified as a single drift type or a combination of different drift types that are compatible with each other. Figure 12 It is a directed acyclic graph (“DAG”) classifier 1200 that determines the workload drift type based on the use decision 1210 of some embodiments.

[0081] If exactly one drift type is detected, the classifier 1200 determines that the observed SAR is the detected drift type. If two or more drift types are detected, the classifier distinguishes between compatible and incompatible drift combinations. According to some embodiments, linear and recurring combinations are considered compatible (e.g., Figure 7 and Figure 11D In In contrast, because the exponential drift asymptotically contains linear drift, the two are incompatible combinations. In this case, classifier 1200 preferably has a strong Pearson correlation coefficient. The type of drift. Furthermore, exponential drift is superior to recurring drift because exponential drift can... It is represented by a sine curve (e.g., Figure 11B In ).

[0082] Table II shows the results for... Figures 11A to 11F The six SQL statements shown are for future discrete timestamps The classification results and predicted sentence arrival frequencies were analyzed. This was achieved using a 6-hour observation window and a 5-minute discretization interval. , The timestamp ranges between 0 and 71, while The timestamps start from 72. If none of these conditions are met, classifier 1200 determines that the observed SAR is irregular.

[0083]

[0084] Refer again Figure 9 In S940, the system can use observed SAR and previous classification results to predict future SAR. To this end, the system can perform predictions for each statement. Calculate future timestamps Predicted statement arrival frequency Then, the prediction Obtained as and The series of arrival frequencies of consecutive statements. According to some embodiments, the prediction is based on the classification of observed SAR:

[0085] Linear: Uses a linear approximation function obtained by ordinary least squares regression on the observed SAR.

[0086] Exponential: First, ordinary least squares regression is performed on the natural logarithm of the observed SAR. Future SARs are then calculated by raising α to the power of the predictions produced by a linear approximation function.

[0087] Reappearance: Amplitude greater than or equal to the threshold All sine curves are transformed into the sum of trigonometric functions in the time domain.

[0088] Static: using average observed statement arrival frequency As a prediction of the frequency of future statement arrivals.

[0089] Irregular: Similar to the reverse approach, we copy the observed SAR and paste it into the future.

[0090] The linear sum recurs: The sum of the linear function and the trigonometric function is calculated. The linear function is obtained by ordinary least squares regression on the observed SAR. The normalized observed SAR is then calculated by subtracting the linear function from the observed SAR. Finally, the normalized SAR is transformed... The amplitude is greater than or equal to the threshold. We obtain trigonometric functions from all the sine curves.

[0091] When dealing with recurring drift, sine curves with small amplitudes may be filtered out because they may be caused by short-term fluctuations or noise, thus leading to overfitting. Since irregular drifts cannot be modeled or predicted, the framework cannot know the future. Instead, the system can replicate the observed SAR into the future (e.g., a look-back approach). Because this method has relatively low overhead, the system can periodically run workload predictors and advisors (making it respond promptly to the results of irregular drifts).

[0092] Figure 13 This is a host variable assignment method 1300 according to some embodiments, used to predict the assignment of future parameter values ​​to host variables. For practical reasons, note that the system can predict the assignment of host variables independently of other host variables. To illustrate the main idea of ​​method 1300, consider... Figure 14A Figure 1410 is Figure 1A An abstract representation of the workload observed in the data. Each point represents a timestamp. (x-axis) to assign host variable parameter values Single assignment (y-axis) For now, understanding the following is sufficient: if the assignment occurs close to the start of the observation period, the assignment is classified as "indeterminate"; if... Assigning values ​​according to rules Then the assignments are classified as "regular" (e.g., these assignments are time-independent because they occur so frequently that no periodic / recurring pattern can be observed; therefore, they belong to static workloads); if No value has been assigned in the recent time interval. If the assignment is positive, it is classified as "fresh"; otherwise, it is classified as "subsequent". Furthermore, note that subsequent assignments usually occur shortly after fresh assignments (rarely much later). Finally, the regular assignments are distributed across the entire observed workload.

[0093] Refer again Figure 13 In S1310, the system can perform detection and classification. For assignment... To categorize, you can select a length of... The threshold range (where ) as the discretization interval Multiples of. According to some embodiments, the threshold range... Used to distinguish between time-dependent and time-independent assignments. Given a length of... The threshold range is assigned a value. Classified as:

[0094] if Then it is uncertain.

[0095] if Then it is a rule.

[0096] if Then it is fresh.

[0097] Otherwise, it will be a follow-up.

[0098] The same parameter value each Assignments where a time unit is assigned to the same host variable at least once are time-independent and therefore part of a static workload. Such assignments are categorized as regular. For example, in... Figure 14A In the middle, this is the parameter value. In contrast, there is no such situation. Repeated assignment of parameter values ​​in time units implies that the assignment is time-dependent and therefore part of workload drift. If the parameter values ​​in such assignments are... The first assignment after a time unit is classified as fresh; otherwise (the second, third, etc.), it is classified as subsequent. For example, in Figure 14A middle, timestamp The assignment is fresh because In the previous No value was assigned to the time unit, but The later assignment is subsequent. Finally, the interval... The assignments in the range are classified as indeterminate because the system cannot rule out the possibility that the fresh assignments in that range are actually continuations of previous assignments. The possibility of subsequent assignments following a previous fresh assignment.

[0099] In S1320, the system predicts future fresh assignments and models the distribution of subsequent assignments and rule-based assignments. For fresh assignments, the system can extrapolate the observed series of fresh assignments in the workload to the future, thereby predicting the future series of fresh assignments. For example, Figure 14B This is a diagram 1420 illustrating a fresh assignment with linear characteristics, which can be extrapolated to future predictions 1422 (e.g., from...). arrive ).

[0100] In the observed workload Host variables in The observed fresh assignment series (observed “SFA”) can be defined as The set of all fresh assignments in:

[0101] .

[0102] As mentioned earlier, the system can detect which types of drift exist in the observed SFA, and then use a classifier (such as...) Figure 12 A classifier in the algorithm is used to resolve conflicts between multiple detected drift types. For example, Figure 14A The SFA observed was classified as linear (as can be seen from...) Figure 14B (As can be seen from this). Then, the system can be based on... Using the results of previous classifications to predict future freshness assignments series.

[0103] The system can model the distribution of subsequent assignments of parameter values ​​relative to their corresponding fresh assignments. This model can then be used to predict future subsequent assignments relative to their predicted fresh assignments. To do this, the time difference between subsequent assignments and their corresponding fresh assignments is first formalized as a time offset between the two. Let... To provide the observed workload Host variables in The set of subsequent assignments. Subsequent assignments The time difference between its corresponding fresh assignment Defined as:

[0104] .

[0105] Note that for the same parameter value, there can be more than one fresh assignment. This can occur, for example, with recurring drifts. To handle such ambiguity, some embodiments only consider the time offset relative to the most recent corresponding fresh assignment.

[0106] Another special case is that there are no fresh assignments for subsequent assignments because fresh assignments occur at the start of the observed workload and are classified as indeterminate. For example, in Figure 14A In China, this is aimed at This occurs. In this case, the system can estimate the fresh assignment by extrapolating the observed SFA to the past.

[0107] A probabilistic quality function can be established to model the probability of the time difference between a subsequent assignment and its corresponding fresh assignment. Some embodiments focus on time intervals. Modeling the time difference. Some implementations use... As the interval length, this is solely for practical reasons, namely that the observed workload has already been measured in terms of length. The step size is discretized. According to some embodiments, the probability mass function... Defined as a host variable parameter values The time difference between subsequent assignments and their corresponding fresh assignments Falling into the range The probability within:

[0108] .

[0109] Figure 15 This is a probability mass function 1500 (y-axis shown on a logarithmic scale) according to some embodiments. Function 1500 shows the probability mass function for... Figure 1A The probability mass function of the observed workload Note that a larger time difference between fresh and subsequent assignments is associated with a lower probability. Note that the x-axis shows the relationship between... The time interval between 0 and 72 .

[0110] Finally, some embodiments can model the distribution of rule assignments based on the probability mass function. Using such a model, the system can then predict future rule assignments. Let... It is the observed workload give host variables The set of rules assigned. The probability mass function. g Defined as a host variable parameter values The probability of the rule assignment:

[0111] .

[0112] This prediction is based on two assumptions: first, the rule assignments to host variables in the observed workloads will continue in future workloads; second, the rule assignments are uniformly distributed. To stimulate these two assumptions, consider... Figure 1A The parameter values ​​of id_test_profile between 72,154,000 and 72,159,000 belong to the background work that checks the fragmentation of the tests. The goal of these works is to ensure that all tests are stable and produce deterministic results. Note that and Both are merely examples of this probability mass function. Both can be interchanged with any other probability distribution.

[0113] Table III contains the workloads that can be used to calculate forecasts. The algorithm expects the input to be used for each SQL statement. Predicted statement arrival rate and for statements Each host variable in Predicted freshness assignment Series and probability mass function and .

[0114]

[0115] The algorithm first Initialize to an empty set (line 1). Next, the algorithm iterates through all SQL statements. (Row 2) and all future equidistant timestamps (Line 3). Then, the system derives the value in the current interval from the predicted SAR. The number of statement instantiations to be predicted ,in Indicates the timestamp The statement arrival frequency at the location (line 4). For each statement instantiation to be predicted, the algorithm starts from... Uniformly sample random timestamps (Lines 5 and 6), and allocate a capacity of A vector of parameter values (Line 7). Finally, the system iterates over all host variables. To pass through parameter values Predict their assignments (lines 8 through 20) and instantiate the predicted statements. Add to the predicted workload (Line 21).

[0116] Some implementations assume that fresh assignments, subsequent assignments, and rule assignments in observed workloads and future workloads are the same. Therefore, in order to predict host variables... timestamp Future parameter values ​​at The system can first connect with them in The probability of generating a subsequent assignment is the same as the probability of the previous assignment (line 9). ,in Indicates in give host variables The set of subsequent (representing uncertain) assignments.

[0117] Host variables timestamp Parameter value at The prediction depends on which assignment category is extracted:

[0118] Fresh: at timestamp Generate parameter values Freshness assignment set to prediction Fresh assignment of values ​​to the series (line 12).

[0119] Subsequent assignments: The timestamps and parameter values ​​of subsequent assignments are modeled based on their corresponding fresh assignments. Subsequent assignments are generated in two steps: In the first step, the timestamp is extracted... The subsequent assignment generated at that point is related to its position. Time difference between corresponding fresh assignments More specifically, according to Given probability extraction interval (Line 14), then draw evenly from this interval. (Line 15), and calculate. As and The difference (line 16). In the second step, in Generate parameter values Freshness assignment set to prediction Subsequent assignments to the series of values ​​(line 17).

[0120] Rule-based: based on The given probability extraction parameter values Assign values ​​to the generation rules (line 19).

[0121] Figures 16A to 16C Showing random future timestamps assign host variables The predictions include rule-based assignment, fresh assignment, and subsequent assignment. The predictions are also shown. 1612. For example... Figure 16A As shown in line 1612, fresh assignment The value is obtained as Obtain. For subsequent assignments, such as... Figure 16B As shown in 1620, the time difference Shown as in and Extract from between. Subsequent assignment. The value is then obtained as Finally, the value assigned by the rule. It has already been assigned a value according to the rules. ,like Figure 16C As shown in 1630 (see also) Figure 14A In ).

[0122] The second phase of the framework feeds the predicted workload to physical database design advisors (e.g., table partitioning advisors). According to some embodiments, the table partitioning advisor for columnstores is minimized to... The main memory cost is expressed in US dollars, while satisfying all performance guarantees guaranteed in the Service Level Agreement (“SLA”). Although US dollars are used as an example here, note that the embodiment can be associated with any other currency or representation of monetary cost. This is particularly important for DBaaS providers, as the amount of DRAM provided dominates the hardware cost. Consultants can group frequently accessed data into hot column partitions that remain in main memory, while grouping infrequently accessed data into cold column partitions that can be moved to a cheaper storage tier.

[0123] Given (I) the workload in seconds as the performance SLA Maximum execution time (ii) workload Column partitioning Number of visits (iii) expresses the economic balance between storing data in DRAM and performing disk I / O on each access. Seconds rule (which takes into account recent DRAM and disk prices, capacity, and performance), if column partitions The frequency of being accessed exceeds every Seconds, then column partitioning Classified as hot:

[0124]

[0125] .

[0126] The classification of column partition C determines its cost. Column partitions with cold classifications can be pruned during the evaluation of the selected predicate (e.g., as referenced). Figure 1A and Figure 1B Therefore, they do not need to be stored in DRAM during workloads. Because disk I / O is performed on every access, their memory cost depends on the workload per disk I / O. The number of pages accessed and the cost in US dollars. In contrast, the column partitioning for hot classification depends on the DRAM cost in US dollars / byte / second and the column partition size in bytes. and the maximum workload execution time in seconds. :

[0127]

[0128]

[0129] .

[0130] According to some embodiments, the physical layout is changed only if the expected benefits of workload cost savings outweigh the cost of data reorganization, and the cost model may include table repartition costs. A straightforward approach is to calculate repartition costs globally (i.e., at the entire physical layout level). However, if the enumeration algorithm operates at the column partition level, the system can calculate the repartition cost for each column partition. Further embodiments may model repartition costs in dollars to enable comparisons with workload costs.

[0131] If future planning The column partitions listed in the text Already exists in the current layout In this case, the cost of repartitioning can be set to $0. Otherwise, By splitting or merging The partition is created from the available space. In this case, some assumptions might be made:

[0132] (1) Since column storage often uses read-optimized data structures that cannot be easily modified (e.g., compressed dictionaries), it is assumed that It was created from scratch.

[0133] (2) Write column partition Each record is from The data is read from the existing column partitions. Therefore, it is assumed that the total amount of data accessed during repartitioning is equal to the column partition size. Twice as much.

[0134] (3) To reduce the impact on performance SLA, fast repartitioning times are ensured by assuming that all data accessed during repartitioning is stored in DRAM.

[0135] (4) Assume the system demonstrates how to create column partitions. Repartitioning rate in bytes per second and for initialization Fixed overhead measured in seconds .

[0136] Based on these assumptions, the cost of repartitioning will first be defined, and then the system will combine the workload and the cost of repartitioning into column partitions. Of the total cost in US dollars:

[0137]

[0138]

[0139] As a second modification, the objective function can be modified. This relates to minimizing the observed workload. workload cost In contrast to some systems, some embodiments propose to predict workloads. Combined workloads and repartitioning costs Minimum future layout .

[0140]

[0141] A natural question arises during the optimization of this equation: at which future timestamp would repartitioning be beneficial? Generally, the cost of repartitioning can be viewed as a collateral that needs to be amortized from the benefits for the workload costs in future layouts. The predicted duration of the workload (i.e., the duration of the predicted workload) The longer the forecast period, the more likely it is that partition costs will need to be amortized. Conversely, the longer the forecast period, the less accurate the forecast will be. To resolve this conflict, some implementations calculate the forecast confidence factor based on the duration of the predicted workload. Multiply by the observation period .

[0142]

[0143]

[0144]

[0145] .

[0146] Figure 17 Illustrations are shown according to some embodiments Future database layout The effect of the choice 1700. The x-axis is shown to depend on The choice of future timestamp The y-axis shows the values ​​for... And two future plans and The total cost, denominated in US dollars. Due to rezoning costs, the costs of both future layouts are greater than $0 at the y-intercept. Note that... This is the best option for short-term forecasting because the rezoning costs of the two future layouts have not yet been amortized. In contrast, for medium- and long-term forecasts, and These are the best.

[0147] To integrate consultants into the predictive framework, a fixed observation period is assumed. and fixed prediction confidence factors As a prerequisite. Additionally, the start timestamp of the observed workload will be used. Initialize to the current timestamp and set the layout. Initialize to the current layout. Then, the system can use the observed workload's end timestamp... Calculated as And so on, waiting until that time to begin the first iteration of the framework. Using the algorithm in Table III, the system then bases its iterations on timestamps. and The observed workload between, calculated in the case of setting Start timestamp With set as End timestamp The predicted workload between [the two conditions]. Then, the predicted workload and the current layout [are considered]. It can be fed into the physical database design consultant, and the system can switch to the suggested future layout. Note that the current layout can also be considered the best choice. Finally, we make... Increasing And then begin the next iteration of the framework.

[0148] During the test, the workload was used between 08:00 and 20:00 on a typical weekday. During this period, 401, 280, and 169 SQL statements were executed, respectively. Observation period. It was set to 360 minutes, and the predicted confidence factor was... It was set to 1.0. Additionally, a 5-minute discretization interval was used. And the parameters used to detect linear drift and exponential drift. and All were set to 0.8. To detect recurring drift and static workloads, [the following was done]. and Both are set to 0.1. These two parameters represent the maximum permissible fluctuation (and fluctuations less than 10% may be imperceptible). Finally, use... The error is approximated, and Set to 133 MB / s, d Set it to 0.16 seconds.

[0149] Most existing work uses a backwards approach as a baseline, where observed workloads, rather than predicted workloads, are fed into the advisor. To demonstrate the potential of the embodiments described here, a best-case analysis was performed, where actual future workloads were fed into the system. For all approaches, the layout was changed only if the expected benefits of workload cost savings outweighed the cost of table repartitioning.

[0150] Memory cost and performance experiments analyzed how the partitioning layout and the reverse-look-back approach proposed in the embodiments described herein alter the relationship between memory cost and workload performance. Since hardware cost is primarily determined by DRAM cost, the focus can be placed on memory consumption. It was observed that the embodiments described herein consistently outperform the reverse-look-back approach in terms of performance. For example, with sufficient available memory (i.e., high memory cost), the proposed framework can improve execution time by 1.4x compared to the reverse-look-back approach. The primary reason for the observed performance improvement and memory cost savings is that the frequently accessed data and infrequently accessed data are more clearly partitioned into hot and cold partitions compared to the reverse-look-back approach. This avoids cold data contaminating the buffer cache, allows for more aggressive partition pruning, and accelerates incremental merging.

[0151] The second experiment evaluated the accuracy of the implementation in predicting future workloads. For this, both statement arrival rates and the assignment of parameter values ​​to host variables were considered. The coefficient of determination was used as a measure of how well the observed and predicted statement arrival rates (observed and predicted assignments, respectively) matched the future statement arrival rates (future assignments, respectively). . A score of 1 indicates a perfect prediction, while negative values ​​(infinity) indicate an inaccurate prediction.

[0152] Assigning values ​​to the ten most expensive statements in the SQL plan cache, based on the predicted workload. The score was significantly higher compared to the observed workload. Score. Additionally, the assignment of predicted workload. The score is usually close to 1.

[0153] The third experiment analyzed the observation period. and prediction confidence factor Impact on prediction accuracy. Between 0.1 and 8.0. Calculation for Statements between 60 and 570 minutes of Scores. Generally, it has been observed that longer observation periods lead to higher scores. Score. Furthermore, the longer the prediction period, the higher the score. The larger it is, the less accurate the prediction becomes. It is expected that Greater than 1 results in poorer accuracy because it is no longer possible to detect recurring drifts with a period longer than the observation period. Selecting to be set to 360 minutes and be set to 1.0 both result in accurate predictions and amortization of the repartitioning costs due to the long prediction period.

[0154] The fourth experiment evaluated the accuracy of the revised version of the advisor's evaluation form for repartitioning costs. For this purpose, 363 random partition layouts were generated for different SQL database tables, and then existing partitions were randomly split and merged within these layouts. For each table repartitioning considered, when measuring the repartitioning costs, was set to 133 MB / second, and was set to 0.16 seconds. There was a strong correlation between the estimated repartitioning costs and the actual repartitioning costs, and the maximum error was 2.27.

[0155] The fifth and final experiment analyzed the time required for the framework to predict future workloads with an observation period between 60 and 360 minutes and set to 1.0. The range of the required prediction time was only between 1.2% and 3.2% of the observation period. Since the advisor also has a short optimization time, it can be concluded that the framework is suitable for practical use.

[0156] Figure 18 is a framework for workload prediction and physical database design showing 1800 according to some embodiments. The display 1800 includes a graphical representation 1810 or a dashboard, which may be used by an operator or an administrator to configure the components described herein (e.g., for a cloud computing environment). In particular, the selection of an element (e.g., via a touch screen or a computer mouse pointer 1890) may result in the display of a pop-up window containing more detailed data. The display 1800 may also include a user-selectable "Save" icon 1820 for storing configurations and / or system maps (e.g., to data sources), and an "Update" icon 1830 for appropriately adjusting values.

[0157] Note that the embodiments described herein can be implemented using any number of different hardware configurations. For example, Figure 19 may be, for example, respectively associated with Figure 2 , Figure 3 , Figure 5 and Figure 6A block diagram of the apparatus or platform 1900 associated with systems 200, 300, 500, and 600 (and / or any other systems described herein). Platform 1900 includes a processor 1910 coupled to a communication device 1960, such as one or more commercial central processing units (“CPUs”) in the form of a single-chip microprocessor, the communication device 1960 being configured to communicate via a communication network (not in... Figure 19 (As shown in the diagram) to communicate. Communication device 1960 can be used, for example, to communicate with one or more databases 1924 (e.g., via communication network 1922), system administrators, etc. Platform 1900 also includes input devices 1940 (e.g., a computer mouse and / or keyboard for inputting, creating, and / or managing prediction algorithm parameters) and / or output devices 1950 (e.g., a computer monitor for presenting displays, transmitting recommendations, generating alerts, and / or creating reports on workload prediction, physical database design recommendations, etc.). According to some embodiments, mobile devices and / or PCs can be used to exchange information with platform 1900.

[0158] Processor 1910 also communicates with storage device 1930. Storage device 1930 may include any suitable information storage device, including combinations of magnetic storage devices (e.g., hard disk drives), optical storage devices, mobile phones, and / or semiconductor memory devices. Storage device 1930 stores program 1912 and / or workload prediction framework engine 1914 for controlling processor 1910. Processor 1910 executes instructions of programs 1912 and 1914 to operate according to any of the embodiments described herein. For example, processor 1910 may be associated with a cloud computing environment and receive observed workload information. Based on the observed workload information, SAR predictions may be generated by processor 1910. Furthermore, host variable assignment predictions may be generated by processor 1910 based on the observed workload information. Processor 1910 can then use the SAR predictions and host variable assignment predictions to automatically create workload predictions for the database. A physical database design consultant (e.g., a table partitioning consultant) may receive the workload predictions and, in response, automatically generate a recommended physical layout for the database (e.g., using a cost model, the current physical layout, and an objective function).

[0159] Programs 1912 and 1914 may be stored in compressed, uncompiled, and / or encrypted formats. Programs 1912 and 1914 may also include other program elements, such as operating systems, clipboard applications, database management systems, and / or device drivers used by processor 1910 to interface with peripheral devices.

[0160] As used herein, information may be “received” or “sent” to, for example, (i) a software application or module within platform 1900 from another device; or (ii) a software application, module, or any other source within platform 1900.

[0161] In some embodiments (such as) Figure 19 In the embodiment shown, storage device 1930 also stores workload prediction data storage 2000. Reference will now be made to... Figure 20 A detailed description is provided of an example of a database that can be used in conjunction with Platform 1900. Note that the database described herein is merely an example, and additional and / or different information may be stored therein. Furthermore, various databases may be split or combined according to any embodiment described herein.

[0162] Reference Figure 20 According to some embodiments, a table representing a workload prediction data store 2000 that can be stored at platform 1900 is shown. This table may include, for example, entries identifying workload predictions and physical database design changes for a cloud computing environment. The table may also define fields 2002, 2004, 2006, 2008, 2010, and 2012 for each entry. According to some embodiments, fields 2002, 2004, 2006, 2008, 2010, and 2012 may specify a database identifier 2002, an observed workload identifier 2004, SAR prediction data 2006, host variable assignment prediction data 2008, a workload prediction 2010, and a recommended physical layout for the database 2012. For example, the workload prediction data store 2000 can be created and updated when new observations are received, new DBaaS is implemented, etc.

[0163] Database identifier 2002 can be a unique alphanumeric label associated with the SQL database to be analyzed and / or optimized. Observed workload identifier 2004 can be a file associated with a large number of historical statements. SAR prediction data 2006 and host variable assignment prediction data 2008 can be automatically generated by any of the embodiments described herein (e.g., based on observed workload). Workload prediction 2010 can be created by combining SAR prediction data 2006 and host variable assignment prediction data 2008. The recommended physical layout for the database can be generated by an advisor based on the workload prediction and the current physical layout (e.g., an advisor associated with table partitioning, data compression, buffer pool size, another advisor based on workload access counters, etc.).

[0164] Therefore, the embodiments can provide accurate and efficient workload prediction, which can be used as input for physical database design consultants (e.g., in cloud computing environments). Various additional embodiments of the invention are described below. These embodiments do not constitute a definition of all possible embodiments, and those skilled in the art will understand that the invention is applicable to many other embodiments. Furthermore, although the following embodiments have been briefly described for clarity, those skilled in the art will understand how any changes (if necessary) can be made to the above apparatus and methods to adapt to these and other embodiments and applications.

[0165] Although specific hardware and data configurations have been described herein, it should be noted that any number of other configurations can be provided according to some embodiments of the invention (e.g., some information associated with the database described herein can be combined or stored in an external system). Furthermore, while some embodiments focus on specific types of statements and advisors, any embodiments described herein can be applied to other types of statements and advisors. Additionally, the displays shown herein are provided only as examples, and any other type of user interface can be implemented. For example, Figure 21 A tablet computer 2100 is shown, providing a framework for a display 2110 for workload prediction and physical database design. The display 2110 can be used, for example, to create recommended database tweaks to improve performance / cost in cloud computing or other environments. Furthermore, the display 2110 can be used to update and / or create prediction algorithms, layout recommendations, etc., via a "save" icon 2120.

[0166] The invention has been described with reference to several embodiments for illustrative purposes only. Those skilled in the art will recognize from this description that the invention is not limited to the described embodiments, but can be practiced with modifications and variations limited only by the spirit and scope of the appended claims.

Claims

1. A system associated with a cloud computing environment, comprising: a workload prediction framework, including: a computer processor, and a computer memory storing instructions that, when executed by the computer processor, cause the workload prediction framework to: receive observed workload information associated with a database in the cloud computing environment, generate a statement arrival rate (SAR) prediction based on the observed workload information, generate a host variable assignment prediction based on the observed workload information, and automatically create a workload prediction for the database using the SAR prediction and the host variable assignment prediction; and a physical database design advisor coupled to the workload prediction framework to receive the workload prediction and, in response to the workload prediction and a current physical layout of the database, automatically generate a recommended physical layout for the database.

2. The system of claim 1, wherein the workload prediction is associated with at least one of (i) linear workload drift, (ii) exponential workload drift, (iii) recurring workload drift, (iv) static workload, and (v) irregular workload drift.

3. The system of claim 2, wherein the workload prediction is based on a combination of multiple workload drift types.

4. The system of claim 1, wherein the SAR prediction is generated by an algorithm that: discretizes the observed workload, detects at least one workload drift type, classifies multiple workload drift types, and uses the observed SAR and the classified workload drift to predict future SAR.

5. The system of claim 1, wherein the host variable assignment prediction is generated by an algorithm that: uses threshold intervals to detect and classify assignments, and predicts future fresh assignments and models the distribution of subsequent assignments and rule assignments.

6. The system of claim 1, wherein the workload prediction is based on (i) rule assignments, (ii) fresh assignments, and (iii) subsequent assignments.

7. The system of claim 1, wherein the physical database design advisor is associated with at least one of (i) a table partitioning advisor, (ii) an index advisor, (iii) a data compression advisor, (iv) a buffer pool size advisor, and (v) another advisor based on workload-based access counters.

8. The system of claim 1, wherein the physical database design advisor is associated with a table partitioning advisor that utilizes a cost model, the current physical layout, and an objective function.

9. The system of claim 8, wherein the table partitioning advisor computes a repartitioning cost on a per-column partition basis.

10. The system of claim 9, wherein the table partitioning advisor proposes a future layout that minimizes a combination workload of the workload prediction and the repartitioning cost.

11. A computer-implemented method associated with a cloud computing environment, comprising: receiving, by a computer processor of a workload prediction framework, observed workload information associated with a database in the cloud computing environment; generating a statement arrival rate (SAR) prediction based on the observed workload information; generating host variable assignment predictions based on the observed workload information; automatically creating workload predictions for the database using the SAR predictions and the host variable assignment predictions; and automatically generating a recommended physical layout for the database by a physical database design advisor in response to the workload predictions and a current physical layout of the database.

12. The method of claim 11, wherein the workload predictions are associated with at least one of: (i) linear workload drift, (ii) exponential workload drift, (iii) recurring workload drift, (iv) static workload, and (v) irregular workload drift.

13. The method of claim 12, wherein the workload predictions are based on a combination of multiple workload drift types.

14. The method of claim 11, wherein the SAR predictions are generated by the following algorithms: discretizing the observed workload, detecting at least one workload drift type, classifying multiple workload drift types, and predicting future SARs using the observed SARs and the classified workload drifts.

15. The method of claim 11, wherein the host variable assignment predictions are generated by the following algorithms: detecting and classifying assignments using threshold intervals, and predicting future fresh assignments and modeling the distribution of subsequent and regular assignments.

16. The method of claim 11, wherein the workload predictions are based on: (i) regular assignments, (ii) fresh assignments, and (iii) subsequent assignments.

17. A non-transitory computer-readable medium storing instructions that, when executed by a processor, cause the processor to perform a method associated with a cloud computing environment, the method comprising: receiving, by a computer processor of a workload prediction framework, observed workload information associated with a database in the cloud computing environment; generating statement arrival rate (SAR) predictions based on the observed workload information; generating host variable assignment predictions based on the observed workload information; automatically creating workload predictions for the database using the SAR predictions and the host variable assignment predictions; and automatically generating a recommended physical layout for the database by a physical database design advisor in response to the workload predictions and a current physical layout of the database.

18. The medium of claim 17, wherein the physical database design advisor is associated with at least one of: (i) a table partitioning advisor, (ii) an index advisor, (iii) a data compression advisor, (iv) a buffer pool size advisor, and (v) another advisor based on workload-based access counters.

19. The medium of claim 17, wherein the physical database design advisor is associated with a table partitioning advisor that utilizes a cost model, a current physical layout, and an objective function.

20. The medium of claim 19, wherein the table partitioning advisor computes a repartitioning cost on a per-column partition basis.

21. The medium of claim 20, wherein the table partitioning advisor proposes a future layout that minimizes a combined workload of the workload predictions and the repartitioning cost.

Images