Database Reference
In-Depth Information
7.5.4 Reduced Redundancy and Storage Needs
The original SkyServer system utilizes indexes and replication to speed up
important disk-bound queries. All tables have primary and foreign key con-
straints supported by B-tree indexes, and many tables have covering indexes
created after careful workload analysis. Replicated tables are also used to
speed up some frequent classes of queries. For instance, the
PhotoTag
ta-
ble is a vertical partition of the
PhotoObjAll
table that stores redundantly
its most popular 100+ columns. The
SpecPhotoAll
table stores the most
popular of the columns from the precomputed join of photo and spectrum
tables.
In order to support the original queries we replaced the
PhotoTag
and
SpecPhotoAll
tables with views exploiting the advantages of the column-wise
storage of MonetDB. This replacement had little impact on the performance of
queries that involve those tables because of the column-wise storage organiza-
tion. However, generating the views was still worthwhile, saving approximately
10% of the storage needs.
The index support in MonetDB is limited to primary and foreign keys.
The system generates indexes on-the-fly when columns are touched for the
first time. The net effect of reducing data volume is that the storage needs of
MonetDB database image decreased by approximately 30%.
7.5.5 Flexibility
Although secondary access structures in row-wise storage systems improve
performance substantially in comparison to full table scans, they exhibit rel-
atively static behaviors with respect to changing workloads. Modern DBMSs
come with advanced database design-tuning wizards, which derive design rec-
ommendations using representative workloads. Due to its complexity, the
workload analysis is mostly performed o
ine and requires database adminis-
trator (DBA) competence to decide on the final database design. When the
workload changes, it is probable that the new, unanticipated queries are not
supported (or partially supported) by the existing indexes, which leads to sub-
optimal system performance. The typical solution is that the DBA monitors
the system functionality and periodically reruns the workload analysis and
modifies the supporting secondary structures.
Recently, online tuning tools have been proposed
59
that take the burden
from the DBA but still incur overhead for monitoring and creation of sec-
ondary structures. Dealing with this issue is completely avoided in MonetDB.
When the query load changes to incorporate new attributes, the execution
plans simply transfer to memory only the new columns of interest. This is
achieved without any storage, creation, or monitoring overhead for secondary
structures, but simply based on the architectural principles of the column-wise
storage systems.
