I’ve seen a long response times for this SQL query a few times (> 5).
The most recent occurrence is this one where it took 93.2ms:
Executing action: show
T+173.7 ms
Reader
93.2 ms
lib/freedom_patches/active_record_base.rb:7:in `exec_sql'
lib/sql_builder.rb:67:in `exec'
lib/sql_builder.rb:104:in `map_exec'
app/models/topic_tracking_state.rb:136:in `report'
app/controllers/application_controller.rb:323:in `preload_current_user_data'
app/controllers/application_controller.rb:191:in `preload_json'
lib/middleware/anonymous_cache.rb:129:in `call'
config/initializers/100-quiet_logger.rb:10:in `call_with_quiet_assets'
config/initializers/100-silence_logger.rb:26:in `call'
lib/middleware/request_tracker.rb:73:in `call'
lib/scheduler/defer.rb:85:in `process_client'
lib/middleware/unicorn_oobgc.rb:95:in `process_client'
SELECT
u.id AS user_id,
topics.id AS topic_id,
topics.created_at,
highest_post_number,
last_read_post_number,
c.id AS category_id,
tu.notification_level
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.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, 2880) = -1 THEN u.created_at
WHEN COALESCE(uo.new_topic_duration_minutes, 2880) = -2 THEN COALESCE(u.previous_visit_at,u.created_at)
ELSE ('2016-02-23 19:42:40.186120'::timestamp - INTERVAL '1 MINUTE' * COALESCE(uo.new_topic_duration_minutes, 2880))
END, us.new_since, '2015-11-11 15:24:58.000000') AND tu.last_read_post_number IS NULL AND COALESCE(tu.notification_level, 2) >= 2)) AND
(topics.visible OR u.admin OR u.moderator) AND
topics.deleted_at IS NULL AND
( NOT c.read_restricted OR u.admin OR category_id IN (
SELECT c2.id FROM categories c2
JOIN category_groups cg ON cg.category_id = c2.id
JOIN group_users gu ON gu.user_id = 1 AND cg.group_id = gu.group_id
WHERE c2.read_restricted )
)
AND NOT EXISTS( SELECT 1 FROM category_users cu
WHERE last_read_post_number IS NULL AND
cu.user_id = 1 AND
cu.category_id = topics.category_id AND
cu.notification_level = 0)
UNION ALL
SELECT
u.id AS user_id,
topics.id AS topic_id,
topics.created_at,
highest_post_number,
last_read_post_number,
c.id AS category_id,
tu.notification_level
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.archetype <> 'private_message' AND
(("topics"."deleted_at" IS NULL AND tu.last_read_post_number < topics.highest_post_number AND COALESCE(tu.notification_level, 1) >= 2) OR (1=0)) AND
(topics.visible OR u.admin OR u.moderator) AND
topics.deleted_at IS NULL AND
( NOT c.read_restricted OR u.admin OR category_id IN (
SELECT c2.id FROM categories c2
JOIN category_groups cg ON cg.category_id = c2.id
JOIN group_users gu ON gu.user_id = 1 AND cg.group_id = gu.group_id
WHERE c2.read_restricted )
)
AND NOT EXISTS( SELECT 1 FROM category_users cu
WHERE last_read_post_number IS NULL AND
cu.user_id = 1 AND
cu.category_id = topics.category_id AND
cu.notification_level = 0)
Additionally here is the time before that where it took 31.3ms:
Executing action: show
T+157.0 ms
Reader
31.3 ms
lib/freedom_patches/active_record_base.rb:7:in `exec_sql'
lib/sql_builder.rb:67:in `exec'
lib/sql_builder.rb:104:in `map_exec'
app/models/topic_tracking_state.rb:136:in `report'
app/controllers/application_controller.rb:323:in `preload_current_user_data'
app/controllers/application_controller.rb:191:in `preload_json'
lib/middleware/anonymous_cache.rb:129:in `call'
config/initializers/100-quiet_logger.rb:10:in `call_with_quiet_assets'
config/initializers/100-silence_logger.rb:26:in `call'
lib/middleware/request_tracker.rb:73:in `call'
lib/scheduler/defer.rb:85:in `process_client'
lib/middleware/unicorn_oobgc.rb:95:in `process_client'
SELECT
u.id AS user_id,
topics.id AS topic_id,
topics.created_at,
highest_post_number,
last_read_post_number,
c.id AS category_id,
tu.notification_level
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.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, 2880) = -1 THEN u.created_at
WHEN COALESCE(uo.new_topic_duration_minutes, 2880) = -2 THEN COALESCE(u.previous_visit_at,u.created_at)
ELSE ('2016-02-23 20:05:52.900200'::timestamp - INTERVAL '1 MINUTE' * COALESCE(uo.new_topic_duration_minutes, 2880))
END, us.new_since, '2015-11-11 15:24:58.000000') AND tu.last_read_post_number IS NULL AND COALESCE(tu.notification_level, 2) >= 2)) AND
(topics.visible OR u.admin OR u.moderator) AND
topics.deleted_at IS NULL AND
( NOT c.read_restricted OR u.admin OR category_id IN (
SELECT c2.id FROM categories c2
JOIN category_groups cg ON cg.category_id = c2.id
JOIN group_users gu ON gu.user_id = 1 AND cg.group_id = gu.group_id
WHERE c2.read_restricted )
)
AND NOT EXISTS( SELECT 1 FROM category_users cu
WHERE last_read_post_number IS NULL AND
cu.user_id = 1 AND
cu.category_id = topics.category_id AND
cu.notification_level = 0)
UNION ALL
SELECT
u.id AS user_id,
topics.id AS topic_id,
topics.created_at,
highest_post_number,
last_read_post_number,
c.id AS category_id,
tu.notification_level
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.archetype <> 'private_message' AND
(("topics"."deleted_at" IS NULL AND tu.last_read_post_number < topics.highest_post_number AND COALESCE(tu.notification_level, 1) >= 2) OR (1=0)) AND
(topics.visible OR u.admin OR u.moderator) AND
topics.deleted_at IS NULL AND
( NOT c.read_restricted OR u.admin OR category_id IN (
SELECT c2.id FROM categories c2
JOIN category_groups cg ON cg.category_id = c2.id
JOIN group_users gu ON gu.user_id = 1 AND cg.group_id = gu.group_id
WHERE c2.read_restricted )
)
AND NOT EXISTS( SELECT 1 FROM category_users cu
WHERE last_read_post_number IS NULL AND
cu.user_id = 1 AND
cu.category_id = topics.category_id AND
cu.notification_level = 0)
There isn’t anything noteworthy using the CPU / disk and no active sidekiq tasks.