Today I checked the number of values recorded in the database using the smart home system.

At the moment, there are already 33 million of them. This is certainly a lot, during this time I recorded completely different sensors, recorded different values, some sensors write values once a minute, some even more often.

When saving to the database, I also calculate the energy consumed, if the sensor can consume energy, then I have made a formula that determines the period of change of the sensor, I indicate what consumption of this sensor is per hour, and thus it constantly records the value of the energy consumed for this period. At the same time, I can then build graphs to see how much energy was consumed by this sensor, or by this device, more precisely for the entire period of use, by days, by minutes, by seconds, in hours, by months, which in principle is quite convenient.

Of course, the benefits of writing to the database are quite large, because first of all, it is better to start recording the value before any automation is done, so that you can then compare the differences between how, say, just people use it, and how it works automatically. For example, a recent analysis showed that after automating the light in the kitchen, it began to work, turn on and on much more often, especially I use three lamps located on the ceiling and one of them is central. It turns on much more often than it did before, but the energy consumption itself has fallen by about two times.

Currently, LED lamps are very economical and of course this difference is small, that is, in fact, it is only 500 watts per month, so it is very often said that in principle you can not turn off the light at all, but this is certainly inconvenient. It is inconvenient to sleep and in principle, when you are in the room, you also sometimes want the light to be more muted, for this you can use dimmers, I just turn on or off the light.

Most of the values come from my video cameras. At some point in time, I discovered that the size of the saved frame itself, I save a frame from the camera every one minute and also save the image itself, I have several cameras and each camera records the value in the database. The database records the size of the image in bytes, that is, in fact, it is saved in JPEG and then I check the file size. For a small image, if the camera supports multiple streams, then also for a large image. Observation shows that at night, cameras compress the jpeg image much more strongly, and in bright light, the detail is higher and, accordingly, the file size is larger, so you can easily determine the brightness in the room by the size of the image.

I use this to control the brightness of the dashboard, which shows the value of sensors. This value shows how the illumination of the room changes when morning, afternoon, evening comes, when people are present in the room, when the curtains open, and by the value of these parameters, you can plan some new automation.

I record from the camera every minute and in general it turns out to be a very large stream for a month, so I'm considering reducing the number of records, but again, this information is first needed quickly and I have it recorded every minute so that I can perform a certain scenario, but at the same time I need to have the database values were stored with some rarer frequency, for example, once every five minutes. In principle, this will be enough to do some analysis, but greatly reduce the size of the database.

Also, one of the parameters that saves a lot of the base value is energy consumption. I have set up a self-made electricity meter and it sends the value once a minute, or when the value changes significantly, for example, when the heater, kettle, stove or other devices that consume a lot of energy are turned on. For example, there are devices that constantly change energy consumption, such as a washing machine, and it starts to change the value of energy consumption very much and often during its operation, and the sensor starts saving a lot of values to the database. Therefore, I limited the minimum time on it to 10 seconds. Thus, of course, I also reduce the recording and even on average, if you look at it, it turns out somewhere once a minute, that is, periods of operation of some devices or during the day it can be more often, but if there are a few changes, then the value does not change very often and on average it turns out, as I said once per minute.

I also record the values of the smart home server itself in the database. Many times I encountered a situation where it hung for an unknown reason, and of course it is very convenient to track changes in all parameters, such as RAM, disk space parameters, temperature and processor load parameters. I use two servers, one as a smart home server, the other as an auxiliary server, and it has a media player that works as a multiroom system for me. I also recorded it once a minute before, and now I record it every five minutes on the smart home server, and I also plan to redo it on the multimedia server.

In general, I can't say that 32 million or 33 million records is a lot the database itself works very well it is located on an external hosting and on this hosting I also made a website through which I can view this data.

I use Google Chart charts and use them to build the dashboards I need, which show the average value of parameters at certain moments, that is, for example, by day, and I can also see the maximum and minimum values, I can also see the number of records made during the period and energy consumption, which I also calculate. I can display or the entire period of operation of the smart home system the value by day for the sensor and it turns out in principle quite quickly, and I have made it so that I can display one graph, or I can display four graphs at once with different configurations. Of course, the design of these graphs is not very nice, not very modern, but I made additional buttons for convenience so that you can switch parameters quickly and this greatly facilitated the work and you can safely analyze large amounts of data by parameters.

I recently added an additional feature that allows you to plot deltas by month and show, for example, water or electricity consumption. I can build the charts themselves as linear or as bars, which is sometimes convenient for you, and I can quickly switch modes and analyze them in different ways.

Today I made an upload from the database of the number of values recorded for each sensor, I plan to analyze and further configure the database recording at certain intervals, in order to significantly reduce it, of course. Over the past six months, I have almost doubled or tripled the amount of data recorded, which is of course convenient and useful, and I can already clearly see how behavior and consumption are changing during automation.

For example, if I see that my water meter is constantly increasing dramatically, then I can understand who uses water at what time and, of course, decide how to get around this problem. Maybe explain that you don't need to spend a lot of water or something.

Also, if somewhere I see that the light is constantly turned on and I still have no automation there, I can go ahead and make automation of the light control in this room.

Automation in a smart home should be useful and create additional comfort, but sometimes everything goes wrong… The reasons for this, and how to configure everything, see on my channel.