Investigating slowness of FPM/Friendica combination
With the help of mysqldumpsql -s at -t 10 -a I found some slow SQL queries the current develop code causes:SELECT `id` FROM `contact` WHERE (`network` IN ('dfrn', 'dspr', 'stat', 'apub', 'zot!', 'unkn') AND `self` = 0 AND `id` IN (SELECT `cid` FROM `post-tag` WHERE `cid` = `contact`.`id`) AND (`last-update` < '2022-06-24 18:39:07' OR (NOT `failed` AND `last-update` < '2022-07-17 18:39:07'))) LIMIT 100
This query took over 70 seconds which is WAY to slow (as it also keeps the FPM child process busy and unable to process another request).
An EXPLAIN gives me following extra information:
- table contact: Using index condition; Using where
- table post-tag: Using index; FirstMatch(contact)
It looks already optimized but is still so slow. Another one I came across:
SELECT `account_type`, COUNT(*) AS `count`
FROM `profile` p
JOIN `server` s ON s.`id` = p.`server_id` AND s.`available` AND NOT s.`hidden`
WHERE p.`available` AND NOT p.`hidden` AND p.`region` LIKE 'foo%'
GROUP BY p.`account_type`
ORDER BY `count` DESCWhich is caused by Friendica Directory. The query now runs fast but has an infamous
Using where; Using temporary; Using filesort on the table s (server) which MAY slow it down.But the next one hits the jackpot of most-used tables in a single query:
SELECT `uid`, `id`, `parent`, `guid`, `network`, `gravity`, `uri-id`, `uri`, `thr-parent-id`, `thr-parent`, `parent-uri-id`, `parent-uri`, `commented`, `created`, `edited`, `received`, `verb`, `object-type`, `postopts`, `plink`, `wall`, `private`, `starred`, `origin`, `parent-origin`, `title`, `body`, `language`, `content-warning`, `location`, `coord`, `app`, `rendered-hash`, `rendered-html`, `object`, `allow_cid`, `allow_gid`, `deny_cid`, `deny_gid`, `mention`, `global`, `author-id`, `author-link`, `author-name`, `author-avatar`, `author-network`, `author-updated`, `author-gsid`, `author-addr`, `author-uri-id`, `owner-id`, `owner-link`, `owner-name`, `owner-avatar`, `owner-network`, `owner-contact-type`, `owner-updated`, `causer-id`, `causer-link`, `causer-name`, `causer-avatar`, `causer-contact-type`, `causer-network`, `contact-id`, `contact-uid`, `contact-link`, `contact-name`, `contact-avatar`, `writable`, `self`, `cid`, `alias`, `event-created`, `event-edited`, `event-start`, `event-finish`, `event-summary`, `event-desc`, `event-location`, `event-type`, `event-nofinish`, `event-ignore`, `event-id`, `question-id`, `question-multiple`, `question-voters`, `question-end-time`, `has-categories`, `has-media`, `delivery_queue_count`, `delivery_queue_done`, `delivery_queue_failed` FROM `post-user-view` WHERE ((`uid` = N OR (`uid` = N AND NOT `global`)) AND `uri-id` IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N) AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = N OR `causer-id` IS NULL) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (N, N))) OR `self` OR `gravity` != N OR `contact-uid` = N) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `uid` = N AND `uri-id` = `post-user-view`.`uri-id` AND `hidden`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = N AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = N AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = N AND `cid` = `author-id` AND `ignored` AND `gravity` = N) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = N AND `cid` = `owner-id` AND `ignored` AND `gravity` = N)) ORDER BY `id` DESCThis one causes also the same infamous
Using index condition; Using where; Using temporary; Using filesort over the table post-user and is really scary to see.
Roland Häder🇩🇪
in reply to Roland Häder🇩🇪 • •fpmchild processes are being started, causing the whole web server to slow down and most of them come from my Friendica instance.