To make SQLite more truly lite in terms of IO efficiency for the transactional support, we propose SQLite/SSL, a per-transaction SQL statement logging scheme : when a transaction commits, SQLite/SSL ensures its durability by storing SQL statements of small size, thus writing less and performing faster at no compromise of transactional solidity.
This paper will be published at VLDB 2018
Short Transaction with Strong Update Locality
Mobile workloads have few unique characteristics. First of all, they tend to be very short. mostly running in the autocommit mode, where a transaction consists of a single SQL statement. More importantly, each transaction usually modifies very small data. For instance, the database workload in a mobile messenger application is mostly small insertions and, once stored, most of the messages are seldom deleted or update.
The second, and more important, one is that mobile transactions exhibit strong update locality. Whenever a new record is inserted into a SQLite database table(which is also organized as a B+ tree), it is inserted into the rightmost leaf node of the table B+ tree because a surrogate key automatically created by SQLite is stored together as part of the record and the surrogate keys are increasing monotonically. Consequently, the same leaf node of the table will be consecutively updated until that node becomes full when successive insertions are made to the table.
Opportunities for Logical Logging
From the Table, we can make a few important observations on why logical logging is an attractive alternative for mobile applications. First, as shown in column D, each transaction in all applications except Twitter, mostly running in autocommit mode, updates three to eight pages on average. In this case, it is well known that logical logging is especially useful by replacing multiple page writes with one SQL statement log.
The second observation is that, across all the traces used, the actual number of all distinct pages updated in each trace (column B) is relatively very small, compared to the total number of pages written in each trace (column A). In addition, from column E, we know that one same logical page is repetitively overwritten when each trace was run using vanilla SQLite in the WAL mode. In one extreme case of AndroBench, each page is overwritten on average almost up to 150 times. This confirms that database workload in mobile applications is mostly small updates. Therefore, taking into account that the default size of buffer cache in SQLite (i.e., 1,000 pages) is large enough to buffer all the pages updated by many consecutive transactions, there is no compelling reason to take the force commit policy as long as the durability of each commiting transaction can be guaranteed in other way (e.g., SQL statement logging). Therefore, by taking logical logging approach and thus buffering updated page in DRAM, instead of force-writing them upon every commit, a multitude of successive page writes to the same logical page can be avoided. The third observation is about checkpoint and recovery. Since only small number of active pages will be updated by many consecutive transactions and those pages can be buffered in the cache, those pages can be checkpointed in a transaction-consistent way without causing unacceptable latency spike.
In addition, compared to the force commit policy in vanilla SQLite, the WAL file will be filled up at a much slower rate under logical logging approach because of its write buffering effect. Therefore, checkpoint operations are called much less frequently than vanilla SQLite. In addition, in terms of recovery time, the number of pages to be recovered from crashes is limited so that the recovery can be completed only with small IOs.
The SQLite/SSL Architecture
The design objectives of SQLite/SSL are threefold. First, while embodying its new functionalities, SQLite/SSL takes full advantage of the existing proven features in vanilla SQLite so as to make only minimal changes and thus keep its codebase as reliable as vanilla SQLite. For instance, the WAL mode in vanilla SQLite was leveraged to embody a transaction-consistent checkpoint in SQLite/SSL. Second, SQLite/SSL should be able to keep its recovery logic as simple and efficient as vanilla SQLite. In fact, as is described below, SQLite/SSL introduces an additional data structure for logging SQL statements, SLA, which in turn can, upon crashes, lead to the numerous failure combinations of SLA and the existing WAL journal. Therefore, we deliberately chose to take a simple checkpoint and recovery logic at the cost of some performance overhead. Third, SQLite/SSL aims at making the implementation logic of logging SQL statements in SLA as generic as possible. In addition to PCM, the emerging NVDIMM as well as the existing flash storage can also be used as the log device for SQLite/SSL. Therefore, as is detailed later, we use the mmap and msync calls to achieve both the device independence and the byte-addressability in storing statement logs irrespective of the log devices.
- SQLite/SSL can be ported in commodity PC with mmap interface
- In SQLite/SSL, WAL mode can be used as transaction consistent checkpoint mechanism
- SQLite/SSL reduces huge write amplification
From Figure (a), we see that, when PCM is used as SLA log device, SQLite/SSL can outperform SQLite/PPL and vanilla SQLite by up to 27 and 300 times, respectively. From Figure (b), we see that, even when flash memory SD card is used as SLA log device, SQLite/SSL can outperform vanilla SQLite by up to 6 times. Overall, the performance results presented in Figure 3 confirm two main points: 1) the logical logging approach itself, without the help of NVM device, can give significant performance improvement to SQLite-based mobile applications (Figure (b)), and 2) SQLite/SSL can realize its full potential when PCM is used as its SLA log device (Figure (a)).
we presented the design and implementation of SQLite/SSL, a type of logical logging scheme, for mobile applications. For the durability of committing transactions, it force-writes only all update SQL statements from each transaction while vanilla SQLite force-writes all pages updated by each transaction in their entireties redundantly. Our main contributions are in three folds. First, we made an important observation about the characteristic of transactional workload in SQLite-based mobile applications: short transactions with high update locality. Second, based on this observation, we showed that the concept of logical logging is, though not new at all, a perfect fit for modern SQLite based mobile applications. In addition, we showed how the WAL mode in vanilla SQLite can be used as ** transaction consistent checkpoint mechanism**. Third, we demonstrated that the logical logging can realize its full potential by using a real PCM board with DIMM interface as its log device.