Towards Online Relational Schema Transformations

L. Wevers, M Hofstra, M Tammens, Marieke Huisman, Maurice van Keulen

Research output: Chapter in Book/Report/Conference proceedingConference contributionAcademic

1 Citation (Scopus)
21 Downloads (Pure)

Abstract

Current relational database systems are ill-equipped for changing the structure of data while the database is in use. This is a real problem for systems for which we expect 24/7 availability, such as telecommunication, payment, and control systems. As a result, developers tend to avoid making changes because of the downtime consequences. The urgency to solve this problem is evident by a multitude of tools developed in industry, such as pt-online-schema-change1 and oak-online-alter- table2. Also, MySQL recently added limited support for online schema changes3. Contributions: We want to draw the attention of the database community to the problem of online schema changes. We have defined requirements for online schema change mechanisms, and we have experimentally investigated existing solutions. Our results show that current solutions are unsatisfactory for complex schema changes. We propose lazy schema changes as a solution. Experimental Setup: To assess the performance and behaviour of existing mechanisms for on- line schema changes, we have developed an experiment based on the standard TPC-C benchmark. For each of the relational schema transformation classes that we have identified, we chose a rep- resentative transformation for the TPC-C schema. We perform the schema change online while the TPC-C benchmark is running, and measure the impact on the TPC-C transaction through- put. We have performed our experiment on PostgreSQL, which does not support online schema changes, MySQL, which supports basic online schema changes, and using pt-online-schema-change on MySQL, as a representative for tools that use triggers to allow online schema changes. Results: We found that existing solutions are inadequate except for the simplest of schema changes. Some single-relation transformations can be performed transactionally and online. How- ever, existing solutions do not allow schema transformations to be composed using transactions. As a result, in complex transformations, intermediate states can be exposed to database programs, which are non-trivial to handle correctly. A secondary problem is that these solutions are much slower than offline transformations, which may not be acceptable for certain applications. Proposal: We propose a more fundamental solution based on lazy schema transformations. The main idea is that schema changes can be described as a view on the existing schema, which can be materialized lazily to perform the schema transformation. The data in the new schema is immedi- ately accessible by computing parts of the view on demand. For a large number of cases we expect that this approach allows schema transformations without any downtime, and with minimal impact on running transactions, while the ACID properties are maintained. Moreover, lazy transforma- tions can naturally be composed as transactions, allowing complex online schema transformations. We are developing an implementation of these ideas based on a persistent functional language.
Original languageUndefined
Title of host publicationDutch-Belgian Database Day 2014
Place of PublicationAntwerp, Belgium
PublisherDutch-Belgian Database Day
Pages1
Number of pages1
ISBN (Print)not assigned
Publication statusPublished - 17 Oct 2014

Publication series

Name
PublisherDutch-Belgian Database Day

Keywords

  • EWI-25769
  • IR-94673
  • METIS-309917

Cite this

