Timeouts running Friendica

Support apps
,
1

What app is this about, and its version: Friendica 2026.01~ynh3
What YunoHost version are you running: 12.1.40.1
What type of hardware are you using: VPS bought online

Describe your issue

I am quite a newbie when it comes to admining a friendica instance.
I recently updated to Friendica, version 2026.01. The database version is 1586/1586, the post update version is 1550/1550.

I’m having some peformance issues with my friendica instance - specificically [pool friendica] server reached max_children setting (12)

After a few minutes of use I’m getting amost constant 504 gateway timeouts from Friendica now and errors in the php8.4 logs referring to [pool friendica] server reached max_children setting

I’ve increase memory limit in /etc/php/8.4/fpm/php.ini to 512M - as in earlier versons this seemed to fix performance issues but that hasn’t resolved things. I also increased the max_children setting to 24 in /etc/php/8.4/fpm/pool.d/friendica.conf but this has not improved things. I suspect that this is not the underling cause as I am also seeing errors such as :

[24-May-2026 08:55:16] NOTICE: [pool friendica] child 184612 exited with code 0 after 896.680478 seconds from start
[24-May-2026 08:55:16] NOTICE: [pool friendica] child 186294 started
[24-May-2026 08:55:17] NOTICE: [pool friendica] child 184606 exited with code 0 after 898.334926 seconds from start

The errors seem to be triggered when users view pages on friendica server - (its only used by one or two people)

So my current thought is that for some reason I am getting child processes that are doing database queries maybe that are timing out? I’m not sure how to diagnose that.

I’m also seeing “[Warning] Aborted connection 182612 to db: ‘friendica’ user: ‘friendica’ host: ‘localhost’ (Got an error reading communication packets)” in the mysql logs

Share relevant logs or error messages

2

Any trace in the friendica logs?

3

A tail of the friendica log doesn’t point to anything that I’m immediately seeing as useful. I can see that before I increased the memory allocation then a couple of requests had run out of memory.

I saw plenty of failed writes a few days ago - but not much today or even in the last few days.

