Writing archive

I inserted one million rows into SQLite with 0, 1, 3 and 6 indexes. The table alone took 0.8 s and six indexes took 8.5 s, but the biggest cost was not the number of indexes: one index on a random column made SQLite pass 4 GB to write() for a 106 MB file.

A successful connect() does not mean that the server program saw you. I filled the accept queue of a local server and watched clients connect, send data and wait, while the kernel kept their connections in a queue, half open, or nowhere at all.

SQLite says SCAN or SEARCH before it runs a query. I measured what the two words mean on one million rows: 16,469 pages against 7, and about 34 ms against 6 microseconds when the file is cached. With a cold cache the gap is much smaller.

I added one index to a SQLite table of one million users and read the file page by page. The index is a second sorted copy of the emails, each one with the rowid of its row, and when I broke one entry the database returned a row that does not exist.

On my laptop connect() returns in about 8 microseconds, and the same call can also block for 133 seconds. I followed it with strace and ss: the handshake that the kernel does alone, a port where nobody listens, and a server that never answers.