Hello ! Do you have foreign keys on your tables ? And if so do you have indexes on then ?
A database with foreign keys and no indexes can run very slow for mas insert/update/delete ? Cheers ! ? > Tue Aug 18 2015 12:38:51 CEST from "Paolo Bolzoni" ><paolo.bolzoni.brown at gmail.com> Subject: Re: [sqlite] ATTACH DATABASE >statement speed > > It really seems something strange happens at filesystem level. > > This is a simple copy of slightly less than 1gb. It needs 9 seconds > including sync. > % date && sudo rsync -Pr italy-latest.osm.pbf / && sync && date > Tue Aug 18 19:22:23 JST 2015 > sending incremental file list > italy-latest.osm.pbf > 946,976,283 100% 123.88MB/s 0:00:07 (xfr#1, to-chk=0/1) > Tue Aug 18 19:22:32 JST 2015 > > > However, when I start sqlite3 db 'PRAGMA quick_check;' the IO looks > normal for a while. > (I hope gmail don't mess up with the formatting...) > > 60, 90, 80 MB/s is kinda expected: > 08/18/2015 07:27:38 PM > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s > avgrq-sz avgqu-sz await r_await w_await svctm %util > encplate > 0.00 0.00 820.00 13.00 62.11 0.26 > 153.34 1.87 2.27 1.14 73.46 1.20 99.80 > 0.00 0.00 1214.50 0.00 94.58 0.00 > 159.49 0.96 0.78 0.78 0.00 0.78 95.20 > 0.00 0.00 1008.50 22.00 78.09 0.41 > 155.99 1.50 1.46 0.96 24.16 0.93 95.80 > > but after some seconds it drops terribly to less than 10MB/s > 08/18/2015 07:29:04 PM > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s > avgrq-sz avgqu-sz await r_await w_await svctm %util > encplate > 0.00 0.00 124.00 3.50 9.88 0.12 > 160.72 1.67 12.99 11.21 76.14 7.65 97.50 > 0.00 0.00 69.00 18.00 5.68 0.29 > 140.55 1.81 20.92 14.15 46.86 11.38 99.00 > 0.00 0.00 86.00 0.00 7.05 0.00 > 167.91 1.04 12.03 12.03 0.00 11.24 96.70 > > And so, going to 10MB per second it can easily require few hours... > > > I am out of ideas, but thanks for all the support. > > > > On Tue, Aug 18, 2015 at 7:26 PM, Simon Slavin <slavins at bigfraud.org> wrote: > > >>On 18 Aug 2015, at 7:30am, Paolo Bolzoni <paolo.bolzoni.brown at gmail.com> >>wrote: >> >> >>>Any other idea of what can I try? Perhaps my filesystem is misconfigured? >>> >> The long time you quote is not standard for SQLite and I don't think >>anyone can help you solve it by knowing picky details of SQLite. I'm even >>surprised that it changed with your -O0 compilation since this suggests >>features of your compiler I didn't know about. >> >> It's possible one of the developer team can help but they're reading this >>and can pitch in if they think so. >> >> Simon. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
