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.
I do expect the single-insert performance to be much lower than the
bulk insert performance.
We are using the WAL, and this benchmark includes the cost of a
checkpoint at the end. Futziing with the WAL autocheckpoint size has
little impact for smaller WAL sizes. My working assumption is that
using the WAL for an insertion-heavy workload would consume roughly
half of my available throughput due to checkpoint writes. Indeed, if
the autocheckpoint level is raised high enough that the entire
benchmark fits in the WAL, then I do observe that the write throughput
asymptotically approaches 2.6 MB/s instead of 1.3 MB/s.
That leaves one more factor of two somewhere.
The table in question has the schema:
```
CREATE TABLE IF NOT EXISTS `chunks` (
`target_id` INTEGER NOT NULL,
`chunk_num` INTEGER NOT NULL,
`chunk_blob` BLOB NOT NULL,
PRIMARY KEY(`target_id`,`chunk_num`)
);
```
Other factors that might help understand our workload:
Blobs are a little less than 1 kB each, and we're using the default DB
page size (4 kB). So I would expect that SQLite would pack about 3
rows per page, leaving some extra for primary keys, field delimiters
and other metadata. I understand that the composite primary key
implies an index table that goes with the blobs, which implies some
inherent write amplification to account for the index. Still, my
expectation is that the write throughput added by the index should be
close to the size of the key columns, not the blob columns. So 2x
still seems too high.
Any other ideas?
Thanks,
--
Jonathan Brandmeyer
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
