It’s not a new “feature,” but speed and reliability are a key component of the appeal of a site. A few weeks ago we reported on a new server configuration that cut page-generation times in half (see LibraryThing is Faster). Now we’re reporting on some database tweaks that have made the process of finding, adding and editing data faster.

Like all large database-driven sites, LibraryThing can’t rely on a single database. Instead, we have a single “master” database which replicates its changes to a number of “slave” databases. (See Wikipedia: Database Replication.) Because sites “read” a lot more than they write, scalability is achieved doing most “reads” from the slave machines, which can be multiplied almost indefinitely to deal with increased traffic. Unfortunately, writes still need to move from the master to the slave, which necessarily involves a slight lag. If the lag becomes too great you get stale data or processes that pause (and pile up!) waiting for fresh information to pass from the master to the slaves. You also get bugs. And annoyances, like Talk posts not appearing right away. Replication lag also degrades query speed and therefore site speed generally.

As a heavy database-driven site running on relatively cheap hardware we’ve sometimes struggled to keep replication delay down. The problem is particularly acute on our weaker slaves. Fortunately, our ongoing review of performance issues has disclosed series of code and “schema” changes that have substantially improved replication speed. Here’s a chart of the average replication delay on one of our database servers over the last ten days. As you can see, two changes have made a big difference!

We’re excited about the progress we’ve made so far. It exceeded our expectations. Our performance review is continuing. We won’t stop until LibraryThing is as fast and reliable as it is powerful, rich in data and fun to use!

Come talk about this.

I’m happy to report that LibraryThing’s servers have undergone a considerable improvement. LibraryThing’s server administrator, John Dalton (member Felius), carried through an ambitious restructuring of how LibraryThing’s considerable traffic was distributed across our web servers. And the restructuring worked out.

Across the site “pages” have been sped up by about 100%, dropping from a median of about 1 second to about 0.5 seconds. Catalog, or “Your books,” pages have dropped from a median of 1.6 seconds to 0.8 seconds. Response has also become more predictable, with much a lower standard deviation of response times.

Good, but not everything. While server-rendering speed is important, it isn’t the only factor in perceived speed–the other two being transfer and rendering by the web browser. Server improvements also hide the fact that rendering times also include database actions, which were not improved by this change. The truly bothersome pages on LibraryThing are hindered by this, not by web server load per se. So, this change hasn’t done much to improve response time on catalogs with thousands or tens of thousands of books, hit for the first time that day, or on work-combination requests that require recalculating thousands of items of data. Basically, the improvement speeds up every page by an average of 0.5 seconds, but a 10 second page still takes 9.5 seconds.

Here are some graphs of the effect on different page types. The white band is a period for which we don’t have numbers.

All pages (includes widgets)

Catalog. Savings have been dramatic but, as noted above, mostly on the vast majority of “normal” requests, not on the rare but painful ones.

Talk topic pages. These have gotten much faster, because the data is easy to get from the database but also very copious, so it took a lot of server work to render it. This improvement has a perverse side-effect, however–the faster the page is made the more the Talk page can get ahead of master-slave replication. This issue will be addressed in an upcoming improvement.

Work pages haven’t improved because they were already well-distributed across our servers.