3 ms·

Simple self-hosted SQLite powered server side analytics without Google

4 points by ilovefood 6y ago

rudasn 6y ago
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 6y ago
  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 6y ago
    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 6y ago
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.

4 points by ilovefood 6y ago

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 6y ago
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 6y ago
  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 6y ago

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.