On Mon, 21 Oct 2019 at 23:28, Jonathan Brandmeyer <jbrandme...@planetiq.com> wrote:
> Or, how many times is each page written by SQLite for an insert-heavy > test? The answer appears to be "4", but I can only account for two of > those four. > > I'm working on an embedded system that uses a log-structured > filesystem on raw NAND flash. This is not your typical workstation's > managed flash (SATA/NVMe), or portable managed flash (SD/USB). It's a > bare-nekkid ONFI-speaking chip. All reads and writes are one 2kB page > at a time. There is no readahead, and no write buffering by the > driver or filesystem for page-sized writes. > > We got the following performance numbers out of the flash storage: > > Streaming reads through the filesystem: 7.5 MB/s. > Streaming writes through the filesystem: 5.4 MB/s. > Single insert performance through SQLite: 0.2 MB/s. > Bulk insert performance through SQLIte: 1.3 MB/s, asymptotic for very > large transactions. > There's perhaps a measurement missing here: random-access/seeking writes through the filesystem. Which doesn't sound like it should be a factor based on the technology involved, but it's more reflective of sqlite's workload when updating the DB. The smallest possible insert here modifies at least one DB page for the table and one for the index, so that's 8kb written to the journal and 8kb to the main DB. But bulk inserts should greatly reduce the impact of index writes, as you said. Hm, how does deletion/truncation perform on the target filesystem? -Rowan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
