More details on the fsync solution

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

Today I’d like to go over more details on how we plan to solve the fsync issue with regards to places. This post is a bit heavy on technical details, but will go over how we plan to use views to abstract some of the hairier details out. My goal is to get more eyes on the solution to spot any potential issues before I get too far along in the implementation.

In the last post, I mentioned that we’d need to setup tables to track deletions from the permanent tables that we have temporary tables for. These are very straightforward, and only require one field each. For moz_places, our table looks like this:

moz_places_deleted
fk place_id

And for moz_historyvisits, our table looks like this:

moz_historyvisits_deleted
fk visit_id

Now, I’ll take a look at our views. The views will only be used for insertions, deletions, and updates. We cannot use them for selection because the performance is quite bad. The SQLite folks are working on this, but it will take some time before we can see that in release builds. The moz_places_view 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 id NOT IN (SELECT place_id FROM moz_places_deleted)

And moz_historyvisits_view looks very similar:

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

Each of these views has to have a few triggers created on it to allow for insertions, deletions, and updates. The triggers for moz_places_view are a bit simpler, so we’ll start there. Insertions are actually the easiest to manage. All we have to do is add the data to the temp table, and we’ll be set:

CREATE TEMPORARY TRIGGER moz_places_view_insert_trigger
INSTEAD OF INSERT
ON moz_places_view
BEGIN
  INSERT INTO moz_places_temp
  VALUES (MAX((SELECT MAX(id) FROM moz_places_temp),
              (SELECT MAX(id) FROM moz_places)) + 1,
          NEW.url, NEW.title, NEW.user_title, NEW.rev_host,
          NEW.visit_count, NEW.hidden, NEW.typed, NEW.favicon_id);
END

However, when we do a deletion, we have a bit more work to do. We have to cover the case where data was added to the temporary table, and the case where it only lives in the permanent table. This isn’t terribly hard though, it turns out. We just have to delete the record from the temporary table with the right id, and add that same id to moz_places_deleted:

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;
  INSERT INTO moz_places_deleted
  VALUES (OLD.id);
END

Updates are probably the most interesting. If the data isn’t already in the temporary table, we need to get it there first. However, we don’t want to overwrite any existing data in that table because it is more up-to-date than the data in the permanent one. Regardless of Once we get that data into the temporary table, we can update it:

CREATE TEMPORARY TRIGGER moz_places_view_update_trigger
INSTEAD OF UPDATE
ON moz_places_view
BEGIN
  INSERT INTO moz_places_temp
  SELECT *
  FROM moz_places
  WHERE id = OLD.id
  AND id NOT IN (SELECT id FROM moz_places_temp);
  UPDATE moz_places_temp
  SET url = NEW.url,
      title = NEW.title,
      user_title = NEW.user_title,
      rev_host = NEW.rev_host,
      visit_count = NEW.visit_count,
      hidden = NEW.hidden,
      typed = NEW.typed,
      favicon_id = NEW.favicon_id;
END

Like moz_places_view, moz_historyvisits_view‘s insertions aren’t terrible. There is one additional thing we need to worry about though. Currently, places uses a trigger on insertions and deletions from moz_historyvisits that updates moz_places.visit_count. That would involve a write, however, so we don’t want to do that. Instead, we update moz_places_view.visit_count and let the view handle the details:

CREATE TEMPORARY TRIGGER moz_historyvisits_view_insert_trigger
INSTEAD OF INSERT
ON moz_historyvisits_view
BEGIN
  INSERT INTO moz_historyvisits_temp
  VALUES (MAX((SELECT MAX(id) FROM moz_historyvisits_temp),
              (SELECT MAX(id) FROM moz_historyvisits)) + 1,
          from_visit = NEW.from_visit,
          place_id = NEW.place_id,
          visit_date = NEW.visit_date,
          visit_type = NEW.visit_type,
          session = NEW.session);
  UPDATE moz_places_view
  SET visit_count = visit_count + 1
  WHERE moz_places_view_id = NEW.place_id;
END

Deletions also have to worry about the trigger. Once again, we update moz_places_view.visit_count accordingly:

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;
  INSERT INTO moz_historyvisits_deleted
  VALUES (OLD.id);
  UPDATE moz_places_view
  SET visit_count = visit_count - 1
  WHERE moz_places_view.id = OLD.place_id;
END

We only have one case where we update moz_historyvisits, and I’m not even sure if it’s valid yet. As a result, this trigger may not actually be needed. The update trigger looks nearly identical to the on used for moz_places:

CREATE TEMPORARY TRIGGER moz_historyvisits_view_update_trigger
INSTEAD OF UPDATE
ON moz_historyvisits_view
BEGIN
  INSERT INTO moz_historyvisits_temp
  SELECT *
  FROM moz_historyvisits
  WHERE id = OLD.id
  AND id NOT IN (SELECT id FROM moz_historyvisits_temp);
  UPDATE moz_historyvisits_temp
  SET from_visit = NEW.from_visit,
      place_id = NEW.place_id,
      visit_date = NEW.visit_date,
      visit_type = NEW.visit_type,
      session = NEW.session;
END

That’s the detail-heavy plan so far. If you have any concerns or spot any issues, please let me know with a comment here, or feel free to send me an e-mail.