Timeouts running Friendica

Support apps
,
21

Try this branch GitHub - YunoHost-Apps/friendica_ynh at cache · GitHub

I’ve added FastCGI caching to nginx

22

Thanks. I can’t see that would help as the calls that are coming in are to get updates on new posts and I’m not sure they will benefit from caching when the underlying query (which should be very very quick) is taking 15 minutes to return (that’s longer than an expected page dwell time).

However I’m going to refresh my test system and I’ll try this alongside testing some other updates. I’ve also reached out on the friendica git and now that I have a funcitoning production server on the friendica support groups on friendica.

There does seem something particular around this specfiic query because the entire system is very performant except this specific query.

The moral of this story is admitly don’t put off upgrades too long. These ones have been a bit of a jump and I wonder whether one of the intermediate upgrades did something in the meantime to clear out redudant data or something.

23

Why I proposed this:

We have a scientific journal running using ojs and we recently got indexed via crossref. Since then I noticed heavy traffic and logwatch started to report frequent timeouts especially at cron run for updating metadata on crossref.

I have searched and got some leads :

  • Enabling slow sql queries log > so I tweaked mariadb config (I’ll report settings I changed when I can)

  • This helped a little but still got timeouts so I searched in the app forum and someone reported good performance after adding caching to FastCGI in nginx so I tested it and timeouts vanished.

fastcgi_buffers 16 16k;
fastcgi_buffer_size 32k;
fastcgi_busy_buffers_size 64k;
1 Like
24

Yeah this is definately not a load issue. (A single page request basically knocks out the server) and its definately at the query/mysql server level for a specific friendica query but do appreciate the imput.

25

Consider comparing output of mysqldump -d friendica with official database seed script, perhaps there’s a missing index or something?

26

