Performance Evaluation of memory databases FastDB and SQLite

The evaluation process is simple. It is nothing more than designing a test CASE, writing a test CODE, outputting the test RESULT, and making a conclusion. We usually think that the insert time with index is longer than the query and deletion time, so we first look at the insert performance. A simple table is used to complete all subsequent tests. The table contains only two fields, INTEGER

The evaluation process is simple. It is nothing more than designing a test CASE, writing a test CODE, outputting the test RESULT, and making a conclusion. We usually think that the insert time with an index is longer than the query and deletion time, so we should first look at the insert performance. A simple table is used to complete all subsequent tests. The table contains only two fields, INTEGER

The evaluation process is simple. It is nothing more than designing a test CASE, writing a test CODE, outputting the test RESULT, and making a conclusion. We usually think that the insert time with an index is longer than the query and deletion time, so we should first look at the insert performance. A simple table is used to complete all subsequent tests. The table contains only two fields: INTEGER intKey and VARCHAR strKey. The test platform is Windows 7 32bit System (Evaluation Copy 7127) and the compiler is VC6 SP6. Run on the DELL target instance 640m. the CPU is Intel Core 2 CPU T5500 @ 1.66 GHZ and the memory is 2.5 GB.

For FastDB (in disk mode), the table structure is defined as follows:

Class _ TestTable
{
Public:
Db_int8 intKey;
Char const * strKey;
TYPE_DESCRIPTOR (KEY (intKey, INDEXED), KEY (strKey, INDEXED )));
};

REGISTER (_ TestTable );

For SQLite, the SQL statement for table creation is as follows:

Create table [_ TestTable] ([intKey] integer not null primary key, [strKey] VARCHAR (50) NULL)

2.2 Insert Performance Comparison in different transaction modes

2.2.1 FastDB disk Mode

In the batch transaction processing mode, we first set intKey from 1 to nRecords (number of records), specify the corresponding strKey, and call the corresponding interfaces (both original APIs) respectively) insert to two tables. The batch transaction processing mode here refers to, for example, inserting 10000 records, inserting the first one before starting the transaction, and then ending the transaction. At this time, the performance of inserting different numbers of records is as follows (10 thousand, 100,000, 0.72 million, 1 million ):

Batch transaction commit:

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest.exe
[FASTDB] Elapsed time for inserting 10000 record: 63 MS
[SQLITE] Elapsed time for inserting 10000 record: 639 MS

E: \ intrest \ FastDB \ PerfTest \ Debug> del *. fdb (clear the data generated by the test and perform the test again, the same below .)

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest.exe
[FASTDB] Elapsed time for inserting 100000 record: 1186 MS
[SQLITE] Elapsed time for inserting 100000 record: 6318 MS

E: \ intrest \ FastDB \ PerfTest \ Debug> del *. fdb

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest.exe
[FASTDB] Elapsed time for inserting 7200000 record: 152460 MS
[SQLITE] Elapsed time for inserting 7200000 record: 560121 MS

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest.exe
[FASTDB] Elapsed time for inserting 1000000 record: 15522 MS
[SQLITE] Elapsed time for inserting 1000000 record: 67423 MS

From the above we can see that in the batch transaction mode, FastDB is 3-10 times better than SQLite's insert performance. However, in many cases, we may need to commit transactions one by one. The following shows the test results of the transaction mode one by one:

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest.exe
[FASTDB] Elapsed time for inserting 10000 record: 57315 MS)
[SQLITE] Elapsed time for inserting 10000 record: 780 MS

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest.exe (SQLITE explicit split transaction)
[FASTDB] Elapsed time for inserting 10000 record: 59967 MS
[SQLITE] Elapsed time for inserting 10000 record: 1154 MS

From the above we can see that FastDB's performance in this case is greatly reduced to a level that is almost unacceptable. Through source code analysis of FastDB (the benefits of open source are reflected), it is found that every time FastDB commits a transaction, will synchronize the changed data content to the disk file (this is because we use the disk mode), resulting in a significant reduction in performance.

Intuitively, there are two solutions to the FastDB problem. One is to avoid synchronizing each transaction to the disk, because in this application, such synchronization operations do not need to be performed in real time, generally, you can synchronize time at intervals (for example, 1 S, 1 Min, etc. based on the reliability requirements of specific projects). Second, you can use the aforementioned FastDB DISKLESS mode.

First, let's look at the first solution. Through the SEARCH FastDB documentation (the document and community are a weakness of FastDB), we found that the author has taken this issue into consideration. FastDB provides the database with the precommit interface, it is used to complete all operations except sync to disk files, such as releasing mutex resources. At the same time, the backup interface is provided to back up memory data to disk files. You can even specify the interval between regular backup and disk files when opening the data library. In this way, the efficiency of each transaction commit will be greatly improved theoretically, and the data reliability can be ensured through the regular backup mechanism. Let's take a look at the performance of FastDB when a transaction is committed one by one using precommit:

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest (use precommit to submit transactions one by one)
[FASTDB] Elapsed time for inserting 10000 record: 62 MS
[SQLITE] Elapsed time for inserting 10000 record: 1170 MS

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest
[FASTDB] Elapsed time for inserting 100000 record: 1170 MS
[SQLITE] Elapsed time for inserting 100000 record: 11747 MS

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest
[FASTDB] Elapsed time for inserting 1000000 record: 8081 MS
[SQLITE] Elapsed time for inserting 1000000 record: 125768 MS

