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.