seeking information on the throughput requirement using Sqlite

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

seeking information on the throughput requirement using Sqlite

Zhu, Liang [AUTOSOL/ASSY/US]
Hi Sqlite experts,

In my current project, we have the need to inserting and deleting data to and from the database between 1 and 10 ms for 24/7.   I am seeking the clarification on the following questions


  1.  what is the throughput requirements are possible using Sqlite API?, in other words, what is the max  speed can my inserting and deleting operation be?
  2.  When we inserting and deleting data at the speed mentioned above, what kind database maintenance do we need to do to maintain the performance of the database?
  3.  How is constant deleting and insert effect the database performance?


Thank you,

Liang Zhu | Lead Software Engineer | Branson Ultrasonics
Emerson Automation Solutions | 41 Eagle Road | Danbury, CT 06810 | USA
T (203) 796-2235 | F (203) 796-0380
[hidden email]<mailto:[hidden email]>

The information contained in this message is confidential or protected by law. If you are not the intended recipient, please contact the sender and delete this message. Any unauthorized copying of this message or unauthorized distribution of the information contained herein is prohibited.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: seeking information on the throughput requirement using Sqlite

Simon Slavin-3
On 3 May 2019, at 3:01pm, Zhu, Liang [AUTOSOL/ASSY/US] <[hidden email]> wrote:

> In my current project, we have the need to inserting and deleting data to and from the database between 1 and 10 ms for 24/7.   I am seeking the clarification on the following questions
>
>  1.  what is the throughput requirements are possible using Sqlite API?, in other words, what is the max  speed can my inserting and deleting operation be?

The figures you quote are possible.  The fastest speeds I've seen for SQLite are 96,000 INSERTs per second.

But SQLite speed depends most on the operating system and hardware (especially file storage) system you are using.  Fortunately SQLite performs the same in a tiny test program and in a big production program, so you can write a small test program to run on your hardware and get useful and interesting results from it.

Another thing which influences speed is whether you have one thread/connection talking to the database, or have many trying to talk to the database at the same time.

>  2.  When we inserting and deleting data at the speed mentioned above, what kind database maintenance do we need to do to maintain the performance of the database?

SQLite databases require no maintenance.  They should continue working forever without attention.  However, you might like to use a yearly routine which checks the database for corruption.  It might also rebuild the database (like defragmenting) which might speed things up slightly, or it might make no change at all.

>  3.  How is constant deleting and insert effect the database performance?

SQLite should not slow down much even after millions of changes to a database.  SQLite automatically reclaims file space released by deleting data.

You might find it useful to read

<https://www.sqlite.org/whentouse.html>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: seeking information on the throughput requirement using Sqlite

Stephen Chrzanowski
In reply to this post by Zhu, Liang [AUTOSOL/ASSY/US]
All of that is going to ENTIRELY depend on your hardware, and how fast it's
going to allow the electrical pulses and actual processing of the different
chips on your mobo, and the kind of data you're going to be adding,
requesting, and removing.  There's no clear-cut answer with the information
provided.

You're going to get throttled if this sits in a VM, bar none.  If you have
other machines that are running on the host, you've got a few layers in the
stack between your VM and the metal that needs to handle your data, as well
as other data in the other VMs.  Mind you, if you have a VM sitting solo on
your host, you'll be darn near 1:1 for Metal:VM speeds, but it isn't
exactly 1:1.
You're going to get throttled if you have other processes taking disk IO
and CPU.
You're going to get throttled at your database design if you don't include
indexes.
You're going to get throttled at the volume of data you're adding and
deleting (It'll be faster to delete as SQLite just marks space as being
available and overwrites existing data, but then you could be looking at
fragmentation not only in the database, but, the disk as well)
The type of file system you're using can also be a throttle point.

The physical database size doesn't really come into play, unless you're
talking billions of index records that needs to be dealt with per call.
Actual data is returned to your application one row at a time, not as a
full chunk of data for your entire request.  On any query, SQLite will
reference indexes heavily (If available, and if it makes sense), and
doesn't touch/look@ anything it doesn't need.

The more hardware you throw at it, the faster you're going to get, but on
top of that, ultimately, you're limited by the speed to which the
electricity flows through the tiny little wires and connections.


On Fri, May 3, 2019 at 10:02 AM Zhu, Liang [AUTOSOL/ASSY/US] <
[hidden email]> wrote:

> Hi Sqlite experts,
>
> In my current project, we have the need to inserting and deleting data to
> and from the database between 1 and 10 ms for 24/7.   I am seeking the
> clarification on the following questions
>
>
>   1.  what is the throughput requirements are possible using Sqlite API?,
> in other words, what is the max  speed can my inserting and deleting
> operation be?
>   2.  When we inserting and deleting data at the speed mentioned above,
> what kind database maintenance do we need to do to maintain the performance
> of the database?
>   3.  How is constant deleting and insert effect the database performance?
>
>
> Thank you,
>
> Liang Zhu | Lead Software Engineer | Branson Ultrasonics
> Emerson Automation Solutions | 41 Eagle Road | Danbury, CT 06810 | USA
> T (203) 796-2235 | F (203) 796-0380
> [hidden email]<mailto:[hidden email]>
>
> The information contained in this message is confidential or protected by
> law. If you are not the intended recipient, please contact the sender and
> delete this message. Any unauthorized copying of this message or
> unauthorized distribution of the information contained herein is prohibited.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: seeking information on the throughput requirement using Sqlite

