Solid Queue internals, externals and all the things in between

Slide 1

Slide 1 text

github.com/rosa rosa.codes Principal programmer at 37signals Hi, I’m Rosa 👋

Slide 2

Slide 2 text

Solid Queue internals, externals and all the things in between Rails World 2024 Toronto - September 26th, 2024

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

“I don’t want to look” 🙈

Slide 5

Slide 5 text

“I don’t want to look” 🙈

Slide 6

Slide 6 text

solid_queue repo contributions in 2023 Promises Driven Development™

Slide 7

Slide 7 text

Our Resque setup # Jobs  gem "resque", "~> 2.0.0"  gem "resque_supervised_fork", bc: "resque_supervised_fork"  gem "resque-pool", bc: "resque-pool"  gem "resque-scheduler", github: "resque/resque-scheduler"  gem "resque-pause", bc: "resque-pause"  gem "sequential_jobs", bc: "sequential_jobs"  gem "scheduled_job", bc: "scheduled_job"

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Slide 17

Slide 17 text

Requirements ⬜ Enqueue and run jobs   ⬜ Don’t lose jobs when a worker is killed   ⬜ Run and manage multiple workers   ⬜ Support delayed jobs, a large number, enqueue them fast in batches   ⬜ Pause queues   ⬜ Support cron-style recurring jobs   ⬜ Support running some jobs sequentially   ⬜ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day    

Slide 18

Slide 18 text

What if we used a relational database? 🤔

Slide 19

Slide 19 text

What if we used a relational database? 🤔 Solid Cache inspiration

Slide 20

Slide 20 text

What if we used a relational database? 🤔 Solid Cache inspiration Some successful examples: GoodJob, Delayed

Slide 21

Slide 21 text

What if we used a relational database? 🤔 Solid Cache inspiration Leverage Active Record Some successful examples: GoodJob, Delayed

Slide 22

Slide 22 text

What if we used a relational database? 🤔 Solid Cache inspiration Simplicity, working out-of-the-box, one fewer moving piece Leverage Active Record Some successful examples: GoodJob, Delayed

Slide 23

Slide 23 text

What if we used a relational database? 🤔 Solid Cache inspiration Simplicity, working out-of-the-box, one fewer moving piece Leverage Active Record Some successful examples: GoodJob, Delayed Maybe a new Rails default?

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

Slide 27

Slide 27 text

The performance problem with polling A worker locks jobs to claim them The other workers wait

Slide 28

Slide 28 text

The performance problem with polling id queue priority claimed 1 default 0 0 2 default 1 0 3 default 2 0 4 default 1 0 Start transaction… SELECT id   FROM jobs   WHERE queue = "default"   AND claimed = 0   ORDER BY priority, id   LIMIT 2   FOR UPDATE; Claim the jobs…   Commit transaction… jobs table

Slide 29

Slide 29 text

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Slide 32

Slide 32 text

Slide 33

Slide 33 text

The performance problem with polling id queue priority claimed 1 default 0 1 2 default 1 1 3 default 2 0 4 default 1 0 Start transaction… SELECT id   FROM jobs   WHERE queue = "default"   AND claimed = 0   ORDER BY priority, id   LIMIT 2   FOR UPDATE; Claim the jobs…   Commit transaction… jobs table → 1, 2 All other workers waiting

Slide 34

Slide 34 text

Slide 35

Slide 35 text

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Enter SELECT … FOR UPDATE SKIP LOCKED Available in PostgreSQL from version 9.5 (2016) and MySQL 8.0 (2018)

Slide 39

Slide 39 text

Enter SELECT … FOR UPDATE SKIP LOCKED id queue priority claimed 1 default 0 0 2 default 1 0 3 default 2 0 4 default 1 0 Start transaction… SELECT id   FROM jobs   WHERE queue = "default"   AND claimed = 0   ORDER BY priority, id   LIMIT 2   FOR UPDATE SKIP LOCKED; Claim the jobs…   Commit transaction… jobs table

Slide 40

Slide 40 text

Slide 41

