From b1f97019665fed6c9a2fae4c4269ede1a388c63b Mon Sep 17 00:00:00 2001 From: Scott Ostler Date: Mon, 23 Aug 2010 16:02:12 -0400 Subject: Changed scores and directory update freq, add msgs index to 0-create.psql, change server startup order --- docs/userlog-query.txt | 104 ++++++++++++++++++++++++++++++++++++++----------- src/site.clj | 10 ++--- 2 files changed, 85 insertions(+), 29 deletions(-) diff --git a/docs/userlog-query.txt b/docs/userlog-query.txt index 6781772..a9effb5 100644 --- a/docs/userlog-query.txt +++ b/docs/userlog-query.txt @@ -1,4 +1,5 @@ -Query 1 (USER_ID): +Query 1 (USER_ID) +------------------------------------------ explain analyze SELECT m.content, m.message_id, m.created_on, u.nick, u.avatar, @@ -12,27 +13,8 @@ where u.user_id = 6 and m.is_image = true and m.room_id = 1 order by m.created_on desc -LIMIT 40 offset 0; - -Query 2 (NICK): - -explain analyze SELECT -m.content, m.message_id, m.created_on, u.nick, u.avatar, -array_to_string(ARRAY(SELECT nick || ' ' || tag - FROM tags, users - WHERE message_id = m.message_id AND tags.user_id = users.user_id), ' ') as tags -from users u -left join messages m on u.user_id = m.user_id -where -u.nick = 'jeanette' --- u.user_id = 6 -and m.is_image = true and m.room_id = 1 -order by m.created_on desc -LIMIT 40 offset 0; - +LIMIT 40 offset 1000; -QUERY 1 (USER_ID) ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..1526.37 rows=40 width=157) (actual time=0.121..1.696 rows=40 loops=1) -> Nested Loop (cost=0.00..880298.45 rows=23069 width=157) (actual time=0.118..1.621 rows=40 loops=1) -> Index Scan using messages_user_created_on_idx on messages m (cost=0.00..112630.78 rows=23069 width=80) (actual time=0.056..0.234 rows=40 loops=1) @@ -50,8 +32,23 @@ QUERY 1 (USER_ID) (14 rows) -QUERY 2 (NICK) ---------------------------------------------------------------------------------------------------------------------------------------------------------- +Query 2 (NICK) +---------------------------------------- + +explain analyze SELECT +m.content, m.message_id, m.created_on, u.nick, u.avatar, +array_to_string(ARRAY(SELECT nick || ' ' || tag + FROM tags, users + WHERE message_id = m.message_id AND tags.user_id = users.user_id), ' ') as tags +from users u +left join messages m on u.user_id = m.user_id +where +u.nick = 'jeanette' +-- u.user_id = 6 +and m.is_image = true and m.room_id = 1 +order by m.created_on desc +LIMIT 40 offset 0; + Limit (cost=20371.96..20372.06 rows=40 width=157) (actual time=532.538..532.611 rows=40 loops=1) -> Sort (cost=20371.96..20373.52 rows=624 width=157) (actual time=532.535..532.560 rows=40 loops=1) Sort Key: m.created_on @@ -72,3 +69,64 @@ QUERY 2 (NICK) Index Cond: (users.user_id = tags.user_id) Total runtime: 532.757 ms (19 rows) + + +QUERY 3 (ROOM JOIN) +---------------- + +2010-08-23 02:27:30 EDT LOG: duration: 10817.570 ms execute : + +explain analyze SELECT + m.content, m.message_id, m.created_on, + u.nick, u.avatar, + array_to_string(ARRAY(SELECT nick || ' ' || tag + FROM tags + WHERE message_id = m.message_id AND tags.user_id = u.user_id), ' ') as tags + FROM users u, messages m + WHERE room_id = 2 + AND m.user_id = u.user_id ORDER BY created_on DESC + LIMIT 200 OFFSET 0; + +select + +PLAN +---- + + Limit (cost=115535.24..115535.74 rows=200 width=157) (actual time=7835.228..7835.592 rows=200 loops=1) + -> Sort (cost=115535.24..115546.44 rows=4477 width=157) (actual time=7835.224..7835.356 rows=200 loops=1) + Sort Key: m.created_on + Sort Method: top-N heapsort Memory: 58kB + -> Hash Join (cost=354.59..115341.75 rows=4477 width=157) (actual time=256.324..7807.477 rows=13581 loops=1) + Hash Cond: (m.user_id = u.user_id) + -> Index Scan using messages_room_id_idx on messages m (cost=0.00..2970.23 rows=4477 width=80) (actual time=7.260..5647.945 rows=13581 loops=1) + Index Cond: (room_id = 2) + -> Hash (cost=262.04..262.04 rows=7404 width=85) (actual time=237.419..237.419 rows=5675 loops=1) + -> Seq Scan on users u (cost=0.00..262.04 rows=7404 width=85) (actual time=0.208..227.609 rows=5675 loops=1) + SubPlan 1 + -> Nested Loop (cost=0.00..25.00 rows=2 width=18) (actual time=0.126..0.135 rows=0 loops=13581) + -> Index Scan using tags_message_id_idx on tags (cost=0.00..8.43 rows=2 width=13) (actual time=0.122..0.131 rows=0 loops=13581) + Index Cond: (message_id = $0) + -> Index Scan using users_pkey on users (cost=0.00..8.27 rows=1 width=13) (actual time=0.010..0.011 rows=1 loops=243) + Index Cond: (users.user_id = tags.user_id) + Total runtime: 7837.031 ms +(17 rows) + + +QUERY 4 (FEED_IMAGES) +------------------------ + +explain analyze SELECT * FROM feed_images +WHERE room = 'boys' AND message_id IS NULL +ORDER BY queued_on ASC LIMIT 1; + + Limit (cost=3966.33..3966.33 rows=1 width=231) (actual time=8779.090..8779.093 rows=1 loops=1) + -> Sort (cost=3966.33..3966.33 rows=1 width=231) (actual time=8779.084..8779.084 rows=1 loops=1) + Sort Key: queued_on + Sort Method: top-N heapsort Memory: 17kB + -> Bitmap Heap Scan on feed_images (cost=921.32..3966.32 rows=1 width=231) (actual time=8772.479..8772.497 rows=7 loops=1) + Recheck Cond: (room = 'boys'::text) + Filter: (message_id IS NULL) + -> Bitmap Index Scan on feed_images_pkey (cost=0.00..921.32 rows=12400 width=0) (actual time=5878.657..5878.657 rows=13408 loops=1) + Index Cond: (room = 'boys'::text) + Total runtime: 8779.219 ms +(10 rows) diff --git a/src/site.clj b/src/site.clj index 731e196..92f6eb9 100644 --- a/src/site.clj +++ b/src/site.clj @@ -340,9 +340,7 @@ ORDER BY cnt DESC (def *user-info-query* " SELECT u.nick, u.user_id, - u.avatar, - LAST.content, - LAST.message_id + u.avatar FROM users u LEFT JOIN messages LAST ON u.user_id = LAST.user_id @@ -363,8 +361,8 @@ FROM users u {:list res :map (zipmap (map :nick res) (map :cnt res))})) -(def *scores-refresh-period-sec* (* 30 60)) -(def *user-info-refresh-period-sec* 300) +(def *scores-refresh-period-sec* (* 29 60)) +(def *user-info-refresh-period-sec* (* 31 60)) (def *user-scores* (scheduled-agent build-score-list @@ -523,7 +521,7 @@ FROM users u profile-nick (:nick user-info) ; Update to get right casing nick (session :nick) logger (make-time-logger) - has-avatar (non-empty-string? (user-info :avatar)) + has-avatar (non-empty-string? (:avatar user-info)) offset (maybe-parse-int offset 0) dump-offset (* offset *dumps-per-page*) raw-dumps (logger tags/fetch-dumps-by-nick -- cgit v1.2.3-70-g09d2