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

mariadbperformance

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.

Best Answer

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.

Related Solutions

Sql-server – SQL Server Performance Counters Relating to Disk Access and Log Files That Appear Concerning

SQLServer:Databases\Log Flush Wait Time counter, it is staying fairly constant between 600 ms and 900 ms with an average of 750ms

750ms wait time per flush is huge. It means every transaction statement must wait in average 750ms to commit. If you don't use explicit transactions for writes it means each write statement (INSERT/UPDATE/DELETE) must wait 750ms to complete. However this does not corroborate with the other sttaement 'PhysicalDisk\Avg. Disk sec/Write ... For the Log Files volume, it stays almost steady at 8 ms.'. One explanation is that you have log growths (you do, the counters say so) which skew the log flush wait times but that would only explain averages over long periods.

SQLServer:Databases\Log Growths, I'm at 325

Figure out which database is growing and resize the log appropriately. You should not occur growth events in production, they are extremely expensive. the counter has instances per database.

PhysicalDisk\Avg. Disk sec/Write ... For the Log Files volume, it stays almost steady at 8 ms. Is it normal for the Log Files to have consistent writes with no break?

Yes. A transaction cannot commit unless the log was written to disk. As long as you have transactions, you will have log disk writes.

PhysicalDisk\Avg. Disk sec/Write counters: For the TempDB volume, I get dramatic spikes every few minutes that are upwards of 100 ms.

It would be good to know why. Can you correlate it with a specific activity that causes the spike? Can you correlated it with database checkpoints occurring?

Memory\Pages Input/sec counter, the values fluctuate dramatically and have an average of 6 but have many spikes well upwards of 100 (some as high as 400).

So you're paging. Why? Insufficient memory? Any other offending process on the server host? Virtualized overcommitted environment? Only you can tell. Does swapping IO overlap with any of the IOs above (same physical path, ie. same disks)? Then some of the SQL timers may be influenced by this paging spikes.

MySQL/MariaDB – Best Place to Store Very Large Text Fields

I'd suggest you split this large column off into its own table. While you're there take out any other columns which are only used once in a blue moon. (The smaller your row the faster your queries will run.)

I'll take your current table as MyData. Rename it to, say, MyData_Crucial. Let's call the new table MyData_Sometimes. Create this view:

create view MyData
select
    <all the columns>
from MyData_Crucial
inner join MyData_Sometimes
    on <primary key> = <primary key>

Now, assuming your optimizer is at least a little bit clever, you will not need to change your application code at all.

Best Answer

Related Solutions

Sql-server – SQL Server Performance Counters Relating to Disk Access and Log Files That Appear Concerning

MySQL/MariaDB – Best Place to Store Very Large Text Fields

Related Question