On Thu, 21 Nov 2013 11:43:32 +0200
Baruch Burstein <[email protected]> wrote:
> Hi all,
>
> I know SQLite is supposed to support DB sizes in the TB (I think the
> default configuration can reach 1TB). I am curious if anyone actually uses
> SQlite at anywhere near this. Does anyone use it regularly for DBs 500GB+
> in size, or with tables containing 10 billion rows+? How much concurrency
> does your use require? How long do selects take (assuming indexes are set
> correctly?) Are there problems of locking ("normal" SQLite doesn't usually
> suffer from locking since transactions are very quick, but if transactions
> can be in the order of 100's of ms, I think locking can easily happen if
> the DB is accessed concurrently a few times a second, though I am not sure
> if this may only apply to writes).
>
> I understand that the answer to most of these questions can be very
> hardware (and software) dependent, but I am just trying to get a feel for
> SQLite's applicability for a project I am working on that may reach limits
> like these.
Depends on what type/kind of use you need for your data. If you are going to do
a lot insert, update, delete, perhaps sqlite isn't for you.
If it's principal use is for select, when populate the tables, do it pre-sorted
by the data primary key or by the colum which makes a better quality index for
your use (look for 'low quality indexes' in sqlite docs, and do the opposite).
Normalize your db as much as you can, but not more ;) .
Sqlite allows you to attach up to 30 db files. Split your data between 2-3 db
and put each one in different disks.
Increase cache size before creating any table, other RDBMS uses a lot of memory
from several MBs to GBs, give Sqlite cache 500MB-1GB for example.
Write your queries in different ways and test which is better, there are some
tricks with indexes, joins..., that can help you (study documentation)
Set STATS3 or 4 for analyze your db data before creating any table.
Set autovacuum full before creating any table, even if you don't plan to
delete/update data. If I Remember Correctly (if not correct me please),
autovacuum adds metadata to db file that allows Sqlite engine do some internal
works faster.
>
> Thanks,
> Baruch
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--- ---
Eduardo Morras <[email protected]>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
