Salut,
Nous avons un site assez volumineux (base PG de 97 Go) + nous stockons 256 Go d’images.
Parfois, la dernière page met une éternité à se charger.
Exécution de l’action : latest
T+125.6 ms
Reader
7237.4 ms
app/models/topic_tracking_state.rb:301:in `report'
app/controllers/application_controller.rb:628:in `preload_current_user_data'
app/controllers/application_controller.rb:428:in `preload_json'
app/controllers/application_controller.rb:404:in `block in with_resolved_locale'
app/controllers/application_controller.rb:404:in `with_resolved_locale'
lib/middleware/omniauth_bypass_middleware.rb:71:in `call'
lib/content_security_policy/middleware.rb:12:in `call'
lib/middleware/anonymous_cache.rb:368:in `call'
config/initializers/100-quiet_logger.rb:23:in `call'
config/initializers/100-silence_logger.rb:31:in `call'
lib/middleware/enforce_hostname.rb:23:in `call'
lib/middleware/request_tracker.rb:202:in `call'
SELECT
DISTINCT topics.id as topic_id,
u.id as user_id,
topics.created_at,
topics.updated_at,
topics.highest_staff_post_number AS highest_post_number,
last_read_post_number,
c.id as category_id,
tu.notification_level,
us.first_unread_at,
GREATEST(
CASE
WHEN COALESCE(uo.new_topic_duration_minutes, 10080) = -1 THEN u.created_at
WHEN COALESCE(uo.new_topic_duration_minutes, 10080) = -2 THEN COALESCE(
u.previous_visit_at,u.created_at
)
ELSE ('2022-05-11 22:22:20.701444'::timestamp - INTERVAL '1 MINUTE' * COALESCE(uo.new_topic_duration_minutes, 10080))
END, u.created_at, '2022-05-03 03:50:38'
) AS treat_as_new_topic_start_date
FROM topics
JOIN users u on u.id = 1
JOIN user_stats AS us ON us.user_id = u.id
JOIN user_options AS uo ON uo.user_id = u.id
JOIN categories c ON c.id = topics.category_id
LEFT JOIN topic_users tu ON tu.topic_id = topics.id AND tu.user_id = u.id
LEFT JOIN dismissed_topic_users ON dismissed_topic_users.topic_id = topics.id AND dismissed_topic_users.user_id = 1
WHERE u.id = 1 AND
topics.archetype <> 'private_message' AND
((1=0) OR ("topics"."deleted_at" IS NULL AND (topics.created_at >= (GREATEST(CASE
WHEN COALESCE(uo.new_topic_duration_minutes, 10080) = -1 THEN u.created_at
WHEN COALESCE(uo.new_topic_duration_minutes, 10080) = -2 THEN COALESCE(u.previous_visit_at,u.created_at)
ELSE ('2022-05-11 22:22:20.699581'::timestamp - INTERVAL '1 MINUTE' * COALESCE(uo.new_topic_duration_minutes, 10080))
END, u.created_at, '2022-05-03 03:50:38'))) AND (tu.last_read_post_number IS NULL) AND (COALESCE(tu.notification_level, 2) >= 2) AND topics.created_at > '2022-05-03 03:50:38' AND dismissed_topic_users.id IS NULL)) AND
topics.deleted_at IS NULL AND
NOT (
last_read_post_number IS NULL AND
(
topics.category_id IN (SELECT "categories"."id" FROM "categories" LEFT JOIN categories categories2 ON categories2.id = categories.parent_category_id LEFT JOIN category_users ON category_users.category_id = categories.id AND category_users.user_id = 1 LEFT JOIN category_users category_users2 ON category_users2.category_id = categories2.id AND category_users2.user_id = 1 WHERE ((category_users.id IS NULL AND COALESCE(category_users2.notification_level, 1) = 0) OR COALESCE(category_users.notification_level, 1) = 0))
AND tu.notification_level <= 1
)
)
UNION ALL
SELECT
DISTINCT topics.id as topic_id,
u.id as user_id,
topics.created_at,
topics.updated_at,
topics.highest_staff_post_number AS highest_post_number,
last_read_post_number,
c.id as category_id,
tu.notification_level,
us.first_unread_at,
GREATEST(
CASE
WHEN COALESCE(uo.new_topic_duration_minutes, 10080) = -1 THEN u.created_at
WHEN COALESCE(uo.new_topic_duration_minutes, 10080) = -2 THEN COALESCE(
u.previous_visit_at,u.created_at
)
ELSE ('2022-05-11 22:22:20.701444'::timestamp - INTERVAL '1 MINUTE' * COALESCE(uo.new_topic_duration_minutes, 10080))
END, u.created_at, '2022-05-03 03:50:38'
) AS treat_as_new_topic_start_date
FROM topics
JOIN users u on u.id = 1
JOIN user_stats AS us ON us.user_id = u.id
JOIN user_options AS uo ON uo.user_id = u.id
JOIN categories c ON c.id = topics.category_id
LEFT JOIN topic_users tu ON tu.topic_id = topics.id AND tu.user_id = u.id
WHERE u.id = 1 AND
topics.updated_at >= us.first_unread_at AND
topics.archetype <> 'private_message' AND
((("topics"."deleted_at" IS NULL AND (tu.last_read_post_number < topics.highest_staff_post_number) AND (COALESCE(tu.notification_level, 1) >= 2)) OR (1=0))) AND
topics.deleted_at IS NULL AND
NOT (
last_read_post_number IS NULL AND
(
topics.category_id IN (SELECT "categories"."id" FROM "categories" LEFT JOIN categories categories2 ON categories2.id = categories.parent_category_id LEFT JOIN category_users ON category_users.category_id = categories.id AND category_users.user_id = 1 LEFT JOIN category_users category_users2 ON category_users2.category_id = categories2.id AND category_users2.user_id = 1 WHERE ((category_users.id IS NULL AND COALESCE(category_users2.notification_level, 1) = 0) OR COALESCE(category_users.notification_level, 1) = 0))
AND tu.notification_level <= 1
)
)
LIMIT 5000;
L’une des raisons pour lesquelles ils ont poussé à pg13 était l’accélération qu’il offre. Donc, cela et la reconstruction des index comme décrit dans Mise à jour PostgreSQL 13 est la première chose à faire.
1 « J'aime »
J’ai donc déplacé mon serveur vers un hébergement plus performant via une installation propre et une restauration à partir d’une sauvegarde. Oui, c’est beaucoup plus rapide maintenant ! Mais il y a un goulot d’étranglement dans la fonction Like. Il faut 23 secondes pour créer un Like.
Exécution de l’action : create
T+229.3 ms
Reader
23169.7 ms
app/models/topic_user.rb:463:in `update_post_action_cache'
app/models/post_action.rb:232:in `update_counters'
lib/post_action_creator.rb:268:in `create_post_action'
lib/post_action_creator.rb:112:in `perform'
app/controllers/post_actions_controller.rb:22:in `create'
app/controllers/application_controller.rb:415:in `block in with_resolved_locale'
app/controllers/application_controller.rb:415:in `with_resolved_locale'
lib/middleware/omniauth_bypass_middleware.rb:71:in `call'
lib/content_security_policy/middleware.rb:12:in `call'
lib/middleware/anonymous_cache.rb:368:in `call'
config/initializers/100-quiet_logger.rb:23:in `call'
config/initializers/100-silence_logger.rb:31:in `call'
lib/middleware/enforce_hostname.rb:23:in `call'
lib/middleware/request_tracker.rb:202:in `call'
UPDATE topic_users tu
SET liked = x.state
FROM (
SELECT CASE WHEN EXISTS (
SELECT 1
FROM post_actions pa
JOIN posts p on p.id = pa.post_id
JOIN topics t ON t.id = p.topic_id
WHERE pa.deleted_at IS NULL AND
p.deleted_at IS NULL AND
t.deleted_at IS NULL AND
pa.post_action_type_id = 2 AND
tu2.topic_id = t.id AND
tu2.user_id = pa.user_id
LIMIT 1
) THEN true ELSE false END state, tu2.topic_id, tu2.user_id
FROM topic_users tu2
WHERE (tu2.user_id IN (1)) AND (tu2.topic_id IN (127680))
) x
WHERE x.topic_id = tu.topic_id AND x.user_id = tu.user_id AND x.state != tu.liked
;
Ai-je besoin de plus de temps pour régler la base de données et réindexer ou y a-t-il un problème ?
1 « J'aime »