Optimizing databases in the cloud based on performance and cost savings

Abstract: As cloud service providers becomes more prevalent, so does questions related to cost efficiency of hosted resources. Payment models for cloud hosted resources tend to be either subscription based or pay-as-you-go for computing resources, in this case for compound metrics of CPU, Data IO and Log IO. In order to find necessary resource provisioning, previous methods have tended towards observing the utilization of these compound processing units and deciding based of some utilization threshold. This report aims to find metrics which can methodically present the state of a hosted database as well as suggest whether the current demand for resources is necessary. To do this, first a collection of metrics are found and analyzed in relation to how they present the database state. Then optimizations are done, as suggested by chosen metrics, to make the database efficient enough to require less processing power. From these experiments, it is clear that the behaviour of a less provisioned database come with indirect changes to query processing. As less memory is available, the reliance on reading data from disk becomes more prominent, leading to less efficient execution. As this occurs for many queries that run concurrently, wait times become a more dominant part of execution for overutilized resources. The execution plan for processing a query depends on the predicted impact estimation for available resources which can drastically change the nature of execution. If more resources are provided, and statistics related to previous resource provisioning is available, it is possible that some query performance degrades with more available resources. Essentially, the results of these experiments is that finding over-/under utilized resources depend on not only considering the utilization of resources but also wait times, and limitations to executions as a result of the available resources. The metrics suggested for optimizing the databases showed promising results, but the impact of the optimization remain hard to predict. This limits possibilities to choose changes that will lead to cost reductions under the limitations set by the cloud service provider.