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