Optimizing Little Hills, FaithTree and Open for Business

I’ve been deploying a lot of server optimization tricks to improve the performance of Little Hills, FaithTree and Open for Business these past months. They had all grown slow with the kludge of adding additional features and my once snappy homegrown content management system (also used here), SAFARI, was no longer snappy. That all threatened user frustration and, for that matter, lower rankings on Google, which pays attention to the performance of sites it refers people to.

I’ll blog about some of the other optimizations a different day, but one I’m rather pleased with is support for WebP. I’ll admit to ignoring it in no small part because Apple didn’t support it. I use primarily Apple Safari to browse the web, including pages served by my SAFARI (which I called that before Apple’s Safari was launched, so I’m being stubborn and keeping the name). However, WebP arrived on Safari with iOS 14 and MacOS Big Sur, so I revisited the format and found it could offer substantial savings in download size on many images. With support in every modern browser, I figured I’d implement support in SAFARI.

By support, I mean my goal was to be able to upload an image to the site and the software takes care of turning it into a WebP. SAFARI now does that, taking images of known types and running several layers of optimization. First, it tries to make a more efficient JPEG using ImageMagick’s PerlMagick module; once that is complete, it attempts to make a WebP version. It only keeps the optimized JPEG version if it is at least 20% smaller than the original. Likewise, it only keeps the WebP if it is that much smaller than the optimized JPEG. Since not everything older supports WebP, there’s no reason to mess with it, if a JPEG will do.

This is similar to what I later read described here. I want to implement more of Bernat’s optimizations in the future.

That’s great, I thought, but then I realized the next issue (which is described with a solution in Bernat’s piece, but I hadn’t found it yet at the time): if not every browser supports WebP, I have to be prepared to serve the JPEG still. The HTML5 <picture> element supports giving different image options and letting the browser installed, but my goal was not to require me (or anyone else someday posting to Little Hills’ site) to manually think through the process of describing alternatives. It also does nothing to take advantage of the automatically generated alternatives I previously created when it comes to existing pages.

The solution was complicated by another optimization I made. My long worn solution would be to come up with a series of mod_rewrite rules for my Apache server to analyze if the browser could handle WebP and then replaced the JPEG with a WebP if possible. However, I recently implemented an NGINX caching server to get its advantages over Apache, so I needed to figure out the NGINX way to do it. That turned out to be a bit confusing to me at first, but simple in practice.

Here’s the final bit of NGINX configuration magic used to reroute requests to WebP (or the optimized JPEG), as best suited. It finds any folder location under an images directory and then looks for an .optimized subdirectory. If it finds one, It looks for the WebP version if applicable and the optimized JPEG if the WebP version is unavailable or the browser does not support it.

location ~ ^(?<extra>.*?)(?<prefix>/images/(?:.*/)?)(?<rxfilename>.*)$ {
        expires 365d;
        add_header Pragma "public";
        add_header Cache-Control "public, no-transform";
        add_header Vary Accept;
        try_files $extra$prefix.optimized/$rxfilename.$webp_suffix $extra$prefix.optimized/$rxfilename.jpg $uri =404;
}

This has been working great for a couple of months now — I started writing this post back on July 7 and then got sidetracked — so if you’re looking to optimize your NGINX configuration, I definitely commend it to you.

Resetting MySQL Primary Key in Chronological Order

As longtime readers of asisaid know, this blog — and my other sites — run a custom content management system called SAFARI that I started writing way back in 1999. For a long time, SAFARI evaded spam comments simply because most spammers targeted WordPress and other vastly more common CMSes. However, at some point in the mid-2010’s as I started serving as a pastor and professor, this blog went into something of a hibernation and the spammers went crazy with it. I later implemented more serious anti-spam measures, but there were already thousands of junk comments to deal with. I recently realized that my panicked hacking away at the comments back then wiped out nearly 4,000 early comments to the blog that were not spam.

I wanted to restore them from a backup I dug up, but since more comments have been posted since then, the unique id number given to each comment had been given away to other comments. The database table that stores the comments is set to auto-increment the ids, so I could remove the ids from the backed up comments before restoring them, but the result bugged me: older comments that were restored had higher id numbers than the newer comments. It didn’t ruin the function, but it bugged me.

So, I looked for a way to regenerate the unique ids and to do so in chronological order. I combined several of the answers on this Stack Overflow page to achieve the desired result:

SET @count = 0;
ALTER TABLE `uninet_comments` DROP `cid`;
ALTER TABLE `uninet_comments` ADD `cid` int UNSIGNED NOT NULL FIRST;
UPDATE `uninet_comments` SET `uninet_comments`.`cid` = @count:= @count + 1 ORDER BY `gmt` ASC;
ALTER TABLE `uninet_comments` ADD PRIMARY KEY(`cid`);

I figured I would share the queries I used in case anyone else needs to accomplish the same thing on a database.

Working Out the Bugs

SAFARI 2 is fairly stable from your point of view, but the backend is still a bit of a mess due to two transitional factors and one other factor:

1. It still is recovering from the switch to MySQL from a plain text “flat file” database.
2. It still has the remnants of the old issue-based organizing structure.
3. I knew a lot less about Perl when I started writing thing thing in 1999 than I do now, six and a half years later.

I promised Ed a copy of SAFARI for his blog, and I have several other sites that need it as well, so I've been trying to hack away at the final bits of basic administration interface bugs. There are infrastructure problems still, but so long as I get it so that there is a stable database format and a usable article posting interface, I can worry about the rest even after I start doing multiple deployments of the code base.

Just a bit more work, I think. There are some features I'd like to add, but I'm trying to distinguish between “must have” features and “I want” features. I am also trying to distinguish between ones that will require reorganizing the system and ones that I can plan for now and easily “snap in” later.

Hopefully, I am starting to see the light at the end of the tunnel.