
Simple self-hosted SQLite powered server side analytics without Google - ilovefood
https://funnybretzel.com/self-hosted-analytics-using-sqlite-and-metabase/
======
rudasn
This is great. I really like the simplicity of it.

How does sqlite handle the streaming load/frequent writes per second? What
hardware does it run on?

I recently did something similar for server monitoring, after spending
days/weeks trying out different stacks and services (datadog, netdata, and the
like).

Like you, I don't have huge scale issues (<100 servers) and I don't need to
monitor every single aspect. A few bash scripts, a couple of systemd services,
a node/express server at the receiving end, in-memory data storage and
sms/email alerts when the most recent metrics look a bit off.

Much cheaper, less resource intensive, and easier to maintain by far.

~~~
ilovefood
Thanks for the feedback, I really appreciate it.

I am running it on the DEV1-S machine from Scaleway. It's 2 vCPUs and 2 GB
Ram, not much on the compute side really. So far it peaked at 150 writes per
second. Which is not a lot but there are solutions to remediate in case it
grows and to be honest I heard SQLite could go as far as 50k inserts per
second (never tested)

I think this works well because I'm blocking a lot of bots using this solution
as well: I query weird requests from the sqlite database and pipe the output
to an ipset blacklist shared accross my servers.

That's correct, I decided to share this approach since it benefited me and is
really "lightweight" so to say. Again, glad you liked it.

What are you using for the SMS/Email alerting?

~~~
rudasn
Cool.

For sms I'm using a local provider, for email just an alerts@... account on
our company's email (via Google). The emails ended up in spam, but after
creating filters on the receiving email address they get flagged as non spam.

We were using send grid for some other things but their bounce rate wasn't
very reassuring, so I preferred the Gmail approach for now (it always works).

------
sails
This is very cool. I'd be interested in a docker-compose script that wrapped
this all into a single deployment, as it seems you have created a very nice
self-hosted with analytics solution.

I do wonder if Metabase is too resource intensive for this purpose, and
perhaps a more lightweight data analytics resource might not be better suited?
I say this because I usually run metabase on its own server. Perhaps not an
issue, more a question.

