Re: BTRFS for OLTP Databases

[Roman Mamedov <rm@romanrm.net>]

On Tue, 7 Feb 2017 09:13:25 -0500
Peter Zaitsev <pz@percona.com> wrote:

> Hi Hugo,
> 
> For the use case I'm looking for I'm interested in having snapshot(s)
> open at all time.  Imagine  for example snapshot being created every
> hour and several of these snapshots  kept at all time providing quick
> recovery points to the state of 1,2,3 hours ago.  In  such case (as I
> think you also describe)  nodatacow  does not provide any advantage.

It still does provide some advantage, as in each write into new area since
last hour snapshot is going to be CoW'ed only once, as opposed to every new
write getting CoW'ed every time no matter what.

I'm not sold on autodefrag, what I'd suggest instead is to schedule regular
defrag ("btrfs fi defrag") of the database files, e.g. daily. This may increase
space usage temporarily as it will partially unmerge extents previously shared
across snapshots, but you won't get away runaway fragmentation anymore, as you
would without nodatacow or with periodical snapshotting.

-- 
With respect,
Roman