So yeah something looks quite different because it looks like there is still a history of or perhaps an incomplete transition in the structure of that table (which I guess makes sense give that my instance has upgraded from 2024 versions through to 2026.05

Inside my instances database are the following two mentions of that view - What I am not sure about is whether this the case - or really how to read this in comparison to the structure on git.

–
– Temporary table structure for view post-user-view

DROP TABLE IF EXISTS post-user-view;
/!50001 DROP VIEW IF EXISTS post-user-view/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8mb4;
/*!50001 CREATE VIEW post-user-view AS SELECT
1 AS id,
1 AS post-user-id,
1 AS uid,
1 AS parent,
1 AS uri,
1 AS uri-id,
1 AS parent-uri,
1 AS parent-uri-id,
1 AS thr-parent,
1 AS thr-parent-id,
1 AS conversation,
1 AS conversation-id,
1 AS context,
1 AS context-id,
1 AS quote-uri,
1 AS quote-uri-id,
1 AS guid,
1 AS wall,
1 AS gravity,
1 AS extid,
1 AS external-id,
1 AS replies,
1 AS replies-id,
1 AS created,
1 AS edited,
1 AS commented,
1 AS received,
1 AS changed,
1 AS post-type,
1 AS post-reason,
1 AS private,
1 AS pubmail,
1 AS visible,
1 AS starred,
1 AS unseen,
1 AS deleted,
1 AS origin,
1 AS parent-origin,
1 AS mention,
1 AS global,
1 AS featured,
1 AS network,
1 AS protocol,
1 AS vid,
1 AS psid,
1 AS verb,
1 AS title,
1 AS content-warning,
1 AS raw-body,
1 AS body,
1 AS rendered-hash,
1 AS rendered-html,
1 AS language,
1 AS plink,
1 AS location,
1 AS coord,
1 AS sensitive,
1 AS restrictions,
1 AS app,
1 AS object-type,
1 AS object,
1 AS target-type,
1 AS target,
1 AS resource-id,
1 AS contact-id,
1 AS contact-uri-id,
1 AS contact-link,
1 AS contact-addr,
1 AS contact-name,
1 AS contact-nick,
1 AS contact-avatar,
1 AS contact-network,
1 AS contact-blocked,
1 AS contact-hidden,
1 AS contact-readonly,
1 AS contact-archive,
1 AS contact-pending,
1 AS contact-rel,
1 AS contact-uid,
1 AS contact-contact-type,
1 AS writable,
1 AS self,
1 AS cid,
1 AS alias,
1 AS photo,
1 AS name-date,
1 AS uri-date,
1 AS avatar-date,
1 AS thumb,
1 AS author-id,
1 AS author-uri-id,
1 AS author-link,
1 AS author-addr,
1 AS author-name,
1 AS author-nick,
1 AS author-alias,
1 AS author-avatar,
1 AS author-network,
1 AS author-blocked,
1 AS author-hidden,
1 AS author-updated,
1 AS author-contact-type,
1 AS author-gsid,
1 AS author-baseurl,
1 AS owner-id,
1 AS owner-uri-id,
1 AS owner-link,
1 AS owner-addr,
1 AS owner-name,
1 AS owner-nick,
1 AS owner-alias,
1 AS owner-avatar,
1 AS owner-network,
1 AS owner-blocked,
1 AS owner-hidden,
1 AS owner-updated,
1 AS owner-gsid,
1 AS owner-contact-type,
1 AS causer-id,
1 AS causer-uri-id,
1 AS causer-link,
1 AS causer-addr,
1 AS causer-name,
1 AS causer-nick,
1 AS causer-alias,
1 AS causer-avatar,
1 AS causer-network,
1 AS causer-blocked,
1 AS causer-hidden,
1 AS causer-gsid,
1 AS causer-contact-type,
1 AS postopts,
1 AS inform,
1 AS delivery_queue_count,
1 AS delivery_queue_done,
1 AS delivery_queue_failed,
1 AS allow_cid,
1 AS allow_gid,
1 AS deny_cid,
1 AS deny_gid,
1 AS event-id,
1 AS event-created,
1 AS event-edited,
1 AS event-start,
1 AS event-finish,
1 AS event-summary,
1 AS event-desc,
1 AS event-location,
1 AS event-type,
1 AS event-nofinish,
1 AS event-ignore,
1 AS question-id,
1 AS question-multiple,
1 AS question-voters,
1 AS question-end-time,
1 AS has-categories,
1 AS has-media,
1 AS signed_text,
1 AS parent-guid,
1 AS parent-network,
1 AS parent-owner-id,
1 AS parent-author-id,
1 AS parent-author-link,
1 AS parent-author-name,
1 AS parent-author-nick,
1 AS parent-author-network */;
SET character_set_client = @saved_cs_client;

– Final view structure for view post-user-view

/!50001 DROP VIEW IF EXISTS post-user-view/;
/*!50001 SET @saved_cs_client          = @@character_set_client /;
/!50001 SET @saved_cs_results         = @@character_set_results /;
/!50001 SET @saved_col_connection     = @@collation_connection /;
/!50001 SET character_set_client      = utf8mb4 /;
/!50001 SET character_set_results     = utf8mb4 /;
/!50001 SET collation_connection      = utf8mb4_general_ci /;
/!50001 CREATE ALGORITHM=UNDEFINED /
/!50013 DEFINER=friendica@localhost SQL SECURITY DEFINER /
/!50001 VIEW post-user-view AS select post-user.id AS id,post-user.id AS post-user-id,post-user.uid AS uid,post-thread-user.post-user-id AS parent,item-uri.uri AS uri,post-user.uri-id AS uri-id,parent-item-uri.uri AS parent-uri,post-user.parent-uri-id AS parent-uri-id,thr-parent-item-uri.uri AS thr-parent,post-user.thr-parent-id AS thr-parent-id,conversation-item-uri.uri AS conversation,post-thread-user.conversation-id AS conversation-id,context-item-uri.uri AS context,post-thread-user.context-id AS context-id,quote-item-uri.uri AS quote-uri,post-content.quote-uri-id AS quote-uri-id,item-uri.guid AS guid,post-user.wall AS wall,post-user.gravity AS gravity,external-item-uri.uri AS extid,post-user.external-id AS external-id,replies-item-uri.uri AS replies,post-user.replies-id AS replies-id,post-user.created AS created,post-user.edited AS edited,post-thread-user.commented AS commented,post-user.received AS received,post-thread-user.changed AS changed,post-user.post-type AS post-type,post-user.post-reason AS post-reason,post-user.private AS private,post-thread-user.pubmail AS pubmail,post-user.visible AS visible,post-thread-user.starred AS starred,post-user.unseen AS unseen,post-user.deleted AS deleted,post-user.origin AS origin,post-thread-user.origin AS parent-origin,post-thread-user.mention AS mention,post-user.global AS global,exists(select post-collection.type from post-collection where post-collection.type = 0 and post-collection.uri-id = post-user.uri-id limit 1) AS featured,post-user.network AS network,post-user.protocol AS protocol,post-user.vid AS vid,post-user.psid AS psid,if(post-user.vid is null,‘’,verb.name) AS verb,post-content.title AS title,post-content.content-warning AS content-warning,post-content.raw-body AS raw-body,ifnull(post-content.body,‘’) AS body,post-content.rendered-hash AS rendered-hash,post-content.rendered-html AS rendered-html,post-content.language AS language,post-content.plink AS plink,post-content.location AS location,post-content.coord AS coord,post-content.sensitive AS sensitive,post-user.restrictions AS restrictions,post-content.app AS app,post-content.object-type AS object-type,post-content.object AS object,post-content.target-type AS target-type,post-content.target AS target,post-content.resource-id AS resource-id,post-user.contact-id AS contact-id,contact.uri-id AS contact-uri-id,contact.url AS contact-link,contact.addr AS contact-addr,contact.name AS contact-name,contact.nick AS contact-nick,contact.thumb AS contact-avatar,contact.network AS contact-network,contact.blocked AS contact-blocked,contact.hidden AS contact-hidden,contact.readonly AS contact-readonly,contact.archive AS contact-archive,contact.pending AS contact-pending,contact.rel AS contact-rel,contact.uid AS contact-uid,contact.contact-type AS contact-contact-type,if(post-user.network in (‘apub’,‘dfrn’,‘dspr’,‘stat’),1,contact.writable) AS writable,contact.self AS self,contact.id AS cid,contact.alias AS alias,contact.photo AS photo,contact.name-date AS name-date,contact.uri-date AS uri-date,contact.avatar-date AS avatar-date,contact.thumb AS thumb,post-user.author-id AS author-id,author.uri-id AS author-uri-id,author.url AS author-link,author.addr AS author-addr,if(contact.url = author.url and contact.name <> ‘’,contact.name,author.name) AS author-name,author.nick AS author-nick,author.alias AS author-alias,if(contact.url = author.url and contact.thumb <> ‘’,contact.thumb,author.thumb) AS author-avatar,author.network AS author-network,author.blocked AS author-blocked,author.hidden AS author-hidden,author.updated AS author-updated,author.contact-type AS author-contact-type,author.gsid AS author-gsid,author.baseurl AS author-baseurl,post-user.owner-id AS owner-id,owner.uri-id AS owner-uri-id,owner.url AS owner-link,owner.addr AS owner-addr,if(contact.url = owner.url and contact.name <> ‘’,contact.name,owner.name) AS owner-name,owner.nick AS owner-nick,owner.alias AS owner-alias,if(contact.url = owner.url and contact.thumb <> ‘’,contact.thumb,owner.thumb) AS owner-avatar,owner.network AS owner-network,owner.blocked AS owner-blocked,owner.hidden AS owner-hidden,owner.updated AS owner-updated,owner.gsid AS owner-gsid,owner.contact-type AS owner-contact-type,post-user.causer-id AS causer-id,causer.uri-id AS causer-uri-id,causer.url AS causer-link,causer.addr AS causer-addr,causer.name AS causer-name,causer.nick AS causer-nick,causer.alias AS causer-alias,causer.thumb AS causer-avatar,causer.network AS causer-network,causer.blocked AS causer-blocked,causer.hidden AS causer-hidden,causer.gsid AS causer-gsid,causer.contact-type AS causer-contact-type,post-delivery-data.postopts AS postopts,post-delivery-data.inform AS inform,post-delivery-data.queue_count AS delivery_queue_count,post-delivery-data.queue_done AS delivery_queue_done,post-delivery-data.queue_failed AS delivery_queue_failed,if(post-user.psid is null,‘’,permissionset.allow_cid) AS allow_cid,if(post-user.psid is null,‘’,permissionset.allow_gid) AS allow_gid,if(post-user.psid is null,‘’,permissionset.deny_cid) AS deny_cid,if(post-user.psid is null,‘’,permissionset.deny_gid) AS deny_gid,post-user.event-id AS event-id,event.created AS event-created,event.edited AS event-edited,event.start AS event-start,event.finish AS event-finish,event.summary AS event-summary,event.desc AS event-desc,event.location AS event-location,event.type AS event-type,event.nofinish AS event-nofinish,event.ignore AS event-ignore,post-question.id AS question-id,post-question.multiple AS question-multiple,post-question.voters AS question-voters,post-question.end-time AS question-end-time,exists(select post-category.uri-id from post-category where post-category.uri-id = post-user.uri-id and post-category.uid = post-user.uid limit 1) AS has-categories,exists(select post-media.id from post-media where post-media.uri-id = post-user.uri-id limit 1) AS has-media,diaspora-interaction.interaction AS signed_text,parent-item-uri.guid AS parent-guid,post-thread-user.network AS parent-network,post-thread-user.owner-id AS parent-owner-id,post-thread-user.author-id AS parent-author-id,parent-post-author.url AS parent-author-link,parent-post-author.name AS parent-author-name,parent-post-author.nick AS parent-author-nick,parent-post-author.network AS parent-author-network from (((((((((((((((((((((post-user join post-thread-user on(post-thread-user.uri-id = post-user.parent-uri-id and post-thread-user.uid = post-user.uid)) straight_join contact on(contact.id = post-user.contact-id)) straight_join contact author on(author.id = post-user.author-id)) straight_join contact owner on(owner.id = post-user.owner-id)) left join contact causer on(causer.id = post-user.causer-id)) left join item-uri on(item-uri.id = post-user.uri-id)) left join item-uri thr-parent-item-uri on(thr-parent-item-uri.id = post-user.thr-parent-id)) left join item-uri parent-item-uri on(parent-item-uri.id = post-user.parent-uri-id)) left join item-uri conversation-item-uri on(conversation-item-uri.id = post-thread-user.conversation-id)) left join item-uri context-item-uri on(context-item-uri.id = post-thread-user.context-id)) left join item-uri external-item-uri on(external-item-uri.id = post-user.external-id)) left join item-uri replies-item-uri on(replies-item-uri.id = post-user.replies-id)) left join verb on(verb.id = post-user.vid)) left join event on(event.id = post-user.event-id)) left join diaspora-interaction on(diaspora-interaction.uri-id = post-user.uri-id)) left join post-content on(post-content.uri-id = post-user.uri-id)) left join item-uri quote-item-uri on(quote-item-uri.id = post-content.quote-uri-id)) left join post-delivery-data on(post-delivery-data.uri-id = post-user.uri-id and post-user.origin <> 0)) left join post-question on(post-question.uri-id = post-user.uri-id)) left join permissionset on(permissionset.id = post-user.psid)) left join contact parent-post-author on(parent-post-author.id = post-thread-user.author-id)) /;
/!50001 SET character_set_client      = @saved_cs_client /;
/!50001 SET character_set_results     = @saved_cs_results /;
/!50001 SET collation_connection      = @saved_col_connection */;
27

This part seems to be missing from final section on your end:

INNER JOIN `post-thread` ON `post-thread`.`uri-id` = `post-user`.`parent-uri-id`

Can you try running this query (as in omit the CREATE VIEW xxx AS SELECT, instead running the SELECT part) with appropriate parameters?

If you prepend SELECT with EXPLAIN (so, EXPLAIN SELECT .....) and see if execution plan differs significantly between with and without that INNER JOIN? Probably can be done with phpMyAdmin

28

I am not using friendica but searching lead me to :

29

YunoHost already runs the migration: friendica_ynh/scripts/upgrade at cc9006ed390b056cfe84469b0aa475eb39732c64 · YunoHost-Apps/friendica_ynh · GitHub

30

My bad. I rapidly overlooked the script and I missed it :sweat_smile:

31

Hey .. no worries. thanks anyway. All input is appreciated. People have been so helpful.

I’m still looking the difference between those two queries. I need to get my test system back up runnnig to do that. The other links are useful stuff to learn.

If any of you are runnnig Friendica and would like to connect on ActivityPub I’m abeorch@friendica.ginestes.es - would be great to have a few more fellow instance admins to chat to on there.

32

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.