On Thu, 21 Nov 2013 11:43:32 +0200 Baruch Burstein <bmburst...@gmail.com> 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- --- Eduardo Morras <emorr...@yahoo.es> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users