Benchmarking Trilogy : Is it really the future of Rails ?
Tags: trilogy, rails, ruby, mysql
Published: 2023-10-09 07:30:00

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](https://rubyonrails.org/2023/10/5/Rails-7-1-0-has-been-released).

**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](https://github.com/trilogy-libraries/trilogy/issues/7#issuecomment-1005037628), 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:

```bash
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:

```bash
rails g model Employee name salary:float

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

Along with the accompanying seed data:

```ruby
# 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:

```yml
# 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:

```ruby
# 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]
```

```ruby
# 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:

```bash
# 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:

```bash
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:

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

Here are the results in seconds for the above benchmark:

| Adapter | Min | Max | Sum | Avg |
| --- | --- | --- | --- | --- |
| mysql2 | 0.000183 | 0.186492 | 0.199231 | 0.00398 |
| trilogy | 0.000119 | 0.001275 | 0.009987 | 0.000199 |

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!

```ruby
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:

| Adapter | Min | Max | Sum | Avg |
| --- | --- | --- | --- | --- |
| mysql2 | 32.712003 | 35.604772 | 1711.21506 | 34.2243012 |
| trilogy | 9.86154 | 11.738046 | 545.433172 | 10.90866344 |

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! ⬇️