As can be seen from the above, in the one-by-one transaction mode, through the use of precommit technology, FastDB performance is improved by about 10 times than SQLite. Of course, some readers may doubt the performance after the backup mechanism is added. I did not perform this test, but note that FastDB will force sync to the disk file when the database is closed, however, SQLite does not have this function. At the same time, during this test, neither of the two databases has a regular backup mechanism, so this is fair.

2.2.2 FastDB diskless Mode

Let's look at the second solution. FastDB adopts the DISKLESS mode (which is controlled by the compilation option to generate the DISKLESS version). At this time, FastDB initializes a shared memory (shmat or mmap), which is usually large in size, in addition, it cannot be expanded during runtime (the disadvantage of the diskless mode ). Set the initial shared memory to 1 GB. The test result is as follows:

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest.exe
[FASTDB] Elapsed time for inserting 100000 record: 624 MS (batch transaction commit)
[SQLITE] Elapsed time for inserting 100000 record: 11544 MS

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest.exe
[FASTDB] Elapsed time for inserting 100000 record: 7410 MS (one transaction commit)
[SQLITE] Elapsed time for inserting 100000 record: 11560 MS

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest.exe
[FASTDB] Elapsed time for inserting 1000000 record: 134660 MS
[SQLITE] Elapsed time for inserting 1000000 record: 120167 MS

E: \ intrest \ FastDB \ PerfTest \ Debug> PerfTest.exe
[FASTDB] Elapsed time for inserting 250000 record: 23666 MS
[SQLITE] Elapsed time for inserting 250000 record: 29110 MS

From the above, we can see that the DISKLESS mode is similar to SQLite in the case of large data volumes. This is not a good understanding. We need to study the DISKLESS design mode. Theoretically, it should be similar to the precommit mode. But practice is the only criterion for testing truth. We can see that the precommit mode of the disk mode has superior performance, either horizontally or vertically.

2.3 query performance comparison

The following comparison uses the precommit mode in the disk mode to check the index query performance. During the test, 100,000 pieces of data are inserted first, and then the 100,000 pieces of data are queried separately, note that we have also conducted a horizontal evaluation on FastDB's performance of adding HASH indexes. It is easy to add a HASH index to FastDB by modifying the TYPE-DESCRIPTOR, in the above class, replace it with TYPE_DESCRIPTOR (KEY (intKey, INDEXED), KEY (strKey, INDEXED); that is, the Hash index is added for intKey.

E: \ intrest \ FastDB \ PerfTest \ Debug> perftest (FASTDB hash index)
[FASTDB] Elapsed time for inserting 100000 record: 624 MS
[FASTDB] Elapsed time for 100000 index searches: 328 MS
[SQLITE] Elapsed time for inserting 100000 record: 10312 MS
[SQLITE] Elapsed time for 100000 index searches: 10935 MS

E: \ intrest \ FastDB \ PerfTest \ Debug> perftest (FASTDB non-Hash index)
[FASTDB] Elapsed time for inserting 100000 record: 577 MS
[FASTDB] Elapsed time for 100000 index searches: 515 MS
[SQLITE] Elapsed time for inserting 100000 record: 10343 MS
[SQLITE] Elapsed time for 100000 index searches: 9532 MS

From the test results, we can see that the efficiency of querying 100,000 index records is about 20 times faster than SQLite, and it can be further improved after the HASH index is added.

2.4 Delete Performance Comparison and overall performance

Finally, when we test the deletion efficiency, we also look at the performance of insert, query, and delete between FastDB and SQLite:

Insert, query, and delete:

E: \ intrest \ FastDB \ PerfTest \ Debug> perftest (batch delete, FASTDB. removeall (), SQLITE. delete *)
[FASTDB] Elapsed time for inserting 100000 record: 608 MS
[FASTDB] Elapsed time for 100000 index searches: 687 MS
[FASTDB] Elapsed time for deleting all 100000 records: 16 MS
[SQLITE] Elapsed time for inserting 100000 record: 11107 MS
[SQLITE] Elapsed time for 100000 index searches: 10062 MS
[SQLITE] Elapsed time for deleting all 100000 records: 16 MS

E: \ intrest \ FastDB \ PerfTest \ Debug> perftest (delete one by one)
[FASTDB] Elapsed time for inserting 100000 record: 593 MS
[FASTDB] Elapsed time for 100000 index searches: 562 MS
[FASTDB] Elapsed time for deleting all 100000 records one by one: 905 MS
[SQLITE] Elapsed time for inserting 100000 record: 10406 MS
[SQLITE] Elapsed time for 100000 index searches: 10249 MS
[SQLITE] Elapsed time for deleting all 100000 records one by one: 8923 MS

As can be seen from the above, in terms of the deletion efficiency, the batch deletion speed is similar to the two, while the total number of records deleted one by one is about 10 times faster than that of SQLite.

2.5 Conclusion

Advantage: In the FastDB disk mode, the precommit mode is used, and the performance is far better than SQLite. FastDB provides a sound backup and recovery mechanism to ensure data security. FastDB's diskless mode is superior in small data volumes and does not generate disk data files or load saved database files, it looks more like it is developed for embedded devices (such as smart phones and PDAs). In this scenario, you can consider using the diskless mode.

Disadvantages: FastDB is currently a well-known application that can be searched by PingTel's open-source Unified Communication product SIPX, which adopts the FastDB disk model. This may be somewhat related to the full authorization mode of FastDB, while SQLite adopts the GPL commercial release that does not allow closed source. Of course, this is mainly due to the immature community, which can be seen from the search results of Google Trends. The immature community will increase the learning cost, which also needs to be considered during the selection.