Size limits

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

Size limits

Andy-3
What are reasonable limits for size Sqlite3 database file and large blobs?
_______________________________________________
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: Size limits

Simon Slavin-3
On 7 Jan 2020, at 10:29am, Andy <[hidden email]> wrote:

> What are reasonable limits for size Sqlite3 database file and large blobs?

Unfortunately, recommendations (rather than hard limits) are closely tied to your OS and hardware.  This is because the things you want to avoid are things like busting your cache, or memory faults, and the sizes of those are set by your own computer and OS.

Experience of this list has shown that we really can't make any firm limits without knowledge of your setup, and you'd have to find someone with an identical setup to have a useful conversation.  We can only urge you to test things out and see for yourself where your performance drops off.

In terms of hard limits, you might like to read this:

<https://sqlite.org/limits.html>
_______________________________________________
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: Size limits

Rob Willett
Andy,

I can state that SQLite easily went up to 100GB with zero issues for us.
We decided to change the structure of our database and we reduced our
database size as we didn't want to incur extra VPN costs. We also
realised we designed the database wrong and it had a lot of useless data
in...but thats another different story :)

I have heard people talking about 300GB SQLite databases and I have no
reason to doubt that SQLite would work. As Simon says, environmental
issues may come into affect before you hit SQLite limits.

I can't comment on large blobs as not sure what large is, 1MB, 100MB,
1GB?

Rob

On 7 Jan 2020, at 11:21, Simon Slavin wrote:

> On 7 Jan 2020, at 10:29am, Andy <[hidden email]> wrote:
>
>> What are reasonable limits for size Sqlite3 database file and large
>> blobs?
>
> Unfortunately, recommendations (rather than hard limits) are closely
> tied to your OS and hardware.  This is because the things you want to
> avoid are things like busting your cache, or memory faults, and the
> sizes of those are set by your own computer and OS.
>
> Experience of this list has shown that we really can't make any firm
> limits without knowledge of your setup, and you'd have to find someone
> with an identical setup to have a useful conversation.  We can only
> urge you to test things out and see for yourself where your
> performance drops off.
>
> In terms of hard limits, you might like to read this:
>
> <https://sqlite.org/limits.html>
> _______________________________________________
> 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: Size limits

Andy Bennett
In reply to this post by Andy-3
Hi Andy,

> What are reasonable limits for size Sqlite3 database file and large blobs?

Here are some benchmarks on BLOB performance:

https://sqlite.org/intern-v-extern-blob.html


However, see the note here in section 4.1 about using the incremental BLOB
I/O routines so that you don't thrash your memory allocator:

https://sqlite.org/malloc.html#_computing_and_controlling_parameters_m_and_n


limits.html gives you the actual hard SQLite limits:

https://sqlite.org/limits.html

The default limit for BLOBs is 1GB, but you can raise or lower that value
at compile-time. The maximum is 2GiB.


The overall maximum database size is 140TB (128TiB).


Here are some more reports on using SQLite for BLOB storage:

https://sqlite.org/fasterthanfs.html

https://sqlite.org/affcase1.html


In reality you will be limited by your ability to find the BLOBs
efficiently. i.e. what other metadata your store with them and how you
index them. If your individual data items are larger than 1GB then you'll
probably need to look at storing the underlying data across several BLOBs.




Best wishes,
@ndy

--
[hidden email]
http://www.ashurst.eu.org/
0x7EBA75FF
_______________________________________________
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: Size limits

David Raymond
In reply to this post by Andy-3
Along with the other replies already mentioned I'll pipe in with a reminder that any large blob fields should be the final field in a table's definition. Due to how the data is stored, to get the data for any particular field, SQLite has to go through/decode the data for all previous fields in a record.

So if you have a layout of...

id integer primary key,
big_blob_field blob,
some_field_you_want text

... then if you "select some_field_you_want from table;" and a record has a 1GB blob in big_blob_field, then it'll have to go through 1GB of linked list overflow pages to get the value for some_field_you_want. (Some optimizations may apply)

So be sure to define it as

id integer primary key,
little_field_1 text,
little_field_2 int,
little_field_3 float,
big_blob_at_the_end blob



-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Andy
Sent: Tuesday, January 7, 2020 5:30 AM
To: [hidden email]
Subject: [sqlite] Size limits

What are reasonable limits for size Sqlite3 database file and large blobs?
_______________________________________________
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: Size limits

Andy-3
I try create NNTP server. For text groups blobs will small - 5-80 kB, for
binary  - 0.8 MB

wt., 7 sty 2020 o 13:31 David Raymond <[hidden email]> napisaƂ(a):

> Along with the other replies already mentioned I'll pipe in with a
> reminder that any large blob fields should be the final field in a table's
> definition. Due to how the data is stored, to get the data for any
> particular field, SQLite has to go through/decode the data for all previous
> fields in a record.
>
> So if you have a layout of...
>
> id integer primary key,
> big_blob_field blob,
> some_field_you_want text
>
> ... then if you "select some_field_you_want from table;" and a record has
> a 1GB blob in big_blob_field, then it'll have to go through 1GB of linked
> list overflow pages to get the value for some_field_you_want. (Some
> optimizations may apply)
>
> So be sure to define it as
>
> id integer primary key,
> little_field_1 text,
> little_field_2 int,
> little_field_3 float,
> big_blob_at_the_end blob
>
>
>
> -----Original Message-----
> From: sqlite-users <[hidden email]> On
> Behalf Of Andy
> Sent: Tuesday, January 7, 2020 5:30 AM
> To: [hidden email]
> Subject: [sqlite] Size limits
>
> What are reasonable limits for size Sqlite3 database file and large blobs?
> _______________________________________________
> 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
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users