search

raw1z / amistad

Adds friendships management into a rails application
http://github.com/raw1z/amistad
MIT License
185 stars 71 forks source link

Major performance issue after upgrading to 0.9.x #33

Open patroza opened 11 years ago

patroza commented 11 years ago

Running PostgreSQL, after upgrading to 0.9.x there's been major spikes on our db server, with average response times running well into the 2seconds.

Here's some of the EXPLAIN's: duration: 409.997 ms statement: SELECT 1 duration: 1268.868 ms statement: SELECT "users".* FROM "users" WHERE (("users"."id" IN (SELECT "friendships"."friend_id" FROM "friendships" WHERE (("friendships"."friendable_id" = 16190 AND "friendships"."pending" = 'f' AND "friendships"."blocker_id" IS NULL))) OR "users"."id" IN (SELECT "friendships"."friendable_id" FROM "friendships" WHERE (("friendships"."friend_id" = 16190 AND "friendships"."pending" = 'f' AND "friendships"."blocker_id" IS NULL))))) duration: 1210.892 ms statement: SELECT "users".* FROM "users" WHERE (("users"."id" IN (SELECT "friendships"."friend_id" FROM "friendships" WHERE (("friendships"."friendable_id" = 16017 AND "friendships"."pending" = 'f' AND "friendships"."blocker_id" IS NULL))) OR "users"."id" IN (SELECT "friendships"."friendable_id" FROM "friendships" WHERE (("friendships"."friend_id" = 16017 AND "friendships"."pending" = 'f' AND "friendships"."blocker_id" IS NULL))))) duration: 1053.002 ms statement: SELECT "users".* FROM "users" WHERE (("users"."id" IN (SELECT "friendships"."friend_id" FROM "friendships" WHERE (("friendships"."friendable_id" = 77299 AND "friendships"."pending" = 'f' AND "friendships"."blocker_id" IS NULL))) OR "users"."id" IN (SELECT "friendships"."friendable_id" FROM "friendships" WHERE (("friendships"."friend_id" = 77299 AND "friendships"."pending" = 'f' AND "friendships"."blocker_id" IS NULL)))))

After reverting to https://github.com/raw1z/amistad/commit/f436251bf906a1fc97628aa93a4d14cada5a7e51 all seems to be well again

I suppose some of it could be appointed to Query caches etc needing to catch up, but the issue might be terminal. Perhaps some extra indexes are required?

In any case, I had to revert because our production app was unresponsive.

raw1z commented 11 years ago

the 'friends' method has been modified in order to return an ActiveRecord::Relation instead of an Array. the above query is generated by this new method. I'm investigating on the performance issue.