Rework analytics handling to fix performance issues on large datasets

[jakkuh]

Summary

Long time shlink user here with 10s of millions of recorded visits. Due to the design of the app, the visits/orphan visits counts, along with the link visits counts either never load or take forever to load. https://imgur.com/94hIXTx

Upon doing some research it appears that every time the admin page is loaded a direct database query is run (regardless of if using roadrunner or a web server) to count these entries. On our large dataset with a reasonably cheap VPS (4 core, 8GB ram) this takes over 2 minutes to complete.
localhost | shlink | Query | 147 | executing | SELECT COUNT(v0_.id) AS sclr_0 FROM visits v0_ WHERE v0_.short_url_id IS NOT NULL AND v0_.potential_bot = 0

A far more optimal way to do this would be to store the counts in the database and then query those, for both the global count and individual shortlinks.

In a perfect world, those counts would be iterated on each visit, and then checked for accuracy by doing a full database count on a scheduled basis. It could also just be the latter.

Use case

Visit counts never loading, and sorting short links by visit count taking forever.

[acelaya]

It's funny that you request this precisely now, because I happen to have just implemented it 😅

You can find more context on this other issue #2036

And these PRs:

• Introduce the logic, and use it when calculating short urls visit counts: Feature/slotted counts #2074
• Take advantage of the new logic to drastically improve performance when calculating visits by tag: Improve tags stats performance by using the new short_url_visits_counts table #2078

The overview counts are the only ones missing, but it shouldn't be super hard to implement now.

I did my testing with ~1M visits, 10k short urls and 20k tags, and everything was around 20x faster in average.

[acelaya]

This is now fully implemented.

[jakkuh]

Sweet. Release soon? :D @acelaya

[acelaya]

Shlink 4.1.0 has just been released.