Diagnosing performance issues in Rails applications

 2 years ago
source link: https://buildingvts.com/diagnosing-performance-issues-in-rails-applications-9418eb5d872
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Diagnosing performance issues in Rails applications

Image for post
Image for post
Photo by Carlos Muza on Unsplash

Almost everyone who works on a large Rails application will eventually have to deal with performance problems. For better or worse, there are dozens of different types of performance problems, which can make it hard to identify which one you’re dealing with and how to address it. Here are a few types of performance problems that my team has run into at VTS and some strategies for how to think about and improve them.

N+1 queries

One of the most common performance problems — and happily, one of the easiest to solve — are N+1 queries. Rails guide has a good explanation of what an N+1 query is: essentially, a database query that makes many small requests for related data instead of one larger, more efficient query.

How do I know if I have this problem?

The easiest thing to look for is multiple queries for the same data, differing only by some id. (Note: if you’re not seeing this output, try increasing the Rails log level, e.g. LOG_LEVEL=debug). In this example, to get 5 users and their groups, we end up making 6 different queries (hence the name N + 1, since it’s the number of records + 1).

>>> User.limit(5).map {|user| user.groups}User Load (5.0ms) SELECT “users”.* FROM “users” LIMIT 5Group::Group Load (1.8ms) SELECT “groups”.* FROM “groups” INNER JOIN “group_users” ON “groups”.”id” = “group_users”.”group_id” WHERE “group_users”.”user_id” = 1Group::Group Load (1.8ms) SELECT “groups”.* FROM “groups” INNER JOIN “group_users” ON “groups”.”id” = “group_users”.”group_id” WHERE “group_users”.”user_id” = 2Group::Group Load (1.8ms) SELECT “groups”.* FROM “groups” INNER JOIN “group_users” ON “groups”.”id” = “group_users”.”group_id” WHERE “group_users”.”user_id” = 3Group::Group Load (1.8ms) SELECT “groups”.* FROM “groups” INNER JOIN “group_users” ON “groups”.”id” = “group_users”.”group_id” WHERE “group_users”.”user_id” = 4Group::Group Load (2.7ms) SELECT “groups”.* FROM “groups” INNER JOIN “group_users” ON “groups”.”id” = “group_users”.”group_id” WHERE “group_users”.”user_id” = 5

How do I fix it?

Rails offers a few different ways to fix N+1 queries: preload, eager_loads and includes¹. Using one of those strategies allows us to make a far more performant query with only 2 database calls (this improvement would be even more dramatic if we were loading 100 users):

>>> User.includes(:groups).limit(5).map {|user| user.groups}User Load (2.0ms) SELECT “users”.* FROM “users” LIMIT 5Group::GroupUser Load (2.4ms) SELECT “group_users”.* FROM “group_users” WHERE “group_users”.”user_id” IN (1, 2, 3, 4, 5)

Other strategies

Upgrade to Rails 6.1

Rails 6.1 has added a “strict loading” mode which throws an error if you attempt to access related records that have not been eagerly loaded.


There are also gems such as Bullet that will warn if they find any N+1 queries. However, our team has had mixed success using Bullet, both with false positives and false negatives, so use with caution.

Query Preventer

At VTS, we have a custom QueryPreventer module that subscribes to SQL events from ActiveSupport::Notifications, and raises an error if there are any unexpected database queries. We mainly use it in serializers to wrap expensive queries or queries where there is a high likelihood of model relationships changing and breaking existing preloads.

# app/serializers/deal_serializer.rbdef leases
deal_that_will_error = Deal.includes({ buildings: :tenants }) # this will throw an error because we forgot to preload leases
QueryPreventer.without_query do

Optimizing SQL queries

Sometimes, even if an ActiveRecord query does not produce any N + 1 queries, the SQL might still take a long time to execute. This is usually due to two things: missing indices, or a poorly-chosen SQL scan strategy.

How do I know if I have this problem?

Missing indices

If your query is performing any selection (WHERE / HAVING) or ordering, you generally want to have an index on those fields. For example, at VTS we have a table that shows users a list of their commercial real estate deals. Deals also have a status: they might be in an initial inquiry phase, they might be touring and showing available spaces to customers, or they might be in the final legal negotiations to close that deal. A user might want to view only deals in a specific stage, which could result in a query like this:

>>> Deal.where(status: [:currently_touring, :legal])SELECT deals.* FROM deals WHERE deals.status IN (‘currently_touring’, ‘legal’)

Because this query performs a WHERE on a specific field (in this case a deal’s status), we want to make sure that we have an index on deals and statuses. If we added an order subquery to sort the deals alphabetically by name, we should add an additional index on the deal’s name. Another way to think about this is that we want to give the database a head-start for any queries we plan to run frequently.

(It’s worth noting that Rails automatically adds indexes for primary keys (usually id), and Rails 5 automatically adds an index for foreign keys if you use the references keyword in a migration)

Inefficient query plans

