Raspberry Pi气象站（SQL DB存储和写入表格）

Question

I'm building a weather station based around some basic sensors and a Raspberry Pi. I have a successful working station right now but I'm not sure that the way I have coded and built the station is the right way and most logical. The high level view of how my station works is, Python script reads sensors and assigns the values to variables and then is inserted into a mysql table. A PHP script is running with a select statement to grab my values and display my results in a web-page format that is accessible to anyone on my local network.

The first thing I want to understand is based on my python code, I update my sensor variables every 3s and write the updates into my table. Therefore if I'm wanting to get a history of data, I'm constantly (every 3s) writing to my table. This makes the table large very quickly. If I store data for the whole week, that results in at about 30,240 read/writes per week! Can mysql database handle this large quantity of data and can my SD card handle this many read/writes reliably? (So far, I've tested it for about 1 hour and it seems to work fine).

What makes the most sense in how to write into this database table? I'd like to capture a Max wind speed for the day so that requires me to almost constantly examine my wind speed sensor so that I can get the most accurate reading. I've thought setting up so that I constantly update my variable for wind speed but only update my temperature, humidity and rainfall about every 5 minutes and then write to the table.

Just any general idea's and guidance about how I can improve my system is greatly appreciated. I'm all in this just for learning.

Thanks, Andrew

Answer 1

MySQL can handle that much data -- the constraint will be your SD card, the file system it uses, and how much data it can hold. From this article, navigate to /var/lib/mysql on the Raspberry and run ls -lh to see the size of your database.

As far as the number of read/writes to your SD card, this SuperUser answer would suggest that your calculated number of read/writes wouldn't be an issue.

The way you're handling wind speed sounds about right, and a 5-minute update frequency for your other sensors sounds fine to me as well. Your update frequency really depends on how much data your user (you) expects to see.

I'm working on a similar project using Arduinos, a Raspberry, and Django for the web framework. I'm not writing data to a local database like you, but it's been running well for the past month or so.

If you're concerned with the number of read/writes to your SD card or the size of your local database, or just want to play with something new, you could consider using something like Amazon Web Services to host the database and your web application. That's what I've done and it's been a fun learning experience. I've yet to exceed the free tier with the amount of data that I'm generating.