Moving udd away from sqlite

[Jelmer Vernooij]

On Thu, Jun 14, 2012 at 06:23:24PM -0400, James Westby wrote:
> 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.

Moving UDD to postgres would be a good idea in general. I agree it
would be nice to avoid 4 - UDD wouldn't benefit from your
changes to move to postgres *and* we would end up with two codebases
and the problems associated with that.

(2) seems like a gamble, perhaps even more so than (3) and it gets us
less far. (1) is probably more work, since you're doing two migrations
rather than one.

It would be nice to have a bit more hands on deck if there is another
attempt to migrate to storm, in case any problems come up.

Cheers,

Jelmer
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 198 bytes
Desc: Digital signature
URL: <https://lists.ubuntu.com/archives/ubuntu-distributed-devel/attachments/20120615/f257b2db/attachment.pgp>