Moving udd away from sqlite

[James Westby]

Hi,

We're interested in moving our deployment of udd away from sqlite to
postgres, and we're interested in doing the same thing for the package
importer deployment.

There are two main reasons for this: 1) that we were asked to by
canonical sysadmins, and 2) because it's the right thing to do. udd has
evolved past the point where sqlite is a good choice for the db.

It's not purely about cleanliness though. Currently udd will wait up to
30s to acquire a sqlite lock, and I believe that it frequently uses a
significant portion of that time. Therefore moving to postgres with its
improved locking would improve the performance of the importer (though I
have no numbers to show that it would significantly affect latency or
throughput.)

As you know, we've tried a first step towards this previously. We ported
the db access to storm, so that we could replace the db underneath,
taking advantage of storm's support for multiple database types.

That didn't go so well, as after a few hours/days of usage it would
deadlock due to sqlite locking. That's one of the reasons why sqlite
isn't a great choice, as it shows we are currently operating close to
the threshold that sqlite allows.

After lots of head-scratching I believe I've worked out why changing to
storm made this happen when the underlying db was the same. Storm forces
sqlite to operate at a higher isolation level, so udd was taking locks
more frequently or holding them for longer, leading to more contention
and eventually the deadlock.

I haven't proven that it would fix the deadlock issue, just that reduces
the incidence of "database is locked" errors on a test instance rigged
to stress the db even more.

As I see it we have N options:

  1) Deploy the same code as last time (with some fixes for bugs that
     Max spotted that were unrelated to locking.) Live with the degraded
     performance while we migrate to postgres.

  2) Deploy the same code with a change to stop storm forcing the
     isolation level. This may fix the deadlock issues, but we won't be
     sure. It's also not clear that storm will function correctly in
     that condition.

  3) Deploy the storm code, but migrate the db to postgres at the same
     time. It introduces more changes at the same time so is riskier,
     but we're fairly sure we won't see the locking issues with
     postgres. I'm pretty sure that we can still rollback from this
     fairly easily, as we can just go back to the sqlite dbs and the
     importer will pick up from where it left off, duplicating some
     work.

  4) Leave the package importer on sqlite and do something else for our
     instance (fork basically)

Are there any others?

What do you think of these options? We'd like to avoid option 4 if
possible.

Thanks,

James