Re: BTRFS for OLTP Databases

["Austin S. Hemmelgarn" <ahferroin7@gmail.com>]

On 2017-02-07 15:54, Kai Krakow wrote:
> Am Tue, 7 Feb 2017 15:27:34 -0500
> schrieb "Austin S. Hemmelgarn" <ahferroin7@gmail.com>:
>
>>>> I'm not sure about this one.  I would assume based on the fact that
>>>> many other things don't work with nodatacow and that regular defrag
>>>> doesn't work on files which are currently mapped as executable code
>>>> that it does not, but I could be completely wrong about this too.
>>>
>>> Technically, there's nothing that prevents autodefrag to work for
>>> nodatacow files. The question is: is it really necessary? Standard
>>> file systems also have no autodefrag, it's not an issue there
>>> because they are essentially nodatacow. Simply defrag the database
>>> file once and you're done. Transactional MySQL uses huge data
>>> files, probably preallocated. It should simply work with
>>> nodatacow.
>> The thing is, I don't have enough knowledge of how defrag is
>> implemented in BTRFS to say for certain that ti doesn't use COW
>> semantics somewhere (and I would actually expect it to do so, since
>> that in theory makes many things _much_ easier to handle), and if it
>> uses COW somewhere, then it by definition doesn't work on NOCOW files.
>
> A dev would be needed on this. But from a non-dev point of view, the
> defrag operation itself is CoW: Blocks are rewritten to another
> location in contiguous order. Only metadata CoW should be needed for
> this operation.
>
> It should be nothing else than writing to a nodatacow snapshot... Just
> that the snapshot is more or less implicit and temporary.
>
> Hmm? *curious*
>
The gimmicky part though is that the file has to remain accessible 
throughout the entire operation, and the defrad can't lose changes that 
occur while the file is being defragmented.  In many filesystems (NTFS 
on Windows for example), a defrag functions similarly to a pvmove 
operation in LVM, as each extent gets moved, writes to that region get 
indirected to the new location and treat the areas that were written to 
as having been moved already.  The thing is, on BTRFS that would result 
in extents getting split, which means COW is probably involved at some 
level in the data path too.