Databases are growing continuously and making it costly to retrieve the data due to large number of disk I/O’s necessary. Although if we optimize the database for efficiency in the beginning, as the time passes it losses efficiency due to different query patterns. This leads to the necessity of tuning the database every now and then, when the performance falls below a certain level. Though we can afford a DBA, he should be controlling lots of functions at a time in order to keep the database efficient. So there is a need of self-manageable databases which lead to active research in this field.
One mechanism by which we can reduce the query cost is by decreasing the number of disk I/O's by partitioning a database. Partitioning allows the application to reduce the number of disk I/O’s by fetching only the required information for the query to be processed. The various types of partitioning techniques are:
- Horizontal partitioning
- Vertical partitioning
- Mixed Partitioning
One of the major downside of horizontal partitioning is unnecessary readings of attributes that are not queried by the queries are unavoidable. As in vertical partitioning we will only read those attributes queried by the queries’, which reduces the data read from the disk. Verical Partitioning can be implemented in a static or dynamic fashion. Dynamic partitioning algorithms don’t require user intervention. The database will have an initial configuration on which the queries will run. The algorithm monitors the database continuously using some performance metrics, and triggers the partitioning algorithm to repartition when the performance falls below a specific threshold.
This project deals with 3 dynamic vertical partitioning algorithms namely:
-
AutoStore - It is a dynamic online vertical partitioning technique which dynamically monitors the query workload and clusters the partitioned units. It also analyzes the affinity matrix at regular intervals and decides whether to create partitions or not. The salient feature of this algorithm is that it keeps the data access unaffected while monitoring query workload and analyses the partitions.
-
DYVEP - To retrieve better query results, a new algorithm, DYVEP was designed which adapts the vertical partitioning scheme as per the changes in query workload. Whenever a change in database is realized, the algorithm triggers active rules, which react to the set events generated by the users and the database reacts accordingly.
-
SMOPD - It is a dynamic vertical database partitioning system which has the ability to re-partition the database when the performance of the database is considerably low. This algorithm uses AutoClust algorithm, if the partitioning solution of the database is not good and automatically repartitions the database.
All algorithms are implemented on MICROSOFT SQL SERVER 2014 installed on a single node and used a TPC-H 1GB dataset generated by the TPC-H dbgen program, which comes with the benchmark download. ‘lineitem’ table of TPC-H dataset is used for carrying out the analysis. ‘lineitem’ is the largest of all the tables in TPC-H. Following are the characteristics of lineitem table:
- No. of tuples: ~ 6 million.
- Columns: 16 attributes.
The comparison study has been done on the following performance metrics:
- Analysis time in seconds.
- Workload runtme in seconds.
- Percentage of improvement of LINEITEM table.