transparently splitting a single table so the program doesn't notice

Question

I am using homeassistant with mariadb. This is hosted on a Pi 4 with 8gb of RAM I have been collecting data a long time, and a single table grows to 20gb. The storage is a M.2 NVMe via USB 3. CPU usage is normal less than 20 but climbs to 40%. A few times a day it briefly spike. Memory usage has never been above 2gb with more than 6gb free.

Right now 350 (approx) sensors periodically record data to a single table.

Issues to solve:

1. performance gets slower over time
2. Too much data in table creates single point of failure. All my data can be gone with a single error.
3. Backups are slow and errors occur because the entire table is locked during a backup and new data can't be added to the table when it locked.

Solution I can up with

1. Split table by date (1 table per month)
2. Split table by sensor number. (Each sensor has its own table)
3. Ask community for alternative solutions.

Regarding solution 1

All sensors data is combined so even if I only ask for 12 sensors it has to sift through 350 worth of data. When I add senors, the table will continue to grow. Will it grow to be a problem? hope not.

Regarding solution 2

Will having 350 tables (maybe 500 in the future) cause performance issues of its own even though most table are less frequently used.

Implementation

I envision creating a view and/or trigger,procedures. I would create a view with the existing table name and use that to split the data.

In either solution 1 or 2 how would I identify and query only the necessary tables transparently using a view or etc. When homeassistant executes a query it should return all relevant data as if all the data was in a single table.

Using solution 1, The date would have to be extracted so that if the data range exceed a single month the from would have to be altered to include all relevant months. Instead of select * from table where data is between ... I would need that but then the from would be from 062023,052023,042023 (or whatever is relevant)

Using solution 2, For example if homeassistant asks select * from table where sensors =1,2, 3,5,9,11 then query would need to be altered to select * from 1,2,3,5,9,11;

Would using a view allow me to make the necessary changes transparently?

Is there another solution I had overlooked.

Answer 1

Seems like adding 2-3G to the innodb_buffer_pool_size will help performance, as will increasing the innodb_log_file_size to at least 2G. If these don't significantly help performance, raise the issue with upstream homeassistant. Implementing your own mechanism without integration will probably break somethimg.

Looks like a mariadb backup mechanism isn't used by homeassistant. There are non-locking mechanisms here that can perform live backups. A replication replica for the sole purpose of backup with eliminate storage based failure and provide a service that can be interrupted during backup.

Answer 2

This primary key helps with many of the issues you present.

PRIMARY KEY(sensor_id, ts)

(where ts is whatever timestamp you have -- and is unique per sensor)

I don't understand your "too much data" issue. Do you fear running out of disk space? That would lead to a crash, not loss of existing data. Yes, you could not collect more data until the issue was resolved.

Will you be purging "old" data? If so, PARTITION BY RANGE(TO_DAYS(ts)) as discussed in Partition Otherwise, Partitioning is unlikely to help with any performance issues.

Since the data is essentially write-once, you could manipulate partitions in complex ways to avoid dumping old partitions, but that would messy. Instead, suggest you do mysqldump with a WHERE clause to pull out only one hour (or day or whatever) at a time. The data would not be "ready to reload", but at least it would not be lost. Consider, instead, storing the data into a flat file before inserting to the database. If you write it as a CSV, then it (or one file per day) would be ready for LOAD DATA to reinsert.

Multiple, manually maintained, tables is a big no-no. That sentiment has been stated many times in this and other forums. Partitioning, especially by time, is a common (and viable) alternative. Partitioning provides the "View" semantics transparently.

innodb_buffer_pool_size should be about 5G for your 8GB Pi, assuming no other apps are running.

Another tip: Minimize datatype sizes. Eg, sersor_id (for 500 sensors) could be a 2-byte SMALLINT UNSIGNED.