Hi there,
We have pretty large site (PG base is 97GB) + we store 256GB of images.
It runs on Digital Ocean server — 6 vCPUs / 16GB / 320GB Disk.
Is there any tips, tricks, scripts to optimise PG, rebuild something to speed things up?
THANKS
Some time Latest page takes ages to draw.
Executing 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;
One of the reasons they pushed to pg13 was the speed ups that it offers. So that and rebuilding indexes as described in PostgreSQL 13 update is the first thing to do.
1 Like
So I moved my server to another more powerfull hosting via clean install and restore from backup. Yep, it much faster now! But there is some bottleneck in Like function. It takes 23 second to create Like.
Executing 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
;
I need to more time to settle DB and re-indexing or there is a problem?
1 Like