Databases have to make a best guess about the most efficient way to query tables. Although they are usually pretty good at this, they sometimes come up with inefficient plans.

A good way to see whether this might be the case is to run an EXPLAIN ANALYZE statement in your database console (note: not the rails console) and then use a tool like depesz to help understand the plan². Don’t worry if this looks like a lot of gibberish: there are a couple of main highlights you want to keep an eye out for:

Red and orange colors and/or high numbers lower down in the plan

The top of the query is always going to look scary because that’s the total time that the query takes to execute. You want to keep an eye out for moderately expensive queries that happen within a loop, because those may be getting executed thousands of times.

Image for post
Image for post
Looks scary – not that much

Although these numbers look scary, they are at the top of the query plan and inclusive which means that they include every operation that happens within that outermost Nested Loop. There’s no real point in trying to optimize this.

Image for post
Image for post
Check out the number of rows

This shows that the Nested Loop on row 14 is being performed over 52k rows, which is a lot. We might see whether we can perform some kind of filter operation earlier in the query, so that we can iterate over fewer rows.

Image for post
Image for post
High numbers in the exclusive column should be a red flag

This is notable because of the “exclusive” column, which means that this one Unique query took nearly 43 seconds to complete. This is probably a good candidate for optimization.

Seq(uential) scans

A sequential scan is when the database must go through every single row of a table to find a matching condition; although this might be efficient for tables with a very small number of rows, this can be extremely expensive on larger tables.

If you see that the database is doing a sequential scan instead of an index scan, this might be an indication that you are missing an index.

How do I fix it?

Rails Guides talks about adding indexes in their guide to ActiveRecord migrations. There are also gems that claim to identify missing indexes, though I haven’t tried them and can’t recommend any.

You can also use the query plan to help you identify performance “hot spots” and either make sub-queries more efficient or filter earlier in the query to reduce the number of rows that must be scanned.

Speeding up application code

Sometimes the SQL is as performant as it can get, which means that any performance improvements must take place in the application code. There are a couple of ways to tackle this:

Moving queries to ruby instead of the database

Sometimes, if you need to filter a set of Active Record relations and the original query is expensive, it may be more performant to perform the filtering in ruby.

>>> some_group_of_deals
.where(status: [:currently_touring, :legal])
.or('last_tour_date > ?', Date.yesterday)
.or(‘last_comment_date > ?’, Date.yesterday)
.order(:name, :asc)

If the logic for this is complex, or we have a lot of deals, this may actually end up being an expensive query. Instead, we can perform the filtering in ruby, and potentially speed up performance

>>>  some_group_of_deals.select do |deal| 
[:currently_touring, :legal].include?(deal.status)
end.select do |deal|
deal.last_tour_date > Date.yesterday ||
deal.last_comment_date > Date.yesterday


We are big believers in leveraging linting tools to help create a standard for well-written code at VTS. One of the tools we use is rubocop-performance which makes recommendations for common performance gotchas. This certainly isn’t a silver bullet, but it is a good way to catch some well-known performance problems and raise awareness.

Breaking up front-end requests

Sometimes a particular API request takes a long time because it needs to gather a lot of data. If the deals page on VTS also showed the status of the deal, the building it was on, the contact information for interested customers, marketing material, and the latest financial details, we might naively decide to lump all of that information into a single API request. This is fine… until it’s not. As that list of information grows over time (and it will!) the request becomes slower and slower.

One way to address this is by splitting the API request into several smaller requests, which can be used to render different parts of the page. For example, the area that shows the building details can take care of loading building information, the area that shows financial information can make a request for financial data, etc.

This creates some additional complexity, since the front-end must now coordinate the fetching, loading, and rendering of multiple requests, and the back-end must manage additional endpoints. However, this is a good incentive to create modular architecture (something that frameworks like React encourage) and it often improves the user experience as well, since customers can see some information sooner.

Reducing API payloads

In addition to breaking up API calls into smaller requests, it might also be worth auditing those requests to ensure they contain only relevant data.

Is all data being used?

If your API is returning data that the front-end doesn’t use, removing it is an instant performance boost! This is also a good opportunity to look for existence checks — e.g. the API returns an array of objects that is only ever checked to see whether it’s empty — or cases where just the first element of an array is being accessed.

If you use React, you can also leverage PropTypes to help track down extraneous data.


Another way to reduce the payload size is by paginating results. This adds additional complexity, but is often worth it. We use kaminari at VTS, a popular Rails pagination library, but there are plenty of others.


At some point, almost every Rails application will run into a performance problem; the key is having the tools to figure out what kind of problem you’re dealing with so that you can choose the right solution.

[1] There are slight differences between preload and includes that aren’t relevant for this simple example, but might be worth looking into if you’re not seeing the query strategy that you expect.

[2] It is a good idea to run the query a few times to “warm up” the database before you inspect the results

About Joyk

Aggregate valuable and interesting links.
Joyk means Joy of geeK