Re: Very slow filesystem

[Russell Coker <russell@coker.com.au>]

On Fri, 6 Jun 2014 14:06:53 Mitch Harder wrote:
> Every time you update your database, btrfs is going to update
> whichever 128 KiB blocks need to be modified.
> 
> Even for a tiny modification, the new compressed block may be slightly
> more or slightly less than 128 KiB.
> 
> If you have a 1-2 GB database that is being updated with any
> frequency, you can see how you will quickly end up with lots of
> metadata fragmentation as well as inefficient data block utilization.
> I think this will be the case even if you switch to NOCOW due to the
> compression.
> 
> On a very fundamental level, file system compression and large
> databases are two use cases that are difficult to reconcile.

The ZFS approach of using a ZIL (write-back cache that caches before 
allocation) and L2ARC (read-cache on SSD) mitigates these problems.  Samsung 
1TB SSDs are $565 at my local computer store, if your database has a working 
set of less than 2TB then SSDs with L2ARC should solve those performance 
problems at low cost.  The vast majority of sysadmins have never seen a 
database that's 2TB in size, let alone one with a 2TB working set.

That said I've seen Oracle docs recommending against ZFS for large databases, 
but the Oracle definition of "large database" is probably a lot larger than 
anything that is likely to be stored on BTRFS in the near future.

Another thing to note is that there are a variety of ways of storing 
compressed data in databases.  Presumably anyone who is storing so much data 
that the working set exceeds the ability to attach lots of SSDs is going to be 
using some form of compressed tables which will reduce the ability of 
filesystem compression to do any good.

On Fri, 6 Jun 2014 19:59:55 Duncan wrote:
> Similarly for checksumming.  When there are enough updates, in addition 
> to taking more time to calculate and write, checksumming simply invites 
> race conditions between the last then-valid checksum and the next update 
> invalidating it.  In addition, in many, perhaps most cases, the sorts of 
> apps that do constant internal updates, have already evolved their own 
> data integrity verification methods in ordered to cope with issues on the 
> after all way more common unverified filesystems, creating even more 
> possible race conditions and timing issues and making all that extra work 
> that btrfs normally does for verification unnecessary.  Trying to do all 
> that in-place due to NOCOW is a recipe for failure or insanity if not both

http://www.strchr.com/crc32_popcnt

The above URL has some interesting information about CRC32 speed.  In summary 
if you have a Core i5 system then you are looking at less than a clock cycle 
per byte on average.  So if your storage is capable of handling more than 
4GB/s of data transfer then CRC32 might be a bottleneck.  But doing 4GB/s for 
a database is a very different problem.

-- 
My Main Blog         http://etbe.coker.com.au/
My Documents Blog    http://doc.coker.com.au/