@Simon I tried zpool scrub on both my disks and it returned nothing, I also tried executing stress[1] on the disk and no error appeared in the log or in stress itself. However, coping the sqlite db on an external disk connected via usb3 and formatted with Ntfs actually does the pragma quick_check in little more than 20 seconds and pragma integrity_check in 5 minutes.
So I think it is not an hardware problem, but Zfs messes up somewhat. Is there any known disagreement between Zfs and sqlite? In fact I have this feeling that my system is working fine in everything a part of sqlite. I read in the man that recordsize could be relevant. Otherwise I have to reinstall the system. But it is of course time consuming. [1] http://people.seas.harvard.edu/~apw/stress/ @nameless person known as sqlite-mail, Yes, I do have foreign keys. But each relate to a primary key; there are no explicit indexes on this primary keys, but they should not be needed because primary keys are indexed automatically. Or are they? @Marcus Grimm, It seems have no effect actually in my disk. The test goes fast for a while and slow down after. On Tue, Aug 18, 2015 at 9:12 PM, Marcus Grimm <mgrimm at medcom-online.de> wrote: > Just another guess: > Have you tried to increase the page chache drastically ? > I can remeber that "PRAGMA quick_check" is pretty slow > for bigger DBs without an increased page cache. > Maybe something like: > PRAGMA cache_size=500000; > PRAGMA quick_check; > > Marcus > > > Am 18.08.2015 um 12:38 schrieb Paolo Bolzoni: >> >> 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 >> > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
