Ok I totally stole the title from this article, which was really good and help me fix my problems so I wanted to link to it.

I recently upgraded all my servers from Jessie to Stretch, which was long overdue. The catalyst being that certbot from Let's Encrypt started complaining about the security I was using to fetch new certificates. (It complained in a very nice way and helped fix the problem, I should add.)

Anyway the upgrades went fine, Debian is very good at that. The problem was that part of the upgrade was switching from MySQL 5 to MariaDB, and all my tables were using an old character set and collation type. This showed up as emoji not rendering properly in my reader. Easy fix: switch the reader items table to the utf8mb4 character set and collation type to utf8mb4_unicode_ci. Problem solved.

This is where the fun really started though, and where my desire to understand the inner workings of databases started fading... My reader became so slow at loading items that it was unusable. The rest of the site worked fine, so I isolated the problem down to one database query. Due to the way I now have channels set up, this was a particularly complicated query running on the largest table in my database, reader_items which was 400k rows and growing. I delved deeper into the murky world of database performance, learning about sargable queries and how to keep your indexes fast.

Making sure all the tables joined in the same query have a matching collation type seemed to do the trick. My query which had blown out to over a minute was now running in a couple of seconds. A good reminder to look after your indexes! Since I was well and truly into database tuning now though, I decided I could get more performance improvements out of my reader.

The problem with adding new feed items to the same table is that you generally only want to read the new stuff. It's great to have fast indexes, but most of the time I really just need smaller tables. To do that my reader needed to be able to look up items in an arbitrary number of tables, but optimised to find new items in the first table it reads from. It can now do that, and the data partitioning process is automated to keep the items tables small. It can also handle reading across tables to return the correct number of items requested.

The problematic query now returns in less than a second for the optimised case. (I would measure it but it's not a noticeable part of using the reader any more.) My plan to fix the performance issues before making this change was to increase the specs of the server it's running on, which are pretty modest for all the work it's doing. I do however like the idea of improving the efficiency of code rather than throwing more hardware at a poorly running solution.

🙊

It's been over a month since I added channels to my reader, but I didn't use them initially. I've added some new features though which have changed my reading habits completely!

What I really wanted was to split silo feeds into different channels based on author. Without this I would mostly be viewing the silos channel since that gets such a large number of posts. To do that, I've added hovercards to authors so I can set their channel while reading. You can see from the screenshot that @letsencrypt has been added to the tech channel, instead of it's default in the silos channel for the rest of the twitter feed.

I've gone through my silo authors and added them to tech, news or friends and this simple breakdown has been great so far. The other thing I've added is unread status, it doesn't have to show a count to highlight that there are unread items, which is configurable per channel. I like that it's quite simple, when you change channels the unread count resets, it doesn't save unread status per item.

Now that the underlying structure is in place the next step is to finish adding Microsub support.