Database MySQL memory

[stefycraft]

Hi, everyone
I'm working for a project to measure some data with sensor, like temperature, pressure and altitudine, save them to a database MySQL and then send them to a web server (Apache, php). I done all the steps and is all working. I have a few doubts and questions:
1. Where is situated the database? Is it in the Raspberry?
2. Is there a way to consult the data table?
3. How much memory has the database?
4. When the database reaches the max memory, what does it do to the new data?

I'm working with a Raspberry 3B and with a sensor BMP180. I measure tha data every 5 minutes

[W. H. Heydt]

Hi, everyone
I'm working for a project to measure some data with sensor, like temperature, pressure and altitudine, save them to a database MySQL and then send them to a web server (Apache, php). I done all the steps and is all working. I have a few doubts and questions:
1. Where is situated the database? Is it in the Raspberry?
2. Is there a way to consult the data table?
3. How much memory has the database?
4. When the database reaches the max memory, what does it do to the new data?

I'm working with a Raspberry 3B and with a sensor BMP180. I measure tha data every 5 minutes

1. I think you need to learn a bit more about relational databases, of which MySQL is one example. It doesn't keep the data in memory. Rather, it stores it in the file system. When the filesystem runs out of space, the OS will complain, so pick your storage media (SD card, USB stick, SSD, HDD) to have enough capacity for whatever you plan to do. This is not to say that the database *has* to be stored on the Pi. One can use remote databases, so it could be stored--rather literally--anywhere. It's just more common to store the data and run the database engine on the local system. I have, for instance, a pair of machines that run MySQL. One of them is a "hot backup" (reliability and data integrity are very important for the system in question) for the other and does that by running in slave mode, replicating everything that happens to the database on the master machine.

