Need help with site/server tweaking


(Michael) #1

We are using Discourse 2.1.0.beta2 on a docker installation. System OS is Ubuntu Server 16.04 “Xenial Xerus” LTS. Our site has 4.5 million topics. We are getting error, site is lagging seriously while it’s above 450 users in Google Analytics Real Time.

This is the error: “Due to extreme load, this is temporarily being shown to everyone as a logged out user would see it.”.

Dedicated server configuration:
Intel® Xeon® CPU E5-1650 v3 @ 3.50GHz
Cores : 12
RAM: 128GB
Disk: 450GB

app.yml params:
db_shared_buffers: “16384MB”
db_work_mem: “64MB”
UNICORN_WORKERS: 18

If there’s any other info needed, please let me know. Any help/suggestion will be highly appreciated. Thanks.


(Bhanu Sharma) #2

First things first! what is getting overloaded?
checked the logs and sidekiq?


(Michael) #3

Hi Bhanu, thanks for your fast reply. As a new user, I’m unable to attach file, write link and can upload only a single image. Looks like need to paste some info.

nginx error log:

2018/07/01 02:37:31 [error] 77#77: *16525734 upstream prematurely closed connection while reading response header from upstream, client: xxx.xxx.xxx.xx, server: _, request: "GET /search/query?term=What%20are%20C&include_blurbs=true&_=1530411872318 HTTP/1.1", upstream: "127.0.0.1:3000/search/query?term=What%20are%20C&include_blurbs=true&_=1530411872318", host: "domain-name", referrer: "domain-name/t/topic-slug/28182"
2018/07/01 02:44:32 [error] 77#77: *16139249 upstream timed out (110: Connection timed out) while reading response header from upstream, client: 158.69.210.30, server: _, request: "POST /topics/timings HTTP/1.1", upstream: "127.0.0.1:3000/topics/timings", host: "domain-name", referrer: "domain-name/t/other-topic-slug/3283282"
2018/07/01 02:44:34 [error] 68#68: *16550237 upstream prematurely closed connection while reading response header from upstream, client: xxx.xxx.xxx.xx, server: _, request: "GET /search/query?term=Which%20%22C%22%20&include_blurbs=true&_=1530409523281 HTTP/1.1", upstream: "127.0.0.1:3000/search/query?term=Which%20%22C%22%20&include_blurbs=true&_=1530409523281", host: "domain-name", referrer: "domain-name/t/another-topic-slug/4732727"

