Database Use Cases - At Home Experience

Notice: Page may contain affiliate links for which we may earn a small commission through services like Amazon Affiliates or Skimlinks.

opensourcefan

Member
Aug 24, 2022
65
22
8
I'm struggling to figure how best to create a situation where I can deploy a MariaDB or MySQL instance. I just want to learn a little bit as they appear to be a mainstay in the network world and I'm a deer in headlights with them.

Any suggestions for at home, non commercial, non self hosted website use cases?

Maybe using metrics from my network machines and instances as data? Is there a such thing as importing a database that may be useful, like birds of the world or something? Rocks, Stars...
 

amalurk

Active Member
Dec 16, 2016
311
116
43
102
While default Home Assistant uses SQLite you can have it use MariaDB/MySQL instead for its database. There are also lots of self-hosted apps that use a databases that do things like organize media, calendaring, Wiki etc....
 

oldpenguin

Member
Apr 27, 2023
30
10
8
EU
Some monitoring tools can use it as a database backend, with more or less success (zabbix, cacti, pretty sure others). Freeradius can use it too. I'm sure there's a plethora of other cases even for a home environment.
If you have 3 different machines able to do virtualization (well, who knows?), you can keep your data prety much downtime safe with Percona's XtraDB cluster implementation (I find it a bit more reliable compared to mariadb+galera). Not saying it's completely headache free, but you said you wanted to learn :cool:
 

T_Minus

Build. Break. Fix. Repeat
Feb 15, 2015
7,625
2,043
113
Once your brain starts down the path you'll always want your own database... or 5 :D :D

Historic weather data from your own sensors is another use-case. If you have enough data you can check trends by time of day, month of year... compare different time periods, etc.. really basic but useful use cases with very clear, easy to read data in the database :)
 
  • Like
Reactions: opensourcefan

zac1

Well-Known Member
Oct 1, 2022
432
358
63
At some point, you may wonder why the vast majority of applications shoehorn their models into a quasi-relational schema, start researching other DBMSs and formalisms, fall down the rabbithole of graphs, hypergraphs, metagraphs, and embark on a fool's journey to do something different. Or maybe that's just me.
 

newabc

Active Member
Jan 20, 2019
465
243
43
If real world big data, there is a database category called time series database.

Usually a top tier time series database allows >100k rows per second continuously writing to the database(a real world example is 900k/s on peak, 200k/s regularly), and < 20 seconds query time for 1 year's data for the 1st time query and drop to < 5 seconds (sometime < 0.5s)for the cached query result.

Financial and logistics industry use these a lot. The time series databases used on financial areas are much faster than above, but usually they are closed source. The above performance numbers are from a logistics industry case on an open source time series database.
 
  • Like
Reactions: opensourcefan

opensourcefan

Member
Aug 24, 2022
65
22
8
I was also thinking about using Scrapy and doing some specific web scraping.

Data data data, I need data, feed me!

I have a feeling this might get bad.
 

newabc

Active Member
Jan 20, 2019
465
243
43
Another real world example for text searching with grep, not database, no pre-indexing, from Pudding(yuheng.io):
The wikipedia.org in Chinese language, XML format with marks originally from wikipedia, total 10Gbytes size after decompression, using grep with regular expressions, < 1 minute, on a M1 macbook pro.

My comment: This speed is relying on the speed of the file system and the hardware of what this file system is on.
 

DavidWJohnston

Active Member
Sep 30, 2020
242
188
43
I run Arkime (network traffic collection) on my WANs, which creates a huge amount of metadata for IP flows. You could do the same, and write a script to extract a bunch of this metadata and put it into a SQL database using a schema of your own design.

This could be cool because you could run queries to learn about your own network traffic, like GROUP BY port, hostname, IP, aggregate sum of size of each, etc.

Then you'd be learning about packet capture, metadata, and what IP flows look like. You could even automate loading fresh data and aging-off old data using table partitions, and you'd have a real living database instead of something static.
 

opensourcefan

Member
Aug 24, 2022
65
22
8
I've decided to give NetBox a try. It looks to be an amazing piece of software. Judging by what I've seen so far it's very thoughtfully designed. More importantly for this topic, it is PostgreSQL based, which from what I understand is a slightly more complex, object based DB vs MySQL.
 

opensourcefan

Member
Aug 24, 2022
65
22
8
Wow, just wow. I've spent hours every day plugging my network info into NetBox, still lots to go. However I've played in the CLI to take a look at the db structure but recently just found dbeaver-ce which is an amazing GUI.

Sorry for the excitement but this is cool.
 

ipkpjersi

New Member
Sep 14, 2018
9
4
3
Personally, I'd say a wiki like mediawiki is the way to go. That way, you could even document your home setup/topology and have it all in one location, it'd serve multiple purposes that way. Although I suppose you could do the same with like knowledgebases on a forum.

Then again, there's nothing to say you couldn't run multiple websites like a blog, wiki, and forums and just examine and play around with the database structure of them, maybe even look into backing them up with like mysqldump, etc.