Hello Rob !
How do you use those big databases ?
Append only ? Read only ? Constant read write ? Foreign keys ?
Could you show the schema to talk about it ?
Cheers !
On 02/10/16 17:44, Rob Willett wrote:
We have production databases with 30-50GB and have no issues with
managing them. Mind you we have more memory, though we only have 8GB
as thats all our VM provider allows us.
After saying that we have never seen any performance issues that
weren't due to our schemas or our bad design. Our working assumption
is that if there's a problem, its our fault before we blame SQLite. So
far that working assumption had held true :)
We did some tests (just for the hell of it) to 200GB with dummy data
and had no issues I can recall.
I know that other people on this list have far, far bigger production
databases than us.
I'm not a SQLite expert but I look on machines with 2GB of main memory
as rather small for this sort of thing.
Rob
On 2 Oct 2016, at 21:35, Domingo Alvarez Duarte wrote:
Hello !
I'm still fighting to use sqlite with a 20GB database and now I'm
trying to update a table with 980M rows but sqlite is eating all my
memory (2GB) and making blocking my computer (too much swap).
I'm in doubt if sqlite can really manage databases above 1GB.
Have someone any experience with big databases ?
Cheers !
The query:
--explain query plan
update bolsas_familia set favorecido_id=(select id from favorecidos
where nis=favorecido_id);
Query plan:
selectid|order|from|detail
0|0|0|SCAN TABLE bolsas_familia
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 0
0|0|0|SEARCH TABLE favorecidos USING COVERING INDEX
favorecidos_nis_idx (nis=?)
The schema is:
CREATE TABLE bolsas_familia(
id integer primary key,
favorecido_id integer not null,
valor_parcela numeric,
mes_competencia integer
); //980M rows
CREATE TABLE "favorecidos"(
id integer primary key,
nis integer,
nome varchar collate nocase_slna,
municipio_id integer,
programa_id integer not null,
fonte_finalidade_id integer not null,
first_ano_mes integer
); //14M rows
CREATE UNIQUE INDEX favorecidos_nis_idx ON favorecidos(nis);
Cheers !
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
