Tun-O_CM: A model-driven approach to support database tuning decision making

Highlights

• The development of a DSS depends on issues that affect DB performance, which is addressed by DB tuning decisions.

• DB tuning is essentially a configuration management (CM) task and a given configuration decision may impact others.

• The Tun-O_cm model provides formal and platform-independent knowledge that fosters interoperability among different DBMS.

• Tun-O_cm increases auditability and enables predictive analysis of the impact of tuning actions on existing structures.

• An ontology pattern language for CM can be applied to any DSS domain where part of the purpose is a configuration problem.

Abstract

Database tuning is a task executed by Database Administrators (DBAs) based on their practical experience and on tuning systems, which support DBA actions towards improving the performance of a database system. It is notoriously a complex task that requires precise domain knowledge about possible database configurations. Ideally, a DBA should keep track of several Database Management Systems (DBMS) parameters, configure data structures, and must be aware about possible interferences among several database (DB) configurations. We claim that an automatic tuning system is a decision support system and DB tuning may also be seen as a configuration management task. Therefore, we may characterize it by means of a formal domain conceptualization, benefiting from existing control practices and computational support in the configuration management domain. This work presents Tun-O_CM, a conceptual model represented as a well-founded ontology, that encompasses a novel characterization of the database tuning domain as a configuration management conceptualization to support decision making. We develop and represent Tun-O_CM using the CM-OPL methodology and its underlying language. The benefits of Tun-O_CM are discussed by instantiating it in a real scenario.

Introduction

Database tuning is a continuous process whose goal is to increase the performance of applications accessing a database. It is typically executed by a team of Database Administrators (DBAs). They are responsible for adjusting and experimenting with several combinations of parameters that impact the physical characteristics (e.g., buffer size) of a computational environment or even the amount of data transferred between disk and memory for each disk access or the number of checkpoint segments. They also perform several related tasks, such as deciding where data should be physically allocated across distributed platforms, creating and maintaining secondary data access structures. In addition, DBAs typically deal with a lack of standards at the physical database level due to the existence of a highly heterogeneous and competitive industry. There are specificities at each Database Management System (DBMS) implementation that impact on the physical design of the database and on the choice of adequate access structures to improve the performance of the information systems connected to a DBMS. Additionally, the DBA decision needs to be disseminated to the entire team, as each decision may impact the computational environment and, consequently, future decisions.

There are many heuristics proposed in the database literature to help DBAs improve the performance of DBMSs [[1], [2], [3], [4], [5], [6]]. However, their implementation typically requires commands or parameters that are native to particular DBMSs. Consequently, there is a demand for DBAs to acquire a large amount of specific knowledge, which impairs the interoperability among different DBMS and the management of data infrastructures composed by heterogeneous DBMS platforms. This scenario is increasingly common in modern organizations, especially in the case of data service providers, making the DB tuning task even more difficult.

It is crucial to establish a common conceptualization to address these issues among DBAs involving DBMSs elements and heuristics definitions, at a level that makes it independent of specific DBMSs. This common conceptualization also facilitates the understanding of each decision made, either by the team or by the tuning system throughout the tuning task. It can also enable semantic support for their automatic implementation in different DBMS platforms.

A complicating factor is that organizations are increasingly outsourcing their physical data storage to cloud environment providers. In this case, performance parameter adjustments need to be defined and controlled through service level agreements (SLAs). There is no control over some physical configurations, which further complicates the configuration management of the DBMS. Moreover, some parameters may influence and even preclude the use of others. For example, the PostgreSQL DBMS has two settings: shared_buffers and checkpoint_segments. The shared_buffers configuration parameter determines how much memory is dedicated to PostgreSQL to use for caching data. The checkpoint_segments parameter is the maximum number of log file segments between automatic WAL (Write-Ahead Logging) checkpoints. PostgreSQL recommends for larger settings of shared buffers a corresponding increase in checkpoint_segments to spread out the process of writing vast quantities of new or changed data over a larger extended time [7].

It is thus necessary to construct a precise and extensible conceptual framework to support corporate data management strategies that establish quality criteria at the physical level. Its precision enables the translation of data quality constraints to the physical level as accurately as possible without loss of semantics. Its extensibility allows such limitations to be particularized for each operational environment (operating system, DBMS, memory architecture, distributed architecture) available in the organization. Each physical data management platform may be semantically integrated into a defined conceptual backbone. For this, a data model is used as an abstraction to provide a high-level representation of this universe of interest.

This paper proposes Tun-O_CM, a model-driven approach to support database tuning decision-making adopting a configuration management perspective. To build Tun-O_CM, we have used ontological analysis and an ontology pattern language (OPL) to support our conceptual model development strategy. OPLs have been largely used for constructing expressive models through the reuse of well-grounded and validated fragments (patterns), improving the models quality and speeding up the development process. We derive the proposed model using CM-OPL, which is a specific ontology pattern language defined for this task and applied to the configuration management domain. The proposal is evaluated through a proof of concept by instantiating the proposed model in an actual scenario, showing that the modeling process becomes more agile, precise, and with fewer ambiguities. In addition, Tun-O_CM allows tracking each step taken during the tuning process until a decision is made, thus its impacts may be audited and assessed.

This paper is organized as follows. Section 2 characterizes the database self-tuning system as a decision support system and Section 3 discusses it from a configuration management perspective. Section 4 presents an overview of CM-OPL, our proposed OPL applied to the CM domain. Section 5 illustrates how CM-OPL was used to develop Tun-O_CM, the proposed database tuning model that we instantiate for evaluation purposes. Section 6 discusses how the perspective of the CM domain can help the database tuning task. Finally, Section 7 concludes this paper.