Issues Issues…

This is part three in a continuing series about how we are working around the slow fsync issue in Mozilla. Part one can be found here, and part two here. You may find the schema diagram of places to be a bit helpful when reading this post.

timeless found an interesting flaw to my previous proposal. If you were to visit a page, delete it, and then visit another page not yet seen before, you will never see it in any of your queries. When you flush out all the “deleted” things, it will go away forever (unless the ordering was a bit different than what I expect, but that’s besides the point). This is because of our id selection algorithm used in the insertion triggers.

I ended up talking to dietrich on irc about this, and we decided that deleting isn’t a common case, so we should feel free to do the write (with the needed fsyncs) at that point. As a result, our views change, as well as a few of our triggers.

moz_places_view now looks like this:

SELECT *
FROM moz_places_temp
UNION ALL
SELECT *
FROM moz_places
WHERE id NOT IN (SELECT id FROM moz_places_temp)

And moz_historyvisits_view now looks like this:

SELECT *
FROM moz_historyvisits_temp
UNION ALL
SELECT *
FROM moz_historyvisits
WHERE id NOT IN (SELECT id FROM moz_historyvisits_temp)

The trigger for deletion on moz_places_view now looks like this:

CREATE TEMPORARY TRIGGER moz_places_view_delete_trigger
INSTEAD OF DELETE
ON moz_places_view
BEGIN
  DELETE FROM moz_places_temp
  WHERE id = OLD.id;
  DELETE FROM moz_places
  WHERE id = OLD.id;
END

Lastly, the trigger for deletion on moz_historyvisits_view now looks like this:

CREATE TEMPORARY TRIGGER moz_historyvisits_view_delete_trigger
INSTEAD OF DELETE
ON moz_historyvisits_view
BEGIN
  DELETE FROM moz_historyvisits_temp
  WHERE id = OLD.id;
  DELETE FROM moz_historyvisits
  WHERE id = OLD.id;
  UPDATE moz_places_view
  SET visit_count = visit_count - 1
  WHERE moz_places_view.id = OLD.place_id;
END

The net result is two fewer in-memory tables, and slightly less complicated view queries. There isn’t much of a change with the triggers.

Many thanks to timeless for catching that. If you have any other concerns or spot any issues, please let me know with a comment here, or feel free to send me an e-mail.