ZFS and Database fragmentation

[J65nko]

Because of this post I did a little bit of research and found this interesting article discussing fragmentation on ZFS: ZFS and Database fragmentation

I haven't used ZFS for databases yet, but according to that article it seems to be a real problem. Has anyone here run into this problem?

[Oko]

I recently introduced ZFS in our lab via FreeNAS 9.2.1 on two large file servers. For the most part we are not dealing with databases but I will definitely keep an eye on it. As of the ZF and Database fragmentation I pretty much lost interest to read article at the point when guy selected i386 and PAE enabled kernel to use with ZFS and 6GB or RAM. My 7 knows that when we use ZFS we need amd64.

[vermaden]

@J65nko

Its not a database problem, its ZFS problem for all data.

Typical write is:

POOL: WRITE METADATA1
POOL: WRITE DATA1
POOL: ERASE METADATA1
POOL: WRITE METADATA2
POOL: WRITE DATA2
POOL: ERASE METADATA2

As You can imagine there are blocks of free space after METADATA has been erased, which beautifully leads to fragmentation. This can be limited by adding a ZIL device, even on a single disk, then it would look like that:

 ZIL: WRITE METADATA1
POOL: WRITE DATA1
 ZIL: ERASE METADATA1
 ZIL: WRITE METADATA2
POOL: WRITE DATA2
 ZIL: ERASE METADATA2

So there would be no 'holes' ... but casual fragmentation is as usual.

You can check current ZFS fragmentation level with this script:

zfs-fragmentation.sh

#! /bin/sh

if [ ${#} -ne 1 ]
then
  echo "usage: $( basename ${0} ) POOL|DATASET"
  exit 1
fi

zdb -ddddd ${1} | awk --non-decimal-data \
'
/Indirect blocks/ {
  file_number++;
  next_block = 0;
}

/L0/ {
  split($3, fields, ":");
  this_block = ("0x"fields[2])+0;
  this_block_size = ("0x"fields[3])+0;
  total_blocks++;
  if( next_block != 0 ) {
    if( next_block == this_block ) {
      not_fragmented++;
    }
    else {
      fragmented++;
    }
  }
  next_block =  this_block + this_block_size;
}

END {
  total_fragment_blocks = fragmented + not_fragmented;
  printf("There are %d files.\n", file_number );
  printf("There are %d blocks and %d fragment blocks.\n", total_blocks, total_fragment_blocks );
  printf("There are %d fragmented blocks (%2.2f%%).\n", fragmented, fragmented*100.0/total_fragment_blocks );
  printf("There are %d contiguous blocks (%2.2f%%).\n", not_fragmented, not_fragmented*100.0/total_fragment_blocks );
}
'

The REAL solution to ZFS fragmentation is 'Block Pointer Rewrite', which will allow defragmentation and other great features [1] but it did not happened yet.

[1] Rebuild 4-way RAID5 into 5-way RAID5 or remove disks from pool.