[Nix-dev] Merging the SQLite branch

[Eelco Dolstra]

Hi all,

I intend to merge the Nix SQLite branch 
(https://svn.nixos.org/repos/nix/nix/branches/sqlite) soon.  This branch 
replaces the current plain-text format of the Nix database in 
/nix/var/nix/db with a SQLite database.  This has several advantages:

- It has much better performance than the plain-text database, in 
particular because I/O latency is much reduced.  This was a real problem 
for Hydra, where operations such as "nix-store -qR" on a cold cache 
could take several minutes due to all the seeks caused by accessing 
thousands of small files.

- It takes much less disk space (e.g. from 124 MiB to 27 MiB on my home 
machine), especially on file systems with large cluster sizes.

- The implementation is much simpler.  For instance, we don't need a 
"referrer" table anymore because getting the referrers is simply a query 
on the references table.

- It doesn't have the problems that the old Berkeley DB-based database 
had: it should work over NFS (if you have lockd), doesn't require 
recovery when a Nix process crashes, doesn't require write access for 
read-only operations (even though that's not really needed on NixOS 
because of the Nix daemon), and doesn't have weird problems when it runs 
out of locks.

- It's easier to extend while maintaining the required transactional 
semantics.

- You can do queries on the database using the "sqlite3" command line 
tool.  So say that you want to know which path in the store has the 
highest number of referrers:

   $ sqlite3 /nix/var/nix/db/db.sqlite 'select path, (select count(*) 
from refs where reference = id) c from (select id, path from validpaths) 
order by c desc limit 1;'
   /nix/store/g89dk69xil5g5a0lwb59kggh07r621nq-builder.sh|8406

(The database schema is here: 
https://svn.nixos.org/repos/nix/nix/branches/sqlite/src/libstore/schema.sql)

We've been using the SQLite branch without problems for two months or so 
now in the build farm, but more testing is welcome.  On NixOS, you can 
test it by setting the following in configuration.nix:

   environment.nix = pkgs.nixSqlite;

and running "nixos-rebuild switch".  However, since this upgrades the 
Nix database to a new schema, rollback is a bit tricky.  To roll back, 
remove the line above from configuration.nix, then do:

   $ nixstore=$(readlink -f $(which nix-store))
   $ nixos-rebuild switch --fast
   $ $nixstore --dump-db > nix-db.dump # uses SQLite Nix to create a dump
   $ mv /nix/var/nix/db /nix/var/nix/db-backup
   $ nix-store --load-db < nix-db.dump # reload using non-SQLite Nix

-- 
Eelco Dolstra | http://www.st.ewi.tudelft.nl/~dolstra/