Reducing I/O operations for often-updating data in MariaDB

Question

I have a big table where I update something like 10k rows per second on average, but just one column. Currently it looks to me that MariaDB writes those changes rather instantly onto the disk creating a constant significant write I/O.

However, for this one column, I don't care too much, if the data is saved to disk in real time, I rather want to have less write I/O on the disk and a higher risk of data loss.

Is there any way I can tell MariaDB to cache this data for a bit longer time in memory before writing it to the disk?

Basically, the column is a "information_last_verified_at" timestamp that I regularly update and I can live with it being a few minutes off in the unlikely event of some server problems.

Answer 1

There are two sources of write I/O when updating table data: transaction log flushes and checkpoints (dirty buffer pool page flushes). You cannot do much about the former, as it happens every time you commit, apart from placing your log onto faster storage.

Checkpoint activity can be somewhat configured (for InnoDB) by adjusting innodb_max_dirty_pages_pct, innodb_adaptive_flushing*, innodb_adaptive_checkpoint, and innodb_io_capacity parameters, among others -- there are some more details in this blog post. You'll need to monitor checkpoint activity to figure out what changes make sense in your situation.

Answer 2

You could create a replication master to your current server, make the large table in question a memory table on the master only, and update the master.

To reduce IO on the slave is the replication load conducive to slave_paralle_threads in the way that consecutive updates will update different rows?

Answer 3

You could create a memory table containing the PK and information_last_verified_at. Make the frequent update script update this memory table.

Once a minute or so, do a multi-table UPDATE to transfer these updates to the real table hence doing a larger IO in one hit. Because small updates have a high write amplification this will be a lower volumne of IOPs overall.