Re: BTRFS for OLTP Databases

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

On 2017-02-07 08:53, Peter Zaitsev wrote:
> Hi,
>
> I have tried BTRFS from Ubuntu 16.04 LTS   for write intensive OLTP MySQL
> Workload.
>
> It did not go very well ranging from multi-seconds stalls where no
> transactions are completed to the finally kernel OOPS with "no space left
> on device" error message and filesystem going read only.
How much spare space did you have allocated in the filesystem?  At a 
minimum, you want at least a few GB beyond what you expect to be the 
maximum size of your data-set times the number of snapshots you plan to 
keep around at any given time.
>
> I'm complete newbie in BTRFS so  I assume  I'm doing something wrong.
Not exactly wrong, but getting this to work efficiently is more art than 
engineering.
>
> Do you have any advice on how BTRFS should be tuned for OLTP workload
> (large files having a lot of random writes)  ?    Or is this the case where
> one should simply stay away from BTRFS and use something else ?
The general recommendation is usually to avoid BTRFS for such things. 
There are however a number of things you can do to improve performance:
1. Use a backing storage format that has the minimal amount of 
complexity.  The more data structures that get updated when a record 
changes, the worse the performance will be.  I don't have enough 
experience with MySQL to give a specific recommendation on what backing 
storage format to use, but someone else might.
2. Avoid large numbers of small transactions.  The smaller the 
transaction, the worse it will fragment things.
3. Use autodefrag.  This will increase write load on the storage device, 
but it should improve performance for reads.
4. Try using in-line compression.  This can actually significantly 
improve performance, especially if you have slow storage devices and a 
really nice CPU.
5. If you're running raid10 mode for BTRFS, run raid1 on top of two LVM 
or MD RAID0 devices instead.  This sounds stupid, but it actually will 
hugely improve both read and write performance without sacrificing any 
data safety.
6. Look at I/O scheduler tuning.  This can have a huge impact, 
especially considering that most of the defaults for the various 
schedulers are somewhat poor for most modern systems.  I won't go into 
the details here, since there are a huge number of online resources 
about this.
>
> One item recommended in some places is "nodatacow"  this however defeats
> the main purpose I'm looking at BTRFS -  I am interested in "free"
> snapshots which look very attractive to use for database recovery scenarios
> allow instant rollback to the previous state.
Snapshots aren't free.  They are quick, but they aren't free by any 
means.  If you're going to be using snapshots, keep them to a minimum, 
performance scales inversely proportionate to the number of snapshots, 
and this has a much bigger impact the more you're trying to do on the 
filesystem.  Also, consider whether or not you _actually_ need 
filesystem level snapshots.  I don't know about your full software 
stack, but most good OLTP software supports rollback segments (or an 
equivalent with a different name), and those are probably what you want 
to use, not filesystem snapshots.