Speeding up Ruby on Rails

Eliminating the N+1 query problem

The Ruby language is often cited for its flexibility. You can, as Dick Sites said, "write programs to write programs." Ruby on Rails extends the core Ruby language, but Ruby itself makes that extensibility possible. Ruby on Rails uses the language's flexibility to make it easy to write highly structured programs without much boilerplate or extra code: You get a large amount of standard behavior with no extra work. Although this free behavior isn't always perfect, you get a lot of good architecture in your application without much work.

For example, Ruby on Rails is based on a Model-View-Controller (MVC) pattern, which means that most Rails applications are cleanly split into three parts. The model contains the behavior necessary to manage an application's data. Typically, in a Ruby on Rails application, there is a 1:1 relationship between models and database tables; ActiveRecord , the object-relation mapping (ORM) that Ruby on Rails uses by default, manages the model's interaction with the database, which means that the average Ruby on Rails program has very little, if any, SQL coding. The second part, the view, consists of the code that creates the output sent to the user; it typically consists of HTML, JavaScript, etc. The final part, the controller, turns input from the user into calls to the correct models, then renders a response using the appropriate views.

Proponents of Rails often cite this MVC paradigm — along with other benefits of both Ruby and Rails — as increasing its ease of use, claiming that fewer programmers can produce more functionality in less time. This, of course, means more business value for each software development dollar, so Ruby on Rails development has become significantly more popular.

However, the initial development cost is not the entire picture. There are other continuing costs, such as maintenance costs and the hardware costs to run the application. Ruby on Rails developers often use testing and other agile development techniques to keep maintenance costs down, but it can be easy to give much less attention to efficiently running your Rails application with large quantities of data. Although Rails makes it easy to access your database, it does not always do so efficiently.

Why can Rails applications run slowly?

Rails applications can run slowly for few fundamental reasons. The first is simple: Rails makes assumptions for you to speed up development. Usually, these assumptions are correct and helpful. They are not always beneficial for performance, however, and they can result in an inefficient use of resources — particularly database resources.

For example, ActiveRecord selects all fields on a query by default, using a SQL statement equivalent to SELECT * . In situations with a large number of columns — particularly if some are large VARCHAR or BLOB fields — this behavior can be a significant problem in terms of memory usage and performance.

Another significant challenge is the N+1 problem, which this article examines in detail. Essentially, this results in many small queries being performed, rather than one large query. ActiveRecord has no way to know that, for example, a child record is being requested for each of a set of parent records, so it will produce one child record query for each parent record. Because of per-query overhead, this behavior can cause significant performance issues.

Other challenges are more closely related to the development habits and attitudes of Ruby on Rails developers. Because ActiveRecord makes so many tasks so easy, Rails developers can often acquire a "SQL is bad" attitude, eschewing SQL even when it makes more sense. Creating and manipulating large quantities of ActiveRecord objects can be slow, so in some cases, it can be much faster to write a SQL query directly that does not instantiate any objects.

Because Ruby on Rails is often used to reduce the size of development teams, and because Ruby on Rails developers often perform some of the systems administration tasks required to deploy and maintain their applications in production, limited knowledge about their environment may cause problems. Operating system and database settings may not be set correctly. Although it's not optimal, MySQL my.cnf settings are often left at their defaults in Ruby on Rails deployments, for example. In addition, there may not be sufficient monitoring and benchmarking tools to develop a long-term picture of performance. This is not a criticism of Ruby on Rails developers, of course; it's simply a consequence of non-specialization; in some cases, Rails developers may be experts in both areas.

A final issue is that Ruby on Rails encourages programmers to develop in a local environment. Doing so has a number of benefits — such as less development latency and increased distribution — but it does mean that you can work with a limited dataset because of the smaller size of the workstations. The difference between how they develop and where the code will be deployed can be a big problem. You may work for a very long time with a small data size on an unloaded local server with good performance only to find that the application has significant performance problems with a larger data size on a congested server.