2026-05-22T08:41:59Z app [ERROR]: DB Error {“code”:2006,“error”:“MySQL server has gone away”,“params”:“UPDATE session SET data = 'uid|i:2;theme|s:4:“frio”;mobile-theme|N;authenticated|i:1;page_flags|i :0;my_url|s:45:“https://friendica.ginestes.es/profile/abeorch\";my_address|s:29:\“abeorch@friendica.ginestes.es\”;addr|s:13:\“95.19.176.233\”;nickname|s:7:\"abeorch\”;remote|a:11:{i:3;i:87;i:7;i:54572;i:8; i:54570;i:9;i:55990;i:10;i:73303;i:12;i:116274;i:13;i:116276;i:14;i:124266;i:15;i:124261;i:17;i:124264;i:18;i:124268;}new_member|b:0;cid|i:2;network-tab|s:8:“received”;return_path|s:7:“network”;language |s:2:“en”;network-request|a:10:{s:8:“pagename”;s:14:”/ping_network/“;s:1:“f”;s:0:”“;s:1:“p”;s:1:“2”;s:5:“force”;s:1:“0”;s:4:“item”;s:1:“0”;s:14:“first_received”;s:19:“2026-05-22 08: 25:58”;s:13:“first_created”;s:19:“2026-05-22 08:25:22”;s:15:“first_commented”;s:19:“2026-05-22 08:25:59”;s:11:“first_uriid”;s:8:“10734524”;s:1:”“;s:13:“1779438244216”;}post-random|s:12:“5 99083295429”;', expire = 1779619319 WHERE (sid = ‘707kmqkeepjcgc873644sc7nkm’ AND (data != 'uid|i:2;theme|s:4:“frio”;mobile-theme|N;authenticated|i:1;page_flags|i:0;my_url|s:45:“https://friendica. ginestes.es/profile/abeorch”;my_address|s:29:“abeorch@friendica.ginestes.es”;addr|s:13:“95.19.176.233”;nickname|s:7:“abeorch”;remote|a:11:{i:3;i:87;i:7;i:54572;i:8;i:54570;i:9;i:55990;i:10;i:73303;i: 12;i:116274;i:13;i:116276;i:14;i:124266;i:15;i:124261;i:17;i:124264;i:18;i:124268;}new_member|b:0;cid|i:2;network-tab|s:8:“received”;return_path|s:7:“network”;language|s:2:“en”;network-request|a:10:{s :8:“pagename”;s:14:”/ping_network/“;s:1:“f”;s:0:”“;s:1:“p”;s:1:“2”;s:5:“force”;s:1:“0”;s:4:“item”;s:1:“0”;s:14:“first_received”;s:19:“2026-05-22 08:25:58”;s:13:“first_created”;s:19 :“2026-05-22 08:25:22”;s:15:“first_commented”;s:19:“2026-05-22 08:25:59”;s:11:“first_uriid”;s:8:“10734524”;s:1:”“;s:13:“1779438244216”;}post-random|s:12:“599083295429”;’ OR expire != 1779 619319))“} - {“file”:“Database.php”,“line”:796,“function”:“e”,“request-id”:“6a10167e08f7e”,“stack”:“Database::e (1375), Database::update (112), Database::write, session_write_close”,“uid”:“c113b5”,“process_ id”:178413}
2026-05-22T08:41:59Z app [ERROR]: DB Error {“code”:2006,“error”:“MySQL server has gone away”,“params”:“UPDATE session SET data = 'uid|i:2;theme|s:4:“frio”;mobile-theme|N;authenticated|i:1;page_flags|i :0;my_url|s:45:“https://friendica.ginestes.es/profile/abeorch\";my_address|s:29:\“abeorch@friendica.ginestes.es\”;addr|s:13:\“95.19.176.233\”;nickname|s:7:\"abeorch\”;remote|a:11:{i:3;i:87;i:7;i:54572;i:8; i:54570;i:9;i:55990;i:10;i:73303;i:12;i:116274;i:13;i:116276;i:14;i:124266;i:15;i:124261;i:17;i:124264;i:18;i:124268;}new_member|b:0;cid|i:2;network-tab|s:8:“received”;return_path|s:7:“network”;language |s:2:“en”;network-request|a:10:{s:8:“pagename”;s:14:”/ping_network/”;s:1:“f”;s:0:“”;s:1:“p”;s:1:“2”;s:5:“force”;s:1:“0”;s:4:“item”;s:1:“0”;s:14:“first_received”;s:19:“2026-05-22 08: 25:58”;s:13:“first_created”;s:19:“2026-05-22 08:25:22”;s:15:“first_commented”;s:19:“2026-05-22 08:25:59”;s:11:“first_uriid”;s:8:“10734524”;s:1:““;s:13:“1779438244216”;}post-random|s:12:“5 99083295429”;', expire = 1779619319 WHERE (sid = ‘707kmqkeepjcgc873644sc7nkm’ AND (data != 'uid|i:2;theme|s:4:“frio”;mobile-theme|N;authenticated|i:1;page_flags|i:0;my_url|s:45:“https://friendica. ginestes.es/profile/abeorch”;my_address|s:29:“abeorch@friendica.ginestes.es”;addr|s:13:“95.19.176.233”;nickname|s:7:“abeorch”;remote|a:11:{i:3;i:87;i:7;i:54572;i:8;i:54570;i:9;i:55990;i:10;i:73303;i: 12;i:116274;i:13;i:116276;i:14;i:124266;i:15;i:124261;i:17;i:124264;i:18;i:124268;}new_member|b:0;cid|i:2;network-tab|s:8:“received”;return_path|s:7:“network”;language|s:2:“en”;network-request|a:10:{s :8:“pagename”;s:14:”/ping_network/“;s:1:“f”;s:0:”“;s:1:“p”;s:1:“2”;s:5:“force”;s:1:“0”;s:4:“item”;s:1:“0”;s:14:“first_received”;s:19:“2026-05-22 08:25:58”;s:13:“first_created”;s:19 :“2026-05-22 08:25:22”;s:15:“first_commented”;s:19:“2026-05-22 08:25:59”;s:11:“first_uriid”;s:8:“10734524”;s:1:””;s:13:“1779438244216”;}post-random|s:12:“599083295429”;’ OR expire != 1779 619319))”} - {“file”:“Database.php”,“line”:796,“function”:“e”,“request-id”:“6a1016a9d92ec”,“stack”:“Database::e (1375), Database::update (112), Database::write, session_write_close”,“uid”:“093444”,“process_ id”:179309}
2026-05-22T08:41:59Z daemon [ERROR]: DB Error {“code”:2006,“error”:“MySQL server has gone away”,“params”:“SELECT jobs FROM worker-ipc WHERE (key = 0) LIMIT 1”} - {“file”:“Database.php”,“line”:689,“fun ction”:“p”,“request-id”:“6a101663a94d4”,“stack”:“Database::stuck_out_tongue: (1508), Database::select (1393), Database::selectFirst (434), DBA::selectFirst (65), IPC::JobsExists (246)”,“uid”:“12beb7”,“process_id”:157834}
2026-05-22T08:41:59Z daemon [ERROR]: Uncaught Exception mysqli_sql_exception: “No such file or directory” at /var/www/friendica/src/Database/Database.php line 201 {“exception”:“mysqli_sql_exception: No such file or directory in /var/www/friendica/src/Database/Database.php:201\nStack trace:\n#0 /var/www/friendica/src/Database/Database.php(201): mysqli->__construct()\n#1 /var/www/friendica/src/Database/Database .php(264): Friendica\Database\Database->connect()\n#2 /var/www/friendica/src/Database/Database.php(697): Friendica\Database\Database->reconnect()\n#3 /var/www/friendica/src/Database/Database.php(1508): Friendica\Database\Database->p()\n#4 /var/www/friendica/src/Database/Database.php(1393): Friendica\Database\Database->select()\n#5 /var/www/friendica/src/Database/DBA.php(434): Friendica\Database\Data base->selectFirst()\n#6 /var/www/friendica/src/Core/Worker/IPC.php(65): Friendica\Database\DBA::selectFirst()\n#7 /var/www/friendica/bin/daemon.php(246): Friendica\Core\Worker\IPC::JobsExists()\n#8 {ma in}”} - {“file”:null,“line”:null,“function”:null,“request-id”:“6a101663a94d4”,“stack”:“ErrorHandler::handleException (128), ErrorHandler::Friendica\Core\Logger\Handler\{closure}”,“uid”:“12beb7”,“process _id”:157834}
2026-05-23T01:09:06Z app [ERROR]: DB Error {“code”:1205,“error”:“Lock wait timeout exceeded; try restarting transaction”,“params”:“REPLACE apcontact (url, uri-id, type, following, followers, in box, outbox, sharedinbox, featured, featured-tags, manually-approve, discoverable, suspended, posting-restricted, nick, name, about, photo, header, addr, alias, pubkey, sub scribe, baseurl, gsid, following_count, followers_count, statuses_count, updated) VALUES (‘https://mas.to/users/SarcastiCarrie’, 10742037, ‘Person’, ‘https://mas.to/users/SarcastiCarrie/followi ng’, ‘https://mas.to/users/SarcastiCarrie/followers’, ‘https://mas.to/users/SarcastiCarrie/inbox’, ‘https://mas.to/users/SarcastiCarrie/outbox’, ‘https://mas.to/inbox’, ‘ SarcastiCarrie@mas.to (@SarcastiCarrie@mas.to) - mas.to collections/featured’, ‘https://mas.to/users/SarcastiCarrie/collections/tags’, 1, 0, 0, 0, ‘SarcastiCarrie’, ‘SarcastiCarrie@mas.to’, ‘And the villagers rejoiced.’, ‘https://media.mas.to/accounts/avatars/10 9/248/580/203/773/802/original/574dd250df8c8694.jpg’, ‘https://media.mas.to/accounts/headers/109/248/580/203/773/802/original/87591e04f230780b.jpg’, ‘SarcastiCarrie@mas.to’, ‘https://mas.to/@SarcastiCarrie’ , ‘-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvdm6aigyWDGjF5akQcb/\nKhp/6HoSFWvVIrLhshsQ5EXad6Nxj1hT+WM04HeoUo9MXEKZKz5egwpJV2mjuKRd\nKfkj3Z2gw5gq8o9wxbC8pL5etXtf5Z/BllK6/2jK uzjwPetoe7oT19YQgXO+0LvX\nQvl0F/D7MV9NkLpUFlObwwKyifzjwziySKIxRPeyp+8vA6eHolM3TinKb1pKxM23\nh3DhltpNh9scnGjp8bhs08cZ0+JUhbI7fil5H6MoyaXFNvBSpG8Xeu5uvG851rK1\nKQzXlZX0mQATTkFTkzNZWpmHdjdBrKlMkKsrXROUxT+fj 7iWeh7Y5Ge1B+Raz1Or\n3wIDAQAB\n-----END PUBLIC KEY-----’, ‘Log in - mas.to, ‘https://mas.to’, 209, 105, 55, 4822, ‘2026-05-23 01:08:16’)”} - {“file”:“Database.php”,“line”:7 86,“function”:“e”,“request-id”:“6a10fe006aeac”,“stack”:“DBA::replace (152), APContact::compactProfile (502), APContact::getByURL (210), Receiver::processInbox (95), Inbox::post (94), BaseModule::run (231), BaseApi::run (96), Inbox::run (30), App::runFrontend (595), App::processRequest (190)”,“uid”:“e93be5”,“process_id”:64395}
2026-05-23T01:13:31Z worker [ERROR]: DB Error {“code”:1452,“error”:“Cannot add or update a child row: a foreign key constraint fails (friendica.contact, CONSTRAINT contact_ibfk_2 FOREIGN KEY (uri-id ) REFERENCES item-uri (id) ON DELETE CASCADE)”,“params”:“INSERT INTO contact (uid, created, updated, network, name, nick, location, xmpp, matrix, avatar, url, nurl, uri-id, addr, alias, batch, last-update, success_update, failure_update, term-date, last-item, last-discovery, readonly, contact-type, archive, unsearchable, sensitive, baseurl, bd, photo, thumb, micro, name-date, uri-date, avatar-date, writable, forum, prv, bdyear) VALUES (0, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, 0, ‘’, ‘’ , ‘’, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, 0, 0, 0, 0, 0, ‘’, ‘0001-01-01’, ‘’, ‘’, ‘’, ‘0001-01-01 00:00 :00’, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, 0, 0, 0, ‘’)”,“worker_id”:“b6d2219”,“worker_cmd”:null} - {“file”:“Database.php”,“line”:786,“function”:“e”,“request-id”:“6a10febfb947a”,“stack”:“DBA::inser t (171), Contact::insert (223), Contact::createPublicContactFromUserContact (197), Contact::selectAccountUserById (169), Worker::execFunction (570), Worker::execute (378), Worker::processQueue (112), Worker ::doExecute (91), Console::execute (86), Console::doExecute (177), Console::execute (86), App::processConsole (233)”,“uid”:“3a2584”,“process_id”:74122}
2026-05-24T01:13:18Z worker [ERROR]: DB Error {“code”:1452,“error”:“Cannot add or update a child row: a foreign key constraint fails (friendica.contact, CONSTRAINT contact_ibfk_2 FOREIGN KEY (uri-id ) REFERENCES item-uri (id) ON DELETE CASCADE)”,“params”:“INSERT INTO contact (uid, created, updated, network, name, nick, location, xmpp, matrix, avatar, url, nurl, uri-id, addr, alias, batch, last-update, success_update, failure_update, term-date, last-item, last-discovery, readonly, contact-type, archive, unsearchable, sensitive, baseurl, bd, photo, thumb, micro, name-date, uri-date, avatar-date, writable, forum, prv, bdyear) VALUES (0, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, 0, ‘’, ‘’ , ‘’, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, 0, 0, 0, 0, 0, ‘’, ‘0001-01-01’, ‘’, ‘’, ‘’, ‘0001-01-01 00:00 :00’, ‘0001-01-01 00:00:00’, ‘0001-01-01 00:00:00’, 0, 0, 0, ‘’)”,“worker_id”:“7550a86”,“worker_cmd”:null} - {“file”:“Database.php”,“line”:786,“function”:“e”,“request-id”:“6a12503b673be”,“stack”:“DBA::inser t (171), Contact::insert (223), Contact::createPublicContactFromUserContact (197), Contact::selectAccountUserById (169), Worker::execFunction (570), Worker::execute (378), Worker::processQueue (112), Worker ::doExecute (91), Console::execute (86), Console::doExecute (177), Console::execute (86), App::processConsole (233)”,“uid”:“93d660”,“process_id”:125972}
2026-05-24T08:46:38Z app [ALERT]: Fatal Error (E_ERROR): Allowed memory size of 134217728 bytes exhausted (tried to allocate 8192 bytes) {“code”:1,“message”:“Allowed memory size of 134217728 bytes exhausted (tried to allocate 8192 bytes)”,“file”:“/var/www/friendica/src/Database/Database.php”,“line”:1644,“trace”:null} - {“file”:null,“line”:null,“function”:null,“request-id”:“6a12baeaa5e35”,“stack”:“”,“uid”:“19f 040”,“process_id”:184606}
2026-05-24T09:01:26Z app [ALERT]: Fatal Error (E_ERROR): Allowed memory size of 134217728 bytes exhausted (tried to allocate 8192 bytes) {“code”:1,“message”:“Allowed memory size of 134217728 bytes exhausted (tried to allocate 8192 bytes)”,“file”:“/var/www/friendica/src/Database/Database.php”,“line”:1644,“trace”:null} - {“file”:null,“line”:null,“function”:null,“request-id”:“6a12be63280df”,“stack”:“”,“uid”:“314 a2f”,“process_id”:186301}
2026-05-24T10:34:38Z app [ERROR]: No tag id created {“name”:“users/smartsammler/followers”,“url”:“https://chaos.social/users/smartsammler/followers","type”:3} - {“file”:“Tag.php”,“line”:241,“function”:“getI D”,“request-id”:“6a12d3ecedf25”,“stack”:“Tag::getID (241), Tag::store (132), Processor::storeReceivers (1468), Processor::processContent (1030), Processor::createItem (555), Receiver::routeActivities (808), Receiver::processActivity (773), Processor::processActivity (1912), Processor::fetchMissingActivity (1813), Receiver::routeActivities (845), Receiver::processActivity (773), Receiver::processInbox (158), I nbox::post (94), BaseModule::run (231), BaseApi::run (96)”,“uid”:“1cbe43”,“process_id”:196265}

4

The other interesting thing is that it is taking ages for php8.4-fpm service page to display when loaded via the Yunohost Admin interface.

5

Did you increase memory_limit in this file?

6

To make the value persistent acroos future upgrades :

sudo yunohost app setting friendica php_memory_limit -v 512M
sudo yunohost app ssowatconf
7

Yeah this didn’t seem to resolve the situation. (though it did seem to rid of those few errors) - I’m still getting the specificically [pool friendica] server reached max_children setting errors fairly quickly after restarting php8.4

8

I’ve also done an upgrade to the latest version of Friendica on my test instance and that doesn’t seem to solve the issue. As soon as I load a page or two that does a load of DB calls it looks like it starts returning 504 Bad gateway

9

I tried just restarting the database service. - This immediately caused friendica to resume working and respond very quickly but then within minutes it was unresponsive.

10

Looking a bit more. I can see that using top. mariaDB is constantly using between 35-55% of CPU and looking into mysql I can see about ten friendica queries (all starting “SELECT contact.id, contact.name, COUNT(*) AS count FROM post-user-view INNER JOIN `cont” ) running somew with times of 600 so it looks like the DB is being absolutely hammered.

I noticed that my test instance had returned to responsiveness and had sleeping processes but as soon as I loaded a single friendica page they were off again doing those same queries.

11

I’ve managed to view the entire query and run an example of it - which at the moment took 12 minutes to return - Its defined in the countunseenitems() function in the following code

I can’t imagine that this query should take 12 minutes to run (since it looks like it returns new unseen items for each circle on the LHS when viewing a page of posts. (and would be called repeatly )

So I am guessing that there is something wrong with the database engine that is causing this query to take so long or perhaps something wrong with the view/table or indexes - My thought is that this would be the second since other apps are running queries happily - so its something limited to friendica.

12

You can enable slow query log for mariadb

Create log folder in /var/log/mysql

Give it the correct ownership (mysql:mysql)

Edit /etc/mysql/mariadb.conf.d/50-server.cnf

Uncomment the lines beginning with log_slow

Restart mariadb

Then check the log created after you reproduce the issue

1 Like
13

Thanks I’ve set that up (I also reduced some of the criteria to make sure those queries are logged as they only return about 5 rows of data ) … I’m just waiting to get some results.

Looking at the table its querying - just a basic count(*) is taking about ten minutes and returning a count of 2,066,666 records - Should that be an issue?

14

I’ve set that up. Would that tell me why those queries are so processor heavy?

I’m just waiting to get something written to the log but in the meantime I did an EXPLAIN on the query
MariaDB [friendica]> EXPLAIN SELECT contact.id, contact.name, COUNT(*) AS count FROM post-user-view INNER JOIN contact ON post-user-view.contact-id = contact.id WHERE post-user-view.uid = 2 AND post-user-view.visible AND NOT post-user-view.deleted AND post-user-view.unseen AND contact.network IN (‘dfrn’, ‘apub’) AND contact.contact-type = 3 AND NOT contact.blocked AND NOT contact.hidden AND NOT contact.pending AND NOT contact.archive AND contact.uid = 2 GROUP BY contact.id;
The results are

±-----±-------------------±----------------±------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-----------------------------------------±--------±---------------------------------------------------±-----±--------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±-----±-------------------±----------------±------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-----------------------------------------±--------±---------------------------------------------------±-----±--------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | contact | index_merge | PRIMARY,uid_name,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,network_uid_lastupdate,uid_network_self_lastupdate,uid_lastitem,uid_contact-type,uid_self_contact-type,uid_created | uid_contact-type,pending_uid,blocked_uid | 4,4,4 | NULL | 15 | Using intersect(uid_contact-type,pending_uid,blocked_uid); Using where; Using temporary; Using filesort |
| 1 | PRIMARY | post-user | ref | uid_uri-id,parent-uri-id,owner-id,author-id,contact-id,author-id_uid,author-id_created,owner-id_created,parent-uri-id_uid,uid_wall_received,uid_contactid,uid_unseen_contactid,uid_unseen,uid_hidden_uri-id | uid_contactid | 7 | const,friendica.contact.id | 35 | Using where |
| 1 | PRIMARY | post-thread | eq_ref | PRIMARY | PRIMARY | 4 | friendica.post-user.parent-uri-id | 1 | |
| 1 | PRIMARY | contact | eq_ref | PRIMARY | PRIMARY | 4 | friendica.contact.id | 1 | Using index |
| 1 | PRIMARY | author | eq_ref | PRIMARY | PRIMARY | 4 | friendica.post-user.author-id | 1 | Using index |
| 1 | PRIMARY | owner | eq_ref | PRIMARY | PRIMARY | 4 | friendica.post-user.owner-id | 1 | Using index |
| 1 | PRIMARY | post-quote | eq_ref | PRIMARY | PRIMARY | 4 | friendica.post-user.uri-id | 1 | |
| 5 | DEPENDENT SUBQUERY | post-media | ref | uri-id-url,uri-id-id | uri-id-id | 4 | friendica.post-user.uri-id | 1 | Using index |
| 4 | DEPENDENT SUBQUERY | post-category | ref | PRIMARY,uid_uri-id | uid_uri-id | 7 | friendica.post-user.uid,friendica.post-user.uri-id | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | post-collection | eq_ref | PRIMARY,type | PRIMARY | 5 | friendica.post-user.uri-id,const | 1 | Using index |
±-----±-------------------±----------------±------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-----------------------------------------±--------±---------------------------------------------------±-----±--------------------------------------------------------------------------------------------------------+

15

I’m struggling to get the slow log to produce results.

I’ve set the following on my test box

log_slow_query_file = /var/log/mysql/mariadb-slow.log
log_slow_query_time = 5
log_slow_verbosity = query_plan,explain
log-queries-not-using-indexes
log_slow_min_examined_row_limit = 1

and I have the file in /var/log/mysql

root@ginestes:/var/log/mysql# ls -l
total 4
drwxr-sr-x 2 mysql mysql 4096 May 25 09:28 log
-rwxrwxrwx 1 mysql mysql 0 May 25 09:32 mariadb-slow.log

and I have restarted mysql

But nothing is being written even when the timeouts are occuring and I can see the queries running in SQL

16

If this simple count takes 10 mins that it suggest that either the database is in a state that it has too many records or that the sql process is running badly configured doesn’t it?

17

Ok the results from the slow_log for that query are below (sorry badly cut and paste

SET timestamp=1779710259;
SELECT contact.id, contact.name, COUNT(*) AS count FROM post-user-view INNER JOIN contact ON post-user-view.contact-id = contact.id WHERE post-user-view.uid = 2 AND post-user-view.visible AND N>

User@Host: friendica[friendica] @ localhost

Thread_id: 1197 Schema: friendica QC_hit: No

Query_time: 65.663266 Lock_time: 0.000544 Rows_sent: 0 Rows_examined: 874944

Rows_affected: 0 Bytes_sent: 164

Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0

Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: No

Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No

explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra

explain: 1 PRIMARY contact index_merge PRIMARY,uid_name,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,network_uid_lastupdate,uid_network_self_lastupdate,uid_lastitem,uid_contact-type,uid_self_conta>

explain: 1 PRIMARY post-user ref uid_uri-id,parent-uri-id,owner-id,author-id,contact-id,author-id_uid,author-id_created,owner-id_created,parent-uri-id_uid,uid_wall_received,uid_contactid,uid_unseen_contactid,>

explain: 1 PRIMARY post-thread eq_ref PRIMARY PRIMARY 4 friendica.post-user.parent-uri-id 1 0.00 100.00 100.00

explain: 1 PRIMARY contact eq_ref PRIMARY PRIMARY 4 friendica.contact.id 1 NULL 100.00 NULL Using index

explain: 1 PRIMARY author eq_ref PRIMARY PRIMARY 4 friendica.post-user.author-id 1 NULL 100.00 NULL Using index

explain: 1 PRIMARY owner eq_ref PRIMARY PRIMARY 4 friendica.post-user.owner-id 1 NULL 100.00 NULL Using index

explain: 1 PRIMARY post-quote eq_ref PRIMARY PRIMARY 4 friendica.post-user.uri-id 1 NULL 100.00 NULL

explain: 5 DEPENDENT SUBQUERY post-media ref uri-id-url,uri-id-id uri-id-id 4 friendica.post-user.uri-id 1 NULL 100.00 NULL Using index

explain: 4 DEPENDENT SUBQUERY post-category ref PRIMARY,uid_uri-id uid_uri-id 7 friendica.post-user.uid,friendica.post-user.uri-id 1 NULL 100.00 NULL Using index

explain: 3 DEPENDENT SUBQUERY post-collection eq_ref PRIMARY,type PRIMARY 5 friendica.post-user.uri-id,const 1 NULL 100.00 NULL Using index

18

I looked at the definiton of the view post-user-view using SHOW CREATE VIEW post-user-view;

And got these results - I noticed the hefty bracketing in the later part of the view.

CREATE ALGORITHM=UNDEFINED DEFINER=`friendica`@`localhost` SQL SECURITY DEFINER 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`.`conversation-id` AS `conversation-id`,
`context-item-uri`.`uri` AS `context`,
`post-thread`.`context-id` AS `context-id`,
`quote-item-uri`.`uri` AS `quote-uri`,
`post-quote`.`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`.`commented` AS `commented`,
`post-user`.`received` AS `received`,
`post-thread`.`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`.`network` AS `parent-network`,
`post-thread`.`owner-id` AS `parent-owner-id`,
`post-thread`.`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` left join `post-thread-user` on
(`post-thread-user`.`uri-id` = `post-user`.`parent-uri-id` and `post-thread-user`.`uid` = `post-user`.`uid`
)
)
join `post-thread` on
(`post-thread`.`uri-id` = `post-user`.`parent-uri-id`
)
) 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`.`conversation-id`)) left join `item-uri` `context-item-uri` on(`context-item-uri`.`id` = `post-thread`.`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 `post-quote` on(`post-quote`.`uri-id` = `post-user`.`uri-id`)) left join `item-uri` `quote-item-uri` on(`quote-item-uri`.`id` = `post-quote`.`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`.`author-id`))

I then looked at the table defintion from https://github.com/friendica/friendica/blob/07ea97387cee8fad9accc810456ee2de399de3bb/database.sql#L2894 and noticed that nesting doesn’t appear there. 

DROP VIEW IF EXISTS `post-user-view`;
CREATE 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`.`conversation-id` AS `conversation-id`,
`context-item-uri`.`uri` AS `context`,
`post-thread`.`context-id` AS `context-id`,
`quote-item-uri`.`uri` AS `quote-uri`,
`post-quote`.`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`.`commented` AS `commented`,
`post-user`.`received` AS `received`,
`post-thread`.`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 `type` FROM `post-collection` WHERE `type` = 0 AND `uri-id` = `post-user`.`uri-id`) 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’), true, `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 `uri-id` FROM `post-category` WHERE `post-category`.`uri-id` = `post-user`.`uri-id` AND `post-category`.`uid` = `post-user`.`uid`) AS `has-categories`,
EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-user`.`uri-id`) AS `has-media`,
`diaspora-interaction`.`interaction` AS `signed_text`,
`parent-item-uri`.`guid` AS `parent-guid`,
`post-thread`.`network` AS `parent-network`,
`post-thread`.`owner-id` AS `parent-owner-id`,
`post-thread`.`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`
LEFT JOIN `post-thread-user` ON `post-thread-user`.`uri-id` = `post-user`.`parent-uri-id` AND `post-thread-user`.`uid` = `post-user`.`uid`
INNER JOIN `post-thread` ON `post-thread`.`uri-id` = `post-user`.`parent-uri-id`
STRAIGHT_JOIN `contact` ON `contact`.`id` = `post-user`.`contact-id`
STRAIGHT_JOIN `contact` AS `author` ON `author`.`id` = `post-user`.`author-id`
STRAIGHT_JOIN `contact` AS `owner` ON `owner`.`id` = `post-user`.`owner-id`
LEFT JOIN `contact` AS `causer` ON `causer`.`id` = `post-user`.`causer-id`
LEFT JOIN `item-uri` ON `item-uri`.`id` = `post-user`.`uri-id`
LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post-user`.`thr-parent-id`
LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post-user`.`parent-uri-id`
LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread`.`conversation-id`
LEFT JOIN `item-uri` AS `context-item-uri` ON `context-item-uri`.`id` = `post-thread`.`context-id`
LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post-user`.`external-id`
LEFT JOIN `item-uri` AS `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 `post-quote` ON `post-quote`.`uri-id` = `post-user`.`uri-id`
LEFT JOIN `item-uri` AS `quote-item-uri` ON `quote-item-uri`.`id` = `post-quote`.`quote-uri-id`
LEFT JOIN `post-delivery-data` ON `post-delivery-data`.`uri-id` = `post-user`.`uri-id` AND `post-user`.`origin`
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` AS `parent-post-author` ON `parent-post-author`.`id` = `post-thread`.`author-id`;

I’m not sure if I’m missing something - I don’t quite understand how to ‘scroll back’ on github and review changes to see if the structure of this view really did change.

19

The view is defined here, seems to have been uplifted in 2026.05-1.

To be completely honest - what’s the size of your instance? What’s the size of the DB (gauged via phpMyAdmin for example)?

All in all this is either your MariaDB being broken on your end or Friendica crumbling under heavy load (so, an upstream issue).

My question is - what assistance are you hoping to get here?

20

Thanks Yeah so the issue I am having is occuring on 2026_01 so that latest change can’t be the issue (but it also doens’t fix it either) . Total size of my database is about 27GB.

I’m trying to diagnose what might be causing the poor peformance of that particular query as the rest of the queries appear to be running quite well (e.g there’s no backlog on the worker process ) its purely an issue that is effecting using the front end and only that particular query is causing an issue.

What that did prompt me to do is temporarily hard code the user ID in the relevant php code that constructs the query to one that will produce no data (and return the query very quickly) that has meant that the UI now responds and works well -(except of course I get no updates when a circle has new posts.

That also allows me to put something on friendica to the support group there to see if I can engage with some other admins who might have had the same/similar issue.

Longer term I am going to have to work out the cause of this issue.

Your question is a good one when you are in weeds of trying to fix something..