Database Use Cases - At Home Experience

[opensourcefan]

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]

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....

 

[i386]

You could set up a local copy of wikipedia, the software (mediawiki) uses php and mysql/mariadb

 

[oldpenguin]

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

 

[opensourcefan]

I asked Bing AI my initial question and it came up with this: 20 Fun (and Unique) Data Analyst Projects for Beginners in 2023

It also suggested this COVID 19 database:

Novel Corona Virus 2019 Dataset

Day level information on covid-19 affected cases

www.kaggle.com

 

[opensourcefan]

Had no idea Google had a database search engine:

Dataset Search

datasetsearch.research.google.com

 

[unwind-protect]

Host a forum and explore its table structure.

 

[T_Minus]

Once your brain starts down the path you'll always want your own database... or 5

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

 

[zac1]

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]

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.

 

[opensourcefan]

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]

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]

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]

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]

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.