Re: [sqlite] Why sqlite3 running at different time can have very different speed?
On 20 Oct 2019, at 12:36pm, Peng Yu wrote: > I am on Mac OS X. Is there anything equivalent? Thanks. Have the database stored on a Flash Drive. Eject (or unmount) the Flash Drive. Works on all versions of all operating systems. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why sqlite3 running at different time can have very different speed?
> You can try clearing Linux file system cache to convince > yourself that cache misses contributes to performance drop. > > Run this as root: > > # sync; echo 3 > /proc/sys/vm/drop_caches I am on Mac OS X. Is there anything equivalent? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why sqlite3 running at different time can have very different speed?
On 20 Oct 2019, at 1:54am, Peng Yu wrote: > How to prove the large time difference of sqlite3 is indeed due to > cache and where is the cache? Keep the database on an external drive (e.g. USB Flash drive). Unmount the drive to be sure that the cache has been cleared. > Why the caching used by sqlite3 can not persist for a longer time? The cache we're talking about is a file system cache, maintained by your operating system. SQLite has a cache only while it is running. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why sqlite3 running at different time can have very different speed?
Peng Yu wrote: I have never seen such a dramatic difference in non-sqlite3 > operations. For example, cat has some difference but is ~13%. > > $ time cat file.sqa > /dev/null > > real0m7.282s > user0m0.067s > sys0m2.371s > $ time cat file.sqa > /dev/null > > real0m6.316s > user0m0.062s > sys0m2.319s > > How to prove the large time difference of sqlite3 is indeed due to > cache and where is the cache? > You can try clearing Linux file system cache to convince yourself that cache misses contributes to performance drop. Run this as root: # sync; echo 3 > /proc/sys/vm/drop_caches See: https://www.tecmint.com/clear-ram-memory-cache-buffer-and-swap-space-on-linux/ Then run your test again (not as root!). Clearing Linux file system cache can of course degrade performance a lot the first time you re-run your test if it's I/O bound. The 2nd time, things will probably be cached and fast again. > Why the caching used by sqlite3 can not persist for a longer time? File system cache only hold the least recently used pages. If other applications do I/Os, Linux will eventually have to discard old pages to accommodate for more recently used ones. To persist longer, you need to have more memory and/or use less memory (kill unused programs...). All unused memory is used to cache files. Dominique ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why sqlite3 running at different time can have very different speed?
On Saturday, 19 October, 2019 18:55, Peng Yu wrote: >I have never seen such a dramatic difference in non-sqlite3 >operations. For example, cat has some difference but is ~13%. Have you been looking? >$ time cat file.sqa > /dev/null > >real0m7.282s >user0m0.067s >sys0m2.371s >$ time cat file.sqa > /dev/null > >real0m6.316s >user0m0.062s >sys0m2.319s That must be a really huge file to take so long to spool to the bitbucket. I'd need at least a 20 GB file to take that long. Even then the whole thing would be in memory after the first go, and the second go would take only milliseconds. You must have less memory than the size of that file. (and no, there is no real difference between those two timings). >How to prove the large time difference of sqlite3 is indeed due to >cache and where is the cache? You can't prove anything. The disk cache is part of the Operating System. >Why the caching used by sqlite3 can not persist for a longer time? The page cache used by sqlite3 only exists while the program is running. It releases the memory it is using when the program terminates (all programs do this). The Operating System, however, may "help" you by storing files in RAM assuming that you have enough RAM and that it is not needed for something else. >On Sat, Oct 19, 2019 at 7:29 PM Keith Medcalf >wrote: >> >> >> On Saturday, 19 October, 2019 18:05, Peng Yu >wrote: >> >> Looks like the difference between reading from disk and reading from >cache. >> >> >I see that sqlite3 can be very different in terms of run time. >> >> >$ time sqlite3 file.sqa -Atv > /dev/null >> > >> >real0m3.259s >> >user0m0.193s >> >sys0m0.704s >> >$ time sqlite3 file.sqa -Atv > /dev/null >> > >> >real0m0.148s >> >user0m0.086s >> >sys0m0.057s >> > >> ># Then do some else briefly >> > >> >$ time sqlite3 file.sqa -Atv > /dev/null >> > >> >real0m3.853s >> >user0m0.200s >> >sys0m0.828s >> > >> >$ time sqlite3 gncdhgncsym2htmlsqa.sqa -Atv > /dev/null >> > >> >real0m0.172s >> >user0m0.094s >> >sys0m0.065s >> > >> >file.sqa is of size 4.1G and 42282 rows. >> > >> >Does anybody know why there can be such big differences in speed? >> > >> >My testing machine is a MacbookAir. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why sqlite3 running at different time can have very different speed?
I have never seen such a dramatic difference in non-sqlite3 operations. For example, cat has some difference but is ~13%. $ time cat file.sqa > /dev/null real0m7.282s user0m0.067s sys0m2.371s $ time cat file.sqa > /dev/null real0m6.316s user0m0.062s sys0m2.319s How to prove the large time difference of sqlite3 is indeed due to cache and where is the cache? Why the caching used by sqlite3 can not persist for a longer time? On Sat, Oct 19, 2019 at 7:29 PM Keith Medcalf wrote: > > > On Saturday, 19 October, 2019 18:05, Peng Yu wrote: > > Looks like the difference between reading from disk and reading from cache. > > >I see that sqlite3 can be very different in terms of run time. > > >$ time sqlite3 file.sqa -Atv > /dev/null > > > >real0m3.259s > >user0m0.193s > >sys0m0.704s > >$ time sqlite3 file.sqa -Atv > /dev/null > > > >real0m0.148s > >user0m0.086s > >sys0m0.057s > > > ># Then do some else briefly > > > >$ time sqlite3 file.sqa -Atv > /dev/null > > > >real0m3.853s > >user0m0.200s > >sys0m0.828s > > > >$ time sqlite3 gncdhgncsym2htmlsqa.sqa -Atv > /dev/null > > > >real0m0.172s > >user0m0.094s > >sys0m0.065s > > > >file.sqa is of size 4.1G and 42282 rows. > > > >Does anybody know why there can be such big differences in speed? > > > >My testing machine is a MacbookAir. > > > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why sqlite3 running at different time can have very different speed?
On Saturday, 19 October, 2019 18:05, Peng Yu wrote: Looks like the difference between reading from disk and reading from cache. >I see that sqlite3 can be very different in terms of run time. >$ time sqlite3 file.sqa -Atv > /dev/null > >real0m3.259s >user0m0.193s >sys0m0.704s >$ time sqlite3 file.sqa -Atv > /dev/null > >real0m0.148s >user0m0.086s >sys0m0.057s > ># Then do some else briefly > >$ time sqlite3 file.sqa -Atv > /dev/null > >real0m3.853s >user0m0.200s >sys0m0.828s > >$ time sqlite3 gncdhgncsym2htmlsqa.sqa -Atv > /dev/null > >real0m0.172s >user0m0.094s >sys0m0.065s > >file.sqa is of size 4.1G and 42282 rows. > >Does anybody know why there can be such big differences in speed? > >My testing machine is a MacbookAir. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
