Hola,\nTenemos un sitio bastante grande (base de datos PG de 97 GB) + almacenamos 256 GB de imágenes.\n
\n\nSe ejecuta en un servidor de Digital Ocean — 6 vCPUs / 16 GB / disco de 320 GB.\n\n¿Hay algún consejo, truco, script para optimizar PG, reconstruir algo para acelerar las cosas?\n\nGRACIAS
A veces, la última página tarda mucho en dibujarse.
Ejecutando acción: latest
T+125.6 ms
Lector
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;
Una de las razones por las que impulsaron pg13 fueron las aceleraciones que ofrece. Por lo tanto, eso y la reconstrucción de índices, como se describe en Actualización de PostgreSQL 13, es lo primero que hay que hacer.
1 me gusta
Así que moví mi servidor a otro hosting más potente a través de una instalación limpia y restauración desde una copia de seguridad. ¡Sí, ahora es mucho más rápido! Pero hay algún cuello de botella en la función de “Me gusta”. Tarda 23 segundos en crear un “Me gusta”.
Ejecutando acción: crear
T+229.3 ms
Lector
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
;
¿Necesito más tiempo para asentar la base de datos y reindexar o hay un problema?
1 me gusta