Performance analysis and modeling of SQLite embedded databases on flash file systems

Abstract

Databases are more and more used in embedded system applications and especially in consumer electronics. This comes from the need to structure user and/or system data to be more efficiently managed and accessed. The transactional database management systems widely used in embedded systems have been designed considering hard disk drives as the storage device. In embedded systems, NAND flash memory is the main storage media and its intricacies make it fundamentally different from hard drives on many points of view, in particular in terms of performance and access mode. The performance behavior of on-flash database applications remains largely unknown, and we believe a better adequacy between those applications and flash management systems would lead to strong optimizations. A first step in that process is to assess and understand the performance behavior of database applications on flash memory. For this sake, this paper presents a micro benchmarking and modeling methodology, as well as the associated results and analysis for SQLite database queries on embedded flash specific file systems. SQLite is one of the most used database application in embedded systems and especially consumer electronics. This methodology has been applied and validated on two embedded hardware platforms. Flash file systems (FFS) behavior are very specific to flash memory intricacies and the objective of this study is to highlight the interactions between flash memory, FFSs, and SQLite based applications. The model proved to be very accurate in predicting flash I/O performance for a given workload.

This is a preview of subscription content, access via your institution.

Fig. 1
Fig. 2
Fig. 3
Fig. 4
Fig. 5
Fig. 6
Fig. 7
Fig. 8
Fig. 9
Fig. 10
Fig. 11
Fig. 12
Fig. 13
Fig. 14
Fig. 15
Fig. 16

