Optimizing Mastodon Performance: Finding and Fixing its N+1 Queries

N+1 queries happen in a variety of ways, out of sight to many developers who are using an ORM. With the level of abstraction that ORMs provide, it can be easy for developers to accidentally incorporate a query that is executed on each result of the previous query. ORMs can make development faster and more secure, but the automatic generation of SQL queries can make it hard to predict the code's performance when it gets deployed.

Developers can be unaware their functions even query the database and a simple feature like checking user permissions could lead to querying for the same exact data multiple times.

Active Record is a Ruby on Rails framework used to easily save and retrieve objects from a database in programming frameworks. It automatically generates SQL statements to do this. ORMs like Active Record can make it easier for developers to write database-backed applications, but they can also make it harder to understand exactly how their code makes database queries.

In this final part of our Mastodon Series, you’ll learn how to analyze your AppMaps to uncover and fix an N+1 SQL query in an active code base.

In the previous posts you have learned:

In our previous post, you learned how AppMap uses the runtime code data to automatically generate accurate OpenAPI documentation. AppMap contains all the relevant HTTP request information including status codes, headers, and the session operations and AppMap builds complete and accurate OpenAPI documentation by analyzing how the code executes.

This same AppMap data can be used in other ways, for example in this post you’ll learn how to use AppMap Analysis to locate and fix a performance issue. Since the AppMaps shows function calls, SQL queries, exceptions, and more, they can be analyzed to find design flaws that contributes to performance and security issues.

Design related security flaws are rising in the OWASP Top 10 in recent years. Runtime data helps us to uncover design flaws that are contributing to slow performance such as an N+1 query.

Generate Code Analysis Findings

With our rspec tests passing and our AppMaps created back in Part 3, findings are now shown in the section of our VSCode extension titled “Runtime Analysis”. Since AppMap records the runtime application behavior, it analyzes the output of those recordings and scans for code behavior issues that you can’t find with a static analyzer.

AppMap can scan for a variety of Performance, Security, Scalability, and Maintainability issues. These rules are extensible for developers to create and contribute to the open source community.

On the left side of the VS Code extension, you can see a section called “Runtime Analysis”. This is where AppMap will display the results of the analysis scan. This scan should happen within seconds of AppMaps being created or updated.

When clicking on any one of the lines of code referenced in the findings, a new tab will open with additional details about the event, including links to the relevant CWE. The findings page will also show the stack trace leading up to the event, and a list of the AppMaps this flaw is present in.

By navigating to the base controller in Mastodon, AppMap identifies this line of code (Line 92) as making a database call (via User.find) when the doorkeeper_token variable is false. But by looking closer at the stack trace, AppMap shows line 109 is the caller function which checks to see if the current user is known and properly validated. This is correctly reported by AppMap as an N+1 query.

https://github.com/mastodon/mastodon/blob/623d3d2e32ac8ec2819f2cd99e6565d06c9b0023/app/controllers/api/base_controller.rb#L111-L141

A simple fix would be to just fetch current_user once in require_user!, or to memoize it in the current_user function.

AppMap visualizes this function and the repeated SQL queries.

Understanding the Before and After

Before implementing the code change, save the original AppMap in a “Baseline” collection so that AppMap can visually compare the before and after impact of the change.

This code is found in 6 AppMaps and they are listed on the specific findings page.

Now select one of these AppMaps, right click them, and choose the option to “Save To Collection”. Collections can be used to create copies of AppMaps which is useful for saving the current code behavior as a baseline before you implement code changes.

From there we can click <create> to create a new collection of AppMaps or select a previously created collection to add to.

After copying that AppMap to a new collection, you’ll see it included within the list of existing AppMaps.

It’s important to note that this makes a copy of the AppMap in the state it was in when it was added to the Collection. After updating the code and the AppMap, the collection will still contain the previous version of the code.

Implementing the Performance Fix

This poorly performing code is improved by breaking out the user validation into its own function so it will not repeatedly query the database for the same information.

https://github.com/mastodon/mastodon/pull/23057

Identify Out of Date AppMaps

After making this code change (either committed to your project or not) AppMap will display both the baseline and other maps that are listed as out of date because we’ve changed the functions these maps had previously recorded. AppMap knows when the underlying code has been changed and can notify when the AppMaps no longer match the code.

Open the Command Palette in VS Code (View -> Command Palette OR Shift+⌘+P on Mac OR Ctrl+Shift+P on Windows), and search for the option to “Copy Out-of-Date Tests”.

You can choose to copy the file names into the clipboard and paste them into the rspec command.

After successfully running only the tests needed to update our AppMaps the baseline AppMap will continue to be out of date and the other AppMaps will be updated. It’s important to note that when you make a code change to a function recorded in the “Baseline” AppMap, that AppMap will always display as “out of date” since it will always represent the original test case run.

Generating Sequence Diagram Diffs

Another feature of AppMap is the ability to automatically create sequence diagrams of your code’s runtime behavior. This can be extremely useful information to include in a pull request or a code review to help other members of your team understand how the code behavior has changed.

Since a baseline AppMap exists prior to our code change, AppMap can create a sequence diagram diff of the before and after. Assuming the local VS Code environment is configured to support sequence diagram creation, AppMap will be able to both generate a sequence diagram, or compare two sequence diagrams.

To generate the sequence diagram diff, right click on the latest version of the AppMap and select “Compare Sequence Diagrams”

Hit enter twice then select the original baseline AppMap saved in our Baseline collection.

From here a new browser window will open showing a sequence diagram diff visualizing the change in behavior of the code from before and after our changes. The red items in sequence diagram will indicate code behavior that has been removed, and green items will show new code paths created.

Now the changes in the sequence diagram show the code is not longer making similar repeated SQL queries. By reducing these unnecessary queries the speed of this code function has increased.

Summary

In this post, you’ve learned how to find and fix a performance issue in the Mastodon code base using AppMap. The automated code analysis will work for other projects written in languages other than Ruby like Java, Python, and JavaScript/TypeScript.

To get started finding potential issues in your code base, download and install the AppMap VS Code Extension, or the JetBrains Marketplace in your code editor and follow the similar instructions taken in this project. For more language specific information, check out the AppMap documentation, or join the AppMap Community Slack to ask a developer for more information.

Blog