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!
Index ossn_entities.owne
r_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.
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.
Frosted film can transform plain glass into an eye-catching feature by adding patterns or designs. It’s a simple way to enhance windows or glass walls. Visit https://www.tintfit.com/shop/frosted to explore options that combine decorative appeal with practical benefits like privacy and light diffusion.
You can share database to my email on my profile. When i get time i can try to see.
Yeah, everything else is pretty peppy - just a few seconds load time.
Does other pages Load fast? Like terms, admins dashboard etc?
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!
Make sure all tables have indexes Can you export you database and import to some good server and see the results?
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!
Thank you so much! At least I know the endeavor will be worth while.
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.
$199 (Life Time)