Shopify Ruby on Rails distributed monolith runs 19M queries per second on MySQL

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by firefoxd on Wednesday November 29, 2023 @02:03AM (#38456429)

I worked at a fortune 10, backing up our mysql db daily took 8 hours. We had a job that processed data that also took 8 hours. We had a small window of time during work hours to fetch the data, if you happened to query when either service was running, you'd probably not get any data back.

Few months in, both services reached 12 hours to run. We had to change the start time just so they'd happen back to back. I rolled up my sleeves and got to work on the db.

A week later, the job's runtime was down to 2 hours. I was nearly promoted out of the job. I kept making improvements to the database, reorganizing the application, and optimizing. One day I manually ran the job, then went to get coffee. When i came back, prompt was awaiting my next command. I thought it had silently failed. I ran it several times throughout the day and checked the response. It ran in ~17 minutes. The backup was also reduced to less than 20 minutes. This was all on mysql 5.7

We literally gained 23 hours of availability. We had no idea what to do with it. I was fired shortly after.

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by fardinahsan on Wednesday November 29, 2023 @02:17AM (#38456512)

Can you go in some high level details as to why it was slow and what you did to make it fast. That's always the most interesting part of a post like this.

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by kawsper on Wednesday November 29, 2023 @04:21AM (#38457269)

I've done something similar, you start out with something naive and simple like mysqldump that takes an age, and move on to more specialised tools like Percona XtraBackup that allows for incremental backups.

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by forgingahead on Wednesday November 29, 2023 @05:57AM (#38457898)

Any tips on similar tools for Postgres?

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by __s on Wednesday November 29, 2023 @08:34AM (#38459129)

Depends on environment. If you can do disk snapshots that's the way to go (can be hard with disk striping). wal-g works for storing both base backups & wal to various storages in parallel & can be throttled with env variables

Source: worked on Azure's managed Citus pg

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by firefoxd on Wednesday November 29, 2023 @11:34AM (#38461572)

The db connections were poorly managed. Each query started by starting a new connection. It then checked if it failed, slept, retried, then ran. There were several queries called in loops, so the connection pool was always dry. The code spent most of it's time sleeping. Then the queries themselves were highly inefficient, bad joins, no indexes, etc. It was satisfying to fix the mess.

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by dunno7456 on Wednesday November 29, 2023 @06:02PM (#38466560)

I had two similar experiences in the past years: I added some database indices, the application become super fast, the rest of the team starts acting weird, I get shown the door.

At this point I'm pretty sure anything IT relate became a bullshit job.

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by appplication on Wednesday November 29, 2023 @02:05AM (#38456437)

Ha, that was quite a twist at the end there. Was this due to your self-created redundancy?

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by soks86 on Wednesday November 29, 2023 @02:11AM (#38456463)

or embarrassment and concern on the part of the people who created this system before him.

I've made similar improvements (40x speedup, 8x reduction in RAM footprint) and had been shown the door. This was for software that was a bit over 30 years old too so it was a bit involved.

The team winning does not help a narcissist feel better. You'd be better off getting nothing done while stroking their ego daily.

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by alserio on Wednesday November 29, 2023 @07:47AM (#38458694)

it seems like they did you a solid showing you that you were wasted working in that environment

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by thinkingemote on Wednesday November 29, 2023 @05:10AM (#38457602)

"job security through database complexity"

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by intelVISA on Wednesday November 29, 2023 @08:51AM (#38459294)

> We literally gained 23 hours of availability. We had no idea what to do with it. I was fired shortly after.

Unfortunately the case when you do your job too well, always leave a good 15-25% on the table to keep yourself 'required'.

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by TheCleric on Wednesday November 29, 2023 @09:45AM (#38459927)

> I was fired shortly after.

Such a simple summary of how companies will chew you up and spit you out.

[ Parent | on HN ]

Re:Shopify Ruby on Rails distributed monolith runs 19M queri... by dwaltrip on Wednesday November 29, 2023 @11:25AM (#38461445)

What reason did they give for firing you?

[ Parent | on HN ]