Advertisement

Impact of Storage Space Configuration on Transaction Processing Performance for Relational Database in PostgreSQL

  • Mateusz SmolinskiEmail author
Conference paper
Part of the Communications in Computer and Information Science book series (CCIS, volume 928)

Abstract

An information system often uses relational database as a data store. One of the reasons for the popularity of relational databases is transaction processing, which helps to preserve data consistency. The configuration of storage space in database management system has significant influence on efficiency of transaction processing, which is crucial to workload processing in information system. The choice of block device and filesystem for local storage in database management system affects transactions performance in relational databases. This paper shows what impact on database transaction efficiency has usage of modern hard drive versus solid state drive. It also compares database performance when relational database is stored in volatile memory. Finally, it demonstrates how selection of filesystem type for DBMS local storage influences transaction efficiency in supported databases. In this research PostgreSQL was used as powerful, open source relational database management system, which was installed and configured in GNU/Linux operating system.

References

  1. 1.
    Allspaw, J.: The Art of Capacity Planning. O’Reilly, Sebastopol (2008)Google Scholar
  2. 2.
    Bernstein, P.A., Newcomer, E.: Principles of Transaction Processing. Morgan Kaufmann, Burlington (2009)zbMATHGoogle Scholar
  3. 3.
    Borodin, A., Mirvoda, S., Kulikov, I., Porshnev, S.: Optimization of memory operations in generalized search trees of PostgreSQL. In: Kozielski, S., Mrozek, D., Kasprowski, P., Małysiak-Mrozek, B., Kostrzewa, D. (eds.) BDAS 2017. CCIS, vol. 716, pp. 224–232. Springer, Cham (2017).  https://doi.org/10.1007/978-3-319-58274-0_19CrossRefGoogle Scholar
  4. 4.
    Cheong, S.K., Lim, C.S., Cho, B.C.: Database processing performance and energy efficiency evaluation of DDR-SSD and hdd storage system based on the TPC-C. In: International Conference on Cloud Computing and Social Networking, pp. 1–3 (2012)Google Scholar
  5. 5.
    Cornwell, M.: Anatomy of a solid-state drive. Commun. ACM 55(12), 59–63 (2012)CrossRefGoogle Scholar
  6. 6.
    Gregg, B.: Systems Performance, Enterprise and the Cloud. Prentice Hall, Upper Saddle River (2013)Google Scholar
  7. 7.
    Gryglewicz-Kacerka, W.: Influence of architecture and configuration parameters on oracle performance. J. Appl. Comput. Sci. 13(2), 53–70 (2005)Google Scholar
  8. 8.
    Gryglewicz-Kacerka, W., Kacerka, J.: Analysis of the effect of chosen initialization parameters on database performance. In: Kozielski, S., Mrozek, D., Kasprowski, P., Małysiak-Mrozek, B., Kostrzewa, D. (eds.) BDAS 2015. CCIS, vol. 521, pp. 60–68. Springer, Cham (2015).  https://doi.org/10.1007/978-3-319-18422-7_5CrossRefGoogle Scholar
  9. 9.
    Kopytov, A.: Sysbench manual, 2004–2009. http://imysql.com/wp-content/uploads/2014/10/sysbench-manual.pdf
  10. 10.
    Kostrzewa, D., Bach, M., Brzeski, R., Werner, A.: Performance aspect of the in-memory databases accessed via JDBC. In: Kozielski, S., Mrozek, D., Kasprowski, P., Małysiak-Mrozek, B., Kostrzewa, D. (eds.) BDAS 2015-2016. CCIS, vol. 613, pp. 236–252. Springer, Cham (2016).  https://doi.org/10.1007/978-3-319-34099-9_18CrossRefGoogle Scholar
  11. 11.
    Leventhal, A.: A file system all its own. Commun. ACM 56(5), 64–67 (2013)CrossRefGoogle Scholar
  12. 12.
    Love, R.: Linux Kernel Development, a Thorough Guide to the Design and Implementation of the Linux Kernel. Developers Library (2010)Google Scholar
  13. 13.
    Mrozek, D., Paliga, A., Małysiak-Mrozek, B., Kozielski, S.: Database under pressure - scaling database performance tests in microsoft azure public cloud. In: Kozielski, S., Mrozek, D., Kasprowski, P., Małysiak-Mrozek, B., Kostrzewa, D. (eds.) BDAS 2015. CCIS, vol. 521, pp. 69–81. Springer, Cham (2015).  https://doi.org/10.1007/978-3-319-18422-7_6CrossRefGoogle Scholar
  14. 14.
    Mustafa, N.U., Armejach, A., Ozturk, O., Cristal, A., Unsal, O.S.: Implications of non-volatile memory as primary storage for database management systems. In: 2016 International Conference on Embedded Computer Systems: Architectures, Modeling and Simulation (SAMOS), pp. 164–171. IEEE (2016)Google Scholar
  15. 15.
    Negus, C.: Linux Bible. Wiley, Hoboken (2015)CrossRefGoogle Scholar
  16. 16.
    Park, S., Shen, K.: FIOS: a fair, efficient flash I/O scheduler. In: Proceedings of the 10th USENIX Conference on File and Storage Technologies, p. 13 (2012)Google Scholar
  17. 17.
    Shen, K., Park, S.: FlashFQ: a fair queueing I/O scheduler for flash-based SSDs. In: Proceedings of the 2013 USENIX conference on Annual Technical Conference, pp. 67–78 (2013)Google Scholar
  18. 18.
    Smith, G.: PostgreSQL 9.0 High Performance. Packt Publishing, Birmingham (2010)Google Scholar
  19. 19.
    Smolinski, M.: Filesystems performance in GNU/Linux multi-disk data storage. J. Appl. Comput. Sci. 22, 65–80 (2014)Google Scholar
  20. 20.
    Smolinski, M.: Efficient multidisk database storage configuration. In: Kozielski, S., Mrozek, D., Kasprowski, P., Małysiak-Mrozek, B., Kostrzewa, D. (eds.) BDAS 2015. CCIS, vol. 521, pp. 180–189. Springer, Cham (2015).  https://doi.org/10.1007/978-3-319-18422-7_16CrossRefGoogle Scholar
  21. 21.
    Sobell, M.G.: Fedora and RedHat Enterprise Linux. Prentice Hall, Upper Saddle River (2011)Google Scholar
  22. 22.
    Son, Y., et al.: An empirical evaluation of enterprise and SATA-based transactional solid-state drives. In: 2016 IEEE 24th International Symposium on Modeling, Analysis and Simulation of Computer and Telecommunication Systems (MASCOTS), pp. 231–240. IEEE (2016)Google Scholar
  23. 23.
    Stallings, W.: Operating Systems, Internals and Design Principles. Prentice Hall, Upper Saddle River (2014)Google Scholar
  24. 24.
    Wang, Y., Goda, K., Nakano, M., Kitsuregawa, M.: Early experience and evaluation of file systems on SSD with database applications. In: 2010 IEEE Fifth International Conference on Networking, Architecture and Storage (NAS), pp. 467–476. IEEE (2010)Google Scholar
  25. 25.
    Wosiak, A., Koper, R.: Database optimization for improvement of exising systems. J. Appl. Comput. Sci. 23(2), 101–118 (2015)Google Scholar
  26. 26.
    WWW sites of PostgreSQL project: PostgreSQL Documentation, 10 November 2017. http://www.postgresql.org
  27. 27.
    WWW sites of TPC: Transaction Processing Performance Council, 10 November 2017. http://www.tpc.org

Copyright information

© Springer Nature Switzerland AG 2018

Authors and Affiliations

  1. 1.Institute of Information TechnologyLodz University of TechnologyLodzPoland

Personalised recommendations