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
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)
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
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
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
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
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.
6 replies on “More details on the fsync solution”
This seems pretty complicated. Is it really impossible to just make SQLite address the problem internally?
If it were that simple, this probably would have been solved for Firefox 3. Sadly, for ext3 (and many other linux file systems) we have to work around a file system bug. In the case of SSD storage like a USB key, it’s just plain slow. We need to get these writes off the main thread anyway, so this is overall a good thing to do.
I do however agree that it’s complicated. It is, however, the least complicated solution we’ve been able to really come up with.
[…] Calendar More details on the fsync solution […]
On Vista you guys should use the new transactional NTFS API so you don’t have to do this kind of fsync ping-ponging nonsense to try to emulate transactions poorly.
Ditto for unixish systems that use manly file systems like ZFS.
That doesn’t actually provide us with everything we need. We’ve looked into it.
[…] so that it no longer needs to write to the database every time you visit a page, a project well chronicled on Shawn’s blog. Spurred on by one of Shawn’s performance wins, Ed Lee was able to […]