Android SQLite Performance Analysis

As a database module for Android presets, an in-depth understanding of sqlite is necessary to find some direction of optimization. The performance and memory of SQLite are tested and analyzed, and the performance and memory consumption of different operations are compared, which is a summary in this case.

1. Basic Architecture

First look at the main architecture of SQLite (see "The Definitive Guide to SQLite"), need to focus on the compiler and backend two modules. Because of the existence of a virtual machine, it has the value of compiled statement, because it can reduce the pre-compile time, directly to vdbe execution. The backend end of the pager is an important data manager, a real determinant of the performance of data manipulation, as well as memory consumption.

Not to repeat here, the detailed content is also read about the introduction of SQLite information.

2. Performance

This test is based on Sumsung i9103 and Google Nexus S1. Use the Python script and the ADB instructions to operate the Android app via intent.

i. Select Operation

First, the peak distribution of select under different record numbers is observed, and there is an upward trend in the whole. As SQLite officials say it is not suitable for storing large amounts of data, be sure to control the number of records in it.

In addition, select operation Performance also depends on the number of columns in the query, while the @ row Cloud further confirms and returns the content size of the field, that is, the contents of the field and how much also need trade-offs.

ii. on Business

Transaction is an important technique to improve the performance of SQLite, in particular, SQLite realizes the Wal, which makes the transaction and select not mutually exclusive, greatly improving the performance of the application. See attached 2.

The following is the average of three types of operations when transactions are not used:

After the transaction is used, the performance of each operation is greatly reduced.

But the problem is that the time for the transaction to be committed is longer, and this time it is necessary to form a new peak, and also to divide it into individual operations:

therefore cannot too much, the transaction still must submit in time. also note that although compiled statement is good for performance, it is not as straightforward as the effect of using transactions.

Iii. operating performance of different models

First look at the average of the select operation on both models, you can see the i9103 on the volatility is relatively large, while the Nexsus S1 has remained stable:

Scatter plot, you can observe the time distribution:

Look at the performance of the other three operations (both false and true for both the upper and lower parts are not used and transactions are used):

Using Wal can dramatically improve performance, but it also has a side effect. It causes data queries to take two times, which can be understood once in a db file, once in the Wal file. For specific reasons see << the definitive guide toSQLite>> last chapter. The Wal is merged into the main database at full 1000Pages, which is called checking point and can be configured. Computed by default page size:1024, which is checked when the Wal file is close to 1M. If there are no active transactions using these pages, they will be submitted. And the size of the Wal, the impact on select performance is different.

The following is the test data for Wal's impact on query performance spikes (tested on i9103), and the True,false in the figure indicates whether a Wal file exists.

* The overall average difference is 1ms, but the peak is really worth noting.

In order to avoid the Wal too large, you can choose to call Sqlitedatabase::d isablewriteaheadlogging () forced to merge into the main database file, this call will be executed asynchronously inside SQLite, does not significantly block the user's thread.

* In addition, Sqlitedatabase::query () is only a sqlitedatabase::rawquery () package, more than a string assembly process, instead of directly using Sqlitedatabase::rawquery ().

3. Memory

SQLite stores data in page, default one page has 1024 bytes, and then organizes it through B-tree (table uses B + Tree organization):

Lookaside is the application of SQLite memory management technology, optimize the use of memory efficiency, the main idea is to allocate a whole block of memory, divided into a number of slots, and then SQLite and then on-demand use. The idea of many small memory allocators is the same. See attached 1.

To explain page Cache Overflow, the data recorded in one page cannot be placed exactly within a page, and an additional page space is used, which is the Overflow page.

Android also has a Magnum Dumpsys, using Dumpsys meminfo to view the memory information used by SQLite to a process. Such as:

Sql

heap:265 memory_used:265

pagecache_overflow:73 malloc_size:46

DATABASES

Pgsz Dbsz lookaside (b) Cache Dbname

4 199/114/1 webviewcache.db

1/541/1 (Pooled # 1) webviewcache.db

. The three values for the cache are:

Page Cache Hit count, number of misses, and page cache count. You can find the details in the Sqlitedebug.java and Activitythread.java in the Android source code.

. Page size, the unit of DB size is Kbytes, lookaside (b) refers to how many lookaside slots are used.

. Memory reclamation for the heap and overflow pages,sqlitedatabase may not be as timely and can be called sqlitedatabase::releasememory () for active release.

* Using SQLite pragma can directly get some information through sqlitedatabase, of course, if SQLite does not support, will also throw out the exception, see attached 4.

Reproduced please specify the source: Http://blog.csdn.net/horkychen SQLite is a very delicate system, it is worth studying.

Reference

1. SQLite Dynamic Memory Allocation

2. write-ahead Logging or SQLite's wal mechanism

3. The definitive Guide to SQLite (two about the architecture and overflow page comes in this book.)

4. PRAGMA Statement

5. Official documentation

6. NEC Research Report on the performance of memory operation on Android system

Android SQLite Performance Analysis