Re: BTRFS and databases
On 02.08.2018 14:27 Austin S. Hemmelgarn wrote: > On 2018-08-02 06:56, Qu Wenruo wrote: >> >> On 2018年08月02日 18:45, Andrei Borzenkov wrote: >>> >>> Отправлено с iPhone >>> 2 авг. 2018 г., в 10:02, Qu Wenruo написал(а): > On 2018年08月01日 11:45, MegaBrutal wrote: > Hi all, > > I know it's a decade-old question, but I'd like to hear your thoughts > of today. By now, I became a heavy BTRFS user. Almost everywhere I > use > BTRFS, except in situations when it is obvious there is no benefit > (e.g. /var/log, /boot). At home, all my desktop, laptop and server > computers are mainly running on BTRFS with only a few file systems on > ext4. I even installed BTRFS in corporate productive systems (in > those > cases, the systems were mainly on ext4; but there were some specific > file systems those exploited BTRFS features). > > But there is still one question that I can't get over: if you store a > database (e.g. MySQL), would you prefer having a BTRFS volume mounted > with nodatacow, or would you just simply use ext4? > > I know that with nodatacow, I take away most of the benefits of BTRFS > (those are actually hurting database performance – the exact CoW > nature that is elsewhere a blessing, with databases it's a drawback). > But are there any advantages of still sticking to BTRFS for a > database > albeit CoW is disabled, or should I just return to the old and > reliable ext4 for those applications? Since I'm not a expert in database, so I can totally be wrong, but what about completely disabling database write-ahead-log (WAL), and let btrfs' data CoW to handle data consistency completely? >>> >>> This would make content of database after crash completely >>> unpredictable, thus making it impossible to reliably roll back >>> transaction. >> >> Btrfs itself (with datacow) can ensure the fs is updated completely. >> >> That's to say, even a crash happens, the content of the fs will be the >> same state as previous btrfs transaction (btrfs sync). >> >> Thus there is no need to rollback database transaction though. >> (Unless database transaction is not sync to btrfs transaction) >> > Two issues with this statement: > > 1. Not all database software properly groups logically related > operations that need to be atomic as a unit into transactions. > 2. Even aside from point 1 and the possibility of database corruption, > there are other legitimate reasons that you might need to roll-back a > transaction (for example, the rather obvious case of a transaction > that should not have happened in the first place). I thought of a database transaction scheme that is based on btrfs features before. It has practical issues, though. One would put a b-tree database file into a subvolume (e.g. trans_0). When changing the b-tree database one would create a snapshot (trans_1), then change the file in the snapshot. On commit sync trans_1, then delete trans_0. On rollback, delete trans_1. Problems: * Large overhead for small transactions (OLTP) -- problem in general for copy-on-write b-tree databases * Only root can create or destroy snapshots * Per default the Linux memory system starts write-back pretty much immediately, so pages that get overwritten more than once in a transaction (and not kept in RAM) unless Linux is tuned to not do this. I have used this method, albeit by reflinking the database, then modifying the reflink, but I think reflinking it slower than creating a snapshot? -- To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in the body of a message to majord...@vger.kernel.org More majordomo info at http://vger.kernel.org/majordomo-info.html
BTRFS and databases
On 2018-08-02 03:07 AM, Qu Wenruo wrote: > For data, since we have cow (along with csum), it should be no problem > to recover. > > And since datacow is used, transaction on each device should be atomic, > thus we should be able to handle one-time device out-of-sync case. > (For multiple out-of-sync events, we don't have any good way though). > > Or did I miss something from previous discussion? As far as I know, that is indeed correct and works very well. The question was specifically about using nodatacow for databases,, and that's the question I was responding too. In the current state, I do no believe btrfs nodatacow is in any way appropriate for databases/vm hosting when combined with multi-device. signature.asc Description: OpenPGP digital signature
Re: BTRFS and databases
On 2018-08-02 06:56, Qu Wenruo wrote: On 2018年08月02日 18:45, Andrei Borzenkov wrote: Отправлено с iPhone 2 авг. 2018 г., в 10:02, Qu Wenruo написал(а): On 2018年08月01日 11:45, MegaBrutal wrote: Hi all, I know it's a decade-old question, but I'd like to hear your thoughts of today. By now, I became a heavy BTRFS user. Almost everywhere I use BTRFS, except in situations when it is obvious there is no benefit (e.g. /var/log, /boot). At home, all my desktop, laptop and server computers are mainly running on BTRFS with only a few file systems on ext4. I even installed BTRFS in corporate productive systems (in those cases, the systems were mainly on ext4; but there were some specific file systems those exploited BTRFS features). But there is still one question that I can't get over: if you store a database (e.g. MySQL), would you prefer having a BTRFS volume mounted with nodatacow, or would you just simply use ext4? I know that with nodatacow, I take away most of the benefits of BTRFS (those are actually hurting database performance – the exact CoW nature that is elsewhere a blessing, with databases it's a drawback). But are there any advantages of still sticking to BTRFS for a database albeit CoW is disabled, or should I just return to the old and reliable ext4 for those applications? Since I'm not a expert in database, so I can totally be wrong, but what about completely disabling database write-ahead-log (WAL), and let btrfs' data CoW to handle data consistency completely? This would make content of database after crash completely unpredictable, thus making it impossible to reliably roll back transaction. Btrfs itself (with datacow) can ensure the fs is updated completely. That's to say, even a crash happens, the content of the fs will be the same state as previous btrfs transaction (btrfs sync). Thus there is no need to rollback database transaction though. (Unless database transaction is not sync to btrfs transaction) Two issues with this statement: 1. Not all database software properly groups logically related operations that need to be atomic as a unit into transactions. 2. Even aside from point 1 and the possibility of database corruption, there are other legitimate reasons that you might need to roll-back a transaction (for example, the rather obvious case of a transaction that should not have happened in the first place). -- To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in the body of a message to majord...@vger.kernel.org More majordomo info at http://vger.kernel.org/majordomo-info.html
Re: BTRFS and databases
On 2018年08月02日 18:45, Andrei Borzenkov wrote: > > > Отправлено с iPhone > >> 2 авг. 2018 г., в 10:02, Qu Wenruo написал(а): >> >> >> >>> On 2018年08月01日 11:45, MegaBrutal wrote: >>> Hi all, >>> >>> I know it's a decade-old question, but I'd like to hear your thoughts >>> of today. By now, I became a heavy BTRFS user. Almost everywhere I use >>> BTRFS, except in situations when it is obvious there is no benefit >>> (e.g. /var/log, /boot). At home, all my desktop, laptop and server >>> computers are mainly running on BTRFS with only a few file systems on >>> ext4. I even installed BTRFS in corporate productive systems (in those >>> cases, the systems were mainly on ext4; but there were some specific >>> file systems those exploited BTRFS features). >>> >>> But there is still one question that I can't get over: if you store a >>> database (e.g. MySQL), would you prefer having a BTRFS volume mounted >>> with nodatacow, or would you just simply use ext4? >>> >>> I know that with nodatacow, I take away most of the benefits of BTRFS >>> (those are actually hurting database performance – the exact CoW >>> nature that is elsewhere a blessing, with databases it's a drawback). >>> But are there any advantages of still sticking to BTRFS for a database >>> albeit CoW is disabled, or should I just return to the old and >>> reliable ext4 for those applications? >> >> Since I'm not a expert in database, so I can totally be wrong, but what >> about completely disabling database write-ahead-log (WAL), and let >> btrfs' data CoW to handle data consistency completely? >> > > This would make content of database after crash completely unpredictable, > thus making it impossible to reliably roll back transaction. Btrfs itself (with datacow) can ensure the fs is updated completely. That's to say, even a crash happens, the content of the fs will be the same state as previous btrfs transaction (btrfs sync). Thus there is no need to rollback database transaction though. (Unless database transaction is not sync to btrfs transaction) Thanks, Qu > > >> If there is some concern about the commit interval, it could be tuned by >> commit= mount option. >> >> It may either lead to super unexpected fast behavior, or some unknown >> disaster. (And for latter, we at least could get some interesting >> feedback and bugs to fix) >> >> Thanks, >> Qu >> >>> >>> >>> Kind regards, >>> MegaBrutal >>> -- >>> To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in >>> the body of a message to majord...@vger.kernel.org >>> More majordomo info at http://vger.kernel.org/majordomo-info.html >>> >> signature.asc Description: OpenPGP digital signature
Re: BTRFS and databases
On 2018年08月02日 18:35, Andrei Borzenkov wrote: > > > Отправлено с iPhone > >> 2 авг. 2018 г., в 12:16, Martin Steigerwald написал(а): >> >> Hugo Mills - 01.08.18, 10:56: On Wed, Aug 01, 2018 at 05:45:15AM +0200, MegaBrutal wrote: I know it's a decade-old question, but I'd like to hear your thoughts of today. By now, I became a heavy BTRFS user. Almost everywhere I use BTRFS, except in situations when it is obvious there is no benefit (e.g. /var/log, /boot). At home, all my desktop, laptop and server computers are mainly running on BTRFS with only a few file systems on ext4. I even installed BTRFS in corporate productive systems (in those cases, the systems were mainly on ext4; but there were some specific file systems those exploited BTRFS features). But there is still one question that I can't get over: if you store a database (e.g. MySQL), would you prefer having a BTRFS volume mounted with nodatacow, or would you just simply use ext4? >>> >>> Personally, I'd start with btrfs with autodefrag. It has some >>> degree of I/O overhead, but if the database isn't performance-critical >>> and already near the limits of the hardware, it's unlikely to make >>> much difference. Autodefrag should keep the fragmentation down to a >>> minimum. >> >> I read that autodefrag would only help with small databases. >> > > I wonder if anyone actually > > a) quantified performance impact > b) analyzed the cause It's caused by btrfs' poor fsync() performance and lock-hot metadata operations. The root cause is how we design btrfs' btree. For snapshot and only for snapshot, we use one btree for one *subvolume*, unlike other fses which normally use one btree for one *inode* (both dir and file). This means each time we need to modify anything, including updating EXTENT_DATA pointer, or adding new child inode pointer, we need to do write lock of the whole subvolume tree root to the leaf. Which makes the tree root pretty lock hot. In short, in btrfs we need to lock and race on a big tree, while for other fses, they only need to lock and race on different small trees. (And that's why they can't support fast fs level snapshot) That's the root cause of btrfs' slow metadata performance. We have a lot of optimization to speedup the process, from delayed-ref to tree log. But it's still slow compared to other fses. For fsync() we have log tree optimization, which only logs related data pointer and inodes updates, and skips some full transaction operations. It indeeds make fsync() much faster, but still slower than other fses, due to metadata design. BTW, nodatacow indeed improves performance, but it's mostly due to the following factors: 1) No csum calculation Although csum calculation can be balanced to multi cpu cores/threads, and CRC32 is pretty fast, it still introduces overhead. 2) Some overwrite no longer needs to modify subvolume tree If we're doing overwrite, and there is no need to do CoW (for snapshot), we can skip updating EXTENT_DATA, and this reduces a lot of tree write lock and improve performance. > > I work with NetApp for a long time and I can say from first hand experience > that fragmentation had zero impact on OLTP workload. It did affect backup > performance as was expected, but this could be fixed by periodic reallocation > (defragmentation). > > And even that needed quite some time to observe (years) on pretty high load > database with regular backup and replication snapshots. > > If btrfs is so susceptible to fragmentation, what is the reason for it? I heard some reports of fragmentation, but mostly related to extent booking and ENOSPC, not really related to performance. And IIRC I did some old performance tests on HDD using btrfs and xfs/ext4. Using autodefrag mount option in fact reduces performance on btrfs. Thanks, Qu > > -- > To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in > the body of a message to majord...@vger.kernel.org > More majordomo info at http://vger.kernel.org/majordomo-info.html > signature.asc Description: OpenPGP digital signature
Re: BTRFS and databases
Отправлено с iPhone > 2 авг. 2018 г., в 10:02, Qu Wenruo написал(а): > > > >> On 2018年08月01日 11:45, MegaBrutal wrote: >> Hi all, >> >> I know it's a decade-old question, but I'd like to hear your thoughts >> of today. By now, I became a heavy BTRFS user. Almost everywhere I use >> BTRFS, except in situations when it is obvious there is no benefit >> (e.g. /var/log, /boot). At home, all my desktop, laptop and server >> computers are mainly running on BTRFS with only a few file systems on >> ext4. I even installed BTRFS in corporate productive systems (in those >> cases, the systems were mainly on ext4; but there were some specific >> file systems those exploited BTRFS features). >> >> But there is still one question that I can't get over: if you store a >> database (e.g. MySQL), would you prefer having a BTRFS volume mounted >> with nodatacow, or would you just simply use ext4? >> >> I know that with nodatacow, I take away most of the benefits of BTRFS >> (those are actually hurting database performance – the exact CoW >> nature that is elsewhere a blessing, with databases it's a drawback). >> But are there any advantages of still sticking to BTRFS for a database >> albeit CoW is disabled, or should I just return to the old and >> reliable ext4 for those applications? > > Since I'm not a expert in database, so I can totally be wrong, but what > about completely disabling database write-ahead-log (WAL), and let > btrfs' data CoW to handle data consistency completely? > This would make content of database after crash completely unpredictable, thus making it impossible to reliably roll back transaction. > If there is some concern about the commit interval, it could be tuned by > commit= mount option. > > It may either lead to super unexpected fast behavior, or some unknown > disaster. (And for latter, we at least could get some interesting > feedback and bugs to fix) > > Thanks, > Qu > >> >> >> Kind regards, >> MegaBrutal >> -- >> To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in >> the body of a message to majord...@vger.kernel.org >> More majordomo info at http://vger.kernel.org/majordomo-info.html >> > -- To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in the body of a message to majord...@vger.kernel.org More majordomo info at http://vger.kernel.org/majordomo-info.html
Re: BTRFS and databases
Andrei Borzenkov - 02.08.18, 12:35: > Отправлено с iPhone > > > 2 авг. 2018 г., в 12:16, Martin Steigerwald > > написал(а):> > > Hugo Mills - 01.08.18, 10:56: > >>> On Wed, Aug 01, 2018 at 05:45:15AM +0200, MegaBrutal wrote: > >>> I know it's a decade-old question, but I'd like to hear your > >>> thoughts > >>> of today. By now, I became a heavy BTRFS user. Almost everywhere I > >>> use BTRFS, except in situations when it is obvious there is no > >>> benefit (e.g. /var/log, /boot). At home, all my desktop, laptop > >>> and > >>> server computers are mainly running on BTRFS with only a few file > >>> systems on ext4. I even installed BTRFS in corporate productive > >>> systems (in those cases, the systems were mainly on ext4; but > >>> there > >>> were some specific file systems those exploited BTRFS features). > >>> > >>> But there is still one question that I can't get over: if you > >>> store > >>> a > >>> database (e.g. MySQL), would you prefer having a BTRFS volume > >>> mounted > >>> with nodatacow, or would you just simply use ext4? > >>> > >> Personally, I'd start with btrfs with autodefrag. It has some > >> > >> degree of I/O overhead, but if the database isn't > >> performance-critical and already near the limits of the hardware, > >> it's unlikely to make much difference. Autodefrag should keep the > >> fragmentation down to a minimum. > > > > I read that autodefrag would only help with small databases. > > I wonder if anyone actually > > a) quantified performance impact > b) analyzed the cause > > I work with NetApp for a long time and I can say from first hand > experience that fragmentation had zero impact on OLTP workload. It > did affect backup performance as was expected, but this could be > fixed by periodic reallocation (defragmentation). > > And even that needed quite some time to observe (years) on pretty high > load database with regular backup and replication snapshots. > > If btrfs is so susceptible to fragmentation, what is the reason for > it? In the end of my original mail I mentioned a blog article that also had some performance graphs. Did you actually read it? Thanks, -- Martin -- To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in the body of a message to majord...@vger.kernel.org More majordomo info at http://vger.kernel.org/majordomo-info.html
Re: BTRFS and databases
Отправлено с iPhone > 2 авг. 2018 г., в 12:16, Martin Steigerwald написал(а): > > Hugo Mills - 01.08.18, 10:56: >>> On Wed, Aug 01, 2018 at 05:45:15AM +0200, MegaBrutal wrote: >>> I know it's a decade-old question, but I'd like to hear your >>> thoughts >>> of today. By now, I became a heavy BTRFS user. Almost everywhere I >>> use BTRFS, except in situations when it is obvious there is no >>> benefit (e.g. /var/log, /boot). At home, all my desktop, laptop and >>> server computers are mainly running on BTRFS with only a few file >>> systems on ext4. I even installed BTRFS in corporate productive >>> systems (in those cases, the systems were mainly on ext4; but there >>> were some specific file systems those exploited BTRFS features). >>> >>> But there is still one question that I can't get over: if you store >>> a >>> database (e.g. MySQL), would you prefer having a BTRFS volume >>> mounted >>> with nodatacow, or would you just simply use ext4? >> >> Personally, I'd start with btrfs with autodefrag. It has some >> degree of I/O overhead, but if the database isn't performance-critical >> and already near the limits of the hardware, it's unlikely to make >> much difference. Autodefrag should keep the fragmentation down to a >> minimum. > > I read that autodefrag would only help with small databases. > I wonder if anyone actually a) quantified performance impact b) analyzed the cause I work with NetApp for a long time and I can say from first hand experience that fragmentation had zero impact on OLTP workload. It did affect backup performance as was expected, but this could be fixed by periodic reallocation (defragmentation). And even that needed quite some time to observe (years) on pretty high load database with regular backup and replication snapshots. If btrfs is so susceptible to fragmentation, what is the reason for it? -- To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in the body of a message to majord...@vger.kernel.org More majordomo info at http://vger.kernel.org/majordomo-info.html
Re: BTRFS and databases
On 08/02/2018 11:16 AM, Martin Steigerwald wrote: > However I wonder: Is this it? Is there nothing that can be improved in > BTRFS to handle database and VM files in a better way, without altering > any default settings? Poor performance is not the biggest BTRFS problem, it's known for silent data corruption for instance when using KVM with cache=none,aio=native, error counters are worthless too and do not increment in case of csum mismatches. Performance penalty is huge, iowait for 4x SSD on RAID10 with BTRFS is the same like when using RAID1 on 2xSAS with Ext4 for firebird database ~ 20GB which can fit as a whole in RAM for better read performance. -- To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in the body of a message to majord...@vger.kernel.org More majordomo info at http://vger.kernel.org/majordomo-info.html
Re: BTRFS and databases
Hugo Mills - 01.08.18, 10:56: > On Wed, Aug 01, 2018 at 05:45:15AM +0200, MegaBrutal wrote: > > I know it's a decade-old question, but I'd like to hear your > > thoughts > > of today. By now, I became a heavy BTRFS user. Almost everywhere I > > use BTRFS, except in situations when it is obvious there is no > > benefit (e.g. /var/log, /boot). At home, all my desktop, laptop and > > server computers are mainly running on BTRFS with only a few file > > systems on ext4. I even installed BTRFS in corporate productive > > systems (in those cases, the systems were mainly on ext4; but there > > were some specific file systems those exploited BTRFS features). > > > > But there is still one question that I can't get over: if you store > > a > > database (e.g. MySQL), would you prefer having a BTRFS volume > > mounted > > with nodatacow, or would you just simply use ext4? > >Personally, I'd start with btrfs with autodefrag. It has some > degree of I/O overhead, but if the database isn't performance-critical > and already near the limits of the hardware, it's unlikely to make > much difference. Autodefrag should keep the fragmentation down to a > minimum. I read that autodefrag would only help with small databases. I also read that even on SSDs there is a notable performance penalty. 4.2 GiB akonadi database for tons of mails appears to work okayish on dual SSD BTRFS RAID 1 here with LZO compression here. However I have no comparison, for example how it would run on XFS. And its fragmented quite a bit, example for the largest file of 3 GiB – I know this in part is also due to LZO compression. […].local/share/akonadi/db_data/akonadi> time /usr/sbin/filefrag parttable.ibd parttable.ibd: 45380 extents found /usr/sbin/filefrag parttable.ibd 0,00s user 0,86s system 41% cpu 2,054 total However it digs out those extents quite fast. I would not feel comfortable with setting this file to nodatacow. However I wonder: Is this it? Is there nothing that can be improved in BTRFS to handle database and VM files in a better way, without altering any default settings? Is it also an issue on ZFS? ZFS does also copy on write. How does ZFS handle this? Can anything be learned from it? I never head people complain about poor database performance on ZFS, but… I don´t use it and I am not subscribed to any ZFS mailing lists, so they may have similar issues and I just do not know it. Well there seems to be a performance penalty at least when compared to XFS: About ZFS Performance Yves Trudeau, May 15, 2018 https://www.percona.com/blog/2018/05/15/about-zfs-performance/ The article described how you can use NVMe devices as cache to mitigate the performance impact. That would hint that BTRFS with VFS Hot Data Tracking and relocating data to SSD or NVMe devices could be a way to set this up. But as said I read about bad database performance even on SSDs with BTRFS. I do not find the original reference at the moment, but I got this for example, however it is from 2015 (on kernel 4.0 which is a bit old): Friends don't let friends use BTRFS for OLTP 2015/09/16 by Tomas Vondra https://blog.pgaddict.com/posts/friends-dont-let-friends-use-btrfs-for-oltp Interestingly it also compares with ZFS which is doing much better. So maybe there is really something to be learned from ZFS. I did not get clearly whether the benchmark was on an SSD, as Tomas notes the "ssd" mount option, it might have been. Thanks, -- Martin -- To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in the body of a message to majord...@vger.kernel.org More majordomo info at http://vger.kernel.org/majordomo-info.html
Re: BTRFS and databases
On 2018年08月01日 22:33, Remi Gauvin wrote: > On 2018-07-31 11:45 PM, MegaBrutal wrote: > >> I know that with nodatacow, I take away most of the benefits of BTRFS >> (those are actually hurting database performance – the exact CoW >> nature that is elsewhere a blessing, with databases it's a drawback). >> But are there any advantages of still sticking to BTRFS for a database >> albeit CoW is disabled, or should I just return to the old and >> reliable ext4 for those applications? >> > > Be very careful about nodatacow and btrfs 'raid'. BTRFS has no data > synching mechanism for raid, Not completely the case though. Discussed in another thread, for nodatacow/nodatasum case we indeed don't have any anyway to keep data correct. But for RAID1 datacow or metadata case, it should not be case. For tree block, we have generation/first_key/level check done when searching tree blocks. And for scrub, we also have generation check, so in theory we could recovery such problem during scrub. For data, since we have cow (along with csum), it should be no problem to recover. And since datacow is used, transaction on each device should be atomic, thus we should be able to handle one-time device out-of-sync case. (For multiple out-of-sync events, we don't have any good way though). Or did I miss something from previous discussion? Thanks, Qu > so if your mirrors end up different > somehow, your Array is going to be inconsistent. > signature.asc Description: OpenPGP digital signature
Re: BTRFS and databases
On 2018年08月01日 11:45, MegaBrutal wrote: > Hi all, > > I know it's a decade-old question, but I'd like to hear your thoughts > of today. By now, I became a heavy BTRFS user. Almost everywhere I use > BTRFS, except in situations when it is obvious there is no benefit > (e.g. /var/log, /boot). At home, all my desktop, laptop and server > computers are mainly running on BTRFS with only a few file systems on > ext4. I even installed BTRFS in corporate productive systems (in those > cases, the systems were mainly on ext4; but there were some specific > file systems those exploited BTRFS features). > > But there is still one question that I can't get over: if you store a > database (e.g. MySQL), would you prefer having a BTRFS volume mounted > with nodatacow, or would you just simply use ext4? > > I know that with nodatacow, I take away most of the benefits of BTRFS > (those are actually hurting database performance – the exact CoW > nature that is elsewhere a blessing, with databases it's a drawback). > But are there any advantages of still sticking to BTRFS for a database > albeit CoW is disabled, or should I just return to the old and > reliable ext4 for those applications? Since I'm not a expert in database, so I can totally be wrong, but what about completely disabling database write-ahead-log (WAL), and let btrfs' data CoW to handle data consistency completely? If there is some concern about the commit interval, it could be tuned by commit= mount option. It may either lead to super unexpected fast behavior, or some unknown disaster. (And for latter, we at least could get some interesting feedback and bugs to fix) Thanks, Qu > > > Kind regards, > MegaBrutal > -- > To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in > the body of a message to majord...@vger.kernel.org > More majordomo info at http://vger.kernel.org/majordomo-info.html > signature.asc Description: OpenPGP digital signature
Re: BTRFS and databases
On 2018-07-31 11:45 PM, MegaBrutal wrote: > I know that with nodatacow, I take away most of the benefits of BTRFS > (those are actually hurting database performance – the exact CoW > nature that is elsewhere a blessing, with databases it's a drawback). > But are there any advantages of still sticking to BTRFS for a database > albeit CoW is disabled, or should I just return to the old and > reliable ext4 for those applications? > Be very careful about nodatacow and btrfs 'raid'. BTRFS has no data synching mechanism for raid, so if your mirrors end up different somehow, your Array is going to be inconsistent. <>
Re: BTRFS and databases
On 2018-07-31 23:45, MegaBrutal wrote: Hi all, I know it's a decade-old question, but I'd like to hear your thoughts of today. By now, I became a heavy BTRFS user. Almost everywhere I use BTRFS, except in situations when it is obvious there is no benefit (e.g. /var/log, /boot). At home, all my desktop, laptop and server computers are mainly running on BTRFS with only a few file systems on ext4. I even installed BTRFS in corporate productive systems (in those cases, the systems were mainly on ext4; but there were some specific file systems those exploited BTRFS features). But there is still one question that I can't get over: if you store a database (e.g. MySQL), would you prefer having a BTRFS volume mounted with nodatacow, or would you just simply use ext4? I know that with nodatacow, I take away most of the benefits of BTRFS (those are actually hurting database performance – the exact CoW nature that is elsewhere a blessing, with databases it's a drawback). But are there any advantages of still sticking to BTRFS for a database albeit CoW is disabled, or should I just return to the old and reliable ext4 for those applications? You still have snapshotting, which can be useful if you for some reason can't just dump all the tables to SQL for backups (but seriously, that's _really_ what you should be doing to back up your database, it decouples it from whatever back-end storage you're using). You also still have the guaranteed metadata consistency (nodatacow disables COW for data chunks only, COW still happens for metadata chunks), but you can get (close) to that with the newest versions of XFS too. If you want to use BTRFS, I'd suggest playing around with the different on-disk storage formats offered by your RDBMS (MySQL in this case). Some of the offer measurably better performance on BTRFS than others, but it's at least partially dependent on how your software uses that database. -- To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in the body of a message to majord...@vger.kernel.org More majordomo info at http://vger.kernel.org/majordomo-info.html
Re: BTRFS and databases
On Wed, Aug 01, 2018 at 05:45:15AM +0200, MegaBrutal wrote: > But there is still one question that I can't get over: if you store a > database (e.g. MySQL), would you prefer having a BTRFS volume mounted > with nodatacow, or would you just simply use ext4? > > I know that with nodatacow, I take away most of the benefits of BTRFS > (those are actually hurting database performance – the exact CoW > nature that is elsewhere a blessing, with databases it's a drawback). > But are there any advantages of still sticking to BTRFS for a database > albeit CoW is disabled, or should I just return to the old and > reliable ext4 for those applications? Is this database performance-critical? If yes, you'd want ext4 -- nocow is a crappy ext4 lookalike, with no benefits of btrfs. Or, if you snapshot it, you get bad fragmentation yet no checksums/etc. If no, regular cow (especially with autodefrag) will be enough. Sure, this particular load won't be as performant (mysql really loves fsync, which is an anathema to btrfs), but you get all the data safety improvements, frequent cheap backups, and so on. Thus: if the server's primary purpose is that database, you don't want btrfs. If the database is merely incidental, not microoptimizing it will save a lot of your time. In neither case nocow is a good idea. Especially if raid (!= 0) is involved. Meow! -- // If you believe in so-called "intellectual property", please immediately // cease using counterfeit alphabets. Instead, contact the nearest temple // of Amon, whose priests will provide you with scribal services for all // your writing needs, for Reasonable And Non-Discriminatory prices. -- To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in the body of a message to majord...@vger.kernel.org More majordomo info at http://vger.kernel.org/majordomo-info.html
Re: BTRFS and databases
On 1 August 2018 at 04:45, MegaBrutal wrote: > But there is still one question that I can't get over: if you store a > database (e.g. MySQL), would you prefer having a BTRFS volume mounted > with nodatacow, or would you just simply use ext4? > > I know that with nodatacow, I take away most of the benefits of BTRFS > (those are actually hurting database performance – the exact CoW > nature that is elsewhere a blessing, with databases it's a drawback). > But are there any advantages of still sticking to BTRFS for a database > albeit CoW is disabled, or should I just return to the old and > reliable ext4 for those applications? Also note that no data CoW implies no data checksums too. https://btrfs.wiki.kernel.org/index.php/FAQ#Can_I_have_nodatacow_.28or_chattr_.2BC.29_but_still_have_checksumming.3F Mike -- To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in the body of a message to majord...@vger.kernel.org More majordomo info at http://vger.kernel.org/majordomo-info.html
Re: BTRFS and databases
On Wed, Aug 01, 2018 at 05:45:15AM +0200, MegaBrutal wrote: > I know it's a decade-old question, but I'd like to hear your thoughts > of today. By now, I became a heavy BTRFS user. Almost everywhere I use > BTRFS, except in situations when it is obvious there is no benefit > (e.g. /var/log, /boot). At home, all my desktop, laptop and server > computers are mainly running on BTRFS with only a few file systems on > ext4. I even installed BTRFS in corporate productive systems (in those > cases, the systems were mainly on ext4; but there were some specific > file systems those exploited BTRFS features). > > But there is still one question that I can't get over: if you store a > database (e.g. MySQL), would you prefer having a BTRFS volume mounted > with nodatacow, or would you just simply use ext4? Personally, I'd start with btrfs with autodefrag. It has some degree of I/O overhead, but if the database isn't performance-critical and already near the limits of the hardware, it's unlikely to make much difference. Autodefrag should keep the fragmentation down to a minimum. Hugo. > I know that with nodatacow, I take away most of the benefits of BTRFS > (those are actually hurting database performance – the exact CoW > nature that is elsewhere a blessing, with databases it's a drawback). > But are there any advantages of still sticking to BTRFS for a database > albeit CoW is disabled, or should I just return to the old and > reliable ext4 for those applications? > > > Kind regards, > MegaBrutal -- Hugo Mills | In theory, theory and practice are the same. In hugo@... carfax.org.uk | practice, they're different. http://carfax.org.uk/ | PGP: E2AB1DE4 | signature.asc Description: Digital signature
Re: BTRFS and databases
MegaBrutal posted on Wed, 01 Aug 2018 05:45:15 +0200 as excerpted: > But there is still one question that I can't get over: if you store a > database (e.g. MySQL), would you prefer having a BTRFS volume mounted > with nodatacow, or would you just simply use ext4? > > I know that with nodatacow, I take away most of the benefits of BTRFS > (those are actually hurting database performance – the exact CoW nature > that is elsewhere a blessing, with databases it's a drawback). But are > there any advantages of still sticking to BTRFS for a database albeit > CoW is disabled, or should I just return to the old and reliable ext4 > for those applications? Good question, on which I might expect some honest disagreement on the answer. Personally, I tend to hate nocow with a passion, and would thus recommend putting databases and similar write-pattern (VM images...) files on their own dedicated non-btrfs (ext4, etc) if at all reasonable. But that comes from a general split partition-favoring viewpoint, where doing another partition/lvm-volume and putting a different filesystem on it is no big deal, as it's just one more partition/volume to manage of (likely) several. Some distros/companies/installations have policies strongly favoring btrfs for its "storage pool" features, trying to keep things simple and flexible by using just the one solution and one big btrfs and throwing everything onto it, often using btrfs subvolumes where others would use separate partitions/volumes with independent filesystems. For these folks, the flexibility of being able to throw it all on one filesystem with subvolumes overrides the down sides of having to deal with nocow and its conditions, rules and additional risk. And a big part of that flexibility, along with being a feature in its own right, is btrfs built-in multi-device, without having to resort to an additional multi-device layer such as lvm or mdraid. So if you're using btrfs for multi-device or other features that nocow doesn't affect, it's plausible that you'd prefer nocow on btrfs to /having/ to do partitioning/lvm/mdraid and setup that separate non-btrfs just for your database (or vm image) files. But from your post you're perfectly fine with partitioning and the like already, and won't consider it a heavy imposition to deal with a separate non-btrfs, ext4 or whatever, and in that case, at least here, I'd strongly recommend you do just that, avoiding the nocow that I honestly see as a compromise best left to those that really need it because they aren't prepared to deal with the hassle of setting up the separate filesystem along with all that entails. -- Duncan - List replies preferred. No HTML msgs. "Every nonfree program has a lord, a master -- and if you use the program, he is your master." Richard Stallman -- To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in the body of a message to majord...@vger.kernel.org More majordomo info at http://vger.kernel.org/majordomo-info.html
BTRFS and databases
Hi all, I know it's a decade-old question, but I'd like to hear your thoughts of today. By now, I became a heavy BTRFS user. Almost everywhere I use BTRFS, except in situations when it is obvious there is no benefit (e.g. /var/log, /boot). At home, all my desktop, laptop and server computers are mainly running on BTRFS with only a few file systems on ext4. I even installed BTRFS in corporate productive systems (in those cases, the systems were mainly on ext4; but there were some specific file systems those exploited BTRFS features). But there is still one question that I can't get over: if you store a database (e.g. MySQL), would you prefer having a BTRFS volume mounted with nodatacow, or would you just simply use ext4? I know that with nodatacow, I take away most of the benefits of BTRFS (those are actually hurting database performance – the exact CoW nature that is elsewhere a blessing, with databases it's a drawback). But are there any advantages of still sticking to BTRFS for a database albeit CoW is disabled, or should I just return to the old and reliable ext4 for those applications? Kind regards, MegaBrutal -- To unsubscribe from this list: send the line "unsubscribe linux-btrfs" in the body of a message to majord...@vger.kernel.org More majordomo info at http://vger.kernel.org/majordomo-info.html