DaemonForums  

Go Back   DaemonForums > Miscellaneous > General software and network

General software and network General OS-independent software and network questions, X11, MTA, routing, etc.

Reply
 
Thread Tools Display Modes
  #1   (View Single Post)  
Old 25th February 2014
J65nko J65nko is offline
Administrator
 
Join Date: May 2008
Location: Budel - the Netherlands
Posts: 3,251
Thanked 182 Times in 149 Posts
Default ZFS and Database fragmentation

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?
__________________
You don't need to be a genius to debug a pf.conf firewall ruleset, you just need the guts to run tcpdump
Reply With Quote
  #2   (View Single Post)  
Old 26th February 2014
Oko's Avatar
Oko Oko is offline
Fsck Surgeon
 
Join Date: May 2008
Location: Kosovo, Serbia
Posts: 850
Thanked 36 Times in 32 Posts
Default

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.
Reply With Quote
  #3   (View Single Post)  
Old 26th February 2014
vermaden's Avatar
vermaden vermaden is offline
Administrator
 
Join Date: Apr 2008
Location: pl_PL.lodz
Posts: 1,052
Thanked 118 Times in 93 Posts
Default

@J65nko

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

Typical write is:
Code:
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:
Code:
 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
Code:
#! /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.
__________________
religions, worst damnation of mankind
"If 386BSD had been available when I started on Linux, Linux would probably never had happened." Linus Torvalds

Linux is not UNIX! Face it! It is not an insult. It is fact: GNU is a recursive acronym for “GNU's Not UNIX”.
vermaden's: links resources deviantart spreadbsd
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
When is a database not so relational? J65nko News 1 23rd May 2011 05:38 AM
Xtracting Data after Fragmentation / Block Count / Partition Problems on Boot IronForge OpenBSD Installation and Upgrading 3 16th December 2010 01:09 AM
Huge FTP transfer file fragmentation problem under Windows eakinasila Other OS 2 13th December 2009 02:50 PM
PHP database interfaces TerryP Programming 6 11th September 2008 01:03 PM
Slocate database coverage ivanatora FreeBSD General 2 4th August 2008 08:51 AM


All times are GMT. The time now is 08:57 PM.


Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content copyright © 2007-2010, the authors
Daemon image copyright ©1988, Marshall Kirk McKusick