Tag Archives: MySQL

Database Testing

I am going to use a local and remote mysql database to host the weather information. So I had to think about which variables to store and at 1 minute resolution, how much space would I need to accomplish this, as I want to store the information year on year. So I will store the following information:-

  • Date and Time of the reading
  • Humidity Outside
  • Temperature on the Humidity Sensor
  • Temperature on the Barometric Sensor
  • Humidity Inside
  • Temperature Inside
  • Wind Speed Average over 1 Minute
  • Wind Gusting Speed
  • Wind Lowest Speed
  • Light Full
  • Light Visible
  • Light Infrared
  • Barometric (Pressure)

This works out to 1,440 readings a day (60 minutes x 24 hours). Which is 525,600 readings a year, so making the database as small as possible is critical. For this I will be using the DECIMAL data type, so you can specify exactly how much information to store. So the rough table looks something like this:-

So using a local database on a desktop PC via phpMyAdmin, I used a store procedure to generate the dummy information. First create and store the procedure:-

Note: The dummy database name is “pi”. Then you can just call:-

Which will take a few minutes (~10 mins) to finish populating the database. You’ll notice on the stored procedure that I counted for 366 days, rather than 365, just to simulate what a leap year would be.

Final size on disk of the database is ~61MB. Plus a bit more for the unique index on the time_stamp column.

Issues encountered: 1. Day light saving time. I was originally trying to store the timestamp, but on the 30th March 2014 at 2am I would get 60 rows with exactly the same date and time and since the time_stamp field needs to be unique, this was a massive issue, so to cure the problem, I changed the store value to DATETIME and inserted the utc_timestamp() value, so I will probably need to convert the utc timestamp to localised time allowing for day light saving time if I need to refer to a date and time specifically.

2. Log files. During my testing and repeated procedure running, I managed to generate a >600MB log file. So disabling the log files on the testing and production machine will need to be done. Find the following in your my.ini or my.cnf file:-

find the line “log-bin=mysql-bin” and place a # and space in front of it, this will disable the log file generation.

The next step will be see how fast the hour average calls are for the last 24 to 48 hours for the chart mapping.