How to minimize number of bytes written to disk by mysqld?

Question

I'm using mysql server on raspberry pi (raspbian system) to store sensor readings. Each minute one record of length 20 bytes is inserted to mysql table.

But iotop command shows that mysqld process writes 200K each minute. I would like to minimize ammount of data that is written to sd card in order to extend it's durability (I've already applied fixes for preventing sd card damage, like tmpfs for logs and other hacks).

Why mysqld writes 200K bytes when only 20B of data is inserted to table? Is there ony way to minimize writes in above scenario, tune mysql server?

Interesting thing is that vmstat command does not show so much data written to disk in "io/bo" column

I use mysql server version 5.5.33-0+wheezy1 and innoDB tables.

UPDATE:

Bill's thorough answer below made me realize that using mysql server was an overkill for simple sensor logging. Eventually I migrated to sqlite. For the same table structure sqlite writes 6 times less bytes per transaction (about 30kb) than mysql, which is better for RPi sd card. I'm still working on optimization because, as I said before, I write only 20b of actual data per transaction.

Answer 1

InnoDB writes records to the transaction log (ib_logfile*) which are 512-byte aligned. It also writes information to the header of the first log. This is not configurable, but you can decrease the frequency of writes by changing the innodb_flush_log_at_trx_commit variable to 0.

Then modified pages are written to disk in 16KB pages, regardless of whether you wrote just one 20-byte row on that page. In MySQL 5.6, you can reduce the page size with the innodb_page_size variable, down to 4KB.

Pages are also written to the doublewrite buffer before being written to the respective location in the tablespace. You can disable the doublewrite buffer with the innodb_doublewrite variable.

Pages may also be written to the rollback segment. This is not configurable.

There's also the binary log. This is not related to InnoDB specifically, it is used for any storage engine. I assume you have no need for the binary log if you're on a Raspberry Pi. You can disable the binary log with the log_bin variable (just don't set this variable, and the default condition is that it's disabled).

You can also disable the general query log and the slow query log.

There's also an error log, but you can't disable that.

Answer 2

@BillKarwin answered the why really well. The best you can do in v5.5 is 16kb writes. If you cannot live with this, then I will add some design considerations.

Rather than writing each sensor reading, buffer them in the application level (eg a csv file in tmpfs) and after a certain amount have accumulated use a LOAD DATA to read them. If you must have access to them (eg searches), you can update your application query to consider both the "permanent" MySQL store and the "temporary" CSV store.

All of this is, of course, if you can tolerate loss across a device power cycle.