In this post, I’ll talk about an approach to simplify things a little bit when using Sidekiq1 workers to process small updates on tables inside a database such as counters.
Another day at atos6, when investigating why our service was struggling and running under pressure, I found that one of our Sidekiq workers was causing the outage for two reasons: it was eating up the database connection pool and it ran hundreds of times per day. You’d think that this isn’t much to take down a service, but this was happening concurrently with other users, and this worker was doing one thing: updating count cache columns (3 of them), for statistics purposes and to measure customer growth, on a table that had high access in our system.
When the outage occurred, I removed small workers from our queue, which I didn’t care about that much. Some of them updated latitude and longitude, and this task could be called through a rake task that runs periodically. Another action was to increase the database connection pool and wait for the queue to flush.
After the recovery, I took some notes about why the outage occurred and took some screenshots from Scout APM2, which we’ve been using for quite some time.
If you look at the screenshot, the pattern was repeating over time, but on that day, besides the worker being called hundreds of times, other heavy jobs were running too and our service was busy (normally between Wednesday and Sunday), with users accessing reports, updating data, and so on.
To find a possible refactoring approach, or maybe decrease the priority of that worker so it wouldn’t slow down our service anymore, I collected at least 2 months of logs and processed them to analyze how many times that worker was called, and not surprisingly it was in our top 5 (called 39522 times).
With my hands full of data, I started to analyze the feature that eventually fired those workers. The service object that handled all the logic to update those counters was causing a lot of N+1 queries, because it received one ID from the worker and called:
class CountersUpdateBusiness
def initialize(customer_id)
@customer = Customer.find(customer_id)
end
# ...
private
attr_reader :customer
end
Along the code, I had calls like customer.reports_count = customer.reports.count or
customer.active_reports_count = customer.reports.active.count. For the first example, a simple
belongs_to :customer, counter_cache: :reports_count (since we’re using Rails and ActiveRecord)
should suffice, but the last one can’t be accomplished with the same approach, and I thought
about creating a SQL trigger3 to update the counter after creating or updating records scoped by
the active flag.
During the process, I refactored and simplified a lot of code. To take a peek, see the screenshot below:
Conclusion
That was a great result, because I simplified counter updates, left the Sidekiq queues to handle heavy workers asynchronously (email deliveries, bulk data changes, etc.), and avoided workers competing with each other and contributing to another outage.