Of course, there are many more possible reasons why a Rails application has performance issues. The best way to find out what potential performance issues your Rails application has is to look at diagnostic tools that can give you accurate, repeatable measurements.

Detecting performance problems

One of the best tools is the Rails development log, which resides on each development machine in the log/development.log file. It has various gross metrics available: total time taken to respond to the request, percentage of time spent in the database, percentage of time spent generating the view, etc. Tools are available to analyze the log file for you, such as the development-log-analyzer .

During production, you can find valuable information by examining the mysql_slow_log . The full details are outside of the scope of this discussion, but you can find out more in the Related topics section.

One of the most powerful and useful tools is the query_reviewer plug-in (see Related topics). This plug-in shows you how many queries are executing on the page and how long the page took to generate. And it automatically analyzes SQL code that ActiveRecord generates for potential problems. For example, it finds queries that do not use a MySQL index, so if you have forgotten to index an important column and that is causing you performance issues, you can find it easily (see Related topics for more information about MySQL indices). The plug-in displays all of this information in a pop-up <div> , which is visible only during development mode.

Finally, don't forget to use tools like Firebug, yslow , Ping, and tracert to detect whether your performance problems may be coming from network or asset loading problems.

Next, let's deal with some specific Rails performance problems and their solutions.

The N+1 query problem

The N+1 query problem is one of the biggest problems with Rails applications. For example, how many queries does the code in Listing 1 produce? This code is a simple loop through all posts in a hypothetical posts table, displaying the category and the body of the post.

Listing 1. Unoptimized Post.all code

<%@posts = Post.all(@posts).each do |p|%> <h1><%=p.category.name%></h1> <p><%=p.body%></p> <%end%>

Answer: The code generates one query plus one query per row in @posts . Because of per-query overhead, this can be a significant challenge. The culprit is the call to p.category.name . This call applies only to that particular post object, not the entire @posts array. Fortunately, you can fix this by using eager loading.

Eager loading means that Rails will automatically perform the necessary queries to load the object of any specified child objects. Rails will use a JOIN SQL statement or a strategy in which multiple queries are performed. However, assuming that you specify all the children you are going to use, it will never result in an N+1 situation, where each iteration of a loop produces an additional query. Listing 2 is a version of the code in Listing 1 that uses eager loading to avoid the N+1 problem.

Listing 2. Optimized Post.all code with eager loading

