From 6d10f3fa4f7fc892e848b52458a2fc4938d56d20 Mon Sep 17 00:00:00 2001 From: Geunsik Lim Date: Wed, 17 May 2017 10:36:43 +0900 Subject: [PATCH] [MIC] tel-plugin-databases: Speed up SQLite3 insertion operations while running MIC command This commit is to speed up exeuction time of a MIC operation. The "INSERT INTO ..." operation has been taken more than 500 milli seconds when we run MIC command to make a Tizen platform image on the below system environment. - CPU: Intel(R) Core(TM) i7-4770 CPU @ 3.40GHz - Memory: DDR3 8GiB - OS: Ubuntu 14.04.5 LTS We don't need to keep the synchronous operation in case that we only run insertion commands without any data modification such as MODIFY and DELETE statement. @To maintainer: ---------------- First, Note that the number of insertion commands results in more synchronization cost in kernel space. For example, the more we call a write(2) syscall, the more Linux kernel calls submit_bio(9) and futex_wait(9) kernel function. Disabling a synchronous operation will cause SQLite3 to not wait on data to reach the disk surface, which will make write operations appear to be much faster. Second, Note that you have to check frequently if the version of SQLite3 is state-of-the-art. As we all know, the existing bugs is often fixed in the latest version of SQLite3. @Evaluation: ---------------- - How to do unit-test: $ cd ./tel-plugin-databases $ time sqlite3 ./mybenchmark.db < ../cdma_mcc_sid_list.sql - Before this patch: real 0m0.553s user 0m0.019s sys 0m0.013s - After this patch: real 0m0.029s user 0m0.020s sys 0m0.009s @Reference: ---------------- - Pragma statements supported by SQLite, https://www.sqlite.org/pragma.html Change-Id: I0f3c7c36bd768722fa649e1bc2f4236dcebe9e71 Signed-off-by: Geunsik Lim --- packaging/tel-plugin-database.spec | 2 +- res/cdma_mcc_sid_list.sql | 6 ++++++ res/common_mcc_mnc_oper_list.sql | 6 ++++++ 3 files changed, 13 insertions(+), 1 deletion(-) diff --git a/packaging/tel-plugin-database.spec b/packaging/tel-plugin-database.spec index 88c10e1..caf3f76 100644 --- a/packaging/tel-plugin-database.spec +++ b/packaging/tel-plugin-database.spec @@ -1,6 +1,6 @@ %define major 0 %define minor 1 -%define patchlevel 32 +%define patchlevel 33 Name: tel-plugin-database Version: %{major}.%{minor}.%{patchlevel} diff --git a/res/cdma_mcc_sid_list.sql b/res/cdma_mcc_sid_list.sql index c5ba274..9a249e2 100644 --- a/res/cdma_mcc_sid_list.sql +++ b/res/cdma_mcc_sid_list.sql @@ -1,3 +1,9 @@ +PRAGMA journal_mode = PERSIST; +PRAGMA synchronous = OFF; +PRAGMA locking_mode = EXCLUSIVE; +PRAGMA temp_store = MEMORY; +PRAGMA cache_size=-5000; + create table mcc_sid_list (mcc integer, sid_low1 integer, sid_high1 integer, sid_low2 integer, sid_high2 integer, sid_low3 integer, sid_high3 integer, gwt_offset_low1 integer, gwt_offset_high1 integer, gwt_offset_low2 integer, gwt_offset_high2 integer); BEGIN; insert into mcc_sid_list (mcc, sid_low1, sid_high1, sid_low2, sid_high2, sid_low3, sid_high3, gwt_offset_low1, gwt_offset_high1, gwt_offset_low2, gwt_offset_high2) values (202,24192,24319,0,0,0,0,2,2,3,3); diff --git a/res/common_mcc_mnc_oper_list.sql b/res/common_mcc_mnc_oper_list.sql index 3525b0c..cca1800 100644 --- a/res/common_mcc_mnc_oper_list.sql +++ b/res/common_mcc_mnc_oper_list.sql @@ -1,3 +1,9 @@ +PRAGMA journal_mode = PERSIST; +PRAGMA synchronous = OFF; +PRAGMA locking_mode = EXCLUSIVE; +PRAGMA temp_store = MEMORY; +PRAGMA cache_size=-5000; + create table mcc_mnc_oper_list (id integer primary key, country char(3), mcc integer, mnc char(3), oper char(45) ); create unique index uniq_idx on mcc_mnc_oper_list(mcc, mnc); BEGIN; -- 2.7.4