00:00:15.240
Hi everyone, thanks so much for being here with me. I am so excited to be with you all here in Amsterdam for our first ever Rails World. Today, I will tell you the story of how we migrated from MySQL 2 to Trilogy in Shopify's largest Rails codebase and a little bit about how we added support for Trilogy to Rails for the 7.1 release.
00:00:24.640
Before we get started, let me introduce myself. My name is Adrianna Chang, and I'm a Senior Software Engineer at Shopify. Shopify is one of the founding core members of the Rails Foundation, and we have a booth here, so come check us out! We are an e-commerce company offering a variety of products for online and in-person retail. I currently work on a team called Scalable Rails Apps, and our job is to ensure that Shopify's Rails applications are using Rails correctly. We aim to make sure that our applications are easy to build, maintain, and refactor, while also ensuring that Rails continues to evolve to meet Shopify's needs.
00:01:01.440
I'm based in Ottawa, Ontario, Canada. Outside of work, I enjoy cycling, hiking, and spending time outdoors. I also love spending time with my dog, Jasper. He's a Rottweiler and a big sweetheart who keeps me busy. I've been working with Rails since I started at Shopify in 2016. I started as an intern right out of high school and worked there while I completed my computer science degree at university. I am one of the maintainers of the Trilogy project, currently a member of the Rails Issues team, and I am really passionate about open-source software. I am also committed to making the Ruby and Rails communities more diverse and inclusive.
00:01:40.000
One of the things I'm excited about is being part of the WMBR community, which supports women and non-binary Rubyists. We hold virtual meetups once a month, and we have a Slack workspace that is quite active. WMBR is hosting a breakfast sponsored by Shopify tomorrow morning from 8:00 to 9:30. I know it's early, but please join us if you are a woman or non-binary individual. I would love to see you there!
00:02:14.080
Today, we will be discussing Trilogy. If you’ve never heard of Trilogy before, don't worry! We will start with an overview of the Trilogy database client. I will talk about what makes Trilogy different and why you might want to switch. Next, we will cover some of the changes we needed to make to our codebase at Shopify in order to switch from MySQL 2 to Trilogy. Then, I'll discuss how we deployed a large change like this to production and what some of the results were. Finally, we will talk about the process of upstreaming the Trilogy adapter to Rails for version 7.1.
00:02:58.960
We will use the analogy of climbing a mountain to describe the journey we embarked on while migrating from MySQL 2 to Trilogy at Shopify. Our goal was to run Trilogy in production, which was our summit, but the path ahead was not very clear. We weren’t sure what incompatibilities might exist between MySQL 2 and Trilogy or what risks we needed to consider. It became apparent that before we could start our trek up the mountain, we needed to gather information and understand the lay of the land.
00:03:43.120
So let’s start by learning about the Trilogy database client. Trilogy is a MySQL database client written in C with Ruby bindings. A client is any program that can communicate with the server and issue commands. Trilogy was open-sourced by GitHub in 2022 alongside an Active Record adapter. Matthew Dver from GitHub wrote a wonderful blog post about it for GitHub's Engineering Blog, which I will link at the end of the slides. If you're interested in Trilogy, please do give it a read.
00:04:01.280
The neat thing about Trilogy is that it uses a custom implementation of the MySQL network protocol. Most client programs rely on the libmysqlclient library distributed with MySQL to communicate with the server. For example, MySQL 2, a popular MySQL Ruby gem, links against libmysqlclient. However, Trilogy does not depend on libmysqlclient; it defines its own low-level API in C for communicating with a MySQL server. It is entirely self-contained with no external dependencies other than libraries like Posex and OpenSSL. Trilogy supports the most frequently used parts of the protocol, including authentication, handshake, Ping, query, and quit commands. Overall, Trilogy is designed for flexibility, performance, and ease of embedding.
00:05:05.079
So why would you want to switch to Trilogy? One of the biggest reasons is that there are minimal dependencies required for compilation. There is no dependency on libmysqlclient or the similar libmariadb library, which means that it is simpler to install. You don't need to install and link against the libmysqlclient library or deal with compiler flags. Using Trilogy can also eliminate client-server version mismatch issues, a common problem we encountered at Shopify, which made it difficult to upgrade MySQL versions on our servers. In skipping the libmysqlclient dependency, Trilogy sidesteps these issues. Furthermore, it can also improve memory usage by minimizing the number of times data must be copied when handling network packets.
00:06:07.360
Trilogy is designed to perform efficiently in the context of the Ruby VM. It takes care with dynamic memory allocation and the API is designed to use non-blocking operations and IO callbacks where possible, which improves performance in a language like Ruby. However, there are some caveats to switching to Trilogy that you should be aware of. There are still some incompatibilities with MySQL 8 related to authentication plugins. If your application is already on MySQL 8, this might be something to consider. Trilogy may not be as future-complete as MySQL 2 and the libmysqlclient, so if your application relies on a lot of custom database configurations, you might want to take that into account. Features like prepared statement support are still being built into the Ruby driver.
00:07:09.120
Lastly, Trilogy is a fairly recently open-sourced library. Its documentation is perhaps not as robust as other client library options, and there are still not many applications using Trilogy in production. Shopify is now using it, and GitHub has been using it for a while. While those are two prominent Rails applications that are using it successfully, there may be features that are lacking because other existing applications didn’t require those features. I encourage you to consider giving Trilogy a try in your application, and since it’s open source, consider contributing if you notice that it’s missing something you need.
00:08:06.880
So, why did we want to make the switch at Shopify? There were several goals we aimed to accomplish. One of the biggest reasons was to improve developer experience. We were facing compilation headaches with MySQL 2 due to its dependence on libmysqlclient, especially on macOS. Our hope with Trilogy was that it would greatly simplify the process of getting a Rails app up and running with a MySQL database locally. We were also optimistic about speedups in query performance; after all, everyone likes it when things run faster, right? Trilogy made claims about its performance in Ruby, and we were eager to see if those claims held true for us. Finally, we were keen on moving to a library with a strong sense of maintainership.
00:08:56.960
Not only does Trilogy have active contributors, but we also had several engineers at Shopify who had worked on this library while at GitHub, and they could offer their support and expertise. Throughout this process, we kept a larger target in mind: Aon Patterson and Eileen Ussel were the original advocates for open-sourcing Trilogy while at GitHub, and now they work with me on Shopify’s Rails Infrastructure team. If we managed to adopt Trilogy at Shopify, we were confident we could make the case to upstream the Trilogy adapter to Rails, which would mean bigger maintainership for Trilogy. Thus, we were excited about the opportunity to make Trilogy a Rails community standard.
00:09:51.400
In other words, we realized that although our initial goal was to just run Trilogy in production at Shopify, we were actually trying to reach this bigger peak of upstreaming Trilogy support to Rails and making it a community standard. However, we still needed to start with small steps and try out Trilogy in our monolith. If you're thinking about moving to Trilogy in an existing Rails app using MySQL 2, here are the steps we took and some things to watch out for.
00:10:19.040
Step one is to set up your application to use Trilogy. We needed to install the gem for the Trilogy client as well as the gem for the Active Record adapter. At the time, the Active Record adapter was a standalone gem. This adapter was later upstreamed as part of the Rails 7.1 release; we'll discuss this upstream process later on. But just keep in mind that you won't need to install the separate Active Record gem if your app is using Rails 7.1. The gem does offer support for Trilogy for Rails versions earlier than 7.1, starting at Rails 6.0.
00:10:55.440
If you are not yet on Rails 7.1 but still want to give Trilogy a try, you can do so with the gem. Next, we set the adapter to Trilogy in our database YAML file, and that is pretty much it in terms of configuration. It's a very plug-and-play process. However, there were a couple of other considerations. We needed to address differences in the API when working directly with the database client or client results.
00:11:14.960
Most of the time, we go through Active Record APIs when dealing with the database, so it’s relatively uncommon to need to interact with the client directly. However, if there are places where you are interacting with the client, you'll need to watch for some small API changes. For example, we have a method that executes some custom SQL on the database connection and enumerates the result. With MySQL 2, calling execute with the query would return a MySQL 2 result object, which we would enumerate using the each_hash method. With Trilogy, we get a Trilogy result object and enumerate it with the each_hash method instead, which is a small change.
00:12:27.440
Another small example is that the MySQL 2 client has a query_options method that exposes options passed at connection time when the client is initialized. We accessed the client by using raw_connection and then using the query_options method to grab the host and port for the current connection in order to use them in a cache key. Trilogy has a concept called query_flags, a bitmask that controls options set at query time, such as casting options, which are distinct from MySQL 2's query_options. To mitigate confusion, we added a method to Trilogy that exposes the options passed in at connection time, naming it connection_options instead of query_options.
00:13:42.720
Finally, we needed to address differences in error classes. If you're rescuing MySQL 2 errors directly, you'll need to change these to rescue Trilogy's BaseError instead, which is TrilogyError. Trilogy overall raises errors that are comparable to MySQL 2. For example, there is a MySQL 2 timeout error and a related Trilogy timeout error, as well as a MySQL 2 connection error and a related Trilogy connection error. The mapping should be relatively straightforward, so if you're rescuing specific errors, the Trilogy error should be similar. However, the error messages will differ slightly, so if you’re checking explicit error messages, ensure you adjust those accordingly.
00:14:47.920
There is one key difference in connection-related error handling between MySQL 2 and Trilogy: if the database is down. MySQL 2 raises a connection_not_established error in this case. Conversely, with Trilogy, if the database is down, the client-side connection is closed. Thus, if the database is down, Trilogy will surface that as a closed_connection error. The adapter translates closed_connection errors to ActiveRecord connection_failed errors, which is a more appropriate classification. This means that when testing for database down scenarios, your assertions may need to change to accommodate this new behavior.
00:15:53.280
That was a brief overview of the changes we made to our app code to facilitate the migration. However, saying that these were all we needed to do would be an oversimplification. There were some other significant changes we needed to implement to ensure feature parity between MySQL 2 and Trilogy for Shopify's needs. One of the first steps was ensuring that Trilogy's features were comprehensive for Shopify's requirements. We needed to build multi-statement support into the Ruby bindings for the Trilogy database client. Multi-statement allows executing multiple SQL statements in a single query command to the database.
00:16:48.000
This was crucial for us because Active Record uses multi-statement capabilities for bulk fixture insertion. Our monolith has numerous fixture sets, and we relied on this bulk fixture insertion to minimize the number of SQL queries required to insert all of our fixture data. If you're interested in learning about this work and seeing code in the Trilogy C library and Ruby driver, I gave a talk about this at RailsConf in Atlanta earlier this year. I will include a link to that talk at the end of the presentation.
00:17:38.720
We also worked on ensuring that any patches to the MySQL 2 adapter were upstreamed where possible to avoid rewriting patches for Trilogy. One example is the Active Record Pant MySQL 2 adapter, which was a custom MySQL adapter we wrote on top of the MySQL 2 Active Record adapter. Its purpose was to report SQL warnings, allowing applications to capture and address SQL warnings progressively. By default, Pant raises all SQL warnings as errors, but applications can fine-tune the behavior around warnings in an initializer, allowing for customization.
00:18:33.840
Rather than re-implement Pant to work with Trilogy, we decided to upstream SQL warning reporting to Rails. This allows users to log, report, or raise warnings, and enables them to ignore certain warnings and customize warning behavior through a proc. We came up with a straightforward API by introducing two Active Record configurations: one for specifying the action to take when a SQL warning is emitted (DB warnings action) and another for listing warnings to ignore (DB warnings ignore).
00:19:19.680
After upstreaming this to Rails, we could remove our dependency on Pant, which we deprecated for Rails 7.1. The gem will be removed in a future version of Rails. Lastly, we needed to ensure parity across all external tools. If your application relies on database tools or frameworks that assume MySQL 2 is being used as the database client, make sure to adjust them to be compatible with the Trilogy client.
00:20:12.520
For example, at Shopify, we utilized a tool called Semian for controlling access to slow or unresponsive services. Semian is an open-source library that we wrote to intercept resource access and fail quickly when a service is unresponsive. Since the database could be slow or unresponsive, Semian needed to intercept database calls. We initially had Semian code compatible with MySQL 2, but it wasn’t directly compatible with Trilogy, so we wrote a resource adapter to ensure fast failure with Trilogy.
00:21:06.680
Semian is open source, so you can check out the code if you're interested. I have included a link at the end of the slides. If you are interested in learning more about Semian or have questions, feel free to approach me later. Another small example is OpenTelemetry, an open-source observability framework we had been using to instrument our SQL queries at Shopify. We initially relied on the OpenTelemetry MySQL 2 gem, but we noticed that there was a similar OpenTelemetry Trilogy gem already available. However, it had not been maintained as consistently as the MySQL 2 version.
00:21:50.520
As a result, we made some changes to OpenTelemetry Trilogy to ensure it was at feature parity with the MySQL 2 version, and now it supports Trilogy out of the box, which is great! At this point, our CI was passing, and we were eager to try things in production. However, we were cautious about deploying such a massive change all at once, so we needed to devise a plan to roll things out safely.
00:23:14.040
To begin, we decided to run the Trilogy database client on just 1% of production traffic. We achieved this by exporting an environment variable to 1% of our pods and then configuring the adapter in our database YAML file based on whether this variable was set. This meant that we had to support both MySQL 2 and Trilogy in our application code. Fortunately, this wasn’t too difficult, as the APIs are quite similar. For instance, wherever we were dealing directly with the client API, we would check the connection class and handle any differences.
00:24:42.280
However, we were unsure how to run tests during this process. We didn’t want to create a separate CI pipeline for Trilogy, as we have a large CI suite, and that would be costly. We also didn’t want to remove MySQL 2 test coverage because the majority of production was still using MySQL 2. We considered running half of our CI workers with Trilogy and the other half with MySQL 2, but we were concerned that a failing test with one worker might get mingled with another worker using a different database client, obscuring the issues. Thus, it was a no-go.
00:25:43.440
In the end, we opted for the simplest route: keeping our tests running fully against MySQL 2 while manually running CI against a branch running Trilogy daily for the entire rollout period to ensure compatibility. Next, we scaled Trilogy up to run on 100% of a single cluster. This allowed us to compare Trilogy's performance to MySQL 2 by examining metrics on that designated cluster against our others. We were thrilled to obtain significant results.
00:27:05.760
For instance, this graph shows request times. The top line is MySQL 2, showing an average request time of 3.46 milliseconds, while Trilogy demonstrated an average request time of 2.7 milliseconds—about a 22% speedup. When we looked at MySQL query times, MySQL 2 averaged 1.49 milliseconds per query, while Trilogy recorded just 1.24 milliseconds—a remarkable 177% faster! This was incredibly exciting for us.
00:28:00.000
We saw no issues running Trilogy at 100% on one of our European clusters for 24 hours, prompting us to take the plunge and roll it out to 100% of our production environment. At this point, we also shared all our test changes, so our test suite was fully operating against Trilogy instead of MySQL 2, and the rollout was successful.
00:28:35.900
As previously mentioned, our final goal was to upstream the Active Record adapter for Trilogy to Rails to facilitate support for Trilogy out of the box. This was crucial for us for several reasons. Firstly, it meant that more people would understand and share ownership of the adapter code. Secondly, it would allow us to run all of the Rails adapter tests against Trilogy, ensuring better test coverage without duplicating every test into another codebase. Lastly, it reduced the risk of the Trilogy adapter deviating from changes in the abstract MySQL adapter class in Rails since everything would remain within the same codebase.
00:29:36.000
Once our monolith was up and running with Trilogy in production, Shopify began collaborating with the GitHub team to advocate for upstreaming the adapter to Rails. As we discussed earlier, Trilogy started as a standalone gem open-sourced by GitHub alongside the client library. The gem itself is relatively lightweight; it features a Trilogy adapter class inheriting from Rails' abstract MySQL adapter, containing all the core logic for the adapter.
00:30:29.200
There are a handful of other modules defined within the gem that facilitate Trilogy's integration with Rails. For instance, a DB console module ensures that the DB console command functions out of the box for Trilogy. You might be wondering if most of the work to upstream Trilogy involved merely copying and pasting code from the gem to Rails—and mostly, it did. However, to be fair, some additional changes were necessary for the integration process.
00:31:38.360
First, we modified the Active Record rake file so that all abstract MySQL adapter tests would run against Trilogy. Some of the tests were MySQL 2 specific, so we generalized them to ensure they would adequately test both MySQL 2 and Trilogy. While running the abstract MySQL adapter tests against Trilogy, we discovered a few features that Trilogy was missing in comparison to MySQL 2 and the abstract MySQL adapter's API. For example, Trilogy did not support additional options on the explain method, so we added this functionality.
00:32:17.600
We also added an option for Trilogy in the database generator so that applications could use Trilogy out of the box. Furthermore, we included support for Trilogy within Buildkite to ensure CI could run the Trilogy tests. After these initial PRs had been merged, we proceeded to some cleanup work. For instance, we eliminated redundant tests from the Trilogy test suite in Rails, as this suite contained numerous tests that overlapped with functionality already covered by the abstract MySQL test suite.
00:33:15.680
Initially, we ported everything over to simplify the initial PR, but it became clear that these tests were redundant, prompting us to remove them. We also extracted shared database statement code between MySQL 2 and Trilogy into a shared abstract MySQL database statements module, which was a nice cleanup effort. Lastly, the Trilogy adapter included several custom error classes which translated specific DB client errors into Active Record errors. While we initially ported these error classes over in the initial PR, we recognized they lacked much value and determined it would be better for Trilogy to raise generic Active Record errors, just like the other adapters in Rails.
00:34:10.960
So, that is the story of our journey migrating from MySQL 2 to Trilogy at Shopify and subsequently upstreaming it to Rails. As mentioned earlier, Trilogy is now available out of the box in Rails 7.1. At the beginning, I noted that embarking on this journey was challenging due to the unclear path ahead. My hope is that you can follow the path we've forged and learn from our experiences. So go forth and give Trilogy a try in your Rails applications.
00:35:08.280
Thank you all so much for attending my talk! I would also like to extend a huge thank you to the wonderful people who assisted with Shopify's journey in adopting Trilogy and contributed to the upstreaming efforts; we couldn't have succeeded without them. If you'd like to learn more about Trilogy or have questions or comments, please feel free to find me and say hi. I'd be happy to chat. My contact details are on the slide, along with a list of any PRs I referenced or any slides shown. There's also a QR code linking to my slides if you would like to see them. Thank you!
