Abbiamo recentemente registrato un afflusso di utenti e un picco di traffico, il che ha portato a un maggior numero di ricerche eseguite simultaneamente.
Stiamo iniziando a riscontrare timeout nelle ricerche su circa 1,2 milioni di post.
Cosa stanno facendo i forum più grandi a livello di ricerca? Siete passati oltre la funzionalità di ricerca core di Discourse o sta ancora funzionando bene? Avete aggiornato l’algoritmo di ricerca o le query per renderle più restrittive o specifiche? Avete effettuato ottimizzazioni del database?
Quali sono le specifiche del tuo server e quali risorse sembrano essere più limitate?
Sto valutando la possibilità di spostare una community con circa 20 milioni di post (post pubblici e conversazioni private), quindi sono molto interessato a conoscere eventuali problemi di prestazioni legati a community di grandi dimensioni.
C’è un bel salto da 1 milione di post a 20 milioni, quindi ci sono poche indicazioni che possano valere per entrambi. A titolo informativo, ho un sito con 9 milioni di post che offre prestazioni accettabili su un i7-7500U @ 2,7 GHz con 16 GB di RAM. Sullo stesso server ci sono anche alcuni altri siti a basso volume.
Un sito che utilizza un database AWS con 8 GB di RAM e 4 milioni di post sta riscontrando alcune difficoltà, soprattutto nelle ricerche.
@Jumanji, quali sono le specifiche del tuo server?
Siamo su AWS. I forum sono in esecuzione su 4 istanze, credo t3.large, quindi 4 vCPU e 16 GB di RAM. Stiamo eseguendo Kubernetes su un’immagine Docker personalizzata. Il nostro database è su un’istanza separata, m4.large, quindi 2 vCPU e 8 GB di RAM. La RAM sulle istanze del database potrebbe sicuramente essere il collo di bottiglia.
Le query che stanno andando in timeout sono generalmente termini più generici, parole singole. Alcune hanno superato i 60 secondi. Ora, abbiamo anche visto un afflusso di traffico sul nostro sito web nell’ultimo mese. Il mese scorso, il traffico rispetto al mese precedente è aumentato del 40%. Inoltre, durante questo periodo siamo passati dalla versione 1.9 alla 2.4, e so che dalla 1.9 la ricerca ha aggiunto la ricerca con completamento automatico, quindi immagino che questo metta più carico sul server del database.
Abbiamo effettuato un’analisi delle query e la seguente query è stata eseguita in un tempo compreso tra 20 e 60 secondi:
`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.visible)
AND (topics.archetype <> 'private_message')
AND (post_search_data.search_data @@ TO_TSQUERY('english', '''a'':*ABD & ''price'':*ABD'))
AND (categories.id NOT IN (SELECT categories.id WHERE categories.search_priority = 1))
AND ((categories.id IS NULL) OR (NOT categories.read_restricted))
GROUP BY topics.id
ORDER BY
MAX((
TS_RANK_CD(
post_search_data.search_data,
TO_TSQUERY('english', '''a'':*ABD & ''price'':*ABD'),
1|32
) * (
CASE categories.search_priority
WHEN 2
THEN 0.6
WHEN 3
THEN 0.8
WHEN 4
THEN 1.2
WHEN 5
THEN 1.4
ELSE
CASE
WHEN topics.closed
THEN 0.9
ELSE 1
END
END
)
)) DESC,
topics.bumped_at DESC
LIMIT 6
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;
---
Sort (cost=495214.55..495214.56 rows=1 width=1177) (actual time=20529.725..20529.727 rows=6 loops=1)
Sort Key: (row_number() OVER (?))
Sort Method: quicksort Memory: 29kB
-> Nested Loop (cost=495164.08..495214.54 rows=1 width=1177) (actual time=20525.899..20529.703 rows=6 loops=1)
-> WindowAgg (cost=495163.65..495163.80 rows=6 width=16) (actual time=20520.976..20521.078 rows=6 loops=1)
-> Limit (cost=495163.65..495163.66 rows=6 width=24) (actual time=20520.969..20521.063 rows=6 loops=1)
-> Sort (cost=495163.65..495232.24 rows=27438 width=24) (actual time=20520.967..20520.969 rows=6 loops=1)
Sort Key: (max((ts_rank_cd(post_search_data.search_data, '''price'':*ABD'::tsquery, 33) * (CASE categories.search_priority WHEN 2 THEN 0.6 WHEN 3 THEN 0.8 WHEN 4 THEN 1.2 WHEN 5 THEN 1.4 ELSE CASE WHEN topics.closed THEN 0.9 ELSE '1'::numeric END END)::double precision))) DESC, topics.bumped_at DESC
Sort Method: top-N heapsort Memory: 25kB
-> GroupAggregate (cost=493642.90..494671.83 rows=27438 width=24) (actual time=19082.214..20506.763 rows=32951 loops=1)
Group Key: topics.id
-> Sort (cost=493642.90..493711.50 rows=27438 width=400) (actual time=19082.184..19283.907 rows=191436 loops=1)
Sort Key: topics.id
Sort Method: external merge Disk: 77632kB
-> Hash Left Join (cost=36655.60..486646.69 rows=27438 width=400) (actual time=1562.696..18611.724 rows=191436 loops=1)
Hash Cond: (topics.category_id = categories.id)
Filter: (((categories.id IS NULL) OR (NOT categories.read_restricted)) AND (NOT (SubPlan 1)))
-> Gather (cost=36645.63..486471.60 rows=58991 width=400) (actual time=1562.623..18249.349 rows=191436 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=35645.63..479572.50 rows=24580 width=400) (actual time=1556.547..18541.793 rows=63812 loops=3)
-> Hash Join (cost=35645.20..328688.61 rows=157831 width=25) (actual time=1551.912..13356.416 rows=285279 loops=3)
Hash Cond: (posts_1.topic_id = topics.id)
-> Parallel Seq Scan on posts posts_1 (cost=0.00..286245.92 rows=504559 width=12) (actual time=0.280..11249.160 rows=404770 loops=3)
Filter: ((deleted_at IS NULL) AND (post_type = ANY ('{1,2,3}'::integer[])))
Rows Removed by Filter: 21884
-> Hash (cost=33938.80..33938.80 rows=92912 width=17) (actual time=1549.103..1549.103 rows=80351 loops=3)
Buckets: 65536 Batches: 2 Memory Usage: 2557kB
-> Seq Scan on topics (cost=0.00..33938.80 rows=92912 width=17) (actual time=0.010..1492.606 rows=80351 loops=3)
Filter: ((deleted_at IS NULL) AND visible AND ((archetype)::text <> 'private_message'::text))
Rows Removed by Filter: 216751
-> Index Scan using posts_search_pkey on post_search_data (cost=0.43..0.96 rows=1 width=383) (actual time=0.017..0.017 rows=0 loops=855836)
Index Cond: (post_id = posts_1.id)
Filter: (search_data @@ '''price'':*ABD'::tsquery)
Rows Removed by Filter: 1
-> Hash (cost=9.43..9.43 rows=43 width=9) (actual time=0.059..0.059 rows=43 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on categories (cost=0.00..9.43 rows=43 width=9) (actual time=0.007..0.045 rows=43 loops=1)
SubPlan 1
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=191436)
One-Time Filter: (categories.search_priority = 1)
-> Index Scan using index_posts_on_topic_id_and_post_number on posts (cost=0.43..8.45 rows=1 width=1169) (actual time=1.435..1.435 rows=1 loops=6)
Index Cond: ((topic_id = topics.id) AND (post_number = (min(posts_1.post_number))))
Filter: (deleted_at IS NULL)
Planning time: 3.508 ms
Execution time: 20541.411 ms
(46 righe)`