Predicting database system performance

Abstract

A prediction system may perform capacity planning for one or more resources of a database systems, such as by understanding how different workloads are using the system resources and/or predicting how the performance of the workloads will change when the hardware configuration of the resource is changed and/or when the workload changes. The prediction system may use a detailed, low-level tracing of a live database system running an application workload to monitor the performance of the current database system. In this manner, the current monitoring traces and analysis may be combined with a simulation to predict the workload's performance on a different hardware configuration. More specifically, performance may be indicated as throughput and/or latency, which may be for all transactions, for a particular transaction type, and/or for an individual transaction. Database system performance prediction may include instrumentation and tracing, demand trace extraction, cache simulation, disk scaling, CPU scaling, background activity prediction, throughput analysis, latency analysis, visualization, optimization, and the like.

1 - 20 . (canceled) 
     
     
         21 . A method comprising:
 generating a trace of at least one transaction of a live workload in a database system, the trace indicating at least one of buffer cache accesses, I/O requests, database scheduler events, operating system scheduler events, or stored procedure invocations on current hardware resources of the database system;   generating an I/O request stream of a virtual hardware configuration based upon the trace, the virtual hardware configuration reflecting at least one hardware resource that is different than the current hardware resources of the database system;   generating an I/O service time of the at least one transaction on the virtual hardware configuration based upon the generated I/O request stream;   generating a CPU service time of the at least one transaction on the virtual hardware configuration based upon the trace; and   predicting performance of the database system having the virtual hardware configuration based on the I/O service time and the CPU service time.   
     
     
         22 . The method according to  claim 21 , wherein the trace is independent of speed and capacity of the current hardware resources of the existing database system. 
     
     
         23 . The method according to  claim 21 , further comprising:
 suggesting an improved hardware configuration for the database system based upon the predicted performance.   
     
     
         24 . The method according to  claim 21 , further comprising:
 generating graphics that reflect the predicted performance of the database system having the virtual hardware configuration.   
     
     
         25 . The method according to  claim 24 , wherein the generated graphics comprise a graph reflecting a mean amount of CPU time of the database system having the virtual hardware configuration. 
     
     
         26 . One or more computer readable storage media containing computer readable instructions that, when executed, perform acts comprising:
 generating a trace of at least one transaction of a live workload on current hardware resources of an existing database system, the current hardware resources of the existing database system including a memory having a buffer cache with a current size, the trace indicating buffer cache accesses to the buffer cache;   simulating behavior of the buffer cache for a virtual hardware configuration of the existing database system using the trace, the virtual hardware configuration reflecting a new buffer cache size that is different than the current size of the buffer cache, the simulated behavior including buffer cache hits and buffer cache misses with the new buffer cache size; and   determining a simulated buffer cache hit rate or buffer cache miss rate based on the simulated behavior of the virtual hardware configuration reflecting the new buffer cache size.   
     
     
         27 . The one or more computer readable storage media according to  claim 26 , wherein the buffer cache accesses include one or more of prefetch requests, blocking accesses, write latches, commits, synchronous write requests, cache evictions, or asynchronous writebacks. 
     
     
         28 . The one or more computer readable storage media according to  claim 26 , wherein the buffer cache accesses include:
 prefetch requests,   blocking accesses,   write latches,   commits,   synchronous write requests,   cache evictions, and   asynchronous writebacks.   
     
     
         29 . The one or more computer readable storage media according to  claim 26 , wherein the trace excludes buffer eviction events. 
     
     
         30 . The one or more computer readable storage media according to  claim 26 , wherein the trace excludes input/output (I/O) events. 
     
     
         31 . The one or more computer readable storage media according to  claim 26 , wherein the trace comprises a reference trace extracted from a demand trace. 
     
     
         32 . The one or more computer readable storage media according to  claim 26 , wherein the new buffer cache size is larger than the current size of the buffer cache. 
     
     
         33 . The one or more computer readable storage media according to  claim 26 , wherein the simulating comprises replaying the trace and annotating the buffer cache accesses as the buffer cache hits or the buffer cache misses with the new buffer cache size. 
     
     
         34 . The one or more computer readable storage media according to  claim 26 , the acts further comprising:
 plotting the simulated buffer cache miss rate on a graph.   
     
     
         35 . The one or more computer readable storage media according to  claim 34 , wherein a first axis of the graph reflects different buffer cache sizes and a second axis of the graph reflects different buffer cache miss rates. 
     
     
         36 . A method comprising:
 accessing a trace of at least one transaction of a live workload on current hardware resources of an existing database system, the current hardware resources of the existing database system comprising one or more of a current disk or a current central processing unit (CPU);   predicting at least one of:   an input/output (I/O) service time for a new disk to satisfy I/O requests in the trace, or   a computation time for a new CPU to satisfy computations in the trace; and   predicting throughput of the existing database system when at least one of:   the new disk is added to the existing database system, the throughput being predicted based on the predicted I/O service time for the new disk, or   the new CPU is added to the existing database system, the throughput being predicted based on the predicted computation time for the new CPU.   
     
     
         37 . The method according to  claim 36 , further comprising predicting the I/O service time for the new disk by scaling using disk bandwidth for the new disk. 
     
     
         38 . The method according to  claim 36 , wherein the I/O service time is predicted as a function of one or more of a number of cylinders of the new disk or seek times of the new disk. 
     
     
         39 . The method according to  claim 36 , further comprising predicting the computation time for the new CPU by scaling using processor speed for the new CPU. 
     
     
         40 . The method according to  claim 36 , further comprising predicting a background computation time for the new CPU to satisfy background requests.