Slide 1
Slide 1 text
Migrating shopify’s core
rails monolith
to trilogy
Adrianna Chang - RailsConf 2023
Slide 2
Slide 2 text
Hi, i’m adrianna!
Senior Software Engineer
Based in Ottawa, ON
Working with Rails since 2016 ❤
Rails Triage Team
Passionate about OSS and
making Rails more diverse!
🏻 ♀
☀
Slide 3
Slide 3 text
No content
Slide 4
Slide 4 text
Trilogy db client
1
An overview of Trilogy, and why
you might want to use it.
Building a feature in trilogy
2
Adding a new feature to
Trilogy that we needed for our
application.
Migrating to trilogy
3
Changes we made to our app code
to get CI passing with Trilogy.
Deploying trilogy
4
How we deployed a large
change like this to production,
and what the results were.
On the agenda today
Slide 5
Slide 5 text
Running Trilogy in production
Slide 6
Slide 6 text
Part 1: the trilogy db client
Slide 7
Slide 7 text
Trilogy db client
● Custom implementation of MySQL protocol
● Supports most frequently used parts of the
protocol
○ Authentication handshake
○ Ping, query, and quit commands
● Designed for flexibility, performance and ease of
embedding
Check out Matthew Draper’s article
on GitHub’s engineering blog:
https://github.com/github/trilogy
● MySQL database client with Ruby bindings
● Open sourced by GitHub in 2022
Slide 8
Slide 8 text
Why switch?
● Minimal dependencies required for compilation
○ No dependency on libmysqlclient / libmariadb, which means:
■ Simpler installation
■ Can eliminate client / server version mismatch issues
■ Minimizes times data must be copied when handling packets
● Designed to perform efficiently in context of Ruby VM
○ Conscious use of dynamic memory allocation
○ API designed to use non-blocking operations and IO callbacks where
possible
Slide 9
Slide 9 text
CAVEATS
● A few incompatibilities with MySQL 8.0
● Not as feature-complete as Mysql2 / libmysqlclient
● Newly open-sourced library
○ Looking for an open source project to contribute to? Consider
helping us out! 🫶
Slide 10
Slide 10 text
Trilogy +
● Repo: github/activerecord-trilogy-adapter
● Database config pointing to trilogy
● Gems: Trilogy DB client, Active Record
adapter
Slide 11
Slide 11 text
Why shopify made the switch
● Better developer experience
● Speedups in query performance
● Strong maintainership
● Upstream Trilogy to rails/rails
● Make Trilogy a community standard
Slide 12
Slide 12 text
Part 2: adding a feature
to the trilogy client
Slide 13
Slide 13 text
Mysql client / server protocol
Client Server
Connects
Handshake packet
Authentication packet
OK or ERR
● Server listens for connections on TCP/IP port or
local socket
● When a client connects, the client and server go
through an authentication phase
1) Server checks whether host is allowed to
connect
2) If allowed, server sends handshake packet
3) Client sends back authentication packet
4) Server verifies information, responds with OK
(success) or ERR
Slide 14
Slide 14 text
Mysql client / server protocol
Command packet
Result
Client Server ● If the authentication phase is successful,
the client can enter query phase and
issue commands
● Many type of commands:
○ COM_PING
○ COM_QUERY
○ COM_QUIT
Examples of other types of commands
available in the query phase:
● Server responds with result:
○ OK, EOF, ERR or result set
Slide 15
Slide 15 text
Protocol capabilities
● During the authentication phase, the client and
server exchange info about what they’re able to
do via a bitmask of protocol capabilities
Client Server
Connects
Handshake packet
Authentication packet
OK or ERR
Interested in learning more? Check out
the MySQL docs on capabilities flags:
● Examples:
○ CLIENT_CONNECT_WITH_DB
○ CLIENT_SSL
○ CLIENT_MULTI_STATEMENT
Slide 16
Slide 16 text
”MySQL supports the execution of a string
containing multiple statements separated by
semicolon (;) characters. This capability is
enabled by special options specified either
when you connect to the server [...] or after
connecting by calling set_server_option().“
MySQL docs on multi-statement
execution support:
Multi-statement support
Slide 17
Slide 17 text
Multi-statement support
”MySQL supports the execution of a
string containing multiple
statements separated by
semicolon (;) characters. This
capability is enabled by special options
specified either when you connect to the
server [...] or after connecting by calling
set_server_option().“
Multi-statement support
Slide 18
Slide 18 text
Protocol Capabilities
”During the authentication phase, the client and
server exchange info about what they’re able to do.“
Client Server
Connects
Handshake packet
Authentication packet
OK or ERR
Flag: CLIENT_MULTI_STATEMENTS
Hex Value: 0x10000
When set, indicates that the client can send more than
one statement in one query.
Multi-statement support
Slide 19
Slide 19 text
Multi-statement support
Multi-statement support
Slide 20
Slide 20 text
Multi-statement support
● Fixture insertion!
○ Reducing queries required to
insert fixtures
● Multi-statement queries can be more
performant
○ Shopify: ~1000 fixture sets,
some with 100s of fixtures
Multi-statement support
Why does this matter for Rails?
Slide 21
Slide 21 text
● #multi_statements_enabled?
○ Are multi-statements enabled?
A look at the Mysql2 adapter in Rails…
● #with_multi_statements
○ Runs block in multi-statement
context
Multi-statement support
Slide 22
Slide 22 text
Multi-statement support
Slide 23
Slide 23 text
● #execute_batch
○ Combines statements into smaller
number of SQL strings
● #abandon_results!
○ Get connection back to usable state
A look at the Mysql2 adapter in Rails…
○ Adds SQL chunks up up to
@@max_allowed_packet
Multi-statement support
Slide 24
Slide 24 text
Multi-statement support
Slide 25
Slide 25 text
MySQL docs on multi-statement
execution support:
”MySQL supports the execution of a string
containing multiple statements separated by
semicolon (;) characters. This capability is
enabled by special options specified either
when you connect to the server [...] or
after connecting by calling
set_server_option().“
Multi-statement support
Slide 26
Slide 26 text
Requirement #1: Initialize new clients with MULTI_STATEMENTS option set.
@paarthmadan
Multi-statement support
Slide 27
Slide 27 text
Multi-statement support
Slide 28
Slide 28 text
Multi-statement support
Slide 29
Slide 29 text
Multi-statement support
Slide 30
Slide 30 text
Multi-statement support
Requirement #1: Initialize new clients with MULTI_STATEMENTS option set.
Slide 31
Slide 31 text
Requirement #2: Allow MULTI_STATEMENTS flag to be set after connecting.
Multi-statement support
Slide 32
Slide 32 text
Reminder:
C Extension
Ruby
Trilogy C API
● Commands in C API split into send_ and
recv_ functions
● trilogy_set_option_send: builds set
option packet and writes to socket
Multi-statement support
Slide 33
Slide 33 text
C Extension
Ruby
Trilogy C API
Builds a COM_SET_OPTION packet with the
server option to set
COM_SET_OPTION packet
Result
Client Server
Multi-statement support
Slide 34
Slide 34 text
Multi-statement support
C Extension
Ruby
Trilogy C API
Slide 35
Slide 35 text
Multi-statement support
● Commands in C API split into send_
and recv_ functions
● trilogy_set_option_recv: reads
response packet from server
C Extension
Ruby
Trilogy C API
Slide 36
Slide 36 text
Calls #trilogy_set_option_send
with the specified option
● Expose #set_server_option in Ruby
Waits for response from server via
#trilogy_set_option_recv
Returns true on success, handles timeouts
/ errors
Multi-statement support
C Extension
Ruby
Trilogy C API
● In our C extension…
Slide 37
Slide 37 text
Requirement #2: Allow MULTI_STATEMENTS flag to be set after connecting.
Multi-statement support
Slide 38
Slide 38 text
Multi-statement support
● #multi_statements_enabled?
○ Are multi-statements enabled?
Now with the Trilogy adapter…
● #with_multi_statements
○ Runs block in multi-statement
context
Slide 39
Slide 39 text
Multi-statement support
Slide 40
Slide 40 text
Multi-statement support
Slide 41
Slide 41 text
Original PR for
multi-statement
support in Trilogy:
Support for
setting server
options in Trilogy:
Adding multi-statement
capabilities to Trilogy
Active Record adapter:
Multi-statement support
Slide 42
Slide 42 text
Part 3: migrating to
trilogy
Slide 43
Slide 43 text
Goal: have a green ci build with trilogy
Step 1: Set application up to use Trilogy
Slide 44
Slide 44 text
Step 2: Address small API changes in client
Goal: have a green ci build with trilogy
Mysql2::Result
Trilogy::Result
Slide 45
Slide 45 text
Goal: have a green ci build with trilogy
Step 2: Address small API changes in client
Slide 46
Slide 46 text
Step 3: Handle error class changes
Mysql2::Timeout
Error
Trilogy::Timeout
Error
Mysql2::Connection
Error
Trilogy::Connection
Error
Goal: have a green ci build with trilogy
Slide 47
Slide 47 text
Goal: have a green ci build with trilogy
Step 3: Handle error class changes
Slide 48
Slide 48 text
Step 3: Handle error class changes
Goal: have a green ci build with trilogy
Slide 49
Slide 49 text
Part 4: summit?
Deploying trilogy to production
Slide 50
Slide 50 text
● Core Rails application is a modular monolith
Shopify Core
● BFCM 2022: 14 million queries per second at peak
● Database infrastructure
○ Database is horizontally partitioned
○ MySQL instances run on Google Cloud
Platform VMs managed by Chef
○ ProxySQL
■ ~100 000 client connections to
~2000 backend connections
Slide 51
Slide 51 text
Step 1: Attempt to run Trilogy DB client on 1% of production
● Export ENV var on 1% of production pods
● Switch out adapter in config/database.yml based on ENV var
Slide 52
Slide 52 text
Step 1: Attempt to run Trilogy DB client on 1% of production
● Export ENV var on 1% of production pods
● Switch out adapter in config/database.yml based on ENV var
● Ensure application code supported both Mysql2 and Trilogy
Slide 53
Slide 53 text
Step 1: Attempt to run Trilogy DB client on 1% of production
● Separate CI pipeline for Trilogy
○ Too expensive!
● Remove Mysql2 test coverage
○ Not ideal, 99% of prod using Mysql2
● Half of CI on Trilogy, half with Mysql2
○ Difficult to spot bugs
Decided to keep test coverage on
Mysql2, and run CI against Trilogy
daily
How to run tests?
Slide 54
Slide 54 text
Step 2: Try running Trilogy on 50% of production
Slide 55
Slide 55 text
Step 2: Try running Trilogy on 50% of production
What happened?!
● If client capabilities are different, ProxySQL
needs to perform COM_CHANGE_USER
● ProxySQL keeps pool of backend connections
Shopify Core
Mysql2
Trilogy
COM_CHANGE_USER
● Using clients with different protocol options
overwhelmed DB with change user commands
Slide 56
Slide 56 text
Step 2: Try running Trilogy on half of production
● Queried a ProxySQL pod’s connection list
● Mysql2 was setting CLIENT_MULTI_RESULTS, but Trilogy was not!
Flag: CLIENT_MULTI_RESULTS
Hex Value: 0x20000
When set, tells the server it's
capable of handling multiple result
sets from a query.
Slide 57
Slide 57 text
Step 3: Attempt to run Trilogy subset of production… again
● Targeted 100% of single cluster instead of 50% of production
● Confirmed that the connection options matched between Trilogy and Mysql2
● Collected some metrics
Request time:
● Mysql2: Avg 3.46 ms
● Trilogy: Avg 2.70ms
~22% faster
Slide 58
Slide 58 text
MySQL query time:
● Mysql2: Avg 1.49ms
● Trilogy: Avg 1.24 ms
~ 17% faster
Step 3: Attempt to run Trilogy subset of production… again
● Targeted 100% of single cluster instead of 50% of production
● Confirmed that the connection options matched between Trilogy and Mysql2
● Collected some metrics
Slide 59
Slide 59 text
Step 4: Run Trilogy on 100% of production
● No issues running Trilogy at 100% on one of our European clusters
● Took the plunge and migrated fully to Trilogy
● Changed tests to run against Trilogy instead of Mysql2
● Success! 🎉
Slide 60
Slide 60 text
No content
Slide 61
Slide 61 text
surprise announcement…
The Trilogy adapter will be available in Rails 7.1!
Slide 62
Slide 62 text
What’s next?
● Give Trilogy a try in a new application!
● Try migrating an existing application from Mysql2 to Trilogy.
● Consider contributing to the Trilogy client, or the adapter
(now in Rails!)
● Flex your new MySQL knowledge on all your coworkers 😂
Slide 63
Slide 63 text
Thanks! ❤
adriannakchang adriannachang.me Presentation template by Slidesgo,
icons by Flaticon.
@paarthmadan @eileencodes @casperisfine @matthewd @composerinteralia @jhawthorn @HParker @brianmario @tenderlove