On Thursday, 31 October, 2019 10:52, Simon Slavin <slav...@bigfraud.org> wrote:
>On 31 Oct 2019, at 4:29pm, Keith Medcalf <kmedc...@dessus.com> wrote: >> If the elapsed time is much greater than the sum of user+sys time then >> I would suspect it is still I/O thrashing (or the machine is really busy >> doing something else -- those original timings represent only a 57% >> process dispatch rate, which is pretty low). > I'm betting it's a rotating hard disk with a slow spin speed, and most of > the time is spent waiting for the disk to be in the right place. > As Dominique Devienne spotted, there is now some doubt about the > comparative figure. Was the MariaDB filespace hosted on this same hard > disk ? While nice, that is irrelevant. MariaDB is a client/server database so it probably uses a database cache that is considerably larger by default than the SQLite default. The SQLite default cache is 2 MB which is 500 pages at the default page size of 4K. >The original post had >> The largest table contains about 230'000 entries. > Five tables and eleven indexes. Sixteen pages and sixteen page indexes. > Even if you think that all the required indexes for FOREIGN KEY lookup > are now available, I think that 88 minutes is still longer than SQLite > should take for anything but a slow disk. That is why I asked about the cache_size. If the cache_size has not been increased from the default then a large operation will be thrashing and spilling pages sto perform a large operation like that. For I/O intensive operations the size of SQLite's page cache makes a huge difference, far more than an adequate filesystem or block cache in reducing I/O. -- 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
