With the release of Rails 7.1, a new feature has emerged: native support for the Trilogy database adapter. This development marks a significant step toward the future of Ruby on Rails, as it is planned for Trilogy to become the default database driver in Rails 8.

Trilogy allows interaction with a MySQL database. Naturally, we'll compare it to the already established adapter, mysql2.

After some research, I discovered that both Shopify and GitHub are already using Trilogy as their database adapter. So, what's behind the enthusiasm for this adapter?

In this article, we will dive deep into this adapter and explore the results of a benchmark I conducted to better understand its performance.

The Promise of Trilogy

According to one of its creators, Trilogy's main motivation is to provide a library that exposes a flexible architecture, offering both non-blocking and blocking client APIs. This versatility makes it suitable for a wide range of applications, from high-concurrency and event-driven systems to more traditional synchronous operations. By minimizing dependencies and reducing dynamic memory allocation to a minimum, Trilogy prioritizes performance and resource efficiency.

So, in theory, Trilogy is:

• Lighter than mysql2
• Faster than mysql2

After reading all this, one might wonder, "Where do I sign up?"

On a more serious note, I was puzzled by why this adapter is so highly regarded when I had never heard of it before. So, before rushing to change your adapters in production, let's together verify the three points mentioned above.

The Benchmark

Setup

For the setup, I chose to create a Rails 7.1 application with as few dependencies as possible:

rails new app-with-trilogy --database=trilogy --api --minimal --skip-test --skip-system-test --skip-javascript

For our testing scenario, we need a database with some data. Let's create two simple tables as follows:

rails g model Employee name salary:float

rails g model PerformanceReview employee:references reviewer:references score:integer

Along with the accompanying seed data:

# db/seeds.rb
FactoryBot.define do
  factory :employee do
    name { Faker::Name.name }
    salary { Faker::Number.decimal(l_digits: 2) }
  end
  factory :performance_review do
    employee
    reviewer factory: :employee
    score { Faker::Number.between(from: 1, to: 100) }
  end

end
FactoryBot.create_list(:employee, 10_000)

Employee.find_each do |employee|
  FactoryBot.create_list(:performance_review, 3, employee: employee, reviewer: Employee.select(:id).sample)
end

Finally, in our database.yml config, we will modify set the adapter to trilogy:

# config/database.yml

default: &default
  adapter: trilogy
  [ ... ]

We are now ready to test!

1. Is Trilogy Lighter?

To verify this, we will compare the disk size of two configurations:

# GEMFILE with mysql2
source "https://rubygems.org"

ruby "3.2.0"

gem "rails", "~> 7.1.0"
gem "mysql2"
gem "puma", ">= 5.0"
gem "tzinfo-data", platforms: %i[windows jruby]

# GEMFILE with trilogy
source "https://rubygems.org"

ruby "3.2.0"

gem "rails", "~> 7.1.0"
gem "trilogy", "~> 2.6"
gem "puma", ">= 5.0"
gem "tzinfo-data", platforms: %i[windows jruby]

To isolate our gems, I use the command bundle --path vendor to build all the gems in the vendor folder to compare the size on disk using the du -sh vendor command.

Here are the results:

# with mysql2 gem
du -sh vendor
63M

# with trilogy gem
du -sh vendor
62M

So, we have a minimal 1MB difference between the two gems, with Trilogy winning.

It's interesting to note that since Trilogy has no dependencies on libmysql, the Docker image produced will also be lighter. Let's put it to the test!

2. Is the Image Using Trilogy Lighter?

It's true that in recent versions of Rails, when we generate a new application with rails new, the application comes with a Dockerfile.

Let's compare the two Docker images generated by the Rails generator.

To do this, we'll create a second Rails application using the mysql2 adapter:

rails new app-with-mysql --database=mysql --api --minimal --skip-test --skip-system-test --skip-javascript

Comparing the two Dockerfiles, I notice two differences:

1. We install the default-libmysqlclient-dev package to build the mysql2 gem in the app's build image.
2. We install the default-mysql-client package to run the mysql2 gem on the image.

This small difference will certainly have an impact, which is reflected in memory usage for both images.

I left my two images running for a few minutes before recording stable memory usage for both:

• For the image using the mysql2 adapter, it stabilizes at 60.5MB.
• For the image using the trilogy adapter, it stabilizes at 55MB.

The gap is starting to widen in favor of trilogy!

Now that we know Trilogy is lighter than mysql2, let's verify together whether Trilogy is faster than MySQL.

3. Is Trilogy Really Faster Than MySQL?

To compare our two database adapters, I decided to use the benchmark gem, which is a well-known gem for benchmarking.

You can find the complete benchmark on this gist: https://gist.github.com/just-the-v/606b7ad4900f8c09f4e09a9d8d239765.

In essence, I start by comparing two simple interactions, the find and pluck methods:

50.times do |i|
  x.report "#{i}/ find" do
    Employee.find(20000)
  end
end

Here are the results in seconds for the above benchmark:

For now, this is very positive for Trilogy. However, the action in the benchmark is very simple and does not represent a real-world use case.

Let's conduct some more interesting tests!

50.times do |i|
  x.report "#{i}/ find 1000 times in 5 threads" do
    threads = []
    5.times do
      threads << Thread.new do
        1000.times do
          Employee.find(Employee.pluck(:id).sample)
        end
      end
    end
    threads.each(&:join)
  end
end

For each adapter, let's see how they perform in this test. We parallelize the process of finding an Employee randomly 1000 times, 5 times. This action is quite heavy. Parallelization allows us to see how the adapter reacts under concurrent loads, which is one of Trilogy's promises.

Here are the results in seconds:

The result is astonishing: Trilogy is 3 times faster than mysql2 in this benchmark. That's significant!

Additional benchmarks are available on the gist if you want more details.

TL;DR

So, based on the tests we conducted, we can conclude several things:

1. The trilogy adapter is slightly lighter than the mysql2 adapter.
2. The trilogy adapter does not depend on libmysqlclient, which means goodbye to version conflicts during installation on a new computer.
3. The trilogy adapter is more efficient at handling interactions with a MySQL database than the mysql2 adapter.

Conclusion

In conclusion, the introduction of native support for the Trilogy adapter in Rails 7.1 marks a significant advancement for the Rails community. This new option promises improved performance, simplified dependency management, and increased flexibility for developers. With the goal of becoming the default database driver in Rails 8, Trilogy is destined to play a central role in the future of Ruby on Rails. I strongly encourage you to consider migrating your applications using the mysql2 adapter to trilogy.

Thank you for reading this far; I hope it has sparked your interest as much as mine to try it in production ASAP. Feel free to ask any questions below! ⬇️