Hi!
I have configured my persistence to write the data to a MariaDB that is running on a Synology NAS. I want to 1) lower the load for permanent write accesses on the NAS and 2) cache the data if the NAS is not available. Optimally only write the persistence data to the NAS only once a week.
Is this possible somehow?
Thanks in advance!
Christoph
No.
And if write load is an issue, you should really really be redesigning your system setup.
Just use the standard rrd4j on ZRAM. Faster, less dependencies.
FWIW you can have multiple persistence services in parallel.
First let me say that with the information provided thus far I agree with @mstormi, this sounds like a bad idea.
However, I do see a way to achieve your two stated requirements: cache the data and only write once a week.
OH 4.2 introduced an inmemory persistence service. So you could write to this inmemory service by default and then have a rule that dumps the new records written there and pushes the to MariaDB. Of course you’ll need to then delete those records once they have been transfered or else you’ll run out of RAM eventually.
Of course, if your OH shuts down or crashes you’ll lose everything in the “cache”. Your charting will be pretty much unusable also. And any use of query based persistence actions will have to be smart enough to first try the inmemory engine and then try the MariaDB engine and combine the results. restoreOnStartup is simply not going to work at all.
In short, it’s technically feasible to meet your two requirements, but it will break pretty much everything else about persistence.
I believe there might be some settings on MariaDB to cache transactions before it saves them to disk, but you’re not going to get a week out of that.
Isn’t a NAS’s whole job to write data to disk? If a NAS can’t keep up with the writes to a database it seems like it’s a pretty poor NAS. Maybe you need to run MariaDB somewhere else with a cron job to backup the DB contents to the NAS once a week. That reduces the load, caches the data, and only writes once a week while still keeping everything about persistence working.
This can be done easily with a simple cron job. I use the following to backup my PostgreSQL databases once a day (note I don’t use PostgreSQL for OH but I do use it for Zabbix, Nextcloue, Paperless, Manyfold, Vaultwarden, Heimdal, and Guacamole).
#!/bin/bash
echo "Backing up PostgreSQL"
file=/srv/backups/postgres/postgres-$(date +%Y-%m-%d_%H%M).gz
cd /srv/postgres
docker exec postgres /bin/bash -c "export PGPASSWORD=mypassword && /usr/bin/pg_dumpall -U postgres" | gzip -9 > $file
fsize=$(ls -lh $file | cut -d ' ' -f 5)
body=${file}'\nBackup size: '${fsize}
sendmail=/usr/sbin/sendmail
email=myemail@domain.org
to='To: '$email'\n'
from='From: '$email'\n'
subject='Subject:PostgreSQL Backed Up\n\n'
body=${body}
msg=${to}${from}${subject}${body}
echo -e "$msg" | $sendmail $email
I also backup each individual database separately so I can restore on a service by service basis.
I’m certain MariaDB has a similar command to dump and restore. /srv/backups is NFS mounted from my NAS.