Hello everyone!
After the update, the Stream query executes about 2,5-3 minutes. Before the update, the Stream loaded approximately 25-30 seconds.
OS: Ubuntu 20.04
DB: MariaDB 10.4.30
RAM: 2 GB
SSD: 30 GB
A query like “select * from posts order by created_at DESC limit 100;” takes 0.068 seconds in mysql console.
Perhaps there are some parameters that I did not set up on my pod, without which queries are executed for a long time?
I’m not entirely sure how you even managed to do that, as the default response timeout is 90 seconds.
Either way, the way streams are generated is not ideal, but it hasn’t changed in many years. MySQL/MariaDB tends to be horribly slow with cold caches after a server reboot. Give it a bit of time and usage and it’ll normalize. Look into migrating to PostgreSQL if you have quite a bit of time available.
Yes, I saw this parameter and I don’t change it.
Now I added a parameter "slow_query_log " to mariadb.cnf, I’ll try to see the queries that are running the longest.
Here is the top of slowest queries:
# 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;
# 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;
# 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;
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.
Basically my problem is similar to this: Slow stream loading problem · Issue #7981 · diaspora/diaspora · GitHub
For myself, I solved the problem this way: I removed DISTINCT from the problematic queries. I know that this is not entirely correct, but it is better than loading one page for 2 minutes…
Examples:
**№1**
Before:
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;
Time: 2 min 25 sec
After:
SELECT posts.*, `posts`.`id` FROM `posts` LEFT 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;
Time: 0.146 sec
**№2**
Before:
SELECT COUNT(*) FROM `posts` INNER JOIN `people` ON `people`.`id` = `posts`.`author_id` WHERE `posts`.`type` = 'StatusMessage' AND `people`.`owner_id` IS NOT NULL;
Time: 1 min 40 sec
After:
SELECT STRAIGHT_JOIN COUNT(*) FROM `posts` INNER JOIN `people` ON `people`.`id` = `posts`.`author_id` WHERE `posts`.`type` = 'StatusMessage' AND `people`.`owner_id` IS NOT NULL;
Time: 29 sec
Maybe this will be useful to someone…
To anyone reading this: do not, under any circumstance, change any of the queries built by diaspora*. If you do, you’re on your own, we cannot and will not help you debug weird issues with that. It’s a never-ending source of unexpected surprises, and we don’t want to even get close to encouraging this.
Also, the recommended thing still is to set up new pods on Postgres only, and if possible, migrate existing pods from MySQL/MariaDB to PG as well. For some reason, PG is much faster than other engines, and in the future, we might even explore further optimizations that only work on PG.