On the IO characteristics of the SQLite Transactions

Abstract

This work is dedicated to study the IO characteristics of SQLite transaction in Android platform. We collect the block level IO trace from for six months. We develop an elaborate pattern matching algorithm. It allows us to identify the individual SQLite transactions from the raw IO trace, which is essentially an interleaved mixture of IO requests from concurrently running smartphone applications. Among the various observations obtained from the study, we can summarize the key findings as follows. We carefully believe that these deserve special attention. First, SQLite transaction is under extreme inefficiency. In an SQLite transaction, the IO's for SQLite journaling and EXT4 filesystem journaling account for over 75% of the entire IO volume in a transaction. Second, the suspend and the wakeup feature of the smartphone can leave the SQLite transaction to an extreme delay, a few minutes. Third, in fair number of occasions, the SQLite transactions are being used in inconsiderate manner. We find that a single SQLite transaction inserts 17.5 MByte of data to the database. It turns out to be the operation of initializing the map database. We hope that the findings obtained in this study are bifurcated to various efforts to makes the SQLite related IO more efficient and effective.