1

I have a Raspberry PI computer with some measurement device and a script that takes a measurement every five minutes and stores the result in MySQL database. I have configured MySQL server to keep the database on external USB HDD in order to minimize wear on the SD card used as root filesystem in Rpi.

Entire data saved on my HDD every five minutes consists of only a few numbers - two numbers as a result, timestamp and id as primary key for MySQL table - and yet MySQL server keeps writing it out to the disk every time a new data is available. This causes the disk to spin-up every five minutes, which increases power usage and wears down the HDD. Is there a way to configure MySQL to cache some data in RAM until it has enough of it to write to HDD? I assume the default configuration is optimized for large quantities of data in short periods and reliability in case of eg. power failure, but I actually don't care about either of those. Even if a power failure occurs (rather unlikely) it will not be a problem if I miss a few results more than necessary anyway.

I have looked into MySQL documentation and googled for it, but all resources concerning MySQL and disk usage seem to concentrate on increasing HDD throughput or optimizing it for quick response times.

In case it's important, mysqld -V returns:

mysqld  Ver 5.5.46-0+deb7u1 for debian-linux-gnu on armv7l ((Debian))

EDIT:

I do realize that I am using MySQL a bit like a golden hammer here - MySQL was never written with my kind of application in mind. Perhaps somebody could propose an alternative solution to my problem that would require similar amount of work to setup.

0

You should take a look at "InnoDB Change Buffering"

https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_change_buffering

  • I have no time to try it at the moment, but form the documentation it looks that what's buffered is just changes to secondary indexes - it is important while doing eg. bulk inserts - I doubt it will allow me to avoid disc access at all for elongated periods of time. I will have a try though and comment again when I get the results. – j_kubik Nov 3 '15 at 13:31
  • Sorry, but your advice did not help - I am not sure if this is because only applies to secondary indices, or because it was just buffering the changes until the server was idle - which was just after my data was added. – j_kubik Nov 15 '15 at 3:25
0

I have found a way to work around my problem that doesn't rely on server's configuration, but on database scheme. I created additional array with engine=memory storage setting, where my measurement process added data. I also added a cron job that moves data from this temporary table into disk-backed one. It is being run every couple of hours and on system shutdown, so I don't think that it's likely that I loose much data. I also needed to modify my SQL queries by selecting from both tables and combining the result sets using 'UNION'.

This setup might not be usable for everyone who wants to minimize MySQL disc access, and if I find another solution (including a different database server that is network transparent and allows simple queries) that can be configured to use HDD as little as possible, I will post it here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.