Re: BTRFS for OLTP Databases

W dniu 2017-02-13 o 13:44 PM, Austin S. Hemmelgarn pisze: > On 2017-02-09 22:58, Andrei Borzenkov wrote: >> 07.02.2017 23:47, Austin S. Hemmelgarn пишет: >> ... >>> Sadly, freezefs (the generic interface based off of xfs_freeze) only >>> works for block device snapshots. Filesystem level

Re: BTRFS for OLTP Databases

On 2017-02-09 22:58, Andrei Borzenkov wrote: 07.02.2017 23:47, Austin S. Hemmelgarn пишет: ... Sadly, freezefs (the generic interface based off of xfs_freeze) only works for block device snapshots. Filesystem level snapshots need the application software to sync all it's data and then stop

Re: BTRFS for OLTP Databases

On 2017-02-07 22:35, Kai Krakow wrote: [...] >> >> Atomicity can be a relative term. If the snapshot atomicity is >> relative to barriers but not relative to individual writes between >> barriers then AFAICT it's fine because the filesystem doesn't make >> any promise it won't keep even in the

Re: BTRFS for OLTP Databases

W dniu 2017-02-08 o 13:14 PM, Martin Raiber pisze: > Hi, > > On 08.02.2017 03:11 Peter Zaitsev wrote: >> Out of curiosity, I see one problem here: >> If you're doing snapshots of the live database, each snapshot leaves >> the database files like killing the database in-flight. Like shutting >> the

Re: BTRFS for OLTP Databases

Hi, When it comes to MySQL I'm not really sure what you're trying to achieve. Because MySQL manages its own cache flushing OS cache to the disk and "freezing" FS does not really do much - it will still need to do crash recovery when such snapshot is restored. The reason people would use

Re: BTRFS for OLTP Databases

W dniu 2017-02-08 o 14:32 PM, Austin S. Hemmelgarn pisze: > On 2017-02-08 08:26, Martin Raiber wrote: >> On 08.02.2017 14:08 Austin S. Hemmelgarn wrote: >>> On 2017-02-08 07:14, Martin Raiber wrote: Hi, On 08.02.2017 03:11 Peter Zaitsev wrote: > Out of curiosity, I see one

Re: BTRFS for OLTP Databases

On 2017-02-08 08:26, Martin Raiber wrote: On 08.02.2017 14:08 Austin S. Hemmelgarn wrote: On 2017-02-08 07:14, Martin Raiber wrote: Hi, On 08.02.2017 03:11 Peter Zaitsev wrote: Out of curiosity, I see one problem here: If you're doing snapshots of the live database, each snapshot leaves the

Re: BTRFS for OLTP Databases

On 08.02.2017 14:08 Austin S. Hemmelgarn wrote: > On 2017-02-08 07:14, Martin Raiber wrote: >> Hi, >> >> On 08.02.2017 03:11 Peter Zaitsev wrote: >>> Out of curiosity, I see one problem here: >>> If you're doing snapshots of the live database, each snapshot leaves >>> the database files like

Re: BTRFS for OLTP Databases

On 2017-02-08 07:14, Martin Raiber wrote: Hi, On 08.02.2017 03:11 Peter Zaitsev wrote: Out of curiosity, I see one problem here: If you're doing snapshots of the live database, each snapshot leaves the database files like killing the database in-flight. Like shutting the system down in the

Re: BTRFS for OLTP Databases

Hi, On 08.02.2017 03:11 Peter Zaitsev wrote: > Out of curiosity, I see one problem here: > If you're doing snapshots of the live database, each snapshot leaves > the database files like killing the database in-flight. Like shutting > the system down in the middle of writing data. > > This is

Re: BTRFS for OLTP Databases

On 2017-02-07 15:54, Kai Krakow wrote: Am Tue, 7 Feb 2017 15:27:34 -0500 schrieb "Austin S. Hemmelgarn" : 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

Re: BTRFS for OLTP Databases

Hi Kai, I guess your message did not make it to me as I'm not subscribed to the list. I totally understand what the the snapshot is "crash consistent" - consistent to the state of the disk you would find if you shut down the power with no notice, for many applications it is a problem however it

Re: BTRFS for OLTP Databases

On 02/07/2017 10:35 PM, Kai Krakow wrote: > Am Tue, 7 Feb 2017 22:25:29 +0100 > schrieb Lionel Bouton : > >> Le 07/02/2017 à 21:47, Austin S. Hemmelgarn a écrit : >>> On 2017-02-07 15:36, Kai Krakow wrote: Am Tue, 7 Feb 2017 09:13:25 -0500 schrieb

Re: BTRFS for OLTP Databases

On 02/07/2017 07:59 PM, Peter Zaitsev wrote: > > So far the most frustating for me was periodic stalls for many seconds > (running sysbench workload). What was the most puzzling I get this > even if I run workload at the 50% or less of the full load - Ie > database can handle 1000

Re: BTRFS for OLTP Databases

Am Tue, 7 Feb 2017 22:25:29 +0100 schrieb Lionel Bouton : > Le 07/02/2017 à 21:47, Austin S. Hemmelgarn a écrit : > > On 2017-02-07 15:36, Kai Krakow wrote: > >> Am Tue, 7 Feb 2017 09:13:25 -0500 > >> schrieb Peter Zaitsev : > >> > [...] >

Re: BTRFS for OLTP Databases

Le 07/02/2017 à 21:47, Austin S. Hemmelgarn a écrit : > On 2017-02-07 15:36, Kai Krakow wrote: >> Am Tue, 7 Feb 2017 09:13:25 -0500 >> schrieb Peter Zaitsev : >> >>> Hi Hugo, >>> >>> For the use case I'm looking for I'm interested in having snapshot(s) >>> open at all time.