Wevers, L., Hofstra, M., Tammens, M., Huisman, M., & van Keulen, M. (2014). Towards Online Relational Schema Transformations. In Dutch-Belgian Database Day 2014 (pp. 1). Antwerp, Belgium: Dutch-Belgian Database Day.
Wevers, L. ; Hofstra, M ; Tammens, M ; Huisman, Marieke ; van Keulen, Maurice. / Towards Online Relational Schema Transformations. Dutch-Belgian Database Day 2014. Antwerp, Belgium : Dutch-Belgian Database Day, 2014. pp. 1
@inproceedings{0987791e699e46be90e92de1655504bd,
title = "Towards Online Relational Schema Transformations",
abstract = "Current relational database systems are ill-equipped for changing the structure of data while the database is in use. This is a real problem for systems for which we expect 24/7 availability, such as telecommunication, payment, and control systems. As a result, developers tend to avoid making changes because of the downtime consequences. The urgency to solve this problem is evident by a multitude of tools developed in industry, such as pt-online-schema-change1 and oak-online-alter- table2. Also, MySQL recently added limited support for online schema changes3. Contributions: We want to draw the attention of the database community to the problem of online schema changes. We have defined requirements for online schema change mechanisms, and we have experimentally investigated existing solutions. Our results show that current solutions are unsatisfactory for complex schema changes. We propose lazy schema changes as a solution. Experimental Setup: To assess the performance and behaviour of existing mechanisms for on- line schema changes, we have developed an experiment based on the standard TPC-C benchmark. For each of the relational schema transformation classes that we have identified, we chose a rep- resentative transformation for the TPC-C schema. We perform the schema change online while the TPC-C benchmark is running, and measure the impact on the TPC-C transaction through- put. We have performed our experiment on PostgreSQL, which does not support online schema changes, MySQL, which supports basic online schema changes, and using pt-online-schema-change on MySQL, as a representative for tools that use triggers to allow online schema changes. Results: We found that existing solutions are inadequate except for the simplest of schema changes. Some single-relation transformations can be performed transactionally and online. How- ever, existing solutions do not allow schema transformations to be composed using transactions. As a result, in complex transformations, intermediate states can be exposed to database programs, which are non-trivial to handle correctly. A secondary problem is that these solutions are much slower than offline transformations, which may not be acceptable for certain applications. Proposal: We propose a more fundamental solution based on lazy schema transformations. The main idea is that schema changes can be described as a view on the existing schema, which can be materialized lazily to perform the schema transformation. The data in the new schema is immedi- ately accessible by computing parts of the view on demand. For a large number of cases we expect that this approach allows schema transformations without any downtime, and with minimal impact on running transactions, while the ACID properties are maintained. Moreover, lazy transforma- tions can naturally be composed as transactions, allowing complex online schema transformations. We are developing an implementation of these ideas based on a persistent functional language.",
keywords = "EWI-25769, IR-94673, METIS-309917",
author = "L. Wevers and M Hofstra and M Tammens and Marieke Huisman and {van Keulen}, Maurice",
year = "2014",
month = "10",
day = "17",
language = "Undefined",
isbn = "not assigned",
publisher = "Dutch-Belgian Database Day",
pages = "1",
booktitle = "Dutch-Belgian Database Day 2014",

}

Wevers, L, Hofstra, M, Tammens, M, Huisman, M & van Keulen, M 2014, Towards Online Relational Schema Transformations. in Dutch-Belgian Database Day 2014. Dutch-Belgian Database Day, Antwerp, Belgium, pp. 1.

Towards Online Relational Schema Transformations. / Wevers, L.; Hofstra, M; Tammens, M; Huisman, Marieke; van Keulen, Maurice.

Dutch-Belgian Database Day 2014. Antwerp, Belgium : Dutch-Belgian Database Day, 2014. p. 1.

Research output: Chapter in Book/Report/Conference proceedingConference contributionAcademic

TY - GEN

T1 - Towards Online Relational Schema Transformations

AU - Wevers, L.

AU - Hofstra, M

AU - Tammens, M

AU - Huisman, Marieke

AU - van Keulen, Maurice

PY - 2014/10/17

Y1 - 2014/10/17

