Hello Keith !
Probably have you seem in the code attached that also tried that once
and even the mmap but it doesn't had any visible improvement. Also due
to the data distribution 80% of the data inserts took 20% of the total
time and the other 20% of data insert took 80% of the total time.
The final database is has a poor overal performance for what I'm used
when using sqlite for small databases, I also tried to do it with mysql
and postgresql but the performance for a so simple database is terrible.
Cheers !
db.exec_dml("PRAGMA synchronous = 0;");
db.exec_dml("PRAGMA journal_mode = WAL");
//db.exec_dml("PRAGMA journal_mode = MEMORY;");
//db.exec_dml("PRAGMA journal_mode = OFF;");
//db.exec_dml("PRAGMA locking_mode = EXCLUSIVE;");
db.exec_dml("PRAGMA temp_store = MEMORY;");
//db.exec_dml("PRAGMA threads = 4;");
//db.exec_dml("PRAGMA mmap_size = 64000000;");
auto gigabyte = 1024*1024*1024;
db.exec_dml("PRAGMA mmap_size=" + (gigabyte*16));
//print("mmap_size", db.exec_get_one("PRAGMA mmap_size;"));
//db.exec_dml("PRAGMA cache_size = -64000");
//print("cache_size", db.exec_get_one("PRAGMA cache_size;"));
On 01/10/16 19:21, Keith Medcalf wrote:
Did you change the cache size? The default is rather small for a database of
22 GB.
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Domingo Alvarez Duarte
Sent: Saturday, 1 October, 2016 15:19
To: SQLite mailing list
Subject: Re: [sqlite] Why so much I/O ? Can sqlite be improved ?
Hello Simon !
Thanks for reply !
I already know your suggestions and if you look at the database schema
and the program used to insert data you can see that there is no
unnecessary indices active and all inside transactions.
About the vacuum I also understand the need to rewrite the whole
database but I'm not sure if it's really necessary to do almost 5 times
the database size in both reads and writes (also an equivalent amount of
I/O happened during insertions).
Cheers !
On 01/10/16 18:12, Simon Slavin wrote:
On 1 Oct 2016, at 9:27pm, Domingo Alvarez Duarte <mingo...@gmail.com>
wrote:
I'm using sqlite (trunk) for a database (see bellow) and for a final
database file of 22GB a "vacuum" was executed and doing so it made a lot
of I/O ( 134GB reads and 117GB writes in 2h:30min).
Can something be improved on sqlite to achieve a better performance ?
VACUUM rewrites the entire database. It will always do a lot of IO.
You should never need to use VACUUM in a production setting. Perhaps in a
once-a-year maintenance utility but not in normal use.
The fastest way to do lots of insertion is
DROP all INDEXes
DELETE FROM all TABLEs
Do your insertions, bundling up each thousand (ten thousand ?
depends on your system) uses of INSERT in a transaction
if you really want to do VACUUM, do it here
reCREATE all your INDEXes
ANALYZE
(the ANALYZE will also do lots of IO, not as much as VACUUM, but it may
speed up all WHERE / ORDER BY clauses).
Simon.
_______________________________________________
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
