Indices on endpoint tables

[g-hodgson-tup]

Hi,

For our installation we increased the endpoint limits, resulting in over 700k endpoint result conditions. This caused sqlite to consume a lot of CPU when it performed cascading deletes via forign constraints.

We found adding the following indices resolved the problem, and they are probably useful regardless of the endpoint limits.

diff --git a/storage/store/sql/specific_sqlite.go b/storage/store/sql/specific_sqlite.go
index 0fe7fa54..f8f6d642 100644
--- a/storage/store/sql/specific_sqlite.go
+++ b/storage/store/sql/specific_sqlite.go
@@ -81,5 +81,27 @@ func (s *Store) createSQLiteSchema() error {
        `)
        // Silent table modifications TODO: Remove this in v6.0.0
        _, _ = s.db.Exec(`ALTER TABLE endpoint_results ADD domain_expiration INTEGER NOT NULL DEFAULT 0`)
+
+       if err != nil {
+               return err
+       }
+       _, err = s.db.Exec(`
+        CREATE INDEX IF NOT EXISTS endpoint_results_endpoint_id_idx ON endpoint_results (endpoint_id);
+       `)
+
+       if err != nil {
+               return err
+       }
+       _, err = s.db.Exec(`
+               CREATE INDEX IF NOT EXISTS endpoint_uptimes_endpoint_id_idx ON endpoint_uptimes (endpoint_id);
+       `)
+
+       if err != nil {
+               return err
+       }
+       _, err = s.db.Exec(`
+               CREATE INDEX IF NOT EXISTS endpoint_result_conditions_endpoint_result_id_idx ON endpoint_result_conditions (endpoint_result_id);
+       `)
+
        return err
 }

Maybe it helps others. I'll happily raise a PR if requested.

[TwiN]

I'd love a PR when you have some time!

Do you query the indice instead of the table for badges and such?

Also, how many endpoints do you have? You mentioned increasing the endpoint limit - do you mean the results (defaults to max of 100)? If so, what's your current max?

I do plan on increasing the maximum number of reaults per endpoint soon, so this is pretty valuable information.

[g-hodgson-tup]

We don't access the tables directly at all. We found that it was sqlite itself that constantly needed a lot of CPU when it was deleting Endpoint Results, i.e. as part of the automatic cleanup logic that calls deleteOldEndpointResults. The "ON DELETE CASCADE" caused sqlite to scan endpoint_result_conditions, which in our case was ~700k

PR is on the way.

Regarding limits, we currently have:

MaximumNumberOfResults = 1000
MaximumNumberOfEvents = 500

and we also increased maximumNumberOfResults & pageSize to 40 in EndpointGroup.vue

[TwiN]

500 events?! I can understand the number of results as this is a regularly requested feature, but the UI must be a sight to behold with a list of 500 events

[g-hodgson-tup]

Well, we only display 40 results per entry at a time, the increased max event count just means we can page back a little further in case we need more historical data.

[TwiN]

FYI, I just made number of results and events configurable in #1110