Slide 41 text

Slide 42

Slide 42 text

Slide 43

Slide 43 text

Slide 44

Slide 44 text

Slide 45

Slide 45 text

Enter SELECT … FOR UPDATE SKIP LOCKED id queue priority claimed 1 default 0 1 2 default 1 1 3 default 2 1 4 default 1 1 Start transaction… SELECT id   FROM jobs   WHERE queue = "default"   AND claimed = 0   ORDER BY priority, id   LIMIT 2   FOR UPDATE SKIP LOCKED; Claim the jobs…   Commit transaction… jobs table

Slide 46

Slide 46 text

The first prototype Main idea: keep the polled table as small as possible

Slide 47

Slide 47 text

The first prototype Main idea: keep the polled table as small as possible

Slide 48

Slide 48 text

The first prototype Main idea: keep the polled table as small as possible

Slide 49

Slide 49 text

The first prototype Main idea: keep the polled table as small as possible

Slide 50

Slide 50 text

The first prototype Main idea: keep the polled table as small as possible

Slide 51

Slide 51 text

The first prototype Main idea: keep the polled table as small as possible

Slide 52

Slide 52 text

The first prototype solid_queue_jobs   solid_queue_ready_executions   solid_queue_claimed_executions   solid_queue_scheduled_executions   solid_queue_failed_executions job data (class, arguments…) temporary execution data

Slide 53

Slide 53 text

The first prototype poll solid_queue_jobs   solid_queue_ready_executions   solid_queue_claimed_executions   solid_queue_scheduled_executions   solid_queue_failed_executions worker dispatcher poll supervisor manage

Slide 54

Slide 54 text

The first prototype move polled   jobs here solid_queue_jobs   solid_queue_ready_executions   solid_queue_claimed_executions   solid_queue_scheduled_executions   solid_queue_failed_executions worker dispatcher move polled   jobs here (in batches)

Slide 55

Slide 55 text

Slide 56

Slide 56 text

Requirements ✅ Enqueue and run jobs   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ⬜ Pause queues   ⬜ Support cron-style recurring jobs   ⬜ Support running some jobs sequentially   ⬜ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities

Slide 57

Slide 57 text

Requirements ✅ Enqueue and run jobs ⬜ Don’t keep a job claimed forever if worker dies   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ⬜ Pause queues   ⬜ Support cron-style recurring jobs   ⬜ Support running some jobs sequentially   ⬜ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities

Slide 58

Slide 58 text

Slide 59

Slide 59 text

Process registry and heartbeats 🫀

Slide 60

Slide 60 text

Process registry and heartbeats 🫀 Let’s add another table

Slide 61

Slide 61 text

Process registry and heartbeats 🫀 solid_queue_processes   last_heartbeat_at solid_queue_claimed_executions   process_id Let’s add another table

Slide 62

Slide 62 text

Process registry and heartbeats 🫀 solid_queue_processes   last_heartbeat_at solid_queue_claimed_executions   process_id supervisor periodic checks Let’s add another table

Slide 63

Slide 63 text

Requirements ✅ Enqueue and run jobs ⬜ Don’t keep a job claimed forever if worker dies   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ⬜ Pause queues   ⬜ Support cron-style recurring jobs   ⬜ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities

Slide 64

Slide 64 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ⬜ Pause queues   ⬜ Support cron-style recurring jobs   ⬜ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities

Slide 65

Slide 65 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ⬜ Pause queues

Slide 66

Slide 66 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ⬜ Pause queues Yep, another table! 👯 solid_queue_pauses

Slide 67

Slide 67 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ✅ Pause queues   ⬜ Support cron-style recurring jobs   ⬜ Support running some jobs sequentially   ⬜ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities

Slide 68

Slide 68 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ✅ Pause queues   ⬜ Support cron-style recurring jobs   ⬜ Support running some jobs sequentially   ⬜ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities

Slide 69

Slide 69 text