Re: BTRFS for OLTP Databases

Am Tue, 7 Feb 2017 10:43:11 -0500 schrieb "Austin S. Hemmelgarn" : > > I mean that: > > You have a 128MB extent, you rewrite random 4k sectors, btrfs will > > not split 128MB extent, and not free up data, (i don't know > > internal algo, so i can't predict when this will

Re: BTRFS for OLTP Databases

Am Tue, 7 Feb 2017 15:27:34 -0500 schrieb "Austin S. Hemmelgarn" : > >> 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

Re: BTRFS for OLTP Databases

On 2017-02-07 15:36, Kai Krakow wrote: Am Tue, 7 Feb 2017 09:13:25 -0500 schrieb Peter Zaitsev : 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

Re: BTRFS for OLTP Databases

Le 07/02/2017 à 21:36, Kai Krakow a écrit : > [...] > I think I've read that btrfs snapshots do not guarantee single point in > time snapshots - the snapshot may be smeared across a longer period of > time while the kernel is still writing data. So parts of your writes > may still end up in the

Re: BTRFS for OLTP Databases

Austin, I recognize there are other components too. In this case I'm actually comparing BTRFS to XFS and EXT4 so I'm 100% sure it is file system related. Also I'm using O_DIRECT asynchronous IO with MySQL which means there are no significant dirty block size on the file system level. I'll

Re: BTRFS for OLTP Databases

Am Tue, 7 Feb 2017 09:13:25 -0500 schrieb Peter Zaitsev : > 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

Re: BTRFS for OLTP Databases

On 2017-02-07 15:19, Kai Krakow wrote: Am Tue, 7 Feb 2017 14:50:04 -0500 schrieb "Austin S. Hemmelgarn" : Also does autodefrag works with nodatacow (ie with snapshot) or are these exclusive ? I'm not sure about this one. I would assume based on the fact that many other

Re: BTRFS for OLTP Databases

Am Tue, 7 Feb 2017 14:50:04 -0500 schrieb "Austin S. Hemmelgarn" : > > Also does autodefrag works with nodatacow (ie with snapshot) or > > are these exclusive ? > I'm not sure about this one. I would assume based on the fact that > many other things don't work with

Re: BTRFS for OLTP Databases

On Tue, 7 Feb 2017 09:13:25 -0500 Peter Zaitsev 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

Re: BTRFS for OLTP Databases

On 2017-02-07 14:39, Kai Krakow wrote: Am Tue, 7 Feb 2017 10:06:34 -0500 schrieb "Austin S. Hemmelgarn" : 4. Try using in-line compression. This can actually significantly improve performance, especially if you have slow storage devices and a really nice CPU. Just a

Re: BTRFS for OLTP Databases

On 2017-02-07 13:59, Peter Zaitsev wrote: Jeff, Thank you very much for explanations. Indeed it was not clear in the documentation - I read it simply as "if you have snapshots enabled nodatacow makes no difference" I will rebuild the database in this mode from scratch and see how performance

Re: BTRFS for OLTP Databases

Am Tue, 7 Feb 2017 10:06:34 -0500 schrieb "Austin S. Hemmelgarn" : > 4. Try using in-line compression. This can actually significantly > improve performance, especially if you have slow storage devices and > a really nice CPU. Just a side note: With nodatacow there'll be

Re: BTRFS for OLTP Databases

On 2017-02-07 14:31, Peter Zaitsev wrote: Hi Hugo, As I re-read it closely (and also other comments in the thread) I know understand there is a difference how nodatacow works even if snapshot are in place. On autodefrag I wonder is there some more detailed documentation about how autodefrag

Re: BTRFS for OLTP Databases

Hi Hugo, As I re-read it closely (and also other comments in the thread) I know understand there is a difference how nodatacow works even if snapshot are in place. On autodefrag I wonder is there some more detailed documentation about how autodefrag works. The manual

Re: BTRFS for OLTP Databases

Jeff, Thank you very much for explanations. Indeed it was not clear in the documentation - I read it simply as "if you have snapshots enabled nodatacow makes no difference" I will rebuild the database in this mode from scratch and see how performance changes. So far the most frustating for me

Re: BTRFS for OLTP Databases

On 2/7/17 8:53 AM, 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"

Re: BTRFS for OLTP Databases

Hi Peter, Le 07/02/2017 à 15:13, Peter Zaitsev a écrit : > 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

Re: BTRFS for OLTP Databases

On 2017-02-07 10:20, Timofey Titovets wrote: I think that you have a problem with extent bookkeeping (if i understand how btrfs manage extents). So for deal with it, try enable compression, as compression will force all extents to be fragmented with size ~128kb. No, it will compress everything

Re: BTRFS for OLTP Databases

>> I think that you have a problem with extent bookkeeping (if i >> understand how btrfs manage extents). >> So for deal with it, try enable compression, as compression will force >> all extents to be fragmented with size ~128kb. > > No, it will compress everything in chunks of 128kB, but it will

Re: BTRFS for OLTP Databases

On 2017-02-07 10:00, Timofey Titovets wrote: 2017-02-07 17:13 GMT+03:00 Peter Zaitsev : 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

Re: BTRFS for OLTP Databases

2017-02-07 17:13 GMT+03:00 Peter Zaitsev : > 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 >

Re: BTRFS for OLTP Databases

> I have tried BTRFS from Ubuntu 16.04 LTS for write intensive > OLTP MySQL Workload. This has a lot of interesting and mostly agreeable information: https://blog.pgaddict.com/posts/friends-dont-let-friends-use-btrfs-for-oltp The main target of Btrfs is where one wants checksums and occasional

Re: BTRFS for OLTP Databases

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

Re: BTRFS for OLTP Databases

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

Re: BTRFS for OLTP Databases

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