N2 - Current relational database systems are ill-equipped for changing the structure of data while the database is in use. This is a real problem for systems for which we expect 24/7 availability, such as telecommunication, payment, and control systems. As a result, developers tend to avoid making changes because of the downtime consequences. The urgency to solve this problem is evident by a multitude of tools developed in industry, such as pt-online-schema-change1 and oak-online-alter- table2. Also, MySQL recently added limited support for online schema changes3. Contributions: We want to draw the attention of the database community to the problem of online schema changes. We have defined requirements for online schema change mechanisms, and we have experimentally investigated existing solutions. Our results show that current solutions are unsatisfactory for complex schema changes. We propose lazy schema changes as a solution. Experimental Setup: To assess the performance and behaviour of existing mechanisms for on- line schema changes, we have developed an experiment based on the standard TPC-C benchmark. For each of the relational schema transformation classes that we have identified, we chose a rep- resentative transformation for the TPC-C schema. We perform the schema change online while the TPC-C benchmark is running, and measure the impact on the TPC-C transaction through- put. We have performed our experiment on PostgreSQL, which does not support online schema changes, MySQL, which supports basic online schema changes, and using pt-online-schema-change on MySQL, as a representative for tools that use triggers to allow online schema changes. Results: We found that existing solutions are inadequate except for the simplest of schema changes. Some single-relation transformations can be performed transactionally and online. How- ever, existing solutions do not allow schema transformations to be composed using transactions. As a result, in complex transformations, intermediate states can be exposed to database programs, which are non-trivial to handle correctly. A secondary problem is that these solutions are much slower than offline transformations, which may not be acceptable for certain applications. Proposal: We propose a more fundamental solution based on lazy schema transformations. The main idea is that schema changes can be described as a view on the existing schema, which can be materialized lazily to perform the schema transformation. The data in the new schema is immedi- ately accessible by computing parts of the view on demand. For a large number of cases we expect that this approach allows schema transformations without any downtime, and with minimal impact on running transactions, while the ACID properties are maintained. Moreover, lazy transforma- tions can naturally be composed as transactions, allowing complex online schema transformations. We are developing an implementation of these ideas based on a persistent functional language.

AB - Current relational database systems are ill-equipped for changing the structure of data while the database is in use. This is a real problem for systems for which we expect 24/7 availability, such as telecommunication, payment, and control systems. As a result, developers tend to avoid making changes because of the downtime consequences. The urgency to solve this problem is evident by a multitude of tools developed in industry, such as pt-online-schema-change1 and oak-online-alter- table2. Also, MySQL recently added limited support for online schema changes3. Contributions: We want to draw the attention of the database community to the problem of online schema changes. We have defined requirements for online schema change mechanisms, and we have experimentally investigated existing solutions. Our results show that current solutions are unsatisfactory for complex schema changes. We propose lazy schema changes as a solution. Experimental Setup: To assess the performance and behaviour of existing mechanisms for on- line schema changes, we have developed an experiment based on the standard TPC-C benchmark. For each of the relational schema transformation classes that we have identified, we chose a rep- resentative transformation for the TPC-C schema. We perform the schema change online while the TPC-C benchmark is running, and measure the impact on the TPC-C transaction through- put. We have performed our experiment on PostgreSQL, which does not support online schema changes, MySQL, which supports basic online schema changes, and using pt-online-schema-change on MySQL, as a representative for tools that use triggers to allow online schema changes. Results: We found that existing solutions are inadequate except for the simplest of schema changes. Some single-relation transformations can be performed transactionally and online. How- ever, existing solutions do not allow schema transformations to be composed using transactions. As a result, in complex transformations, intermediate states can be exposed to database programs, which are non-trivial to handle correctly. A secondary problem is that these solutions are much slower than offline transformations, which may not be acceptable for certain applications. Proposal: We propose a more fundamental solution based on lazy schema transformations. The main idea is that schema changes can be described as a view on the existing schema, which can be materialized lazily to perform the schema transformation. The data in the new schema is immedi- ately accessible by computing parts of the view on demand. For a large number of cases we expect that this approach allows schema transformations without any downtime, and with minimal impact on running transactions, while the ACID properties are maintained. Moreover, lazy transforma- tions can naturally be composed as transactions, allowing complex online schema transformations. We are developing an implementation of these ideas based on a persistent functional language.

KW - EWI-25769

KW - IR-94673

KW - METIS-309917

M3 - Conference contribution

SN - not assigned

SP - 1

BT - Dutch-Belgian Database Day 2014

PB - Dutch-Belgian Database Day

CY - Antwerp, Belgium

ER -

Wevers L, Hofstra M, Tammens M, Huisman M, van Keulen M. Towards Online Relational Schema Transformations. In Dutch-Belgian Database Day 2014. Antwerp, Belgium: Dutch-Belgian Database Day. 2014. p. 1