<%@posts = Post.find(:all, :include=>[:category] @posts.each do |p|%> <h1><%=p.category.name%></h1> <p><%=p.body%></p> <%end%>

That code generates at most two queries, no matter how many rows you have in the posts table.

Of course, not all cases are so simple. It's more work to deal with more complicated N+1 query situations. Is it worth the effort? Let's do some quick testing.

Testing N+1

Using the script in Listing 3, you can find out how slow — or fast — queries can be. Listing 3 demonstrates how to use ActiveRecord in a stand-alone script to establish a database connection, define your tables, and load data. Then, you use Ruby's built-in benchmark library to see which approach is faster and by what margin.

Listing 3. Eager-loading benchmark script

require 'rubygems' require 'faker' require 'active_record' require 'benchmark' # This call creates a connection to our database. ActiveRecord::Base.establish_connection( :adapter => "mysql", :host => "127.0.0.1", :username => "root", # Note that while this is the default setting for MySQL, :password => "", # a properly secured system will have a different MySQL # username and password, and if so, you'll need to # change these settings. :database => "test") # First, set up our database... class Category < ActiveRecord::Base end unless Category.table_exists? ActiveRecord::Schema.define do create_table :categories do |t| t.column :name, :string end end end Category.create(:name=>'Sara Campbell\'s Stuff') Category.create(:name=>'Jake Moran\'s Possessions') Category.create(:name=>'Josh\'s Items') number_of_categories = Category.count class Item < ActiveRecord::Base belongs_to :category end # If the table doesn't exist, we'll create it. unless Item.table_exists? ActiveRecord::Schema.define do create_table :items do |t| t.column :name, :string t.column :category_id, :integer end end end puts "Loading data..." item_count = Item.count item_table_size = 10000 if item_count < item_table_size (item_table_size - item_count).times do Item.create!(:name=>Faker.name, :category_id=>(1+rand(number_of_categories.to_i))) end end puts "Running tests..." Benchmark.bm do |x| [100,1000,10000].each do |size| x.report "size:#{size}, with n+1 problem" do @items=Item.find(:all, :limit=>size) @items.each do |i| i.category end end x.report "size:#{size}, with :include" do @items=Item.find(:all, :include=>:category, :limit=>size) @items.each do |i| i.category end end end end

This script tests the speed of looping over 100, 1,000, and 10,000 objects with and without eager loading using the :include clause. To run this script, you may need to replace the appropriate database connection parameters near the top of the script with parameters appropriate to your local environment. You will also need to create a MySQL database named test. Finally, you'll need the ActiveRecord and faker gems, which you can get by running gem install activerecord faker .

Running the script on my machine produced results like those in Listing 4.

Listing 4. Eager-loading benchmark script output

-- create_table(:categories) -> 0.1327s -- create_table(:items) -> 0.1215s Loading data... Running tests... user system total real size:100, with n+1 problem 0.030000 0.000000 0.030000 ( 0.045996) size:100, with :include 0.010000 0.000000 0.010000 ( 0.009164) size:1000, with n+1 problem 0.260000 0.040000 0.300000 ( 0.346721) size:1000, with :include 0.060000 0.010000 0.070000 ( 0.076739) size:10000, with n+1 problem 3.110000 0.380000 3.490000 ( 3.935518) size:10000, with :include 0.470000 0.080000 0.550000 ( 0.573861)

In all cases, the test using :include was faster — specifically, 5.02, 4.52, and 6.86 times faster, respectively. Of course, the exact outcome depends on your particular situation, but eager loading can clearly lead to significant performance gains.

Nested eager loading

What if you want to reference a nested relation — a relation of a relation? Listing 5 demonstrates a common situation where such a thing might happen: looping through all posts and displaying an author image, where the Author has a belongs_to relationship with Image .

Listing 5. Nested eager-loading use case

@posts = Post.all @posts.each do |p| <h1><%=p.category.name%></h1> <%=image_tag p.author.image.public_filename %> <p><%=p.body%> <%end%>

This code suffers from the same N+1 problem as before, but the syntax for the fix is not immediately apparent, because you're using relationships of relationships. How, then, do you eager-load nested relationships?

The correct answer is to use a hash syntax for the :include clause. Listing 6 provides an example of such a nested eager load using hashes.

Listing 6. Nested eager-loading solution

@posts = Post.find(:all, :include=>{ :category=>[], :author=>{ :image=>[]}} ) @posts.each do |p| <h1><%=p.category.name%></h1> <%=image_tag p.author.image.public_filename %> <p><%=p.body%> <%end%>

As you can see, you can nest hash and array literals. Note that the only difference between a hash and an array in this case is that the hash can have nested sub-items and the array cannot. Otherwise, they are equivalent.

Indirect eager loading

Not all instances of the N+1 problem are as readily perceived. For example, how many queries does Listing 7 produce?

Listing 7. Indirect eager-loading example use case

<%@user = User.find(5) @user.posts.each do |p|%> <%=render :partial=>'posts/summary', :locals=>:post=>p %> <%end%>

Of course, determining the number of queries requires knowledge of the posts/summary partial. You can see the partial in Listing 8.

Listing 8. Indirect eager-loading partial: posts/_summary.html.erb

<h1><%=post.user.name%></h1>

Unfortunately, the answer is that Listing 7 and Listing 8 generate an extra query per row in post , looking up the user's name — even though the post object was generated automatically by ActiveRecord from an already-in-memory User object. In short, Rails does not, as of yet, associate child records with their parents.

The fix is to use self-referential eager loading. Essentially, because Rails reloads child records generated by parent records, you need to eager-load the parent records as if they were an entirely separate relationship. It looks like the code in Listing 9.

Listing 9. Indirect eager-loading solution

<%@user = User.find(5, :include=>{:posts=>[:user]}) ...snip...

Although counter-intuitive, this technique works much like the above techniques. Unfortunately, it's easy to excessively nest using this technique, particularly if you have a complicated hierarchy. Simple use cases are fine, such as that shown in Listing 9, but heavy nesting may cause problems. In some cases, the excessive loading of Ruby objects can actually be slower than dealing with the N+1 problem — particularly if every object does not have the entire tree traversed. In that case, other solutions to the N+1 problem may be more appropriate.

One way to do this is by using caching techniques. Rails V2.1 has simple cache access built in. Using the Rails.cache.read , Rails.cache.write , and related methods, you can create your own simple caching mechanism easily, and the back end can be a simple memory back end, a file-based back end, or a memcached server. You can find out more about Rails's built-in caching support in the Related topics section. You don't need to create your own caching solution, though; you could use a pre-built Rails plug-in like Nick Kallen's cache money plug-in. This plug-in provides write-through caching and is based on code in use at Twitter. See Related topics for more information.

Of course, not all Rails problems are related to the number of queries.

Rails grouping and aggregate calculations

One problem you can encounter involves doing work in Ruby that should be done by your database. This is a testament to how powerful Ruby is. It's difficult to imagine people voluntarily reimplementing parts of their database code in C without a significant incentive, but it's easy to do similar calculations on groups of ActiveRecord object in Rails. Unfortunately, Ruby is invariably slower than your database code. Don't perform calculations using a pure Ruby approach, as is shown in Listing 10.

Listing 10. Incorrect way to perform grouping calculations

all_ages = Person.find(:all).group_by(&:age).keys.uniq oldest_age = Person.find(:all).max

Instead, Rails provides a series of grouping and aggregate functions for you. Use them as shown in Listing 11.

Listing 11. Correct way to perform grouping calculations

all_ages = Person.find(:all, :group=>[:age]) oldest_age = Person.calcuate(:max, :age)

There are a number of options to ActiveRecord::Base#find you can use to mimic SQL. You can find out more in the Rails documentation. Note that the calculate method works with any valid aggregate function that your database supports, such as :min , :sum , and :avg . Additionally, calculate can take a number of arguments, such as :conditions . Check the Rails documentation for details.

Not everything that you can do in SQL can be done in Rails, however. If the built-ins aren't enough, use custom SQL.

Custom SQL with Rails

Suppose you had a table with a list of people, their professions, ages, and the number of accidents they've been involved in within the past year. You could use a custom SQL statement to retrieve the information, as shown in Listing 12.

Listing 12. Custom SQL with ActiveRecord example

sql = "SELECT profession, AVG(age) as average_age, AVG(accident_count) FROM persons GROUP BY profession" Person.find_by_sql(sql).each do |row| puts "#{row.profession}, " << "avg. age: #{row.average_age}, " << "avg. accidents: #{row.average_accident_count}" end

This script would produce results like Listing 13.

Listing 13. Custom SQL with ActiveRecord output

Programmer, avg. age: 18.010, avg. accidents: 9 System Administrator, avg. age: 22.720, avg. accidents: 8

Of course, that's a simple case. You can imagine, though, how you can extend this example to SQL statements of any complexity. You can also run other types of SQL statements, such as ALTER TABLE statements, using the ActiveRecord::Base.connection.execute method, as shown in Listing 14.

Listing 14. Custom non-finder SQL with ActiveRecord

ActiveRecord::Base.connection.execute "ALTER TABLE some_table CHANGE COLUMN..."

Most schema manipulations, such as adding and removing columns, can be done using Rails's built-in methods. But the ability to execute arbitrary SQL code is available if you need it.

Conclusion

Like all frameworks, Ruby on Rails can suffer from some performance issues without the proper care and attention. Fortunately, the correct techniques for monitoring and correcting these challenges are relatively simple and easy to learn, and even complex problems can be solved with some patience and a knowledge of where your performance issues originate.

Downloadable resources

Related topics