References

  1. 1.

    Armadeus Systems (2012) APF27 board datasheet. From http://www.armadeus.com/_downloads/apf27Dev/documentation/dataSheet_APF27Dev.pdf. Accessed 01 July 2014

  2. 2.

    Bellatreche L, Cheikh S, Bre S, Kerkad A, Boukhorca A, Boukhobza J (2013) How to exploit the device diversity and database interaction to propose a generic cost model? In: Proceedings of the 17th international database engineering and applications symposium, ACM, New York, IDEAS ’13, p 142147. doi:10.1145/2513591.2513660

  3. 3.

    Bityutskiy A (2005) JFFS3 design issues

  4. 4.

    Boukhobza J (2013) Flashing in the cloud: shedding some light on NAND flash memory storage systems. In: Data intensive storage services for cloud environments, IGI Global

  5. 5.

    DeWitt DJ, Naughton JF, Schneider DA, Seshadri S (1992) Practical skew handling in parallel joins. In: VLDB, pp 27–40

  6. 6.

    Engel J, Mertens R (2005) LogFS-finally a scalable flash file system. In: 12th international linux system technology conference. http://www.cs.xu.edu/csci390/08s/logfs1.pdf

  7. 7.

    Firebird Developpers (2014) Firebird website. From http://www.firebirdsql.org/. Accessed 07 Jan 2014

  8. 8.

    Gleixner T, Haverkamp F, Bityutskiy A (2006) Ubi: unsorted block images

  9. 9.

    Graefe G (1999) The value of merge-join and hash-join in sql server. In: VLDB, pp 250–253

  10. 10.

    Homma T (2009) Evaluation of flash file systems for large NAND flash memory. http://elinux.org/images/7/7e/ELC2009-FlashFS-Toshiba.pdf

  11. 11.

    Kang WH, Lee SW, Moon B, Oh GH, Min C (2013) X-ftl: transactional ftl for sqlite databases. In: Proceedings of the 2013 international conference on management of data, ACM, pp 97–108

  12. 12.

    Kataria V, Amit S, Singh P, Ch SP (2004) Commercial embedded database aystems, analysis and selection

  13. 13.

    Kim JM, Kim JS (2012) Androbench: benchmarking the storage performance of android-based mobile devices. Frontiers in computer education. Springer, New York, pp 667–674

    Google Scholar 

  14. 14.

    Koltsidas I, Viglas SD (2011) Data management over flash memory. In: Proceedings of the 2011 ACM SIGMOD international conference on management of sata, ACM, New YorkSIGMOD ’11, pp 1209–1212. doi:10.1145/1989323.1989455

  15. 15.

    Lee H, Chang N (2003) Energy-aware memory allocation in heterogeneous non-volatile memory systems. In: Proceedings of the 2003 international symposium on low power electronics and design, p 420423

  16. 16.

    Li H, Yang C, Tseng H (2008) Energy-aware flash memory management in virtual memory system. IEEE Trans Very Large Scale Integr Syst 16(8):952964

    Article  Google Scholar 

  17. 17.

    Li J, Xu Y (2009) Remote monitoring systems based on embedded database. In: 3rd international conference on Genetic and evolutionary computing, WGEC ’09, pp 381–384. doi:10.1109/WGEC.2009.54

  18. 18.

    Lightstone SS, Teorey TJ, Nadeau T (2007) Physical database design: the database professional’s guide to exploiting indexes, views, storage, and more. Morgan Kaufmann Publishers Inc., San Francisco

    Google Scholar 

  19. 19.

    Market Research (2012) Advanced solid state non-volatile memory market to grow 69 % annually through 2015. From http://www.marketresearch.com/corporate/aboutus/press.asp?view=3&article=2223. Accessed 07 Jan 2014

  20. 20.

    Micron (2009) NAND flash and mobile LPDDR 168-ball package-on-package (PoP) MCP combination memory (TI OMAP)

  21. 21.

    Micron Technology, Inc (2007) MT29F2G16ABDHC-ET: D NAND flash memory datasheet. Micron Technology, Inc, Boise

  22. 22.

    Nath S, Kansal A (2007) Flashdb: dynamic self-tuning database for nand flash. In: Proceedings of the 6th international conference on Information processing in sensor networks, ACM, pp 410–419

  23. 23.

    Olivier P, Boukhobza J, Senn E (2014) Flashmon v2: monitoring raw nand flash memory i/o requests on embedded linux. ACM SIGBED Rev 11(1):38–43. doi:10.1145/2597457.2597462

    Article  Google Scholar 

  24. 24.

    Olson MA, Bostic K, Seltzer M (1999) Berkeley DB. In: Proceedings of the annual conference on USENIX annual technical conference. USENIX Association, Berkeley, ATEC ’99, p 4343

  25. 25.

    O’Neil PE, Graefe G (1995) Multi-table joins through bitmapped join indices. SIGMOD Rec 24(3):8–11

    Article  Google Scholar 

  26. 26.

    Opdenacker M (2008) Update on filesystems for flash storage. http://freeelectrons.com/pub/conferences/2008/elce/

  27. 27.

    Opdenacker M (2010) Flash filesystem benchmarks. http://elinux.org/images/d/d7/Elce2010-flash-filesystems.pdf

  28. 28.

    Park S (2013) Flash-aware cost model for embedded database query optimizer. J Inf Sci Eng 29(5):947–967

    Google Scholar 

  29. 29.

    Patchigolla VNR, Springer J, Lutes K (2011) Embedded database management performance. In: 2011 eighth international conference on, IEEE information technology: new generations (ITNG), pp 998–1001

  30. 30.

    Qinlong G, Xingmei C, Weiwei T, Minghai Y (2010) Study and application of SQLite embedded database system based on windows cE. In: 2010 2nd international conference on information science and engineering (ICISE), pp 6920–6923, doi:10.1109/ICISE.2010.5691551

  31. 31.

    Ryu Y (2011) A flash translation layer for nand flash-based multimedia storage devices. IEEE Trans Multimed 13(3):563–572. doi:10.1109/TMM.2011.2114333

    Article  Google Scholar 

  32. 32.

    Schierl A, Schellhorn G, Haneberg D, Reif W (2009a) Abstract specification of the ubifs file system for flash memory. In: Cavalcanti A, Dams D (eds) FM 2009: formal methods, Lecture Notes in Computer Science, vol 5850. Springer, Berlin, pp 190–206. doi:10.1007/978-3-642-05089-3_13

  33. 33.

    Serial ATA International Organization (2011) SATA-ST 3.1: Serial ATA Revision 3.1

  34. 34.

    SQLite Developpers (2014a) Journal mode pragma statement. From http://www.sqlite.org/pragma.html#pragma_journal_mode. Accessed 07 Jan 2014

  35. 35.

    SQLite Developpers (2014b) SQLite homepage. From http://www.sqlite.org/. Accessed 07 Jan 2014

  36. 36.

    Storage Newsletter (2012) NAND chip market growth propelled by smartphones and media tablets. From http://www.storagenewsletter.com/rubriques/market-reportsresearch/ihs-isuppli-nor-nand-chip/. Accessed 07 Jan 2014

  37. 37.

    UBIFS Developpers (2008) UBIFS and linux read-ahead. From http://www.linux-mtd.infradead.org/doc/ubifs.html#L_readahead. Accessed 07 Jan 2014

  38. 38.

    Woodhouse D (2001) JFFS: the journalling flash file system. In: Ottawa linux symposium, vol 2001.

  39. 39.

    Wookey (2004) YAFFS: a NAND flash file system

  40. 40.

    Yue K, Jiang L, Yang L, Pang H (2010) Research of embedded database SQLite application in intelligent remote monitoring system. In: 2010 international forum on information technology and applications (IFITA), vol 2, pp 96–100. doi:10.1109/IFITA.2010.241

Download references

Author information

Affiliations

Authors

Corresponding author

Correspondence to Jalil Boukhobza.

Rights and permissions

Reprints and Permissions

About this article

Verify currency and authenticity via CrossMark

Cite this article

Ouarnoughi, H., Boukhobza, J., Olivier, P. et al. Performance analysis and modeling of SQLite embedded databases on flash file systems. Des Autom Embed Syst 17, 507–542 (2013). https://doi.org/10.1007/s10617-014-9149-2

Download citation

Keywords

  • Embedded databases
  • NAND flash memory
  • Flash file systems
  • SQLite
  • JFFS2
  • UBIFS
  • Performance
  • Modeling