Today I implemented a simple log for the cache and a corresponding report in the "administration" panel for the website. As you can see the Study page caching, which was meant to improve the page loading time, hasn't been very effective so far:

That's because the cache only lasted an hour, and with visits spread across thousands of kanji pages, the cache typically wouldn't last long enough for another visitor to benefit from it.

I've now set the cache of the shared stories list to 12 hours, and the effectiveness is closer to 30% as of writing. Now I have real numbers, and hopefully we will see at least every second request to the Study page to benefit from the cache.

It's a good thing I finally looked into this because the queries for the Shared Stories list have become rather slow. I've seen response times as high as three seconds and typically from one to two seconds... which is actually really bad. I'm not at all an expert in database design... my hunch is that because the stories take several hundred megabytes.. the queries now have to span across a lot of storage and have become slower and slower over time.

The cache will buy me some time to think about better solutions. At some point I'm going to have to think about what to do with all those private stories. One solution to trim the database that I have considered, is to move private stories for very old and unused accounts to a second database. Since the "backup" database will be essentially dormant it should have no impact on performance. Then, if a user tries to sign in an "expired" account, they could export their data from the second database, and/or the account could be restored transparently without the user knowing anything.