Re: BTRFS for OLTP Databases

[Hugo Mills <hugo@carfax.org.uk>]

[-- Attachment #1: Type: text/plain, Size: 1989 bytes --]

On Tue, Feb 07, 2017 at 08:53:35AM -0500, 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.
> 
> I'm complete newbie in BTRFS so  I assume  I'm doing something wrong.
> 
> 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 ?
> 
> 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.

   Well, nodatacow will still allow snapshots to work, but it also
allows the data to fragment. Each snapshot made will cause subsequent
writes to shared areas to be CoWed once (and then it reverts to
unshared and nodatacow again).

   There's another approach which might be worth testing, which is to
use autodefrag. This will increase data write I/O, because where you
have one or more small writes in a region, it will also read and write
the data in a small neghbourhood around those writes, so the
fragmentation is reduced. This will improve subsequent read
performance.

   I could also suggest getting the latest kernel you can -- 16.04 is
already getting on for a year old, and there may be performance
improvements in upstream kernels which affect your workload. There's
an Ubuntu kernel PPA you can use to get the new kernels without too
much pain.

   Hugo.

-- 
Hugo Mills             | I don't care about "it works on my machine". We are
hugo@... carfax.org.uk | not shipping your machine.
http://carfax.org.uk/  |
PGP: E2AB1DE4          |

[-- Attachment #2: Digital signature --]
[-- Type: application/pgp-signature, Size: 836 bytes --]