Further tuning the polling queries CREATE TABLE `solid_queue_ready_executions` (   ìd` bigint NOT NULL AUTO_INCREMENT,   `job_id` bigint DEFAULT NULL,   `queue_name` varchar(255) NOT NULL,   `priority` int NOT NULL DEFAULT '0',   `created_at` datetime(6) NOT NULL,   PRIMARY KEY (ìd`),   UNIQUE KEY ìndex_solid_queue_ready_executions_on_job_id` (`job_id`),

Slide 70

Slide 70 text

Slide 71

Slide 71 text

Further tuning the polling queries queues: recycling  queues: [ incineration, action_mailbox_incineration ]   queues: beta*  queues: * # all queues + paused queues

Slide 72

Slide 72 text

Further tuning the polling queries KEY `index_solid_queue_poll_by_queue` (`queue_name`,`priority`,`job_id`)) KEY `index_solid_queue_poll_all` (`priority`,`job_id`))

Slide 73

Slide 73 text

Slide 74

Slide 74 text

Further tuning the polling queries KEY `index_solid_queue_poll_by_queue` (`queue_name`,`priority`,`job_id`)) KEY `index_solid_queue_poll_all` (`priority`,`job_id`)) SELECT job_id   FROM solid_queue_ready_executions   WHERE queue_name = "incineration"   ORDER BY priority ASC, job_id ASC   LIMIT 4   FOR UPDATE SKIP LOCKED; SELECT job_id   FROM solid_queue_ready_executions   ORDER BY priority ASC, job_id ASC   LIMIT 4   FOR UPDATE SKIP LOCKED;

Slide 75

Slide 75 text

Further tuning the polling queries KEY `index_solid_queue_poll_by_queue` (`queue_name`,`priority`,`job_id`)) KEY `index_solid_queue_poll_all` (`priority`,`job_id`)) Covering indexes SELECT job_id   FROM solid_queue_ready_executions   WHERE queue_name = "incineration"   ORDER BY priority ASC, job_id ASC   LIMIT 4   FOR UPDATE SKIP LOCKED; SELECT job_id   FROM solid_queue_ready_executions   ORDER BY priority ASC, job_id ASC   LIMIT 4   FOR UPDATE SKIP LOCKED;

Slide 76

Slide 76 text

KEY `index_solid_queue_poll_by_queue` (`queue_name`,`priority`,`job_id`)) KEY `index_solid_queue_poll_all` (`priority`,`job_id`)) Current version

Slide 77

Slide 77 text

KEY `index_solid_queue_poll_by_queue` (`queue_name`,`priority`,`job_id`)) KEY `index_solid_queue_poll_all` (`priority`,`job_id`)) Current version Zero or one queue per query

Slide 78

Slide 78 text

KEY `index_solid_queue_poll_by_queue` (`queue_name`,`priority`,`job_id`)) KEY `index_solid_queue_poll_all` (`priority`,`job_id`)) Current version Zero or one queue per query Always the minimum number of queries

Slide 79

Slide 79 text

KEY `index_solid_queue_poll_by_queue` (`queue_name`,`priority`,`job_id`)) KEY `index_solid_queue_poll_all` (`priority`,`job_id`)) Current version 4,900 queries/second   110 microseconds/query   0.04 examined rows/query Zero or one queue per query Always the minimum number of queries

Slide 80

Slide 80 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ✅ Pause queues   ⬜ Support cron-style recurring jobs   ⬜ Support running some jobs sequentially   ⬜ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities

Slide 81

Slide 81 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ✅ Pause queues   ⬜ Support cron-style recurring jobs   ⬜ Support running some jobs sequentially   ✅ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities

Slide 82

Slide 82 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ✅ Pause queues   ⬜ Support cron-style recurring jobs   ⬜ Support running some jobs sequentially   ✅ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities

Slide 83

Slide 83 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ✅ Pause queues   ⬜ Support cron-style recurring jobs   ⬜ Support running some jobs sequentially   ✅ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities 🫣😰😱

Slide 84

Slide 84 text

Sequential jobs → concurrency controls

Slide 85

Slide 85 text

Sequential jobs → concurrency controls Make order not important

Slide 86

Slide 86 text

Sequential jobs → concurrency controls Limit on enqueuing Make order not important

Slide 87

Slide 87 text

Sequential jobs → concurrency controls Semaphores 🚦 Limit on enqueuing Make order not important

Slide 88

Slide 88 text

Sequential jobs → concurrency controls Semaphores 🚦 Limit on enqueuing Make order not important New execution type: blocked

Slide 89

Slide 89 text

Sequential jobs → concurrency controls Semaphores 🚦 Limit on enqueuing Make order not important New execution type: blocked And yes, you guessed it, more tables! 🕺

Slide 90

Slide 90 text

Now with concurrency controls

Slide 91

Slide 91 text

Now with concurrency controls

Slide 92

Slide 92 text

Many new write ops in our DB

Slide 93

Slide 93 text

Many new write ops in our DB Worst case: 11 write operations for a scheduled job with concurrency limits

Slide 94

Slide 94 text

Many new write ops in our DB Worst case: 11 write operations for a scheduled job with concurrency limits

Slide 95

Slide 95 text

Many new write ops in our DB Peak 400 jobs/second Worst case: 11 write operations for a scheduled job with concurrency limits

Slide 96

Slide 96 text

Many new write ops in our DB Peak 400 jobs/second Worst case: 11 write operations for a scheduled job with concurrency limits

Slide 97

Slide 97 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ✅ Pause queues   ⬜ Support cron-style recurring jobs   ⬜ Support running some jobs sequentially   ✅ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities

Slide 98

Slide 98 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ✅ Pause queues   ⬜ Support cron-style recurring jobs   ✅ Support running some jobs sequentially   ✅ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities

Slide 99

Slide 99 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ⬜ Support cron-style recurring jobs

Slide 100

Slide 100 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ⬜ Support cron-style recurring jobs Left as an exercise for the reader* 🤭

Slide 101

Slide 101 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ⬜ Support cron-style recurring jobs Left as an exercise for the reader* 🤭 *Hint: add some new tables 💃

Slide 102

Slide 102 text

Requirements ✅ Enqueue and run jobs ✅ Don’t keep a job claimed forever if worker dies   ✅ Don’t lose jobs when a worker is killed   ✅ Run and manage multiple workers   ✅ Support delayed jobs, a large number, enqueue them fast in batches   ✅ Pause queues   ✅ Support cron-style recurring jobs   ✅ Support running some jobs sequentially   ✅ Allow for ~650 workers, 100ms polling interval, ~19M jobs / day   ✅ Support MySQL, PostgreSQL, SQLite*   ✅ Support priorities

Slide 103

Slide 103 text

There’s so much more 🫢 Bulk operations, logging, instrumentation, error handling, hooks, Mission Control dashboard, so many bug fixes, so many edge cases…

Slide 104

Slide 104 text

There’s so much more 🫢 Bulk operations, logging, instrumentation, error handling, hooks, Mission Control dashboard, so many bug fixes, so many edge cases…

Slide 105

Slide 105 text

📥✉📆

Slide 106

Slide 106 text

~20M jobs/day 📥✉📆

Slide 107

Slide 107 text

~20M jobs/day 74 VMs 📥✉📆

Slide 108

Slide 108 text

~20M jobs/day 74 VMs 800 workers, 4 dispatchers 📥✉📆

Slide 109

Slide 109 text

~20M jobs/day 74 VMs 800 workers, 4 dispatchers 📥✉📆 Same latency as with Resque for user-facing jobs

Slide 110

Slide 110 text

~20M jobs/day 74 VMs 800 workers, 4 dispatchers 📥✉📆 Same latency as with Resque for user-facing jobs Speed increase when enqueuing in bulk

Slide 111

Slide 111 text

~20M jobs/day 74 VMs 800 workers, 4 dispatchers 📥✉📆 Same latency as with Resque for user-facing jobs Speed increase when enqueuing in bulk No impact to response time

Slide 112

Slide 112 text

Good frameworks are extracted, not invented “ DHH

Slide 113