Skip to main content

September 9

Hi all, hope you're doing well. My health is awful atm even compared to usual, so I've been much slower than I'd like.

I've been focusing on improving the performance of the more complex database procedures, as that will be a crucial factor in making the site sustainable in the long term.

The GetGalleryForDisplay stored procedure is by far the most complex SQL procedure Deserted Chateau has. It's also likely to be one of the most frequently called, as it is used for getting user galleries, bookmark folders, search results and so on. Given the number of table JOINs and other operations it has, it's critical to ensure this procedure has good performance, but it's not easy to test that without a large amount of test data to run queries against.

To that end, I've created a mock data framework of sorts that can output large CSV files of test data, and insert them into the test database. It inserts a given number of users, with a percentage of those users being artists or commissioners, and then adds the following:

  • A given number of follow relationships between users (i.e. 20 follows per user, 10000 users = 200000 follow relationships)
  • A given number of block relationships between users, done after the follows are added to prevent bad data being inserted where a user is followed and blocked simultaneously, which can't happen in the real environment
  • A number of artworks per artist or commissioner user, and then a number of likes or reblogs for each of those artworks by any of the test users

I've yet to add more functions to it, like adding a list of randomised tags to each artwork, and it doesn't e.g. upload images for each artwork to use; it just uses mock S3 keys that do not actually have images stored, so it's not useful for populating the site for testing purposes outside of testing SQL procedures. I'll think about extending it to work for that case later, as it might be useful but could also be way more trouble than it's worth.

Making the mock framework function for large data sets proved a bit of a challenge; I spent a while trying to figure out why the rate of database inserts was slowing down heavily after performing some of the work. I initially thought it was a problem with the database configuration, then realised the low cost Lightsail instances I'm using were running out of burst CPU credits due to the amount of work required to process all the data to insert. One of the culprits with that was the fact that to keep the testing 'accurate', I used the existing code for creating users, which means hashing the passwords and activation codes, which are both computationally expensive. I've made test versions to avoid those operations, for the sake of inserting mock data more easily.

I tested the gallery code against ~3000 artworks, and having improved the mock data framework, then tested it with 60,000 artworks. The results gave me a lot of useful insight, and I've been able to improve the performance of the procedure by a huge margin in many cases as a result.

Gallery SQL: performance testing and improvements

Testing the gallery procedure in its current form revealed a big problem: to search 3,000 artworks took it around 3.5 seconds, and logically that number would get larger the more artworks existed in the database. Fortunately the fix turned out to be simple: an inefficiency in how the procedure counted likes and reblogs for each artwork being returned was causing a huge slowdown.

Until now, the procedure did a LEFT JOIN on the user_artworks_likes and user_artworks_reblogs tables. With a large number of artworks, and a large number of reblogs and likes per artwork, that leads to an impossibly huge number of rows for the SQL engine to process: although they're grouped later, it still has to process them all, and having e.g. 3000 artworks, 5 reblogs and 25 likes per artwork would equal 425000 rows, not counting all the other JOINs in the procedure. Changing the procedure to use a subquery, counting the likes for each artwork as a separate column without JOINing or counting the likes or reblogs tables at all, removed the bottleneck and changed the query back to taking ~0.06 seconds.

The work required to process all of the rows leads to other considerations: at the moment, artwork categories are not represented as table columns, but as a set of rows within the artwork_categories table, for which each user artwork has a corresponding set of rows in user_artworks_categories. Since each query requires a LEFT JOIN between the user_artworks and user_artworks_categories tables, that's making for a lot of extra rows. I tested denormalising the user_artworks_categories table to be one row per artwork, but it didn't make a particularly big improvement.

The most important performance improvement I figured out was to change the manner in which the different tables are joined and queried. The simplest way to get data in SQL is to join tables together and then query them, but in tables with one to many relationships, the number of rows becomes astronomical very fast. I restructured the query to select the desired number of rows from the user_artworks table first, and only then join the other tables for extra information. The performance improvement of that is huge, but it came with its own problems to fix: if you want to select a given number of artworks with each query, then you have to filter all of the user_artworks rows within the first table select, which meant having to do some creative thinking with how rows are filtered according to the search criteria.

I'll document this properly in the docs later; the procedure itself will be commented with the logic behind its current structure as well. I also need to modify some parts of the procedure to use some of the new optimisations, and then test out some other ideas for improving the query time and investigating which filters if any are better done client-side.

Mobile layout improvements

I've been slowly making improvements to the mobile layout aspect of the site. The priority is making the menus easy to use for the navigation bar, and making galleries easy to scroll and view; I still need to implement the buttons on the footer menu that appears when the mobile layout is used, to allow easy searching etc in mobile mode.

One thing that's a slight puzzle is making videos easily viewable in mobile mode; I think I'll have to make a JavaScript solution for this, since there's no 'hover' on mobiles per se and so things like play buttons on top of videos need to be constantly visible unless the video is currently being played. The normal code to bring up the artwork details also has to change so that it doesn't interfere with a mobile user playing or pausing a video.

Documentation updates

  • Updated the Infrastructure diagram in the Guide for other art sites shelf.
  • Updated the RDS documentation to have more information about parameter groups for database instances, and the effect of the innodb_log_buffer_size variable on performance when large transactions are being used.

Misc

Minor Improvements
  • Fixed more of the tooltips that weren't yet using the new standard classes.

  • The large play button shown in the center of videos when hovering now still appears when the video is playing, but changed to a pause button.

  • Added scaling animations to tooltips, to give them a nicer appearance.

  • Artwork titles in gallery pages now neatly trail off when they are too long to be displayed in the available width.
Back to top