Too slow stream query after update to 1.0.0-dev

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:

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.