This is not a blog post, just a quick win on a boring Sunday.
Recently I opened my app to more users. I've got around 1000 signups and things instantly became quite slow. I was expecting this btw, my database is purposely not optimized.
My main goal with thisdatabase app is to learn, among other things. So I wanted to hit a bottleneck before I started optimizing my datbase.
irb(main):006:0> Game::ActivityFeed.count
(136.9ms) SELECT COUNT(*) FROM `activity_feeds`
=> 336763
# Before add_index :activity_feeds, [:event_id, :event_type, :identity_id, :identity_type, :collection_id, :collection_type], unique: true, name: :idx_unique_event_identity_and_collection, if_not_exists: true
irb(main):003:0> sql = "SELECT `activity_feeds`.* FROM `activity_feeds` WHERE `activity_feeds`.`identity_type` = 'PlayStation::Identity' AND `activity_feeds`.`identity_id` = 18 AND `activity_feeds`.`collection_type` = 'PlayStation::Collection' AND `activity_feeds`.`collection_id` = 394 AND `activity_feeds`.`event_type` = 'PlayStation::Trophy' AND `activity_feeds`.`event_id` = 89487 AND `activity_feeds`.`activity_type` = 'Trophy' ORDER BY `activity_feeds`.`earned_at` DESC LIMIT 1"
irb(main):004:0> ActiveRecord::Base.connection.exec_query(sql)
SQL (17012.6ms) SELECT `activity_feeds`.* FROM `activity_feeds` WHERE `activity_feeds`.`identity_type` = 'PlayStation::Identity' AND `activity_feeds`.`identity_id` = 18 AND `activity_feeds`.`collection_type` = 'PlayStation::Collection' AND `activity_feeds`.`collection_id` = 394 AND `activity_feeds`.`event_type` = 'PlayStation::Trophy' AND `activity_feeds`.`event_id` = 89487 AND `activity_feeds`.`activity_type` = 'Trophy' ORDER BY `activity_feeds`.`earned_at` DESC LIMIT 1
# After add_index :activity_feeds, [:event_id, :event_type, :identity_id, :identity_type, :collection_id, :collection_type], unique: true, name: :idx_unique_event_identity_and_collection, if_not_exists: true
irb(main):003:0> sql = "SELECT `activity_feeds`.* FROM `activity_feeds` WHERE `activity_feeds`.`identity_type` = 'PlayStation::Identity' AND `activity_feeds`.`identity_id` = 18 AND `activity_feeds`.`collection_type` = 'PlayStation::Collection' AND `activity_feeds`.`collection_id` = 394 AND `activity_feeds`.`event_type` = 'PlayStation::Trophy' AND `activity_feeds`.`event_id` = 89487 AND `activity_feeds`.`activity_type` = 'Trophy' ORDER BY `activity_feeds`.`earned_at` DESC LIMIT 1"
irb(main):004:0> ActiveRecord::Base.connection.exec_query(sql)
SQL (1.6ms) SELECT `activity_feeds`.* FROM `activity_feeds` WHERE `activity_feeds`.`identity_type` = 'PlayStation::Identity' AND `activity_feeds`.`identity_id` = 18 AND `activity_feeds`.`collection_type` = 'PlayStation::Collection' AND `activity_feeds`.`collection_id` = 394 AND `activity_feeds`.`event_type` = 'PlayStation::Trophy' AND `activity_feeds`.`event_id` = 89487 AND `activity_feeds`.`activity_type` = 'Trophy' ORDER BY `activity_feeds`.`earned_at` DESC LIMIT 1
https://preview.redd.it/irra4enbmrp61.png?width=2648&format=png&auto=webp&s=1d3bc3d3b7bdafb57d092da35627b433d7584ee9
The reason why this became a bottleneck so fast? When creating a Game::ActivityFeed for a user, the script has to check if it's there already, to avoid duplicates mainly, but I also need to ensure the achievement or trophy is recorded in my database. Since there's no way of checking for recently earned trophies, I have to loop through all.
It kind of sucks that I have to do it in the first place, considering how many rows I have in that table, but there's no other solution at the moment. It also doesn't help that Identity, Collection and Event are all polymorphic.
activity_feed = ::Game::ActivityFeed.where(
identity: user,
collection: collection,
event: trophy,
activity_type: 'Trophy'
).first_or_initialize
So yeah, anyway. The reason I wanted to post this was that I see a lot of posts on how Rails is so slow. Rails is not slow, but your database probably is. 🙂
Have a nice rest of the weekend.
If you use performance monitoring like datadog or newrelic you’ll be able to see the breakdown on performance. if it’s spending 95% of the request or job in the database, then you have a database issue. If it’s spending 95% in ruby CPU then you have a slow ruby issue lol.
Indexes are also good for uniqueness validation.
There’s a gem called active record doctor that will automatically suggest adding or removing indexes for your app.
I’m confused. What do you mean by ‘Single Index’? I don’t quite understand the solution to this bottleneck.
If you’re using postgres I would look at the `explain (analyze,…) SELECT…` and see how the query is being ran. If you’re looking for more performance here and have lots of rows, eg: > 500k, I would look into adding an index that adds the `earned_at desc` at the end of the composite. I’ll make the query not need to do an in-memory sort and speed up the index even more.