postgres log:
2018-07-01 15:26:00.025 UTC [28098] discourse@discourse LOG:  duration: 192.533 ms  execute <unnamed>: SELECT "posts".* FROM "posts" JOIN (SELECT *, row_number() over() row_number FROM (SELECT  topics.id, min(posts.post_number) post_number FROM "posts" INNER JOIN "post_search_data" ON "post_search_data"."post_id" = "posts"."id" INNER JOIN "topics" ON "topics"."id" = "posts"."topic_id" AND ("topics"."deleted_at" IS NULL) LEFT JOIN categories ON categories.id = topics.category_id WHERE ("posts"."deleted_at" IS NULL) AND "posts"."post_type" IN (1, 2, 3) AND "topics"."deleted_at" IS NULL AND (topics.visible) AND (topics.archetype <> 'private_message') AND (post_search_data.search_data @@ TO_TSQUERY('english', '''1870'':*ABD & ''and'':*ABD & ''between'':*ABD & ''emigrated'':*ABD & ''europeans'':*ABD & ''how'':*ABD & ''i'':*ABD & ''many'':*ABD & ''the'':*ABD & ''to'':*ABD & ''u.s'':*ABD & ''war'':*ABD & ''world'':*ABD')) AND ((categories.id IS NULL) OR (NOT categories.read_restricted)) AND (post_search_data.post_id < 9795129) GROUP BY topics.id ORDER BY MAX(TS_RANK_CD(post_search_data.search_data, TO_TSQUERY('english', '''1870'':* & ''and'':* & ''between'':* & ''emigrated'':* & ''europeans'':* & ''how'':* & ''i'':* & ''many'':* & ''the'':* & ''to'':* & ''u.s'':* & ''war'':* & ''world'':*'))) DESC, topics.bumped_at DESC LIMIT 51 OFFSET 0) xxx) x ON x.id = posts.topic_id AND x.post_number = posts.post_number WHERE ("posts"."deleted_at" IS NULL) ORDER BY row_number
2018-07-01 15:26:00.211 UTC [28098] discourse@discourse LOG:  duration: 177.772 ms  execute <unnamed>: SELECT  "posts".* FROM "posts" INNER JOIN "post_search_data" ON "post_search_data"."post_id" = "posts"."id" INNER JOIN "topics" ON "topics"."id" = "posts"."topic_id" AND ("topics"."deleted_at" IS NULL) LEFT JOIN categories ON categories.id = topics.category_id WHERE ("posts"."deleted_at" IS NULL) AND "posts"."post_type" IN (1, 2, 3) AND "topics"."deleted_at" IS NULL AND (topics.visible) AND (topics.archetype <> 'private_message') AND (post_search_data.search_data @@ TO_TSQUERY('english', '''1870'':*ABD & ''and'':*ABD & ''between'':*ABD & ''emigrated'':*ABD & ''europeans'':*ABD & ''how'':*ABD & ''i'':*ABD & ''many'':*ABD & ''the'':*ABD & ''to'':*ABD & ''u.s'':*ABD & ''war'':*ABD & ''world'':*ABD')) AND ((categories.id IS NULL) OR (NOT categories.read_restricted)) AND (posts.topic_id NOT in (2324757,2297456)) ORDER BY TS_RANK_CD(post_search_data.search_data, TO_TSQUERY('english', '''1870'':* & ''and'':* & ''between'':* & ''emigrated'':* & ''europeans'':* & ''how'':* & ''i'':* & ''many'':* & ''the'':* & ''to'':* & ''u.s'':* & ''war'':* & ''world'':*')) DESC, topics.bumped_at DESC LIMIT 69 OFFSET 0
2018-07-01 15:26:01.163 UTC [28384] discourse@discourse LOG:  duration: 3778.074 ms  execute <unnamed>: SELECT "posts".* FROM "posts" JOIN (SELECT *, row_number() over() row_number FROM (SELECT  topics.id, min(posts.post_number) post_number FROM "posts" INNER JOIN "post_search_data" ON "post_search_data"."post_id" = "posts"."id" INNER JOIN "topics" ON "topics"."id" = "posts"."topic_id" AND ("topics"."deleted_at" IS NULL) LEFT JOIN categories ON categories.id = topics.category_id WHERE ("posts"."deleted_at" IS NULL) AND "posts"."post_type" IN (1, 2, 3) AND "topics"."deleted_at" IS NULL AND (topics.visible) AND (topics.archetype <> 'private_message') AND (post_search_data.search_data @@ TO_TSQUERY('english', '''-1'':*ABD & ''1'':*ABD & ''4'':*ABD & ''5'':*ABD & ''a'':*ABD & ''above'':*ABD & ''addresses'':*ABD & ''age'':*ABD & ''all'':*ABD & ''and'':*ABD & ''applied'':*ABD & ''are'':*ABD & ''as'':*ABD & ''average'':*ABD & ''b'':*ABD & ''below'':*ABD & ''break'':*ABD & ''breed'':*ABD & ''business'':*ABD & ''c'':*ABD & ''case'':*ABD & ''cell'':*ABD & ''column'':*ABD & ''columns'':*ABD & ''created'':*ABD & ''customers'':*ABD & ''d'':*ABD & ''days'':*ABD & ''default'':*ABD & ''dog'':*ABD & ''dog-walking'':*ABD & ''e'':*ABD & ''excel'':*ABD & ''first'':*ABD & ''for'':*ABD & ''has'':*ABD & ''he'':*ABD & ''his'':*ABD & ''in'':*ABD & ''includes'':*ABD & ''insert'':*ABD & ''inserted'':*ABD & ''is'':*ABD & ''keep'':*ABD & ''name'':*ABD & ''names'':*ABD & ''needed'':*ABD & ''new'':*ABD & ''newly'':*ABD & ''numbers'':*ABD & ''of'':*ABD & ''original'':*ABD & ''owners'':*ABD & ''page'':*ABD & ''phone'':*ABD & ''roger'':*ABD & ''row'':*ABD & ''same'':*ABD & ''select'':*ABD & ''services'':*ABD & ''set'':*ABD & ''spreadsheet'':*ABD & ''the'':*ABD & ''to'':*ABD & ''track'':*ABD & ''walking'':*ABD & ''want'':*ABD & ''week'':*ABD & ''where'':*ABD & ''which'':*ABD & ''width'':*ABD & ''worksheet'':*ABD & ''you'':*ABD')) AND ((categories.id IS NULL) OR (NOT categories.read_restricted)) AND (post_search_data.post_id < 9795129) GROUP BY topics.id ORDER BY MAX(TS_RANK_CD(post_search_data.search_data, TO_TSQUERY('english', '''-1'':* & ''1'':* & ''4'':* & ''5'':* & ''a'':* & ''above'':* & ''addresses'':* & ''age'':* & ''all'':* & ''and'':* & ''applied'':* & ''are'':* & ''as'':* & ''average'':* & ''b'':* & ''below'':* & ''break'':* & ''breed'':* & ''business'':* & ''c'':* & ''case'':* & ''cell'':* & ''column'':* & ''columns'':* & ''created'':* & ''customers'':* & ''d'':* & ''days'':* & ''default'':* & ''dog'':* & ''dog-walking'':* & ''e'':* & ''excel'':* & ''first'':* & ''for'':* & ''has'':* & ''he'':* & ''his'':* & ''in'':* & ''includes'':* & ''insert'':* & ''inserted'':* & ''is'':* & ''keep'':* & ''name'':* & ''names'':* & ''needed'':* & ''new'':* & ''newly'':* & ''numbers'':* & ''of'':* & ''original'':* & ''owners'':* & ''page'':* & ''phone'':* & ''roger'':* & ''row'':* & ''same'':* & ''select'':* & ''services'':* & ''set'':* & ''spreadsheet'':* & ''the'':* & ''to'':* & ''track'':* & ''walking'':* & ''want'':* & ''week'':* & ''where'':* & ''which'':* & ''width'':* & ''worksheet'':* & ''you'':*'))) DESC, topics.bumped_at DESC LIMIT 51 OFFSET 0) xxx) x ON x.id = posts.topic_id AND x.post_number = posts.post_number WHERE ("posts"."deleted_at" IS NULL) ORDER BY row_number

redis log:

53:M 01 Jul 15:27:25.081 * 10000 changes in 60 seconds. Saving...
53:M 01 Jul 15:27:25.101 * Background saving started by pid 7695
7695:C 01 Jul 15:27:26.084 * DB saved on disk
7695:C 01 Jul 15:27:26.099 * RDB: 20 MB of memory used by copy-on-write
53:M 012 Jul 15:27:26.201 * Background saving terminated with success

(Michael) #4

Here’s another image:


(Régis Hanol) #5

Do you have large topics (with > 10K posts)?


(Michael) #6

Hi Régis, thanks for your reply. No, most has below 10 posts. None has above 100 posts.