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
