On 17 Jul 2018, at 12:20pm, Rob Willett <rob.sql...@robertwillett.com> wrote:
> Do you have any pointers to stuff we can read up on? We don't understand your > comment "SQLite spent that whole time accessing your 50GB database file in an > apparently random order." and would like to try and get more information > about it. I don't know much about specific virtualisation packages and have no recommendation. I was just comparing virtual machines in general with operations on "actual metal" as you put it. SQLite database files are split up into pages. Each page contains data for exactly one table or index. So even just a table with a primary index requires two different kinds of pages, one for the row of data, one for the corresponding entry in the primary key index. And making changes to them both requires changes at other locations: the places where the page indexes are stored. Worse still, unless all your indexes are monatonic (with values in those indexes only ever increasing) anything progressing through them in order would not be reading through the database file in order. This means you get continuous cache misses: the next piece of data you need is rarely already in the virtual computer's cache, because it's usually in a part of the file far away from the one you just read. On a real computer, storage is only one motherboard connection and storage driver away from the CPU. On a virtual computer the virtual storage is on a file server far away from the processing, and each time data isn't already in the processor's cache something has to work out where, in the racks of file servers, that page of file is stored, talk to that file server, and route traffic back and forth to the virtual processor. So file operations are more slow and expensive than they are for real computers. And an operation which generates cache misses for 50Gig of data ends up doing more work done than it would for a simple "real metal" setup. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
