Here is the top of slowest queries:
98.9 seconds
# Time: 230725 16:26:45
# User@Host: diaspora[diaspora] @ localhost []
# Thread_id: 37 Schema: diaspora_production QC_hit: No
# Query_time: 98.908357 Lock_time: 0.000583 Rows_sent: 15 Rows_examined: 1443899
# Rows_affected: 0 Bytes_sent: 26149
SET timestamp=1690291605;
SELECT DISTINCT posts.*, `posts`.`id` FROM `posts` INNER JOIN `taggings` ON `taggings`.`taggable_type` = 'Post' AND `taggings`.`taggable_id` = `posts`.`id` WHERE `posts`.`type` = 'StatusMessage' AND `posts`.`public` = TRUE AND (taggings.tag_id IN (5974,8345,1,1123,19401,2294,2379,1538,23,6407,654,6,696,8959,543,1328,13921,655,17811,73684,8344,9881,34870,21,8960,8770,6264,6265,6263,8412)) AND (posts.author_id NOT IN (332,338,339,420,4476)) AND (posts.id NOT IN ('3604','3608','655029','654511')) AND (posts.created_at < '2023-07-25 13:24:10') AND `posts`.`type` IN ('StatusMessage', 'Reshare') ORDER BY posts.created_at DESC, posts.id DESC LIMIT 15;
89 seconds
# Time: 230725 16:26:04
# User@Host: diaspora[diaspora] @ localhost []
# Thread_id: 36 Schema: diaspora_production QC_hit: No
# Query_time: 89.062999 Lock_time: 0.000324 Rows_sent: 1 Rows_examined: 423586
# Rows_affected: 0 Bytes_sent: 61
SET timestamp=1690291564;
SELECT COUNT(*) FROM `posts` INNER JOIN `people` ON `people`.`id` = `posts`.`author_id` WHERE `posts`.`type` = 'StatusMessage' AND `people`.`owner_id` IS NOT NULL;
57 seconds
# Time: 230725 16:25:06
# User@Host: diaspora[diaspora] @ localhost []
# Thread_id: 37 Schema: diaspora_production QC_hit: No
# Query_time: 57.432576 Lock_time: 0.002121 Rows_sent: 15 Rows_examined: 756041
# Rows_affected: 0 Bytes_sent: 903
SET timestamp=1690291506;
(SELECT DISTINCT posts.id, posts.updated_at AS updated_at, posts.created_at AS created_at FROM `posts` LEFT OUTER JOIN share_visibilities BY posts.created_at DESC LIMIT 15) ORDER BY created_at DESC LIMIT 15;
5-8 seconds
Queries like this:
# Time: 230725 16:24:13
# User@Host: diaspora[diaspora] @ localhost []
# Thread_id: 35 Schema: diaspora_production QC_hit: No
# Query_time: 5.837654 Lock_time: 0.000305 Rows_sent: 0 Rows_examined: 574145
# Rows_affected: 0 Bytes_sent: 653
SET timestamp=1690291453;
SELECT `open_graph_caches`.* FROM `open_graph_caches` WHERE `open_graph_caches`.`url` = 'https://some.url' LIMIT 1;
I think you can make allowance for restarting mysql, but still these are frightening numbers.