I am stuck in a issue related to slow sqlite3 select. I have searched a
lot on this forum and have applied many of the suggestion which has
somewhere helped me in moving ahead. I assume there are some fault in
the way i am trying to use sqlite or may be the settings which i have
used while compiling it.
I decided to use sqlite3 in c++ after reading a lot about its
performance. Since the data inflow is very high and the server is set in
co-location at exchange , if the packet processing is delayed due to any
reason there can be a packet drop and a delayed packet is of no use in
High Frequency trading environment. There can be a minimum packet flow
of 5 mbps where each packet is of a maximum 45 bytes size. My sqlite is
set for In Memory use.
To understand my complete issue please go through the details below.
Below are the details of the Server:
Server Details on which i am trying to use Sqlite3
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Thread(s) per core: 2
Core(s) per socket: 4
Socket(s): 2
NUMA node(s): 2
Vendor ID: GenuineIntel
CPU family: 6
Model: 45
Model name: Intel(R) Xeon(R) CPU E5-2643 0 @ 3.30GHz
Stepping: 7
CPU MHz: 3400.160
BogoMIPS: 6603.86
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 10240K
NUMA node0 CPU(s): 0,2,4,6,8,10,12,14
NUMA node1 CPU(s): 1,3,5,7,9,11,13,15
Ram: 48 gb
Operating System : CentOS 7
Kernel Version : Linux version 3.10.0-123.el7.x86_64
(buil...@kbuilder.dev.centos.org) (gcc version 4.8.2 20140120 (Red Hat
4.8.2-16) (GCC) )
Details of process which i am using:
Compiling Sqlite3 with the following command
./configure --prefix=/usr --disable-static CFLAGS="-O3 -m64
-DSQLITE_DEFAULT_SYNCHRONOUS=0 -DSQLITE_CONFIG_SINGLETHREAD
-DSQLITE_DEFAULT_AUTOMATIC_INDEX=0 -DSQLITE_DEFAULT_PAGE_SIZE=4096
-DSQLITE_DEFAULT_CACHE_SIZE=4000 -DHAVE_FDATASYNC=0"
Create Table Query :
create table 'Stream0' ( TokenNo int NOT NULL,OrderId integer NOT
NULL,SIDE int NOT NULL,PRICE int NOT NULL,QTY int NOT NULL,PRIMARY KEY
(OrderId));
Index On Table :
CREATE INDEX DataFilterIndex ON 'Stream0'(TokenNo , SIDE, Price,Qty);
Pragma Statement :
void SqliteManager::SetPragma()
{
rc= sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &zErrMsg);
rc= sqlite3_exec(db, "PRAGMA count_changes = false", NULL, NULL,
&zErrMsg);
rc= sqlite3_exec(db, "PRAGMA journal_mode = OFF", NULL, NULL, &zErrMsg);
}
Preparing Sqlite Query :
MyString <<"insert or replace into 'Stream0' values( ?1,?2,?3,?4,?5);";
rc=
sqlite3_prepare_v2(db,MyString.str().c_str(),strlen(MyString.str().c_str()),&insert_stmt,NULL);
Note : -- Insert or replace has been used because the incoming data for
any specified TokenNo may be with modify tag without any insert tag
prior to this.
MyString.str(std::string());
MyString <<"delete from 'Stream0' where OrderId = ?1;";
rc =
sqlite3_prepare_v2(db,MyString.str().c_str(),strlen(MyString.str().c_str()),&delete_stmt,NULL);
Once either data for specific TokenNo is asked to delete/Insert/Modify a
select statement is raised to publish data to the user.
Select Statement:
MyString<<"select TokenNo,Price ,sum(QTY) from 'Stream0' where
TokenNo=?1 and Side=66 group by Price order by Price desc limit 5";
rc =
sqlite3_prepare_v2(db,MyString.str().c_str(),strlen(MyString.str().c_str()),&select_bid_stmt,NULL);
Here Side = 66 stands for price of Buyers and Price desc says price
sorted in decreasing order.
One more is there which has Side = 83 which stands for Sellers and Price
Asc says price sorted in ascending mode
If Insert/Modify data comes for a token then one of the query is raised
either with Side = 66 or Side = 83 depending on the Side received in the
incoming data Packet.
If Delete packet is received then Both of the query has to be released
back to back.
If I run my executable with Insert/Replace/delete every thing goes well
i.e : No packet drop, but the moment I start using Select query either
single after Insert/Replace or both after Delete, packet drop starts.
You can find the same discussion on stackoverflow
<http://stackoverflow.com/questions/39438156/slow-sqlite3-select-query-while-insert-update-delete-has-no-issue>
but I am not able exactly understand the implementation.
I hope I have been able to describe my whole situation. Running Select
query is a must for me. Please help.
Regards
Shailendra Kumar
+91-9015602289
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
