News Feed load time is slow

Damien Kemens Posted in General Discussion 6 months 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

Nathan Hanson Replied 2 months ago

Index ossn_entities.owner_guid. This column is used in multiple JOIN conditions and could benefit from an index.
Consider Indexing ossn_entities_metadata.value. If emd0.value and emd1.value are frequently filtered on, indexing them might improve performance.
Review JOIN Conditions.Ensure that the JOIN conditions are correct and that there are no unnecessary joins.
Optimize Filtering. If possible, move filters that can be applied early in the query to the block blast WHERE clause.

OFN Admin Replied 2 months ago

Have you tried tuning these parameters?

key_buffer_size
max_allowed_packet
thread_stack
thread_cache_size
myisam_recover_options
max_connections
table_cache

Also, is OSSN the only application using MySQL/MariaDB? If so, definitely reduce resources allocated to InnoDB and give as much as possible to MyISAM.

If you spun up a test server, try converting all OSSN tables, structures from MyISAM to InnoDB and give InnoDB as much RAM as possible. MyISAM was much faster for reads but that advantage has all but disappeared and may find the advanced InnoDB engine more robust, especially for large databases.

Arsalan Shah Replied 3 months ago

You can share database to my email on my profile. When i get time i can try to see.

Damien Kemens Replied 3 months ago

Yeah, everything else is pretty peppy - just a few seconds load time.

Arsalan Shah Replied 3 months ago

Does other pages Load fast? Like terms, admins dashboard etc?

Damien Kemens Replied 3 months ago

I've verified all the tables have indexes. I also spun up an m5.xlarge (4 vCPU and 16GB ram) on aws and deployed the database there. No change. It takes about 20+ seconds for each page of the newsfeed to load. So, when you log in, about 20 seconds. Click to page 2 - another 20 seconds. It's pretty rough.

I've confirmed basic cache is on, dcache is using redis. I volumed the redis /data folder to make sure it was actually writing and I saw the file size about double when each page of the newsfeed is loaded - so it's definitely storing content.

I understand this is really complicated to troubleshoot - especially from afar, so I really appreciate the help.

Is it possible there's a specific, substantive modification to the database (an added index or foreign key) that might have been missed in the upgrade process that I can check for?

Thanks!

Arsalan Shah Replied 3 months ago

Make sure all tables have indexes Can you export you database and import to some good server and see the results?

Damien Kemens Replied 3 months ago

Hello!

I've completed the upgrade to 7.6 with php 8.2. I've enabled dcache via redis and, unfortunately, I haven't noticed a performance increase at all. I'm curious if there are other things I can try to increase performance on the news feed page load?

Thanks!

Damien Kemens Replied 6 months ago

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

Arsalan Shah Replied 6 months 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

Premium Version

Due to the many requests in the past for additonal features and components we have decided to develope a premium version. Features like Hashtags, Videos, Polls, Events, Stories, Link Preview, etc included in it.