Post.calculate_avg_time() taking up a long time

Support
21

@Falco I don’t want this lost forever can you do a quick PR to shift the WHERE clause up, I think this is safe enough and will not do any damage as far as I can tell.

3 Likes
23

The SQL changes suggested here are live now.

7 Likes
24

Looks like it went from ~ 85s to 35s here in Meta. In another instance (which runs in our beefier bare metal hosting) it went from 1350ms to 350ms.

7 Likes
25

niiiice-gif-2
niiiice-gif-2.gif2326×1599 1.33 MB

3 Likes
26

Is it possible that changes cause performance issue ?
Our instance is running slow, and I had to cancel a query to get discourse running again (but still looks slow).
The query is :

172 | 02:07:54.107363 | UPDATE posts +| active
| | SET avg_time = (x.gmean / 1000) +|
| | FROM (SELECT post_timings.topic_id, +|
| | post_timings.post_number, +|
| | round(exp(avg(CASE WHEN msecs > 0 THEN ln(msecs) ELSE 0 END))) AS gmean +|
| | FROM post_timings +|
| | INNER JOIN posts AS p2 +|
| | ON p2.post_number = post_timings.post_number +|
| | AND p2.topic_id = post_timings.topic_id +|
| | AND p2.user_id <> post_timings.user_id +|
| | WHERE (p2.topic_id IN (SELECT id FROM topics where bumped_at > '2019-05-03 09:39:22.717153'))+|
| | GROUP BY post_timings.topic_id, post_timings.post_number) AS x +|
| | WHERE (x.topic_id = posts.topic_id +|
| | AND x.post_number = posts.post_number +|
| | AND (posts.avg_time <> (x.gmean / 1000)::int OR posts.avg_time IS NULL)) |

We have a huge database with lots of topics containing 10k+ messages. We are trying to split them but cannot succeed to.

1 Like
27

My changes affected only the daily query, which operates over posts from the last two days.

3 Likes
28

I can see the date in the where clause of the query. But since our last discourse update we have performance issue. That query was running for two hours when I cancelled it.
Don’t you think it’s related ? Should I create a new topic in support ?

29

I will be removing this whole job in the interim, we are getting very low amounts of value out of this data and need to rethink this subsystem.

EDIT:

done per

https://github.com/discourse/discourse/commit/f8eddd40ade5a62cd5f9349b71a2d1b60e3c7d2e

Closing this as this topic is no longer applicable.

8 Likes