2. Yes. Personally, I used the "mysql" command, in the form of "mysql -p -D <database>" and then type in SQL commands to retrieve, update and delete data from tables as needed. There is also a GUI access (that I've never figured out...but I'm comfortable in a command line environment, so I didn't put that much effort into it) called "phpmysqladmin" (IIRC).

3. It will use as much memory as it needs, subject to physical and OS limits. Don't be concerned about memory usage. If you think you're going to be building large databases (and it doesn't sound like it), put your concerns into how much mass storage space you have.

4. If the database runs the filesystem of of space, pretty much everything is going to come to a halt because *nothing* will be able to write new data...including logging functions of the OS. To give you an idea... I run a convention registration system which probably has far bigger row sizes than you are planning to deal with. I use a separate database for each year. I have data going back to 2003 (it's an annual convention). We get about 1800 members each year. A backup file (mysqldump) of all databases is now up to 8.3MB. I have the systems running on a 60GB SSDs. I won't live long enough to fill that up. You will be writing more, but smaller, table rows. So long as you have a few GB of free space on the root device, you aren't likely to have a problem.

[Burngate]

...
1. I think you need to learn a bit more about relational databases, of which MySQL is one example.
...

Where should he* look for that?
I've tried quite a few books, internet sites, etc. but while most of them are good at describing "how to", none of them seem to want to describe "what's under the hood"

A bit like saying "if you want to go faster, press that pedal. If you want to go slower, press this one" but noone says "when that pedal gets pushed, fuel gets pushed in for longer, and the bang lasts longer"

[stefycraft]

Hi, everyone
I'm working for a project to measure some data with sensor, like temperature, pressure and altitudine, save them to a database MySQL and then send them to a web server (Apache, php). I done all the steps and is all working. I have a few doubts and questions:
1. Where is situated the database? Is it in the Raspberry?
2. Is there a way to consult the data table?
3. How much memory has the database?
4. When the database reaches the max memory, what does it do to the new data?

I'm working with a Raspberry 3B and with a sensor BMP180. I measure tha data every 5 minutes

1. I think you need to learn a bit more about relational databases, of which MySQL is one example. It doesn't keep the data in memory. Rather, it stores it in the file system. When the filesystem runs out of space, the OS will complain, so pick your storage media (SD card, USB stick, SSD, HDD) to have enough capacity for whatever you plan to do. This is not to say that the database *has* to be stored on the Pi. One can use remote databases, so it could be stored--rather literally--anywhere. It's just more common to store the data and run the database engine on the local system. I have, for instance, a pair of machines that run MySQL. One of them is a "hot backup" (reliability and data integrity are very important for the system in question) for the other and does that by running in slave mode, replicating everything that happens to the database on the master machine.

2. Yes. Personally, I used the "mysql" command, in the form of "mysql -p -D <database>" and then type in SQL commands to retrieve, update and delete data from tables as needed. There is also a GUI access (that I've never figured out...but I'm comfortable in a command line environment, so I didn't put that much effort into it) called "phpmysqladmin" (IIRC).

3. It will use as much memory as it needs, subject to physical and OS limits. Don't be concerned about memory usage. If you think you're going to be building large databases (and it doesn't sound like it), put your concerns into how much mass storage space you have.

4. If the database runs the filesystem of of space, pretty much everything is going to come to a halt because *nothing* will be able to write new data...including logging functions of the OS. To give you an idea... I run a convention registration system which probably has far bigger row sizes than you are planning to deal with. I use a separate database for each year. I have data going back to 2003 (it's an annual convention). We get about 1800 members each year. A backup file (mysqldump) of all databases is now up to 8.3MB. I have the systems running on a 60GB SSDs. I won't live long enough to fill that up. You will be writing more, but smaller, table rows. So long as you have a few GB of free space on the root device, you aren't likely to have a problem.

Thank you really much. Actually I'm not studying informatics.... I was working for an embedded project at school and I always liked the side of website and database. On net I found almost, how create mysql and how configure it, but till now I didn't find much much about their location and other details.

[W. H. Heydt]

...
1. I think you need to learn a bit more about relational databases, of which MySQL is one example.
...

Where should he* look for that?
I've tried quite a few books, internet sites, etc. but while most of them are good at describing "how to", none of them seem to want to describe "what's under the hood"

A bit like saying "if you want to go faster, press that pedal. If you want to go slower, press this one" but noone says "when that pedal gets pushed, fuel gets pushed in for longer, and the bang lasts longer"

A book or course on RDBMS theory would be a good start.

[W. H. Heydt]

Thank you really much. Actually I'm not studying informatics.... I was working for an embedded project at school and I always liked the side of website and database. On net I found almost, how create mysql and how configure it, but till now I didn't find much much about their location and other details.

Most people don't spend time worrying about where the data is stored. That's what Database Administraors (DBAs) do. How much you need to concern yourself with those issues depends, in large measure, how heavily the databases are going to be used and what sort of OS you're working with. On mainframes, for instance, one might have to allocate space on specific drives or parts of drives. It might aid performance to spread the database allocation over several drives. If you know the details of how a particular database engine structures its data, one might put data on one drive and indices on another.

On a Pi, it's much simpler. You don't worry about it. You've generally only got one mass storage device, so that is where everything is going to go. It looks like your project will have a single table, so you don't need to spend much effort on picking indices or worrying about table join efficiency. So for you...the database contents reside in the "/" (aka root) filesystem on the only block device you've got. Other than making sure it's big enough to hold as much data as you think you're going to have at any one time, you can leave everything up to the mysql engine and let it worry about how to allocate and organize space.

[DougieLawson]

...
1. I think you need to learn a bit more about relational databases, of which MySQL is one example.
...

Where should he* look for that?
I've tried quite a few books, internet sites, etc. but while most of them are good at describing "how to", none of them seem to want to describe "what's under the hood"

A bit like saying "if you want to go faster, press that pedal. If you want to go slower, press this one" but noone says "when that pedal gets pushed, fuel gets pushed in for longer, and the bang lasts longer"

A book or course on RDBMS theory would be a good start.

Something like https://downloads.mysql.com/docs/mysql- ... -en.a4.pdf while not ideal (skip the installation section in chapter 1) might be a good freebie starting point.

sudo apt install mariadb-common mariadb-client mariadb-server
is all that's needed to get (the MariaDB fork of) MySQL running on Raspbian, no configuration needed.