Partitioning as a Database Tuning Technique in Relational Databases

• Ana Carolina Almeida Rio de Janeiro State University (UERJ)
• Antony Seabra de Medeiros Pontifical Catholic University of Rio de Janeiro (PUC-Rio)
• Rogério Luís de Carvalho Costa Pontifical Catholic University of Rio de Janeiro (PUC-Rio)
• Sérgio Lifschitz Pontifical Catholic University of Rio de Janeiro (PUC-Rio)

DOI: https://doi.org/10.5753/sbbd.2017.171414

Abstract

Table partitioning is usually applied for distributing data. It may also be considered for performance issues, since it allows full scans involving only those partitions that satisfy the queries predicates. This work presents heuristics that select a partitioning strategy for each table and estimate their workload benefits. We also present some studies on the use of our proposed heuristics with the TPC-H benchmark. We show that partitioning can be effectively used as a first-order database tuning technique.

Keywords: Table partitioning, Heuristics, Database Tuning

References

Agrawal, S.; Narasayya, V. & Yang, B. (2004). Integrating Vertical and Horizontal Partitioning into Automated Physical Database Design. Anais da ACM International Conference on Management of Data (SIGMOD), pp. 359–370.

Almeida, A.C.B.; Brayner, A., Monteiro, J.M.; Lifschitz, S.; & Oliveira, R.P. (2015). DBX: um framework para auto-sintonia fina baseado em planos hipotéticos. Anais do SBBD Sessão de Demos, pp.149-154.

Bellatreche L., Schneider M., Lorinquer H. & Mohania M. (2004). Bringing Together Partitioning, Materialized Views and Indexes to Optimize Performance of Relational Data Warehouses, Anais da International Conference on Data Warehousing and Knowledge Discovery. DaWaK, pp. 15-25.

Costa, R. L. C.; Lifschitz, S.; Noronha, M. & Salles, M.V. (2005). Implementation of an agent architecture for automated index tuning. Anais da International Conference on Data Engineering (ICDE) Workshop on Self-managing Database Systems, pp. 1215.

Curino, C.; Jones, E.; Zhang, Y. & Madden, S. (2010). Schism: a workload-driven approach to database replication and partitioning. J. of PVLDB 3, I1-2, pp. 48–57.

Medeiros, A. S. (2017). Particionamento com Ação de Sintonia Fina. Dissertação de Mestrado, Departamento de Informática, PUC-Rio.

Monteiro, J. M. (2008). Uma abordagem não intrusiva para a manutenção automática do projeto físico de bancos de dados. Tese de Doutorado, Departamento de Informática, PUC-Rio.

TPC (2017). TPC-H Benchmark, http://www.tpc.org/tpch [Acessado em 24/05/2017].

Valduriez, P. & Özsu, M.T. (2011). Principles of Distributed Database Systems. 3rd. edition. Springer.

Wang, X.; Chen, J.; & Du, X. (2013). ASAWA: An Automatic Partition Key Selection Strategy. APWeb LNCS 7808, pp. 609–620.