News Feed load time is slow

Damien Kemens Posted in General Discussion 2 weeks ago

Hello,

Upon logging in or refreshing the News Feed, the load time is somewhere around 14-20 seconds and, I believe, is largely data driven. This instance is deployed to a private kubernetes cluster, but I've replicated the database locally and am getting the same results. The problem seems to be largely with these two queries that happen in succession when loading the news feed (pulled from mysqlslowlog)

Querytime: 5.919392 Locktime: 0.000041 Rowssent: 10 Rowsexamined: 1319373

SET timestamp=1715705291;
SELECT DISTINCT  o.guid, o.time_created FROM ossn_object as o JOIN ossn_entities as e0 ON e0.owner_guid=o.guid JOIN ossn_entities_metadata as emd0 ON e0.guid=emd0.guid JOIN ossn_entities as e1 ON e1.owner_guid=o.guid JOIN ossn_entities_metadata as emd1 ON e1.guid=emd1.guid WHERE(o.subtype='wall' AND o.type='user' AND (e0.type='object' AND e0.subtype='access' AND (1=1) AND e1.type='object' AND e1.subtype='poster_guid' AND (emd0.value=2 OR emd0.value=3)))  ORDER by o.guid DESC LIMIT 0, 10;

Querytime: 5.495744 Locktime: 0.000017 Rowssent: 1 Rowsexamined: 1168809

SET timestamp=1715705296;
SELECT count(DISTINCT o.guid) as total FROM ossn_object as o JOIN ossn_entities as e0 ON e0.owner_guid=o.guid JOIN ossn_entities_metadata as emd0 ON e0.guid=emd0.guid JOIN ossn_entities as e1 ON e1.owner_guid=o.guid JOIN ossn_entities_metadata as emd1 ON e1.guid=emd1.guid WHERE(o.subtype='wall' AND o.type='user' AND (e0.type='object' AND e0.subtype='access' AND (1=1) AND e1.type='object' AND e1.subtype='poster_guid' AND (emd0.value=2 OR emd0.value=3)))  ORDER by o.guid DESC;

My guess is it's the amount of data and the complexity of the query. Enabling or disabling cache makes no noticeable difference. I'm currently running 5.3 and am planning to upgrade, but that process will take longer and be much more involved. I'm curious if there's anything I can do in the current system to shorten the news feed load time or if there have been adequate changes in the later versions that will likely help/solve the issue via lazy/eager loading changes (or some other mechanism) and warrants pursuing the upgrade now.

Thanks!

Replies
us Damien Kemens Replied 2 weeks ago

Thank you so much! At least I know the endeavor will be worth while.

Indonesian Arsalan Shah Replied 2 weeks ago

You are correct old versions had this problem. In new version there is no such a issue. Many things improved in new version and even introduced the dynamic caching for data (memcached/redis) for less database queries