Skip navigation

Tag Archives: fsync

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.

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.

You know that “fun” fsync bug we had with Firefox 3 that primarily hurt Linux? Well, turns out that it also hurts us with applications on solid state drives (like, Portable Firefox), which makes it even more “fun”. For Firefox 3, we checked in a patch that allows for people to change the default synchronous setting that SQLite uses when a database connection is opened. This was really just a stopgap measure. I’ve spent the last month coming up with real solutions to the problem, and I think I’ve finally got one that will greatly help the issue, and not regress our current performance.

The basic idea of this plan is to reduce the number of times we write to the most active tables in places – moz_places and moz_history_visits. In order to accomplish this, we need to set up temporary tables that live in memory (to avoid the writes) that shadow these two tables. Every so often (time to be determined), we’ll need to write out the temporary tables to their permanent ones living on disk. In order to prevent the UI from hanging, we’ll have to do this on a background thread. I’ve already got patches up to make mozIStorageConnection threadsafe so we can use a connection object on more than one thread, and to get a background thread setup for places to use.

The solution is a bit more complicated than this, since we’ll also have to keep track of entries that we want to delete and flush those when we write everything out as well. The table that keeps track of entries to delete would also have to live in memory so we don’t write and fsync. The good news is that this only manages to regress performance by about 3.2 x 10-6% in my testing, which is something I think we can take.

There is a cost to this plan though. Any query to the places database is going to get a lot hairier to accomplish to get the most recent data. This is because you have to query against both the temporary and the permanent tables. Essentially, any time you want to select from moz_places, you have to use a subselect instead of just the table name that looks something like this:

FROM (
  SELECT * FROM moz_places_tmp
  WHERE url IN (:test)
  UNION All
  SELECT * FROM moz_places
  WHERE url IN (:test)
  AND +id IN (SELECT id FROM moz_places_tmp)
) AS h

That doesn’t even include the bits for deleting an entry, which complicates it a bit more still.

Some of this abstraction can sit behind a view with triggers setup so the code can be simple. The trigger will handle all the details. I’ve put enough thought into this to know that it’s doable, but haven’t figured out the exact triggers that are needed as of yet.

I’m ecstatic that I’ve finally got a viable solution to this issue. I hope to get all the work done and reviewed in time for the next public release of Firefox 3.1 (be it an alpha, or a beta). No bugs have been filed yet (other than the ones previously mentioned) since I haven’t figured out how I want to split the work up yet to make it easy to get reviewed. That will all happen on Monday though, so stay tuned!