Re: [sqlite] Slow Sqlite3 Select Query while Insert/Update/Delete has no issue

[Dominique Devienne]

On Mon, Sep 19, 2016 at 4:56 PM, James K. Lowden 
wrote:

> On Wed, 14 Sep 2016 16:27:37 +0530
>
> But I agree with Teg: SQLite is providing you with transactions you
> don't need, and puts an interpreted language exactly where you don't
> want it: in a performance-critical spot.  The C++ standard library has
> all the bits you need, and is almost as convenient to use.
>
> You have only one table, and probably just a few simple queries.
> std::set gives you lower_bound and upper_bound.  Hand those two
> iterators to std::accumulate, and you have GROUP BY.  Call that for 5
> prices.  Not very much code, and I bet 100x faster than SQL.  If more
> than one thread is updating the table, obviously protect your set with
> a mutex.
>

In addition to James' excellent answer, I'd add that using C++/STL
containers
doesn't preclude you from having SQL on top of them, thanks to SQLite's
virtual tables.

For example, FWIW, I extensively use Boost's Multi-Index containers,
which are like statically indexed tables (you decide what indexes you want,
at compile time), and layer read-only virtual tables on top, properly
exposing
the static indexes to SQLite's query planner (via xBestIndex). That way you
can use C++ when you want, but can still benefit for all the flexibility of
SQL,
which happens to read even faster than SQLite's in-memory DBs. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow Sqlite3 Select Query while Insert/Update/Delete has no issue

[James K. Lowden]

On Wed, 14 Sep 2016 16:27:37 +0530
SinhaK  wrote:

> strlen(MyString.str().c_str())

BTW, as a matter of style, 

MyString.str().size()

gets you to the same place sooner.   

> MyString<<"select TokenNo,Price ,sum(QTY) from 'Stream0' where 
> TokenNo=?1 and Side=66 group by Price order by Price desc limit 5";

You should group by TokenNo, Price.  You should not have 

 'Stream0' 

in single quotes; that makes it a string.  

But I agree with Teg: SQLite is providing you with transactions you
don't need, and puts an interpreted language exactly where you don't
want it: in a performance-critical spot.  The C++ standard library has
all the bits you need, and is almost as convenient to use.  

You have only one table, and probably just a few simple queries.
std::set gives you lower_bound and upper_bound.  Hand those two
iterators to std::accumulate, and you have GROUP BY.  Call that for 5
prices.  Not very much code, and I bet 100x faster than SQL.  If more
than one thread is updating the table, obviously protect your set with
a mutex.  

--jkl


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow Sqlite3 Select Query while Insert/Update/Delete has no issue

[Teg]

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, );
S> rc= sqlite3_exec(db, "PRAGMA count_changes = false", NULL, NULL, 
S> );
S> rc= sqlite3_exec(db, "PRAGMA journal_mode = OFF", NULL, NULL, );
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()),_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()),_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()),_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 

Re: [sqlite] Slow Sqlite3 Select Query while Insert/Update/Delete has no issue

[Simon Slavin]

On 14 Sep 2016, at 11:57am, SinhaK  wrote:

> 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);
> 
> [...]
> 
> "select TokenNo,Price ,sum(QTY) from 'Stream0' where TokenNo=?1 and Side=66 
> group by Price order by Price desc limit 5";

Thank you for your very detailed description of your problem, which has saved a 
lot of question/answer.

The above INDEX is a very good index for this SELECT.  The only improvement 
would be to run ANALYZE on your database file.  You only need to do this once, 
when your database file has some plausible data in it (NOT just after creating 
blank table).  The results of the ANALYZE command are stored in the database 
file so you do not need to include the ANALYZE command in your program's code.

>   rc= sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, );
>   rc= sqlite3_exec(db, "PRAGMA count_changes = false", NULL, NULL, );
>   rc= sqlite3_exec(db, "PRAGMA journal_mode = OFF", NULL, NULL, );

Messing with "synchronous" and "journal_mode" in a multi-accessed network 
environment can cause problems up to and including corrupt databases.  For 
testing purposes, please remove those two PRAGMAs from your program and see 
whether this fixes your problem.  You might put them back later once you 
understand the problem but the situation will be clearer if you're using the 
default setup.  The count_changes PRAGMA exists for historical reasons and 
should not be used in new code.

A few of the configuration changes I see you've listed do not significantly 
speed up SQLite and can make it difficult for you to identify problems.  You 
might find that using default configuration makes your problems disappear.  (I 
understand that speed is critical in HFT but the amount of time SQLite spends 
in software is very small compared to the time it spends waiting for data to be 
read/written.). If you feel like enabling them again, you might do benchmark 
testing on each one to find out whether it makes a difference.

> 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.

Packet drop is a hardware issue.  There is nothing inside SQLite which 
understands that you're accessing its database file across a network.  As far 
as SQLite is concerned, you are accessing a file stored on the computer which 
is running the program doing the SQLite calls.  If you're seeing dropped 
packets then it may be more useful to look for problems in your network driver 
and network hardware.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users