EE usage ping data times out on GitLab.com in admin settings page

It's good thing we're loading this data asynchronously, but it appears to take longer than 60 seconds to gather all the data for GitLab.com.

The Sidekiq task responsible for sending this data should still work.

Milestone changed to %8.13

Added ~481018 label

We could add a Sidekiq-cron task that generates this data every day and caches it so we don't have to load this on a real-time basis.

/cc: @rdavila

nice suggestion @stanhu, thanks!

Mentioned in issue #1022 (closed)

@stanhu @rdavila if we had a link that would load the json in a new page instead, would we have the same timeout problem?

@regisF Yes, the link would still be bound by the 60-second timeout. Basically, we have to precompute this data at some point. Perhaps we just need to store the last computed value since even doing this once per day could bog down the database.

Thanks @stanhu

Is this task something small enough that we can ship in 8.13?

@regisF Yes.

Reassigned to @stanhu

Mentioned in commit 44b68955

Mentioned in merge request !779 (merged)

I just realized that !779 (merged) will fix the case when the usage data takes longer than 60 seconds to generate, but it will not fix the GitLab.com case where one of the queries--the one to count the number of pushes in the events table--times out due to our 5-minute statement timeout. There is a Sentry log for this: https://sentry.gitlap.com/gitlab/gitlabcom/issues/12327/

We COULD disable (or lengthen) the statement timeout for these queries, but somehow that doesn't quite feel right. What do you think @pcarranza?

It doesn't feel right to me to have exceptions for this kind of things.

Do we know what is the reason behind this statement taking so long? should we consider doing some form of aggregation as a pre-processing?

cc/ @yorickpeterse

@pcarranza The code is this:

Event.code_push.count

Which translates into this query:

SELECT COUNT(*) FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND "events"."action" = $1

I think this requires a full table scan, which is why it's taking so long.

No index for action?

@stanhu is the work on this done?

@pcarranza Ahh...burned by default scopes again!

# explain SELECT COUNT(*) FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND "events"."action" = 5;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Aggregate  (cost=4353389.58..4353389.59 rows=1 width=0)
   ->  Seq Scan on events  (cost=0.00..4300864.40 rows=21010072 width=0)
         Filter: ((author_id IS NOT NULL) AND (action = 5))
(3 rows)

Whereas:

# explain SELECT COUNT(*) FROM "events" WHERE "events"."action" = 5;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2639146.76..2639146.77 rows=1 width=0)
   ->  Index Only Scan using index_events_on_action on events  (cost=0.00..2586621.58 rows=21010072 width=0)
         Index Cond: (action = 5)
(3 rows)

Mentioned in commit 9031a4de

Mentioned in commit 2b6b8643

Status changed to closed by merge request !779 (merged)

We need to remove the default scope...

Mentioned in commit 71bffb9f