OCW favorites, sublists, performance, caching, and mysql
Igal Koshevoy
mostly for Reid, but may be a curiosity to others. I welcome QA and
feedback because some of this is visible to users, an there's always
code to review if you feel up for it.
I believe I'm done reworking the user favorites and sublists that
generate listings of sessions/proposals for a record (e.g., the
proposals/sessions for a user, speaker, track, room, etc). The new code
resolves some annoyances. I've built out specs for these too. You'll
find the new code in the with_user_favorites_and_child_events branch.
While working on this, I had some realizations:
- User favorites can be clicked a lot, changing data more rapidly than
anything else in our db.
- Each time the browser hits an uncached page, the server needs to
generate it.
- Generating some pages was very inefficient, but still fine because of
cacing.
- Each time favorites are added or removed, the caches get cleared and
require updating.
- Each time a page with favorites is loaded, it gets a second AJAX hit
to increase the load.
- SQLite is awful at concurrent writes, and writes while generating will
aggravate it.
- SQLite lacks MVCC and locks the database till done, which can cause
deadlocks.
- If I try hard enough, I can make SQLite deadlock on my dev machine.
- If people begin using the favorites enough, this may push SQLite over
the edge.
Here's what I've done:
- Rewrote code to make better use of selective queries to limit what's
fetched, eager fetching to limit how many queries are made, and defer
and delay the queries so they're done within cached view blocks to
eliminate queries from happening at all if there's a cache. This makes
many actions much faster.
- Made sure all the code runs correctly on both SQLite and MySQL.
- Came up with a way to migrate the database's contents between SQLite
and MySQL.
- While SQLite can be faster than MySQL on some operations, MySQL is
2-5x faster than SQLite when writing data even with a single client.
With multiple clients doing concurrent writes, the difference is even
larger. MysQL processes writes more quickly and has MVCC, which make it
less prone to deadlocks.
- I used the demo server -- http://ocw.demo.opensourcebridge.org/ -- and
migrated its SQLite data and am currently running it off MySQL, all
seems well.
- PostgreSQL is still not an option because there's something wrong with
how it interacts with binary objects and I don't have time to debug the
drivers.
Other than providing a generic "rake db:fetch" replacement and update
the backup process to dump the database, I believe we could migrate over
the main server after we poke at this one a bit.
Anyway, additional review of the code I've been committing and/or poking
at the demo server to provide QA and feedback will be appreciated. Thanks!
-igal