Moar Cooties
-
Continuing the discussion from The Official Fermenting Whale Dicks Thread:
.....
and where did all that come from?strong textStatus
wtf discourse?strong text
Someone's been looking at profiles:
Top 30 urls by Server Load Url Duration Reqs --- -------- ---- POST /topics/timings HTTP/1.1 51976.49 49776 GET /t/1000.json?include_raw=1&track_visit=true HTTP/1.1 27843.59 7587 GET /users/boomzilla.json HTTP/1.1 8959.34 304 GET /users/FrostCat.json HTTP/1.1 7304.77 326 POST /draft.json HTTP/1.1 6468.86 4437 GET /users/blakeyrat.json HTTP/1.1 5213.25 241 GET /users/undefined.json HTTP/1.1 5196.95 643 GET /users/darkmatter.json HTTP/1.1 4119.71 165 GET /users/accalia.json HTTP/1.1 3084.71 115 GET / HTTP/1.1 2853.54 5857 GET /users/Gaska.json HTTP/1.1 2715.03 294 GET /users/dkf.json HTTP/1.1 2655.29 88 GET /notifications.json HTTP/1.1 2315.78 1758 GET /users/RaceProUK.json HTTP/1.1 2202.58 69 GET /users/HardwareGeek.json HTTP/1.1 2159.10 75 GET /users/sloosecannon.json HTTP/1.1 2108.32 192 GET /notifications HTTP/1.1 1907.88 1148 GET /users/tar.json HTTP/1.1 1719.79 94 GET /users/flabdablet.json HTTP/1.1 1695.58 175 POST /posts HTTP/1.1 1589.46 403 GET /users/redwizard.json HTTP/1.1 1585.33 55 POST /logs/report_js_error HTTP/1.1 1445.01 302 GET /users/Magus.json HTTP/1.1 1423.00 148 GET /users/Yamikuronue.json HTTP/1.1 1396.56 52 POST /post_actions HTTP/1.1 1395.58 974 GET /t/2/last HTTP/1.1 1350.25 4869 GET /latest.json HTTP/1.1 1293.91 4881 GET /users/ben_lubar.json HTTP/1.1 1277.99 52 GET /users/Arantor.json HTTP/1.1 1248.07 45 GET /users/Jaloopa.json HTTP/1.1 1138.51 107 (all durations in seconds)
...and I think this may be the first time I've seen someone top Anonymous:
Top 30 users by Server Load Username Duration Reqs Routes -------- -------- ---- ------ M_Adams 89647.30 7550 users/show(73874.08) topics/timings (POST)(11272.32) user_avatars/show(3887.30) user_avatars/show_letter(285.78) topics/posts(129.57) [Anonymous] 71515.77 67493 topics/show(25369.33) user_avatars/show(16920.26) -(12374.78) - (POST)(7436.75) list/latest(3356.40)
-
What, trying to dumb-download the site or something?
Does it show the user agent in the logs too?
-
Does it show the user agent in the logs too?
Not in the stuff I was quoting, which is available to all Staff. You'd need shell access to get raw access log type stuff.
-
@M_Adams, the fuck are you doing?
Then again, it wouldn't be an issue if Discourse DIDN'T MAKE THE PROFILE PAGE TAKE 15 FUCKING SECONDS TO LOAD.
-
So how many of us are DDOSing the server now by looking at @M_Adams's profile?
-
Maybe Disco-devs have a long con to make Discourse the Crysis of web applications. Ya know, "Can it run
DucksauceDiscourse and not fall over with 5 concurrent users?"Why? Fuck if I know, and I bet the Disco-devs wouldn't either.
-
: Discourse is designed for civilized discussion, it was not designed to be an address book so you could look at people's information constantly! You're !1111one
-
Maybe Disco-devs have a long con to make Discourse the Crysis of web applications. Ya know, "Can it run DucksauceDiscourse and not fall over with 5 concurrent users?"
Oooh, but the profile pages only start getting slower once your users have commited enough likes, bookmarks, etc. into the whole big dump of an
user_actions
table. And then it runs COUNT(*) queries on it.So if it's a benchmark, it's a benchmark for ten years into the future - once you've garnered so much info there that your server falls the fuck over.
-
-
It's just a meta-benchmark of RDBMSes. If you cant
select count(*) from big_ass_table where true=true
you aren'twebdisco-scale.
-
It's just a meta-benchmark of RDBMSes. If you cant select count(*) from big_ass_table where true=true you aren't webdisco-scale.
I'm just amused how that's the one place where they decided "no, we can't use DiscoRounding here and cache those values for a while, the likes measurements must be precise to a single like!"
Like, from the Likes topic I presume?
We just sped up the inevitable.
-
When you have
roundingcounting errors on the 'like' counter for individual posts are you realllly surprised or just not fully shocked?
-
-
GET /t/1000.json?include_raw=1&track_visit=true HTTP/1.1 27843.59 7587
/t/1000 is still second?
-
The profile background is brilliant.
-
Oooh, but the profile pages only start getting slower once your users have commited enough likes, bookmarks, etc. into the whole big dump of an user_actions table. And then it runs COUNT(*) queries on it.
Off to @nobulate's profile!
EDIT: Whoops, looks like they stopped early.
-
Oooh, but the profile pages only start getting slower once your users have commited enough likes, bookmarks, etc. into the whole big dump of an user_actions table. And then it runs COUNT(*) queries on it.
hmm.... one wonders if performance would be improved by adding one or two judiciously considered FREAKING INDEXES to the database!
-
if performance would be improved by adding one or two judiciously considered FREAKING INDEXES to the database!
-
hmm.... one wonders if performance would be improved by adding one or two judiciously considered FREAKING INDEXES to the database!
It's not like they don't have any indexes. For instance, on
post_actions
:`CREATE INDEX index_post_actions_on_user_id_and_post_action_type_id ON public.post_actions USING btree (user_id, post_action_type_id) WHERE deleted_at IS NULL;
So...that should do it for counting likes. But...I think they might filter that by topics that the user looking at your profile can see. So maybe the index doesn't really matter. The same probably applies for counting up your posts. Plus I guess you run into pgsql's poor counting performance on top of that.
-
-
But...I think they might filter that by topics that the user looking at your profile can see. So maybe the index doesn't really matter.
Considering Discodevs and their usual competence they probably grab the full list of everything then filter it somewhere in the 197 layers of application before spitting it out. Rather than, say, getting the database to do the hard work and presenting it.
I guess we should be grateful they don't send the full unfiltered list to the client and filter it there.
-
few and far between they be.
I don't think that's true. I see 4 different indexes on
post_actions
. 5 onposts
. But I think various decisions make it so that they don't particularly help things. Or don't help out enough.Considering Discodevs and their usual competence they probably grab the full list of everything then filter it somewhere in the 197 layers of application before spitting it out.
No, considering that it definitely takes longer to load big profiles or big topics, I think they're using the DB (even if not optimally) to filter stuff out. A lot of early decisions they made just don't scale, which isn't the worst thing in the world, and while they've improved stuff, they still have a long way to go.
-
-
-
Thinking about it...a lot of indexes include
WHERE deleted_at IS NULL
. Which is surely influenced by how Jeff loves to delete stuff after a while. Man...fuck that guy.
-
-
Memory hole is best hole.
–
-
But...I think they might filter that by topics that the user looking at your profile can see.
Ran it via my mental Ruby parser - the query looks something like this:
SELECT action_type, COUNT(*) count FROM user_actions a LEFT JOIN topics t ON t.id = a.target_topic_id LEFT JOIN posts p on p.id = a.target_post_id LEFT JOIN posts p2 on p2.topic_id = a.target_topic_id and p2.post_number = 1 LEFT JOIN categories c ON c.id = t.category_id WHERE a.user_id = :user_id AND t.deleted_at is null AND p.deleted_at is null AND p2.deleted_at is null AND NOT COALESCE(p.hidden, false) OR p.user_id = current_user_id AND COALESCE(p.post_type, p2.post_type) IN (:visible_post_types) AND a.action_type NOT IN (#{BOOKMARK}) AND t.visible AND a.action_type <> :pending AND (c.read_restricted IS NULL OR NOT c.read_restricted) GROUP BY action_type
That's one nasty join.
-
-
Maybe his own star hole. I think the technical term is Cranial Rectitus.
-
Because no-one does
big topicsprofile pagesUpdated your statement to reflect the current problematic.
Filed Under. No need to thank me. I take cash or paypal
-
No one uses Discourse enough to trigger big profiles?
-
Please tell me you guys aren't secretly doing another likes-topic only using profile edits....
Filed Under: That would be really stupid and really cool at the same time, though
-
No one uses Discourse enough to trigger big profiles?
Not in JeffLand™.
I'm sure Discourse 1.6 or whatever will be scheduled to 'support profiles for users with lots of posts' once paying customers start to see issues, like they've done with large topics in 1.5 - although I've seen no evidence on fail.d of that appearing.
-
few and far between they be.
sockbot@work:~/restores/2015-11-24$ grep "^CREATE INDEX" dump.sql | cat -n 1 CREATE INDEX by_link ON topic_link_clicks USING btree (topic_link_id); 2 CREATE INDEX by_queue_status ON queued_posts USING btree (queue, state, created_at); 3 CREATE INDEX by_queue_status_topic ON queued_posts USING btree (topic_id, queue, state, created_at); 4 CREATE INDEX idx_notifications_speedup_unread_count ON notifications USING btree (user_id, notification_type) WHERE (NOT read); 5 CREATE INDEX idx_posts_created_at_topic_id ON posts USING btree (created_at, topic_id) WHERE (deleted_at IS NULL); 6 CREATE INDEX idx_posts_user_id_deleted_at ON posts USING btree (user_id) WHERE (deleted_at IS NULL); 7 CREATE INDEX idx_search_category ON category_search_data USING gin (search_data); 8 CREATE INDEX idx_search_post ON post_search_data USING gin (search_data); 9 CREATE INDEX idx_search_topic ON topic_search_data USING gin (search_data); 10 CREATE INDEX idx_search_user ON user_search_data USING gin (search_data); 11 CREATE INDEX idx_topics_front_page ON topics USING btree (deleted_at, visible, archetype, category_id, id); 12 CREATE INDEX idx_topics_user_id_deleted_at ON topics USING btree (user_id) WHERE (deleted_at IS NULL); 13 CREATE INDEX idx_user_actions_speed_up_user_all ON user_actions USING btree (user_id, created_at, action_type); 14 CREATE INDEX idx_users_admin ON users USING btree (id) WHERE admin; 15 CREATE INDEX idx_users_moderator ON users USING btree (id) WHERE moderator; 16 CREATE INDEX index_api_keys_on_key ON api_keys USING btree (key); 17 CREATE INDEX index_categories_on_topic_count ON categories USING btree (topic_count); 18 CREATE INDEX index_category_custom_fields_on_category_id_and_name ON category_custom_fields USING btree (category_id, name); 19 CREATE INDEX index_category_featured_topics_on_category_id_and_rank ON category_featured_topics USING btree (category_id, rank); 20 CREATE INDEX index_color_scheme_colors_on_color_scheme_id ON color_scheme_colors USING btree (color_scheme_id); 21 CREATE INDEX index_digest_unsubscribe_keys_on_created_at ON digest_unsubscribe_keys USING btree (created_at); 22 CREATE INDEX index_directory_items_on_period_type ON directory_items USING btree (period_type); 23 CREATE INDEX index_drafts_on_user_id_and_draft_key ON drafts USING btree (user_id, draft_key); 24 CREATE INDEX index_email_logs_on_created_at ON email_logs USING btree (created_at DESC); 25 CREATE INDEX index_email_logs_on_reply_key ON email_logs USING btree (reply_key); 26 CREATE INDEX index_email_logs_on_skipped_and_created_at ON email_logs USING btree (skipped, created_at); 27 CREATE INDEX index_email_logs_on_user_id_and_created_at ON email_logs USING btree (user_id, created_at DESC); 28 CREATE INDEX index_email_tokens_on_user_id ON email_tokens USING btree (user_id); 29 CREATE INDEX index_group_custom_fields_on_group_id_and_name ON group_custom_fields USING btree (group_id, name); 30 CREATE INDEX index_incoming_links_on_created_at_and_user_id ON incoming_links USING btree (created_at, user_id); 31 CREATE INDEX index_incoming_links_on_post_id ON incoming_links USING btree (post_id); 32 CREATE INDEX index_invites_on_email_and_invited_by_id ON invites USING btree (email, invited_by_id); 33 CREATE INDEX index_message_bus_on_created_at ON message_bus USING btree (created_at); 34 CREATE INDEX index_notifications_on_post_action_id ON notifications USING btree (post_action_id); 35 CREATE INDEX index_notifications_on_user_id_and_created_at ON notifications USING btree (user_id, created_at); 36 CREATE INDEX index_notifications_on_user_id_and_topic_id_and_post_number ON notifications USING btree (user_id, topic_id, post_number); 37 CREATE INDEX index_optimized_images_on_upload_id ON optimized_images USING btree (upload_id); 38 CREATE INDEX index_post_actions_on_post_id ON post_actions USING btree (post_id); 39 CREATE INDEX index_post_actions_on_user_id_and_post_action_type_id ON post_actions USING btree (user_id, post_action_type_id) WHERE (deleted_at IS NULL); 40 CREATE INDEX index_post_custom_fields_on_name_and_value ON post_custom_fields USING btree (name, "left"(value, 200)); 41 CREATE INDEX index_post_custom_fields_on_post_id_and_name ON post_custom_fields USING btree (post_id, name); 42 CREATE INDEX index_post_revisions_on_post_id ON post_revisions USING btree (post_id); 43 CREATE INDEX index_post_revisions_on_post_id_and_number ON post_revisions USING btree (post_id, number); 44 CREATE INDEX index_post_stats_on_post_id ON post_stats USING btree (post_id); 45 CREATE INDEX index_post_timings_on_user_id ON post_timings USING btree (user_id); 46 CREATE INDEX index_posts_on_reply_to_post_number ON posts USING btree (reply_to_post_number); 47 CREATE INDEX index_posts_on_user_id_and_created_at ON posts USING btree (user_id, created_at); 48 CREATE INDEX index_screened_emails_on_last_match_at ON screened_emails USING btree (last_match_at); 49 CREATE INDEX index_screened_ip_addresses_on_last_match_at ON screened_ip_addresses USING btree (last_match_at); 50 CREATE INDEX index_screened_urls_on_last_match_at ON screened_urls USING btree (last_match_at); 51 CREATE INDEX index_site_customizations_on_key ON site_customizations USING btree (key); 52 CREATE INDEX index_top_topics_on_daily_likes_count ON top_topics USING btree (daily_likes_count DESC); 53 CREATE INDEX index_top_topics_on_daily_op_likes_count ON top_topics USING btree (daily_op_likes_count); 54 CREATE INDEX index_top_topics_on_daily_posts_count ON top_topics USING btree (daily_posts_count DESC); 55 CREATE INDEX index_top_topics_on_daily_views_count ON top_topics USING btree (daily_views_count DESC); 56 CREATE INDEX index_top_topics_on_monthly_likes_count ON top_topics USING btree (monthly_likes_count DESC); 57 CREATE INDEX index_top_topics_on_monthly_op_likes_count ON top_topics USING btree (monthly_op_likes_count); 58 CREATE INDEX index_top_topics_on_monthly_posts_count ON top_topics USING btree (monthly_posts_count DESC); 59 CREATE INDEX index_top_topics_on_monthly_views_count ON top_topics USING btree (monthly_views_count DESC); 60 CREATE INDEX index_top_topics_on_quarterly_likes_count ON top_topics USING btree (quarterly_likes_count); 61 CREATE INDEX index_top_topics_on_quarterly_op_likes_count ON top_topics USING btree (quarterly_op_likes_count); 62 CREATE INDEX index_top_topics_on_quarterly_posts_count ON top_topics USING btree (quarterly_posts_count); 63 CREATE INDEX index_top_topics_on_quarterly_views_count ON top_topics USING btree (quarterly_views_count); 64 CREATE INDEX index_top_topics_on_weekly_likes_count ON top_topics USING btree (weekly_likes_count DESC); 65 CREATE INDEX index_top_topics_on_weekly_op_likes_count ON top_topics USING btree (weekly_op_likes_count); 66 CREATE INDEX index_top_topics_on_weekly_posts_count ON top_topics USING btree (weekly_posts_count DESC); 67 CREATE INDEX index_top_topics_on_weekly_views_count ON top_topics USING btree (weekly_views_count DESC); 68 CREATE INDEX index_top_topics_on_yearly_likes_count ON top_topics USING btree (yearly_likes_count DESC); 69 CREATE INDEX index_top_topics_on_yearly_op_likes_count ON top_topics USING btree (yearly_op_likes_count); 70 CREATE INDEX index_top_topics_on_yearly_posts_count ON top_topics USING btree (yearly_posts_count DESC); 71 CREATE INDEX index_top_topics_on_yearly_views_count ON top_topics USING btree (yearly_views_count DESC); 72 CREATE INDEX index_topic_custom_fields_on_topic_id_and_name ON topic_custom_fields USING btree (topic_id, name); 73 CREATE INDEX index_topic_custom_fields_on_value ON topic_custom_fields USING btree (value); 74 CREATE INDEX index_topic_invites_on_invite_id ON topic_invites USING btree (invite_id); 75 CREATE INDEX index_topic_links_on_link_post_id_and_reflection ON topic_links USING btree (link_post_id, reflection); 76 CREATE INDEX index_topic_links_on_post_id ON topic_links USING btree (post_id); 77 CREATE INDEX index_topic_links_on_topic_id ON topic_links USING btree (topic_id); 78 CREATE INDEX index_topic_views_on_topic_id_and_viewed_at ON topic_views USING btree (topic_id, viewed_at); 79 CREATE INDEX index_topic_views_on_viewed_at_and_topic_id ON topic_views USING btree (viewed_at, topic_id); 80 CREATE INDEX index_topics_on_bumped_at ON topics USING btree (bumped_at DESC); 81 CREATE INDEX index_topics_on_created_at_and_visible ON topics USING btree (created_at, visible) WHERE ((deleted_at IS NULL) AND ((archetype)::text <> 'private_message'::text)); 82 CREATE INDEX index_topics_on_id_and_deleted_at ON topics USING btree (id, deleted_at); 83 CREATE INDEX index_topics_on_pinned_at ON topics USING btree (pinned_at) WHERE (pinned_at IS NOT NULL); 84 CREATE INDEX index_topics_on_pinned_globally ON topics USING btree (pinned_globally) WHERE pinned_globally; 85 CREATE INDEX index_uploads_on_id_and_url ON uploads USING btree (id, url); 86 CREATE INDEX index_uploads_on_url ON uploads USING btree (url); 87 CREATE INDEX index_uploads_on_user_id ON uploads USING btree (user_id); 88 CREATE INDEX index_user_actions_on_acting_user_id ON user_actions USING btree (acting_user_id); 89 CREATE INDEX index_user_actions_on_target_post_id ON user_actions USING btree (target_post_id); 90 CREATE INDEX index_user_actions_on_user_id_and_action_type ON user_actions USING btree (user_id, action_type); 91 CREATE INDEX index_user_avatars_on_user_id ON user_avatars USING btree (user_id); 92 CREATE INDEX index_user_badges_on_badge_id_and_user_id ON user_badges USING btree (badge_id, user_id); 93 CREATE INDEX index_user_custom_fields_on_user_id_and_name ON user_custom_fields USING btree (user_id, name); 94 CREATE INDEX index_user_histories_on_acting_user_id_and_action_and_id ON user_histories USING btree (acting_user_id, action, id); 95 CREATE INDEX index_user_histories_on_action_and_id ON user_histories USING btree (action, id); 96 CREATE INDEX index_user_histories_on_category_id ON user_histories USING btree (category_id); 97 CREATE INDEX index_user_histories_on_subject_and_id ON user_histories USING btree (subject, id); 98 CREATE INDEX index_user_histories_on_target_user_id_and_id ON user_histories USING btree (target_user_id, id); 99 CREATE INDEX index_user_open_ids_on_url ON user_open_ids USING btree (url); 100 CREATE INDEX index_user_profile_views_on_user_id ON user_profile_views USING btree (user_id); 101 CREATE INDEX index_user_profile_views_on_user_profile_id ON user_profile_views USING btree (user_profile_id); 102 CREATE INDEX index_user_profiles_on_bio_cooked_version ON user_profiles USING btree (bio_cooked_version); 103 CREATE INDEX index_user_visits_on_visited_at_and_mobile ON user_visits USING btree (visited_at, mobile); 104 CREATE INDEX index_users_on_auth_token ON users USING btree (auth_token); 105 CREATE INDEX index_users_on_last_posted_at ON users USING btree (last_posted_at); 106 CREATE INDEX index_users_on_last_seen_at ON users USING btree (last_seen_at); 107 CREATE INDEX index_versions_on_created_at ON versions USING btree (created_at); 108 CREATE INDEX index_versions_on_number ON versions USING btree (number); 109 CREATE INDEX index_versions_on_tag ON versions USING btree (tag); 110 CREATE INDEX index_versions_on_user_id_and_user_type ON versions USING btree (user_id, user_type); 111 CREATE INDEX index_versions_on_user_name ON versions USING btree (user_name); 112 CREATE INDEX index_versions_on_versioned_id_and_versioned_type ON versions USING btree (versioned_id, versioned_type); 113 CREATE INDEX index_warnings_on_user_id ON warnings USING btree (user_id); 114 CREATE INDEX post_timings_summary ON post_timings USING btree (topic_id, post_number); sockbot@work:~/restores/2015-11-24$
But as pointed out previously, the utility of them may be called into question...
-
If an index is never used in any queries, does it really exist?
<yes I know it does but it seemed like a valid zen-ish question to ask
-
If an index is never used in any queries, does it really exist?
<yes I know it does but it seemed like a valid zen-ish question to ask
Yes, it would still slow down writes. How very Discoursy.
-
Database design 101:
- check how Discourse does it.
- Do literally anything else.
-
Discourse doesn't use an XML database, so it must be better to use.
-
Oracle: sure, we're Oracle, but Discourse doesn't use our DB, so we must be doing something right...
-
Highlights from our issue tracker:
It has a search field.
Whenever you enter or remove a letter from it, it reloads ALL THE DATA from an MS-Access database table. The query for that is concatenated in about 300 lines of VB.NET code. It doesn't escape anything.Booleans are stored in multiple ways:
- Long Integer with 0 for false and -1 for true
- Single with 0 for false and 1 for true
- YES/NO MS-Access boolean type
-
Maybe Little Bobby Tables can fix your performance and design problems?
Filed Under: For generous values of "fix"
-
Well, it's actually faster than Discourse ...
-
I could query the whole production database and fugly-join it to hell and back and probably still beat discourse in a race. Your point? :P
-
Highlights from our issue tracker
You know you're supposed to use a forum for that, right?
-
...and I think this may be the first time I've seen someone top Anonymous:
Top 30 users by Server Load Username Duration Reqs Routes -------- -------- ---- ------ M_Adams 89647.30 7550 users/show(73874.08) topics/timings (POST)(11272.32) user_avatars/show(3887.30) user_avatars/show_letter(285.78) topics/posts(129.57)
@M_Adams, the fuck are you doing?
Then again, it wouldn't be an issue if Discourse DIDN'T MAKE THE PROFILE PAGE TAKE 15 FUCKING SECONDS TO LOAD.
Anyone know what happened here? I've not personally been on site since about 22:00 CST on 22-NOV...
Do I have something in my profile causing "Disco-Quakes" on the server?
-
Change your password, your account may be compromised. By Discourse.
-
-
Wouldn't be surprised...
Done.
-
Do you log in as "M_Adams" or do you just type in "System" and hope for the best?
Filed Under: Maybe M_Adams is a variable name in Discourse!