David Raymond
In reply to this post by Zhu, Liang [AUTOSOL/ASSY/US]
Within a single transaction SQLite can do things very quickly. But a reminder that there can be only 1 write transaction happening at a time. So my questions are: What is the planned _transaction_ rate? And how many different connections will be trying to write at once?


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Zhu, Liang [AUTOSOL/ASSY/US]
Sent: Friday, May 03, 2019 10:01 AM
To: [hidden email]
Subject: [sqlite] seeking information on the throughput requirement using Sqlite

Hi Sqlite experts,

In my current project, we have the need to inserting and deleting data to and from the database between 1 and 10 ms for 24/7.   I am seeking the clarification on the following questions


  1.  what is the throughput requirements are possible using Sqlite API?, in other words, what is the max  speed can my inserting and deleting operation be?
  2.  When we inserting and deleting data at the speed mentioned above, what kind database maintenance do we need to do to maintain the performance of the database?
  3.  How is constant deleting and insert effect the database performance?


Thank you,

Liang Zhu | Lead Software Engineer | Branson Ultrasonics
Emerson Automation Solutions | 41 Eagle Road | Danbury, CT 06810 | USA
T (203) 796-2235 | F (203) 796-0380
[hidden email]<mailto:[hidden email]>

The information contained in this message is confidential or protected by law. If you are not the intended recipient, please contact the sender and delete this message. Any unauthorized copying of this message or unauthorized distribution of the information contained herein is prohibited.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: seeking information on the throughput requirement using Sqlite

Zhu, Liang [AUTOSOL/ASSY/US]
The planning transition rate is 1ms per 34-40Kb data,  we only have one connection trying to write to the database.   If we have two connection, will we running to database concurrency issue?

Thank you,
Liang

-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of David Raymond
Sent: Friday, May 03, 2019 11:11 AM
To: SQLite mailing list <[hidden email]>
Subject: [EXTERNAL] Re: [sqlite] seeking information on the throughput requirement using Sqlite

Within a single transaction SQLite can do things very quickly. But a reminder that there can be only 1 write transaction happening at a time. So my questions are: What is the planned _transaction_ rate? And how many different connections will be trying to write at once?


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Zhu, Liang [AUTOSOL/ASSY/US]
Sent: Friday, May 03, 2019 10:01 AM
To: [hidden email]
Subject: [sqlite] seeking information on the throughput requirement using Sqlite

Hi Sqlite experts,

In my current project, we have the need to inserting and deleting data to and from the database between 1 and 10 ms for 24/7.   I am seeking the clarification on the following questions


  1.  what is the throughput requirements are possible using Sqlite API?, in other words, what is the max  speed can my inserting and deleting operation be?
  2.  When we inserting and deleting data at the speed mentioned above, what kind database maintenance do we need to do to maintain the performance of the database?
  3.  How is constant deleting and insert effect the database performance?


Thank you,

Liang Zhu | Lead Software Engineer | Branson Ultrasonics Emerson Automation Solutions | 41 Eagle Road | Danbury, CT 06810 | USA T (203) 796-2235 | F (203) 796-0380 [hidden email]<mailto:[hidden email]>

The information contained in this message is confidential or protected by law. If you are not the intended recipient, please contact the sender and delete this message. Any unauthorized copying of this message or unauthorized distribution of the information contained herein is prohibited.

_______________________________________________
sqlite-users mailing list
[hidden email]
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=xvOxbL_JVO6tRWa--sJ-ZwGPDKJduQVT6rMEECKOzXI&s=MtBAWF7aqxaFuFE8faLYasnpTy6PDvqy01KYmBrmCSg&e=
_______________________________________________
sqlite-users mailing list
[hidden email]
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=xvOxbL_JVO6tRWa--sJ-ZwGPDKJduQVT6rMEECKOzXI&s=MtBAWF7aqxaFuFE8faLYasnpTy6PDvqy01KYmBrmCSg&e=
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: seeking information on the throughput requirement using Sqlite

Simon Slavin-3
On 3 May 2019, at 4:26pm, Zhu, Liang [AUTOSOL/ASSY/US] <[hidden email]> wrote:

> The planning transition rate is 1ms per 34-40Kb data,  we only have one connection trying to write to the database.   If we have two connection, will we running to database concurrency issue?

If you have normal hardware, using one connection to do all your writing will be simpler and allow you to use faster settings.  You can have one writing connection and many reading connections without slowing down access.  If you want to use two writing connections they may have to wait for one-another.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users