Master of Philosophy in Computer Science
School of Computer Science and Software Engineering
Noon, Nan Noon, Automated performance tuning of database systems, Master of Philosophy in Computer Science thesis, School of Computer Science and Software Engineering, University of Wollongong, 2017. https://ro.uow.edu.au/theses1/34
One of the challenging tasks for database administrators is tuning database systems within a short period of time to make quick decisions. Automated performance tuning of database systems improves performance, reduces the cost of administra- tors, reduces high workload levels and decreases the number of tasks for database administrators, because database administrators need more help from database servers when they tune the performance of database systems to reduce the high workload level. To reduce high workload, tuning plans have to be implemented within the low workload time. Sometimes, low workload can be higher than high workload because of implementations of tuning plan within low workload time. To prevent that case, balancing the workload is the best idea. This work considers how to automatically balance workloads to achieve the best performance out of database systems. This thesis presents insights on how to generate vertical partitions based on a predicted workload structure. In particular, we propose that the algorithms analyse the predicted workload and divide the workload length into smaller time frames. Next, the algorithms generate optimized vertical partitions for each time frame. After that, we use multi-layer transient and persistent storage devices to allocate the resources that require implementing the vertical partitions. Based on the vertical partitions, the algorithms distribute the resources required to implement the vertical partitions. After that, algorithms generate the best optimized plans and implement the plans within a low workload time to reduce high workload.
In this thesis, we propose index-only processing to implement vertical partition. There are some rules applied when algorithms implement the plans, and the plans are tracked so that they do not go over the target workload level. The target work- load level limits the level to achieve a balanced workload. Finally, we demonstrate the results of experiments that show the best performance of database systems by using index-only processing over multi-layer storage devices for internal query processing.